Connecting to Data Source

In this tutorial, we learn the mechanics of using ODBC APIs.

Your program doesn't talk directly to the ODBC drivers. It talks to the ODBC manager. The ODBC manager defines a set of APIs your program can call to direct it to do the job for you. In your program, you need to include and odbc32.lib. Also you need to include

The steps in connecting to the data source are as follows:

  1. Allocate an environment handle. You need to do this only once per ODBC session. Once you obtain the handle, you can modify the environment properties to suit your particular needs. You can think of this step as creating the workspace for your DB job.
  2. Indicate what version of ODBC your program wants to use. You can choose between ODBC version 2.x and 3.x. They are different in many respects thus this step is necessary so the ODBC manager can decide which syntax it should use to communicate with your program and interpret the commands from your program.
  3. Allocate a connection handle. This step can be viewed as creating an empty connection. You haven't specify what driver you want to use and which database you need to connect. Such information will be filled in later.
  4. Establish a connection. You call an ODBC function to establish the connection.

When you are done with the connection, you must close and destroy it in the following steps:

  1. Disconnect from the data source.
  2. Destroy the connection handle.
  3. Destroy the environment handle (if you don't want to use this environment for more connections)

Allocating a Handle

In ODBC versions prior to 3.x, you need to call separate functions for allocating environment, connection, and statement handles (SQLAllocEnv, SQLAllocConnect, SQLAllocStmt). Now under ODBC 3.x, those functions are superseded by SQLAllocHandle which has the following syntax:

SQLRETURN SQLAllocHandle( SQLSMALLINT HandleType,      
                                              SQLHANDLE InputHandle, 
                                              SQLHANDLE * OutputHandlePtr

The above line may look daunting. I will simplify it for you.

SQLAllocHandle proto HandleType:DWORD, 

SQLRETURN is defined as type SQLSMALLINT. And SQLSMALLINT is defined as a short integer, ie. a word (16 bits). So the function returns the value in ax, not eax. This is important. However, parameter passing to a function under Win32 is done via the 32-bit stack. Thus even if the parameter is defined as a word-size one, you must extend it to 32-bit. That's why HandleType is a dword instead of a word. You can check with the import lib, odbc32.lib. The entry for SQLAllocHandle is _SQLAllocHandle@12. Which means the combined size of parameters for this function is 12 bytes (3 dwords). However, this doesn't mean that the C function prototype is incorrect. SQLAllocHandle will only use the low word of HandleType and ignore the high word. Thus the C function prototype is functionally correct while our asm function prototype reflects practicality.

With SQL type discussion out of the way, we can turn our attention to the function parameters and the return value.

SQL_HANDLE_ENV Environment handle
SQL_HANDLE_DBC Connection handle
SQL_HANDLE_STMT Statement handle
SQL_HANDLE_DESC Descriptor handle

A descriptor is a collection of metadata that describes the parameters of an SQL statement or the columns of a result set, as seen by the application or driver

The possible return values of SQLAllocHandle can be:

SQL_SUCCESS The function completed successfully.
SQL_SUCCESS_WITH_INFO The function completed successfully but with possible non-fatal errors (warnings).
SQL_ERROR The function failed.
SQL_INVALID_HANDLE The handle passed to the function is invalid.

Whether the function succeeded or failed, you can obtain more information about it by calling SQLGetDiagRec or SQLGetDiagField. They serve the same role as GetLastError in Win32 API.


hEnv dd ?

     invoke SQLAllocHandle, SQL_HANDLE_ENV, SQL_HANDLE_NULL, addr hEnv

Choosing ODBC version

After allocating an environment handle, you need to set an environment attribute, SQL_ATTR_ODBC_VERSION, to the appropriate value. "Attributes" are just variables. Setting the value of an environment attribute is done by calling SQLSetEnvAttr. By now you should be able to guess that there are also SQLSetConnectAttr and SQLSetStmtAttr. SQLSetEnvAttr is defined as:

SQLSetEnvAttr proto EnvironmentHandle:DWORD,
                                   ValuePtr:DWORD,                                    StringLength:DWORD

The list of possible return values is identical to that of SQLAllocHandle.


hEnv dd ?

     invoke SQLAllocHandle, SQL_HANDLE_ENV, SQL_HANDLE_NULL, addr hEnv
         invoke SQLSetEnvAttr, hEnv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, NULL
         .if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO

Allocating a connection handle

This step is quite similar to allocating the environment handle. You also call SQLAllocHandle but pass to it different parameter values.


hEnv dd ?
hConn dd ?

     invoke SQLAllocHandle, SQL_HANDLE_ENV, SQL_HANDLE_NULL, addr hEnv
         invoke SQLSetEnvAttr, hEnv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, NULL
         .if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
             invoke SQLAllocHandle, SQL_HANDLE_DBC, hEnv, addr hConn
                 .if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO

Establish a connection

We are now ready to attempt the actual connection to the data source via selected ODBC driver. There are actually three ODBC functions we can use to achieve that goal. They offer varying degrees of "choices" you can make.

SQLConnect Core This is the simplest function. It needs only the DSN (Data source name) and optional user name and password. It doesn't offer any GUI options such as prompting the user with a dialog box for more information. You should use this function if you already have a DSN for the required database.
SQLDriverConnect Core This function offers more support than SQLConnect. You can connect to a data source that is not defined in the system information, ie. without DSN. Furthermore, you can specify whether this function will display a dialog box prompting the user for more information. For example, if you omitted the filename of the database, it will instruct the ODBC driver to display a dialog box prompting the user to select the database to connect.
SQLBrowseConnect Level 1 This function offers data source enumeration at runtime. It provides more flexibility than SQLDriverConnect because you can call SQLBrowseConnect several times in succession, each time prompting the user for more specific information until finally you obtain the working connection string.

I'll examine SQLConnect first. In order to use SQLConnect, you need to know about DSN. DSN stands for Data Source Name, a string that uniquely identifies a data source. A DSN identifies a data structure that contains info on how to connect to a specific data source. The info includes what ODBC driver to use and which database to connect to. You create, modify and delete DSNs using 32-bit ODBC Administrator in the control panel.

SQLConnect has the following syntax:

SQLConnect proto ConnectionHandle:DWORD

At the mininum, SQLConnect requires the connection handle, DSN and its length: user name and password are optional if the data source doesn't require them. The list of possible return values is identical to that of SQLAllocHandle.

Assuming you have a DSN named "Sales" in your system and you want to connect to it. You can do it like this:

DSN db "Sales",0

          invoke SQLConnect, hConn, addr DSN, sizeof DSN,0,0,0,0

One disadvantage of SQLConnect is that, you have to create a DSN before you can connect to the data source. SQLDriverConnect offers more flexibility. It has the following syntax:

SQLDriverConnect proto ConnectionHandle:DWORD,
                                        pOutConnectStringLength:DWORD,                                         DriverCompletion:DWORD


strConnect db "DBQ=c:\data\test.mdb;DRIVER={Microsoft Access Driver (*.mdb)};",0

buffer db 1024 dup(?)
OutStringLength dd ?

     invoke SQLDriverConnect, hConn, hWnd, addr strConnect, sizeof strConnect, addr buffer, sizeof buffer, addr OutBufferLength, SQL_DRIVER_COMPLETE

Disconnecting from the data source

After the connection is made successfully, you can construct one or more statements and query the data source. I'll examine that part in the next tutorial. For now, let's assume you're done with the data source, you need to disconnect from it by calling SQLDisconnect. This function is quite simple (Reflecting the harsh and sad reality that destruction is much easier than construction). It takes only one parameter, the connection handle.

invoke SQLDisconnect, hConn

Freeing the connection and environment handles

After the successful disconnect, you can now destroy the connection and environment handles by calling SQLFreeHandle. This is a new function introduced in ODBC 3.x. It supersedes SQLFreeConnect, SQLFreeEnv and SQLFreeStmt. SQLFreeHandle has the following syntax:

SQLFreeHandle proto HandleType:DWORD, Handle:DWORD

For example:

invoke SQLFreeHandle, SQL_HANDLE_DBC, hConn
invoke SQLFreeHandle, SQL_HANDLE_ENV, hEnv

[Iczelion's Win32 Assembly Homepage]