Function Categories
In this section, I provide an overview of the major function categories as required in an ODBC driver. Broadly, the functions can be categorized into: Connection functions — SQLConnect, SQLDriverConnect, SQLBrowseConnect, and SQLAllocHandle. I have discussed SQLDriverConnect at reasonable length in the section “A Simple ODBC Client”. Functions to query the functionality supported by the driver — SQLGetInfo, SQLGetFunctions, and SQLTypeInfo. SQLGetInfo is a very important function and returns information about the driver and data source associated with a connection. You will need to implement this function in the very beginning in order for clients like MS Word and Excel to work. These clients query quite a few things about your driver before they can extract data using it as a pipe to your DBMS. The function is not very difficult to implement but it becomes a long switch-case statement. Also you have to pick up all possible query attributes from the header files and return an appropriate value either from within the driver or query your DBMS using our XML-SOAP-socket call mechanism. I have a file GO_INFO.CPP dedicated to this API call. If you use it, you just have to replace the sample values with the correct values for your driver.
SQLGetFunctions returns information about whether a driver supports a specific ODBC function. This function is implemented in the Driver Manager; it can also be implemented in drivers. If a driver implements SQLGetFunctions, the Driver Manager calls the function in the driver. Otherwise, it executes the function itself.
SQLTypeInfo returns information about the data types supported by your driver. This function will be implemented as a call to your server, which will return a resultset with the content as described by the ODBC standard. It is a little confusing when you implement this function since you will be describing the data types in the RESULTDESC, and the data types will come as part of RESULTDATA. Typically the data types describe the data but in this particular API, they are being described. I have provided a possible resultset in the download sample for the dummy server.
Descriptor Functions — SQLGetDescField, SQLGetDescRow, SQLSetDescField, and SQLSetDescRow. I have discussed descriptors in the section “Descriptors — an important design issue”.
Functions to set and retrieve driver attributes — SQLGetEnvAttr, SQLSetEnvAttr, SQLGetConnAttr, SQLSetConnAttr, SQLGetStmtAttr, and SQLSetStmtattr. As the names imply, these are used to get and set properties at the environment, connection, and statement level.
Query submission and execution functions — A query can be executed in a single step using SQLExecDirect or in two steps using SQLPrepare and SQLExecute. The second method is useful when the query is to be executed multiple times maybe with different parameters. The DBMS should be capable of processing and storing it in a form, where it can avoid duplicating some part of the execution process and therefore yield better performance. Queries can be parameterized. I clump these parameter related functions like SQLDescribeParam and SQLParamData also into this category.
Functions for obtaining metadata about result or datasource — SQLTables, SQLColumns, SQLProcedures, SQLSpecialColumns, SQLPrecuresColumns, SQLForeignKeys, and SQLPrimaryKeys. These are also known as catalog functions. They help you build a catalog describing the server and database content. Most of the names are self-explanatory. For example, SQLTables is used to collect the tables in a specified database. This function also allows you to collect the names of the databases and users on your server. Similarly, SQLColumns can be used to get the column details of a particular table and SQLProcedure can be used to get the names of the procedures available for execution. You will also need to implement few of these before clients like MS Word and Excel are able to use your driver. I have provided the samples for the one, which are required to make these clients work successfully.
Functions for obtaining result-data — Once the driver collects the result from the server, the application can use a number of functions to extract it. Say it can use SQLNumResultCols to get the number of columns in the result. The three important functions for this category are SQLColAttribute, SQLBindCol and SQLFetch. SQLColAttribute is used to get the information about a column in the result; we have used this to get the title in our sample client. SQLBindCol is used to specify the column and buffer associations in which the driver will feed the results. SQLFetch is used to fetch one or more rows into the buffers specified by SQLBindCol.
Diagnostic functions — ODBC has a well-defined way of managing diagnostics. As mentioned earlier, a list of messages is to be maintained by the driver at all levels — Environment, Connection, and Statement. The client uses the two functions SQLGetDiagRec and SQLGetDiagField to extract diagnostic information when the function does not return SQL_SUCCESS. It may not necessarily be an error; the driver can return SQL_SUCCESS_WTH_INFO, which means that there are information messages. For example, if truncation has taken place during fetch, the driver will return this as state: 01004 and message: String data, right, truncated. The fields maintained as part of diagnostics include a state code, a message, native error code for your driver, column number, row number, and so on. You will need to have a linked list type structure to maintain the diagnostics messages at every level. The header file GODBC.H shows a possible structure for diagnostics and GO_DIAG.CPP contains the implementation of these functions as well as functions used internally by the driver to maintain these.
Clean-up functions — SQLDisconnect and SQLFreeHandle. SQLDisconnect is used to close a connection available via a connection handle. SQLFreeHandle is used to release any of the handles allocated using SQLAllocHandle.
http://msdn.microsoft.com/library/en-us/odbc/htm/odbcodbc_function_summary.asp on MSDN is a good place to start if you are looking for the categorized list of ODBC functions and a brief description.
Troubleshooting, Test Tools, Samples, and Resources
Tracing — Each call to the ODBC driver manager can be logged along with the parameters and return values. I found this option to be most useful while troubleshooting my driver with clients like MS Word and Excel. There are a number of ways to enable/disable tracing. You can use the Tracing tab on the ODBC administrator from Control Panel and use the button Start Tracing Now. You can also specify the file to which the calls are logged. When the client fails with something like Memory access violation, you can check this log file and precisely locate the call in which the failure occurred or after which the failure occurred. SQLSetConnectAttr to can be used with SQL_ATTR_TRACE to enable of disable tracing from within your program.
DBMON — This an executable program provided on the windows platform as part of Platform SDK. It allows you to track your program using the OutputDebugString Windows API. The DBMON program runs as a separate process in a console window and does not show any output by itself. The string you output using OutputDebugString from within your program or DLL is immediately visible on the DBMON window. This is especially useful in debugging DLLs. The skeleton driver we developed uses this API for tracking calls to the ODBC functions within our driver.
Your own log — I strongly suggest that you create your own log mechanism in your driver. Entry to every call is logged along with the parameter details. I suggest this in addition to the Driver Manager log since there are situations where the driver manager does some intermediate processing or changes the call name to due to version issues or even calls a number of functions from your driver on a single call from the client. Besides in cases of abnormal termination, you also need to know whether the failure occurred on an instruction within your driver code, driver manager code, or the application code. I found this very useful in tracking the errors much faster. There was also a situation where the driver manager was failing to call my function although I had exported it. The driver manager showed a log that indicated that it was calling my function but there was a prototype and calling convention mismatch and it was failing to do so.
Test clients — Microsoft also provides test clients like ODBCTE32.EXE to test your driver. You can select the calls and parameter values for each call and see what your driver is returning or executing. Once you are comfortable with test clients like these, you can switch over to Microsoft Query, which is the tool used by most of the other Office components like Word and Excel to get data using your driver. MSQRY32.Exe comes as a part of MS Office.
DLL and LIBs — You must make sure that calling convention for your functions is set correctly to stdcall. This is important or else name mangling and stack-cleanup issues will cause the driver manager or client to fail in calling functions from your driver.
Diagnostic functions — If the function fails with an error, the driver manager is supposed to return the details of the errors via the diagnostic functions. Ideally your driver should also maintain and return complete details of the error as discussed in the previous section.
I have covered the major concepts with code snippets about how you can convert our skeleton driver into a real life driver. The complete source code is available as download, and you can use the associated readme.txt file to test it out along with the dummy server. Once you are clear with the concepts, I believe it’s an issue of coding it all in a week or two. Also I hope that this article will save you from many trial and error situations I faced while developing my driver.
Microsoft Office or Star Office
Your clients can use MS Word directly to mail merge forms and letters picking up data from the server just by using point and click. They can now prepare a balance sheet as per their format or statutory guidelines using the formatting capabilities of Excel and raw data from your server. The possibilities aren’t limited to Microsoft products. You can access your data in Star Office as well.
The option in MS Excel 2000 is on the Tools menu - Get External Data - New Database Query and from then on, it’s a normal ODBC query. Similarly, the Mail Merge option is available on the Tools menu in MS Word 2000. You can even do ADO programming and extract data using VB or VBA.
Conclusion
One of the requirements for ODBC was that a single application binary had to work with multiple DBMSs. It is for this reason that ODBC does not use embedded SQL or module languages, each of which is tied to DBMS-specific precompilers. Thus, applications must be recompiled for each DBMS and the resulting binaries work only with a single DBMS. It is a logistical nightmare to deliver multiple versions of high-volume, shrink-wrapped software to customers. Also, personal computer applications often need to access multiple DBMSs simultaneously.
On the other hand, a call-level interface can be implemented through libraries, or database drivers, that reside on each local machine; a different driver is required for each DBMS. Because modern operating systems can load such libraries at run time, a single application can access data from different DBMSs without recompilation and can also access data from multiple databases simultaneously. As new database drivers become available, users can just install these on their computers without having to modify, recompile, or relink their database applications.
The architecture we have used for IPC/RPC makes your database easily accessible across a WAN also. You need not have to go through the extensive plumbing and sophisticated configuration and compatibility issues required for COM or CORBA.
You are open to the world without much change to your internal architecture. The system for which I developed an ODBC driver is not a typical RDBMS system and is highly optimized for our kind and scale of application, but we were able to expose it in the form of an RDBMS by adding a thin layer without much difficulty.
With this article, I hope that somebody trying to develop an ODBC driver will not be restricted to only two options — the very fat ODBC DDK from Microsoft or an expensive consultancy from companies specializing in this area.
Vikash K Agarwal is working as Technology Architect for Tally Solutions, Bangalore. He’s worked for over 10 years in software development with C as the prime focus. Vikash has also worked in and with VC++, Delphi, VB, COM, PHP, ASP, MYSQL, SQL Server, Lex and Yacc, Sockets, XML, and PKI. When not making software work, he ponders on what makes the universe work. He can be contacted at [email protected].
