A Simple ODBC Client
In this section, Ill cover the bare minimum ODBC calls required for fetching data from any ODBC data source. Ill also discuss the three handle types or levels involved in communication between an application and an ODBC driver/driver manager. Its important to be familiar with the ODBC administrator and to know the header files, libraries, and DLLs required for client and driver development.
Example 1.
// ----------- allocate ENVIRONMENT status = SQLAllocHandle ( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv ); // ----------- set the ODBC version for behavior expected------- this is compulsory status = SQLSetEnvAttr ( hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC2, 0 ); // ----------- allocate CONNECTION status = SQLAllocHandle ( SQL_HANDLE_DBC, hEnv, &hConn ); #ifdef _CONNECT_WITH_PROMPT // ----------- actual connection takes place at this point // ----------- option 1: user is prompted for DSN & options status = SQLDriverConnect ( hConn, GetDesktopWindow(), ( unsigned char* )"", SQL_NTS, szConnStrOut, 1024, &x, SQL_DRIVER_COMPLETE ); #else // ----------- OR // ----------- actual connection takes place at this point // ----------- option 2: all connection params for SQL server driver specified status = SQLDriverConnect ( hConn, GetDesktopWindow(), ( unsigned char* )"DRIVER={SQL Server};SERVER=MYSERVER;<BR> DATABASE=pubs;UID=sa;PWD=sa", SQL_NTS, szConnStrOut, 1024, &x, SQL_DRIVER_COMPLETE ); #endif // ----------- CONGRATUALTIONS ---- u r connected to a DBMS via an ODBC driver // ----------- allocate STATEMENT status = SQLAllocHandle ( SQL_HANDLE_STMT, hConn, &hStmt ); // ----------- execute the statement specified on command line status = SQLExecDirect ( hStmt, ( unsigned char* )argv[1], SQL_NTS ); // ----------- RESULTS READY // ----------- get the title of first col ( first 64 bytes only ) status = SQLColAttribute ( hStmt, 1, SQL_DESC_NAME, szColTitle, sizeof(szColTitle), &x, NULL ); // ----------- bind buffer for obtaining column data ( first 255 bytes only ) status = SQLBindCol ( hStmt, 1, SQL_C_TCHAR, (SQLPOINTER)szColData, <BR> sizeof(szColData), &i ); // ----------- fetch a row i.e. the first row status = SQLFetch ( hStmt ); // ----------- show the title and value of the first col, first row printf ( "Row:1, Col:1 Title: %s, Value: %s", szColTitle, szColData );
Example 1 shows the minimum nine calls required to connect, execute a query, specify a buffer for results, and get the title and value of the first column of the first row. Error checking has been omitted for brevity. We discuss error handling as expected from an ODBC driver in the diagnostics section. A more complete client is available as a download CLIENT.CPP. Although the client you get with the download shows you all the rows and columns obtained on executing the query, it still uses these nine calls only, so you can start with these, but clients like MS Query and MS SQL Server DTS expect more from your driver.
The ODBC driver provides SQLAllocHandle and SQLFreeHandle to allocate and free all three types of handles environment, connection, and statement. The three handles can be described as follows:
- The environment handle encapsulates one or more connections along with global information like version for expected ODBC behavior from the driver and also the environment level diagnostics.
- The connection handle represents a connection to the DBMS/data source along with connection specific information like connection timeout, transaction isolation, etc. and the connection level diagnostics.
- The statement handle can be thought of as a single query like SELECT * FROM employee. As we can see from Example 1, this is the handle used to execute the query SQLExecDirect, get information about the result SQLColAttribute, specify buffers in which the driver can feed the results SQLBindCol, and finally fetch the rows SQLFetch. All three are internally represented as C structures in our sample driver GODBC.H.
- SQLDriverConnect is the API that actually helps you choose a driver, specify the connection parameters and make a connection. Before making a connection, two things are required a driver and the connection parameters for that driver to connect to the DBMS or data source. I emphasize this because as you work with ODBC, you will find a number of ways in which these are collected. The Driver Manager, Driver, DSN, ODBC Administrator, and user prompts all combine to achieve this simple goal. From the perspective of a driver writer, you will get the connection parameters for your DBMS or you will need to prompt the user for it.
There are other connection APIs like SQLConnect and SQLBrowseConnect but SQLDriverConnect has the following advantages over SQLConnect:
- To let the application use driver-specific connection information
- To request that the driver prompt the user for connection information
- To connect without specifying a data source
In Example 1, you can see that I have given you two possible ways the SQLDriverConnect API can be called. In the first case, we specify an empty string as connection parameters. The driver manager helps us choose a driver in the form of a DSN as shown in Figure 2.
The DSN is a collection of settings for making an ODBC connection. The most practical being the File DSN, a simple text file which allows you to specify the driver and parameters in the form of key-value pairs. For example:
DRIVER=SQL Server SERVER=MYSERVER DATABASE= UID=sa
These are concatenated by Driver Manager and sent to your driver. The second option is to specify the driver and connection parameters yourself, as I have done for my SQL server "DRIVER={SQL Server};SERVER=MYSERVER;DATABASE=pubs;UID=sa;PWD=sa". You need to parse these key-value pairs in your driver, and if anything is missing or something is required you need to prompt the user for it; password being the most relevant example. The download has a file GO_CONN.CPP dedicated to this API.
Figure 2 shows the ODBC administrator, as invoked by the Driver Manager when you call SQLDriverConnect with empty strings as in-connection parameters. You will need to walk through the tabs to get an idea about ODBC on your system. The different types of DSNs are basically different ways to store or scope the connection settings, for example restrict it to a user (UserDSN) or machine (SystemDSN). The File DSN is portable and the most usable. The drivers tab as we will see is going to show our driver the General ODBC driver 0 along with other drivers installed on the system. The tracing option is discussed in the troubleshooting section. The About tab is important, dont skip that, it tells you the files, which constitute your driver manager, ODBC administrator, and other core components of ODBC on the MS platform. As you will see, odbc32.dll and odbccp32.dll are the most relevant ones for us now.
The client includes the SQL.H and SQLEXT.H header files. SQL.H contains the ODBC function prototypes along with definition for constants used to get basic information about the driver at the three levels. This file in turn includes the SQLTYPES.H, which as the name indicates contains definitions for data types used in ODBC like SQLHANDLE, SQLRETURN, etc. SQLEXT.H defines more constants required for working with ODBC and is an extension to SQL.H as the name indicates. Also required is the ODBC32.LIB. This is the import library for the driver manager. You are going to use the same header files when you develop your driver. Note that the driver does not require these LIBs or DLLs to be compiled, but a client typically uses the driver in conjunction with the Driver Manager (odbc32.dll) and ODBC administrator (odbccp32.dll).
Your First Driver and Its Recognition
I divide the driver development into two stages. First, develop a skeleton driver. This is to know the basic structure, compilation process, installation process, and registry settings and also to test communication between client, driver manager, and ODBC administrator. Then I discuss how the functions are implemented to make it a real-life driver. The download has folders ODBCDRV0 and ODBCDRV1 representing this division.
I will use two files to compile my first ODBC driver, ODBCDRV0.C and ODBCDRV0.DEF, and then register this compiled driver DLL (ODBCDRV0.DLL) as General ODBC Driver. Finally, I create a FileDSN and test connectivity with ODBC Administrator. ODBCDRV0.C contains all the ODBC functions with bare-minimum body with the exception of DllMain and SQLDriverConnect (shown in Example 2).
All the other functions contain a call to OutputDebugString that can be tracked through DBMON.EXE. You can use MessageBox if you like instead of OutputDebugString. These are dummy implementations just like SQLExecDirect and SQLAllocHandle shown in Example 2.
DllMain shows a message when the application or driver manager loads or unloads our driver DLL (shown in Figure 3).
SQLDriverConnect copies the in-connection string to out-connection string with due respect to their lengths so that the caller does not reject the driver as invalid. The DEF file is a standard way of exporting functions from a DLL and is a simple text file used by the linker. Example 3 shows how a DEF file looks.
Example 3.
Note that you are not restricted to using any files I have previously mentioned. Their purpose is to make things easy for you. You can write both the files on your own and use Example 2 if you like. The problem is picking up each function prototype from SQL.H file and implementing it by putting something like MessageBox or OutputDebugString into it. Writing DEF is a trivial job; you merely need to list the function names and put a few compulsory lines like the LIBRARY statement on the top.
After you compile the DLL, the next step is to register it so that the system and clients recognize it. I suggest you copy the DLL to the \TEMP folder of your drive to keep the registered driver separate from the one being updated and compiled. You can register the driver using either the API call SQLInstallDriverEx or you can manually change the registry; its not very difficult. I will explain both.
Start REGEDIT.EXE. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers and create a new String Value with Name General ODBC Driver 0 and Data Installed. Figure 3 shows REGEDIT working. This adds your driver to the list of installed drivers on your system. Now navigate up one level to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI and create a new key under it. Name it General ODBC Driver 0. Under this key create the three Name-Data pairs shown in Table 1, which basically tell the system the name and location of your driver DLL.
Table 1.
You are now ready to test a connection to your driver. The system has the following info about our driver:
Defining some more values in the registry like ConnectFunction and APILevel can further refine the configuration, but its your choice. In most cases, the default should be good enough. Let us now have a look at SQLInstallDriverEx.
SQLInstallDriverEx adds information about the driver to the Odbcinst.ini entry in the system information and increments the drivers UsageCount by one. This function does not actually copy any files. You can do it yourself or through a setup program. The first two parameters to the function are important. The first parameter is made up of key value pairs separated by a zero char and the last pair is terminated with an extra zero char. The format can be defined as follows:
and example for our driver is:
The second parameter is the path to our driver DLL, which is C:\TEMP, where the drive letter is changed as per your system. I have provided an ODBCREG.CPP file as an example for using this API. The corresponding API for removal is SQL RemoveDriver. The LIB required for this API is ODBCCP32.LIB and the corresponding header file is ODBCINST.H.
ODBC files are basically divided into two categories which is also how your driver should be arranged. One set of files, like ODBCCP32.LIB and ODBCINST.H, are for installation and other administration issues. The other, like the Driver Manager ODBC32.LIB and SQL.H, participate in the core functionality. I mention this since I have created a String-Value called SETUP during our installation using REGEDIT. The two functions ConfigDriver and ConfigDSN make the Setup DLL, which can be used to configure your driver and associated DSNs. You can implement it separately or within the same DLL. The setup DLL and its functions are accessed via ODBCCP32.DLL just like your core driver functions are accessed via ODBC32.DLL. I show the relationship in Figure 4. To start, we can rely on the native Installer DLL ODBCCP32.LIB for all our needs.
To see if everything worked properly, start the ODBC Administrator (odbcad32.exe) from the Control Panel and go to the Drivers Tab. You will find the General ODBC Driver 0 listed there. Now proceed to make the first connection. Go to the FileDSN TAB and choose Add. You will get a list of drivers to choose from; choose our Driver and click Next. Specify any name for this FileDSN you are creating; this will be the name of the file in which the settings will be stored. GDSN0 is a good choice. You are notified that a FileDSN is about to be created for your driver. Click finish. You should get the message box, which is written in DllMain with the message ODBCDRV0 loaded by application/Driver Manager. Congratulations! Immediately following this the ODBC Administrator calls the SQLDriverConnect API which should give you another message box telling you Connection dialog would go here. We have covered our first major milestone. Now let us see how the function calls are encoded and sent to the database server and response from the server sent back to the client.
////// DLL entry point for global initializations/finalizations if any
BOOL WINAPI DllMain ( HINSTANCE hinstDLL, DWORD fdwReason, LPVOID lpvReserved )
{
if ( fdwReason == DLL_PROCESS_ATTACH ) { // DLL is being loaded
MessageBox ( GetDesktopWindow(),
"ODBCDRV0 loaded by application or driver manager",
"Congrats !!! ", MB_OK);
// any initialization global to DLL goes here
}
else if ( fdwReason == DLL_PROCESS_DETACH ) { // DLL is being unloaded
// any finalization global to DLL goes here
MessageBox ( GetDesktopWindow(), "ODBCDRV0 is being unloaded",
"Congrats !!! ", MB_OK);
}
return TRUE;
}
///// SQLDriverConnect
RETCODE SQL_API SQLDriverConnect (
HDBC hDBC, HWND hWnd,
UCHAR* szConnStrIn, SWORD cbConnStrIn,
UCHAR* szConnStrOut, SWORD cbConnStrOut,
SWORD* pcbConnStrOut, UWORD uwMode )
{
OutputDebugString ( "SQLDriverConnect called\n" ); // for DBMON
if ( cbConnStrIn == SQL_NTS && szConnStrIn ) // get in-string length
cbConnStrIn = strlen ( szConnStrIn );
MessageBox ( hWnd, "Connection dialog would go here",
"Sample driver", MB_OK );
// copy in-conn string to out string
if( szConnStrOut && cbConnStrOut > 0 ) {
strncpy ( szConnStrOut, szConnStrIn,
( cbConnStrIn == SQL_NTS ) ? cbConnStrOut - 1 :
min( cbConnStrOut, cbConnStrIn ));
szConnStrOut[cbConnStrOut - 1] = '\0';
}
if ( pcbConnStrOut ) // set length of out string also
pcbConnStrOut = cbConnStrIn;
return SQL_SUCCESS;
}
///// SQLExecDirect /////
RETCODE SQL_API SQLExecDirect (
HSTMT hStmt,
UCHAR* sStmtText,
SDWORD iStmtLen )
{
OutputDebugString ( "SQLExecDirect called\n" );
return SQL_SUCCESS;
}
///// SQLAllocHandle /////
RETCODE SQL_API SQLAllocHandle (
SQLSMALLINT HandleType,
SQLHANDLE HandleParent,
SQLHANDLE* NewHandlePointer )
{
OutputDebugString ( "SQLAllocHandle called\n" );
return SQL_SUCCESS;
}
LIBRARY ODBCDRV0
DESCRIPTION "General ODBC Driver 0"
EXPORTS
SQLAllocConnect
SQLAllocEnv
SQLAllocStmt
.
.
.
all function to be exported
driver-desc\0
Driver=driver-DLL-filename\0
[Setup=setup-DLL-filename\0]
[driver-attr-keyword1=value1\0]
[driver-attr-keyword2=value2\0]...\0
General ODBC Driver\0
Driver=ODBCDRV0.DLL\0
Setup=ODBCDRV0.DLL\0\0