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 odbc32.inc and odbc32.lib. Also you need to include windows.inc.

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

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

Disconnect from the data source.
Destroy the connection handle.
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, 
                                    InputHandle:DWORD, 
                                    OutputHandlePtr: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.

HandleType is a constant that defines what type of handle you want to allocate. The possible values are:

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

InputHandle is the handle of the parent "context". That is, if you want to allocate a connection handle, you need to pass an environment handle because the connection will be made in the context of that environment. If you want to allocate an environment handle, this parameter must be SQL_HANDLE_NULL (beware of the value of SQL_HANDLE_NULL in windows.inc version 1.18 and below. It is defined improperly as 0L. You need to delete the "L" else your program will not assemble. The fault is mine alone since I'm the one who updated the SQL/ODBC part of windows.inc.) because there is no parent context for an environment. As for the statement and descriptor handles, you must pass the connection handle as this parameter as both statement and descriptor occur in the context of a connection
OutputHandlePtr points to a dword variable that will receive the allocated handle if the call is successful.

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.

Example:

.data?
hEnv dd ?

.code
invoke SQLAllocHandle, SQL_HANDLE_ENV, SQL_HANDLE_NULL, addr hEnv
.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO

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,
                                   Attribute:DWORD,
                                   ValuePtr:DWORD,
                                   StringLength:DWORD

EnvironmentHandle. As the name speaks for itself, it contains the handle to the environment which attribute you want to set.
Attribute. A constant that represents the attribute you want to set. For our purpose, it's SQL_ATTR_ODBC_VERSION. You can look up the full list from MSDN.
ValuePtr. The meaning of this parameter depends on the attribute you want to set. If the attribute is a 32-bit value, this parameter is treated as the value you want to set. If the attribute is a text string or a binary buffer, it is interpreted as the pointer to the string or the buffer. If you specify SQL_ATTR_ODBC_VERSION, there are two possible values that you can use: SQL_OV_ODBC3 and SQL_OV_ODBC2, for ODBC version 3.x and 2.x respectively.
StringLength. The size of the value pointed to by ValuePtr. If the value is a string or a binary buffer, this parameter must be valid. If the attribute you want to set is a dword, this parameter is ignored. Since SQL_ATTR_ODBC_VERSION attribute contains a dword value, you can pass NULL as this parameter.

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

Example:

.data?
hEnv dd ?

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.

Example:

.data?
hEnv dd ?
hConn dd ?

.code
     invoke SQLAllocHandle, SQL_HANDLE_ENV, SQL_HANDLE_NULL, addr hEnv
     .if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
         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
                              pDSN:DWORD,
                              DSNLength:DWORD,
                              pUserName:DWORD,
                              NameLength:DWORD,
                              pPassword:DWORD,
                              PasswordLength:DWORD

ConnectionHandle. The handle to the connection you want to use.
pDSN. Pointer to the DSN string.
DSNLength. The length of the DSN string
pUserName. Pointer to the user name string
NameLength. The length of the user name string
pPassword. Pointer to the password associated with the user name
PasswordLength. The length of the password

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:

.data
DSN db "Sales",0

.code
......
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,
                                        hWnd:DWORD,
                                        pInConnectString:DWORD,
                                        InStringLength:DWORD,
                                        pOutConnectString:DWORD,
                                        OutBufferSize:DWORD,
                                        pOutConnectStringLength:DWORD,
                                        DriverCompletion:DWORD

ConnectionHandle The handle to the connection
hWnd The handle to your window. If you pass NULL as this parameter, the driver will not prompt the user with a dialog box for more information (if needed).
pInConnectString The pointer to the connection string. This is an ASCIIZ string that follows the format described by the specific ODBC driver you want to connect to. It describes the name of the driver and the data source and additional attributes. The full description of connection string is available in MSDN. I won't go into detail here.
InStringLength The length of the connect string.
pOutConnectString The pointer to the buffer that will be filled with the complete connection string. The size of this buffer should be at least 1,024 bytes. This may sound confusing. The connection string you passed to the function may not be complete. In that case, the ODBC driver may prompt the user for more information. The ODBC driver then constructs the complete connection string from all available information and puts it in the buffer. Even if the connection string you supplied was functional, this buffer will be filled with more attributes. The purpose of this parameter is so you can save the complete connection string for future connection.
OutBufferSize The size of the buffer pointed to by pOutConnectString.
pOutConnectStringLength The pointer to a dword variable that will receive the actual length of the complete connection string returned by the ODBC driver.

DriverCompletion A flag that specifies whether the ODBC manager/driver will prompt the user for more information. However, the flag depends on whether you pass a window handle to hWnd parameter of SQLDriverConnect. If you didn't, the ODBC manager/driver won't prompt the user even if this flag instructs it to.

SQL_DRIVER_PROMPT	The ODBC driver prompts the user for information. It uses the information to construct the connection string.
SQL_DRIVER_COMPLETE SQL_DRIVER_COMPLETE_REQUIRED	The ODBC driver will prompt the user only if the connection string supplied by your program is not complete.
SQL_DRIVER_NOPROMPT	The ODBC driver won't prompt the user for more information.

Example:

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

.data?
buffer db 1024 dup(?)
OutStringLength dd ?

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

HandleType is a constant that identifies the type of handle you passed to this function as the second parameter. The possible values are the same as those used in SQLAllocHandle
Handle is the handle you want to destroy.

For example:

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

[Iczelion's Win32 Assembly Homepage]