Creating a custom ODBC driver

Question

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, or using an SDK that allows for programmers to 'fill in' the data specific parts and allow higher levels of abstraction.

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?

Any comments and answers would be greatly appreciated. Thanks!

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

Answer 1

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.

There are at least two options:

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

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

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.

Answer 2

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.

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.

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.

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.

Answer 3

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.

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