SQL 创建自定义 ODBC 驱动程序

Creating a custom ODBC driver

提问by Nicholas Mancuso

At my current job, we're looking to implement our own odbc driver to allow many different applications to be able to connect to our own app as a datasource. Right now we are trying to weigh the options of developing our own driver to the implementation spec, which is massive, orusing an SDK that allows for programmers to 'fill in' the data specific parts and allow higher levels of abstraction.

在我目前的工作中，我们希望实现我们自己的 odbc 驱动程序，以允许许多不同的应用程序能够连接到我们自己的应用程序作为数据源。现在，我们正在尝试权衡开发我们自己的驱动程序的选项，这是庞大的实现规范，还是使用允许程序员“填充”数据特定部分并允许更高级别抽象的 SDK。

Has anyone else implemented a custom odbc driver? What pitfalls did you run into? What benefits did you see from doing it yourself? How many manhours would you approximate it took? Did you use an SDK, and if so, what benefits/downsides did you see from that approach?

有没有其他人实现了自定义 odbc 驱动程序？你遇到了哪些陷阱？你自己做有什么好处？你估计需要多少工时？您是否使用了 SDK，如果使用，您从该方法中看到了哪些好处/坏处？

Any comments and answers would be greatly appreciated. Thanks!

任何评论和答案将不胜感激。谢谢！

EDIT:We are trying to maintain portability with our code, which is written in C.

编辑：我们正试图用我们的代码来保持可移植性，它是用 C 编写的。

采纳答案by ConcernedOfTunbridgeWells

I have not, but I once interviewed at a company that had done exactly this. They made a 4GL/DBMS product called AMPS of the same sort of architecture as MUMPS - a hierarchical database with integrated 4GL (a whole genre of such systems came out during the 1970s). They had quite a substantial legacy code base and customers wishing to connect to it using MS Access.

我没有，但我曾经在一家做过这件事的公司面试过。他们制作了一个名为 AMPS 的 4GL/DBMS 产品，其架构与 MUMPS 相同——一个集成了 4GL 的分层数据库（此类系统的整个类型在 1970 年代出现）。他们有相当多的遗留代码库，客户希望使用 MS Access 连接到它。

The lead developer who interviewed me shared some war stories about this. Apparently it is exceedingly painful to do and shouldn't be taken lightly. However, they did actually succeed in implemnenting it.

采访我的首席开发人员分享了一些关于此的War故事。显然，这样做非常痛苦，不应掉以轻心。然而，他们确实成功地实现了它。

One alternative to doing this would be to provide a data mart/BI product (along the lines of SAP BW) that presents your application data in an external database and massages it into a more friendly format such as a star or snowflake schema.

这样做的一种替代方法是提供一个数据集市/BI 产品（沿着 SAP BW 的产品线），它将您的应用程序数据呈现在外部数据库中，并将其转换为更友好的格式，例如星形或雪花模式。

This would suffer from not supporting real-time access, but might be considerably easier to implement (and more importantly maintain) than an ODBC driver. If your real-time access requirements are reasonably predicitable and limited, you could possibly expose a web service API to support those.

这会受到不支持实时访问的影响，但可能比 ODBC 驱动程序更容易实现（更重要的是维护）。如果您的实时访问需求是可合理预测和有限的，您可能会公开 Web 服务 API 来支持这些需求。

回答by codeape

Another option: Instead of creating a ODBC driver, implement a back end that talks the wire protocol that another database (Postgresql or MySQL for instance) uses.

另一种选择：不是创建 ODBC 驱动程序，而是实现一个后端，该后端与另一个数据库（例如 Postgresql 或 MySQL）使用的有线协议进行对话。

Your users can then download and use for instance the Postgresql ODBC driver.

然后您的用户可以下载并使用例如 Postgresql ODBC 驱动程序。

Exactly what back-end database you choose to emulate should probably depend the most on how well the wire protocol format is documented.

您选择模拟的后端数据库究竟应该取决于有线协议格式的记录情况。

Both Postgresand MySQLhas decent documentation for their client-server protocols.

双方的Postgres和MySQL的拥有自己的客户端-服务器协议体面的文件。

A simple Python 2.7 example of a server backend that understands parts of the Postgresql wire protocol is below. The example script creates a server that listens to port 9876. I can use the command psql -h localhost -p 9876to connect to the server. Any query executed will return a result set with columns abc and def and two rows, all values NULL.

下面是理解部分 Postgresql 有线协议的服务器后端的简单 Python 2.7 示例。示例脚本创建了一个侦听端口 9876psql -h localhost -p 9876的服务器。我可以使用该命令连接到该服务器。执行的任何查询都将返回一个结果集，其中包含 abc 和 def 列以及两行，所有值均为 NULL。

Reading the Postgresql docs and using something like wireshark to inspect real protocol traffic would make it pretty simple to implement a Postgresql-compatible back end.

阅读 Postgresql 文档并使用诸如 wireshark 之类的东西来检查真实的协议流量将使实现与 Postgresql 兼容的后端变得非常简单。

import SocketServer
import struct

def char_to_hex(char):
    retval = hex(ord(char))
    if len(retval) == 4:
        return retval[-2:]
    else:
        assert len(retval) == 3
        return "0" + retval[-1]

def str_to_hex(inputstr):
    return " ".join(char_to_hex(char) for char in inputstr)

class Handler(SocketServer.BaseRequestHandler):
    def handle(self):
        print "handle()"
        self.read_SSLRequest()
        self.send_to_socket("N")

        self.read_StartupMessage()
        self.send_AuthenticationClearText()
        self.read_PasswordMessage()
        self.send_AuthenticationOK()
        self.send_ReadyForQuery()
        self.read_Query()
        self.send_queryresult()

    def send_queryresult(self):
        fieldnames = ['abc', 'def']
        HEADERFORMAT = "!cih"
        fields = ''.join(self.fieldname_msg(name) for name in fieldnames)
        rdheader = struct.pack(HEADERFORMAT, 'T', struct.calcsize(HEADERFORMAT) - 1 + len(fields), len(fieldnames))
        self.send_to_socket(rdheader + fields)

        rows = [[1, 2], [3, 4]]
        DRHEADER = "!cih"
        for row in rows:
            dr_data = struct.pack("!ii", -1, -1)
            dr_header = struct.pack(DRHEADER, 'D', struct.calcsize(DRHEADER) - 1 + len(dr_data), 2)
            self.send_to_socket(dr_header + dr_data)

        self.send_CommandComplete()
        self.send_ReadyForQuery()

    def send_CommandComplete(self):
        HFMT = "!ci"
        msg = "SELECT 2\x00"
        self.send_to_socket(struct.pack(HFMT, "C", struct.calcsize(HFMT) - 1 + len(msg)) + msg)

    def fieldname_msg(self, name):
        tableid = 0
        columnid = 0
        datatypeid = 23
        datatypesize = 4
        typemodifier = -1
        format_code = 0 # 0=text 1=binary
        return name + "\x00" + struct.pack("!ihihih", tableid, columnid, datatypeid, datatypesize, typemodifier, format_code)

    def read_socket(self):
        print "Trying recv..."
        data = self.request.recv(1024)
        print "Received {} bytes: {}".format(len(data), repr(data))
        print "Hex: {}".format(str_to_hex(data))
        return data

    def send_to_socket(self, data):
        print "Sending {} bytes: {}".format(len(data), repr(data))
        print "Hex: {}".format(str_to_hex(data))
        return self.request.sendall(data)

    def read_Query(self):
        data = self.read_socket()
        msgident, msglen = struct.unpack("!ci", data[0:5])
        assert msgident == "Q"
        print data[5:]


    def send_ReadyForQuery(self):
        self.send_to_socket(struct.pack("!cic", 'Z', 5, 'I'))

    def read_PasswordMessage(self):
        data = self.read_socket()
        b, msglen = struct.unpack("!ci", data[0:5])
        assert b == "p"
        print "Password: {}".format(data[5:])


    def read_SSLRequest(self):
        data = self.read_socket()
        msglen, sslcode = struct.unpack("!ii", data)
        assert msglen == 8
        assert sslcode == 80877103

    def read_StartupMessage(self):
        data = self.read_socket()
        msglen, protoversion = struct.unpack("!ii", data[0:8])
        print "msglen: {}, protoversion: {}".format(msglen, protoversion)
        assert msglen == len(data)
        parameters_string = data[8:]
        print parameters_string.split('\x00')

    def send_AuthenticationOK(self):
        self.send_to_socket(struct.pack("!cii", 'R', 8, 0))

    def send_AuthenticationClearText(self):
        self.send_to_socket(struct.pack("!cii", 'R', 8, 3))

if __name__ == "__main__":
    server = SocketServer.TCPServer(("localhost", 9876), Handler)
    try:
        server.serve_forever()
    except:
        server.shutdown()

Example command line psql session:

示例命令行 psql 会话：

[~]
$ psql -h localhost -p 9876
Password:
psql (9.1.6, server 0.0.0)
WARNING: psql version 9.1, server version 0.0.
         Some psql features might not work.
Type "help" for help.

codeape=> Select;
 abc | def
-----+-----
     |
     |
(2 rows)

codeape=>

An ODBC driver that speaks the Postgresql protocol should work as well (but I have not tried it yet).

使用 Postgresql 协议的 ODBC 驱动程序也应该可以工作（但我还没有尝试过）。

回答by Einstein

ODBC drivers are very complex - the decision to write one should not be taken lightly. Reviewing existing open source drivers are a good approach for examples but most have shortcommings you may not want to emulate :) APIs are the same regardless of OS platform. FreeTDS for MSSQL/Sybase has one of the better open source ODBC Driver implementations I've seen.

ODBC 驱动程序非常复杂 - 不应掉以轻心地决定编写一个驱动程序。查看现有的开源驱动程序是一个很好的示例方法，但大多数都有您可能不想效仿的缺点:) 无论操作系统平台如何，API 都是相同的。用于 MSSQL/Sybase 的 FreeTDS 具有我见过的更好的开源 ODBC 驱动程序实现之一。

If you control the application you can get away with implementing what may be just a very small subset of the spec in a reasonable amount of time. To use in a general purpose environment can require quite a bit more effort to get right. Off the top of my head in addition to simply implementing dozens of wrapper calls you will also have to implement:

如果您控制应用程序，您可以在合理的时间内实现可能只是规范的一个非常小的子集。在通用环境中使用可能需要更多的努力才能正确使用。除了简单地实现数十个包装器调用之外，您还必须实现：

• Metadata access functions
• ODBC specific query syntax parsing
• SQLSTATE Error message mappings
• Multibyte/Character set marshalling
• ODBC version 2,3 support - error messages/function mappings
• Cursors
• DM configuration UI for managing the datasource

• 元数据访问功能
• ODBC 特定查询语法解析
• SQLSTATE 错误消息映射
• 多字节/字符集编组
• ODBC 版本 2,3 支持 - 错误消息/函数映射
• 光标
• 用于管理数据源的 DM 配置 UI

回答by Bill Karwin

I have not implemented an ODBC driver, but just wanted to offer a suggestion that you can start with an open-source implementation and add your own customizations. This may get you started a lot faster.

我还没有实现 ODBC 驱动程序，只是想提供一个建议，您可以从开源实现开始并添加您自己的自定义。这可能会让你更快地开始。

There are at least two options:

至少有两种选择：

• unixODBCis licensed under LGPL, which means if you modify the code you have to make your modifications open-source.

• iODBCis licensed under either LGPL or New BSD, at your choice. New BSD allows you to make modifications withoutmake your modifications open-source.

• unixODBC在 LGPL 下获得许可，这意味着如果您修改代码，您必须将修改开源。

• iODBC根据您的选择在 LGPL 或新 BSD 下获得许可。新的 BSD 允许您进行修改，而无需将修改开源。

However, it's not clear if these packages run on Windows, as opposed to running on UNIX/Linux with a client API consistent with standard ODBC. You don't state which platform you're using, so I don't know if this is relevant to you.

但是，尚不清楚这些包是否在 Windows 上运行，而不是在具有与标准 ODBC 一致的客户端 API 的 UNIX/Linux 上运行。您没有说明您使用的是哪个平台，所以我不知道这与您是否相关。

回答by KylePorter

This post is now a bit old, but worth mentioning that if you need to have an ODBC driver, you can use an SDK like this: http://www.simba.com/drivers/simba-engine-sdk/It takes care of most of the points raised in the other answers and gives you a much simplified interface to implement.

这篇文章现在有点旧了，但值得一提的是，如果你需要一个 ODBC 驱动程序，你可以使用这样的 SDK：http: //www.simba.com/drivers/simba-engine-sdk/它会照顾其他答案中提出的大多数要点，并为您提供了一个非常简单的界面来实现。

I happen to work for Simba, so I'm a bit biased, but using an SDK does make it fairly easy to create an ODBC driver for whatever you're trying to do. You can get something going in 5 days if you're somewhat proficient at coding.

我碰巧为 Simba 工作，所以我有点偏见，但是使用 SDK 确实可以很容易地为您尝试做的任何事情创建 ODBC 驱动程序。如果你有点精通编码，你可以在 5 天内完成一些事情。

One of the other posts recommends unixODBC or iODBC as starting points, however this will not work. It's important to realize the distinction between a driver manager (unixODBC, iODBC, etc) and a driver. The Driver Manager acts as the middle-man between the application and the driver, removing the need to link directly to a driver.

其他帖子之一建议将 unixODBC 或 iODBC 作为起点，但这行不通。意识到驱动程序管理器（unixODBC、iODBC 等）和驱动程序之间的区别很重要。驱动程序管理器充当应用程序和驱动程序之间的中间人，无需直接链接到驱动程序。

You could start with the Postgres or MySQL drivers as a starting point and fork them to use your own database, however this is unlikely to be a trivial task. Creating a driver from scratch is even more difficult and will likely have ongoing (and higher than expected) maintenance costs. As long as you're aware of the costs of this approach, it can be viable as well.

您可以从 Postgres 或 MySQL 驱动程序作为起点开始，并将它们分叉以使用您自己的数据库，但这不太可能是一项微不足道的任务。从头开始创建驱动程序更加困难，并且可能会持续（并且高于预期）维护成本。只要您了解这种方法的成本，它也是可行的。