Dr. Dobb's is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.


Channels ▼
RSS

.NET

ODBC Driver Development


A Simple ODBC Client

In this section, I’ll cover the bare minimum ODBC calls required for fetching data from any ODBC data source. I’ll also discuss the three handle types or levels involved in communication between an application and an ODBC driver/driver manager. It’s 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.

Figure 2: DSN selection using ODBC administrator.

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, don’t 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).


//////  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;
}

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).

Figure 3: Driver registration and first connection.

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.

LIBRARY   ODBCDRV0
DESCRIPTION "General ODBC Driver 0"
EXPORTS
            SQLAllocConnect
            SQLAllocEnv
            SQLAllocStmt
            .
            .
            .
            all function to be exported

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.

Table 1: Three Name-Data pairs describing the name and location of your driver.

You are now ready to test a connection to your driver. The system has the following info about our driver:

  • It knows where to find the DLL for the driver
  • It knows that the same DLL also implements configuration functions and therefore acts as the Setup DLL for the driver
  • The number of times the driver has been installed is one.

Defining some more values in the registry like ConnectFunction and APILevel can further refine the configuration, but it’s 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 driver’s 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:

driver-desc\0
    Driver=driver-DLL-filename\0
    [Setup=setup-DLL-filename\0]
    [driver-attr-keyword1=value1\0]
    [driver-attr-keyword2=value2\0]...\0

and example for our driver is:

General ODBC Driver\0
    Driver=ODBCDRV0.DLL\0
    Setup=ODBCDRV0.DLL\0\0

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.

Figure 4: Installer DLL and driver Setup DLL relationship.

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.


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.