Developing ODBC Driver

Well to say I have been developing ODBC drivers for a while. Thought would write down things to understand before we write(implement) a driver. Not much help on web, so here we go…

We are on this blog, simply means we already know what is an ODBC Driver. If don’t there is a lot of material available. Also, I assume we know few frequently used ODBC APIs – SQLAllocHandle, SQLConnect, etc.

Writing a driver is not that difficult, one of the reasons being existence of Driver Manager(DM, detailed later in the blog). It does most of the heavy lifting – basic error handling, maintaining handle states (keeps track of APIs called on a particular handle), connecting ODBC applications to ODBC Driver and most important finding the driver!

Finding the driver?? DM stands between ODBC App and driver. It is a generic piece of code that can load and run DBMS-specific driver code. So how does DM know which driver to pick up? When we create DSN, we link it to our installed ODBC driver. While calling SQLConnect API, we provide this DSN – and DM knows its us! All the APIs called before SQLConnect are kept in queue by DM – how would it know which driver’s APIs to call when application hasn’t provided any identification of the driver yet (conn string or DSN name saves us)!! Call SQLConnect(dsnname,.., username,.. passwrd,…), and DM knows which Driver to load.

Driver Manager:

For linux I would suggest go ahead and install unixODBC DM. Windows has a Driver Manager pre-installed and on the date I am writing this blog – windows DM is the most updated DM as per latest ODBC specifications.

All about the handles:

Environment, Connection, Statement and Descriptor are the ones to say! This part is confusing, what are these handles and how do I represent them internally, is there any particular specification laid down by ODBC for these handles? MSDN only reveals: a particular handle should contain diagnostics, state, attributes, etc.

Here is the answer – go ahead and create a struct if writing in C or a class if in C++ or I would say any data structure available for you to use and put some elements you want in it, e.g. for connection handle – host, port, user, password – i wouldn’t store this, timeout, a diagnostic data structure(this one has a fixed elements defined by ODBC spec – MSDN will help you out)… whatever you think you would need ahead in the ODBC flow. In my experience – this data structure continuously evolves with each new API you implement! Internals of these data structures are never exposed to DM or ODBC Application – but yes pointers to these structures are.

When ODBC application calls SQLAllocHandle API – DM’s API is called first that internally allocates a structure (oh yes, DM maintains its own handle structures as well). Then DM calls Driver’s API and driver internally allocates it’s own structure. In a nutshell, ODBC application points to DM’s handle which internally contains a pointer pointing to Driver’s handle. Now, as handle memory areas are exposed to application – there is a chance they be corrupted! Hell, I told you DM is the savior – keeps checksums or some other strategy!! I would say worry about this if your bypassing THE DM!

Implementing APIs:

MSDN has been a great help. It tells you what a particular API should do. And there are a lot of things which are DBMS dependent. E.g. – SQLConnect would internally try to connect to the DBMS server engine you have. Depends on the design, to give an open-source example – Apache Hive ODBC Driver internally uses thrift client to connect to thrift server embedded in Hive server.

Installation and adding DSN:

This depends on the platform. odbcinst.ini and odbc.ini play important part here.

On linux systems, unixODBC documentation can be used to locate the path of these files.

Windows has a GUI based installation, copy DLL to program files and then make entries into system registry(ODBC components and Data sources). Implement ConfigDSN API to add, configure and delete DSN. For editing odncinst.ini – SQLWriteDSNtoIni or SQLRemoveDSNFromIni APIs can be used. For odbc.ini – SQLWritePrivateProfileString, SQLGetPrivateProfileString APIs help.

I hope this helps. If I’ve used a lot of parentheses or exclamations! – can’t help it!