[ACCEPTED]-Creating a custom ODBC driver-odbc-bridge

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

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

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

Both 16 Postgres and MySQL has decent documentation for their 15 client-server protocols.

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

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

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 3 command line psql session:

[~]
$ 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 2 that speaks the Postgresql protocol should 1 work as well (but I have not tried it yet).

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

If you control the application you 8 can get away with implementing what may 7 be just a very small subset of the spec 6 in a reasonable amount of time. To use in 5 a general purpose environment can require 4 quite a bit more effort to get right. Off 3 the top of my head in addition to simply 2 implementing dozens of wrapper calls you 1 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

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

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

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

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

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

There are at 11 least two options:

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

• iODBC is 8 licensed under either LGPL or New BSD, at 7 your choice. New BSD allows you to make 6 modifications without make your modifications open-source.

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

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

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

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

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