Stage 1: The application allocates environment and connection handles in the driver manager

ODBC-enabled applications communicate with the rest of the ODBC system by making function calls. The first step in the process is allocating an environment handle and a connection handle. Two invocations of the function call SQLAllocHandle do the job. The driver manager allocates space in memory for the requested handles and returns the handles to the application. The first invocation of SQLAllocHandle initializes the ODBC interface, in addition to allocating memory for global information. If the first SQLAllocHandle function executes successfully, execution can proceed to the second SQLAllocHandle function. This particular invocation of SQLAllocHandle allocates memory for a connection handle and its associated connection information. SQLAllocHandle takes the active environment handle as input and returns a pointer to the newly allocated connection handle as an output. Depending on which development tool they’re using, application programmers may or may not have to explicitly allocate environment and connection handles.

Stage 2: The driver manager finds the appropriate driver

After environment and connection handles have established a link between the application and the driver manager, the next step in the process is linking the driver manager to the appropriate driver. Two functions are available for accomplishing this task: SQLConnect and SQLDriverConnect. SQLConnect is the simpler of the two functions, requiring only the connection handle, data source name, user identifier, and user password as input parameters. When the establishment of a connection requires more information than SQLConnect provides, SQLDriverConnect is used. It passes a connection string to the driver attached to the data source.

Stage 3: The driver manager loads the driver

In a Windows system, after the connection between the driver manager and the driver has been established, the driver manager obtains a library handle for the driver; then it calls the Windows function GetProcAddress for each function in the driver. The function addresses are stored in an array associated with the connection handle.

Stage 4: The driver manager allocates environment and connection handles in the driver

Now that the driver has been loaded, environment and connection handles can be called in it. The function SQLAllocHandle can be used for this purpose, as it was used to call the environment and connection handles in the driver manager. If the application uses the function SQLSetConnectOption to set options for the connection, the driver manager calls the driver’s SQLSetConnectOption function to enable those options to be set.

Stage 5: The driver manager connects to the data source through the driver

At last, the driver manager completes the connection to the data source by calling SQLConnect or SQLDriverConnect. If the driver is a one-tier driver, there’s no network connection to make, so this stage is trivial. If the driver is multitier, it calls the network interface software in the client machine, which connects to the server machine that holds the data source. To make this connection, the driver uses information that was stored in the ODBC.INI file when the data source name was created.

After the connection is established in a client/server system, the driver usually sends the username and password to the server for validation. If the username and password are valid, the driver returns a standard SQL_SUCCESS code to the driver manager. If they’re not valid, the server returns an error code to the driver. Then the driver translates this error code to the standard ODBC error code and returns it to the driver manager as SQLSTATE, and the driver manager returns SQLSTATE to the application.

Stage 6: The data source (finally) executes an SQL statement

With the connection at last established, an SQL statement can be executed. Even this process, however, requires multiple stages. First, a statement handle must be allocated. The application does this by issuing an SQLAllocHandle call. When the driver manager receives this call, it allocates a statement handle and then sends an SQLAllocHandle call to the driver. Then the driver allocates its own statement handle before returning control to the driver manager, which returns control to the application.

After the statement handle has been allocated, an SQL statement can be executed. There is more than one way to do this, but the simplest is the SQLExecDirect function. SQLExecDirect takes a character string as input and sends it to the server. The character string should be a valid SQL statement. If necessary, the driver translates the statement from ODBC-standard SQL to commands understood by the data source on the server. When the data source receives the request for action, it processes the command and then returns any results to the application via the driver and driver manager. The exact details of this processing and how the results are returned to the client application may differ from one data source to another. These differences are masked by the driver so that the application always sees standard ODBC responses, regardless of what data source it is communicating with.

Following is a fragment of C code showing the allocation of environment, connection, and statement handles and connection to a data source:

SQLRETURN cliRC = SQL_SUCCESS;

int rc = 0;

SQLHANDLE henv; /* environment handle */

SQLHANDLE hdbc; /* connection handle */

SQLHANDLE hstmt; /* statement handle */

char dbAlias[SQL_MAX_DSN_LENGTH + 1];

char user[MAX_UID_LENGTH + 1];

char pswd[MAX_PWD_LENGTH + 1];

/* check the command line arguments */

rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd);

if (rc != 0)

{

return 1;

}

/* allocate an environment handle */

cliRC = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

if (cliRC != SQL_SUCCESS)

{

printf(" --ERROR while allocating the environment handle. ");

printf(" cliRC = %d ", cliRC);

printf(" line = %d ", __LINE__);

printf(" file = %s ", __FILE__);

return 1;

}

/* set attribute to enable application to run as ODBC 3.0 application */

cliRC = SQLSetEnvAttr(henv,

SQL_ATTR_ODBC_VERSION,

(void *)SQL_OV_ODBC3,

0);

ENV_HANDLE_CHECK(henv, cliRC);

/* allocate a database connection handle */

cliRC = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

ENV_HANDLE_CHECK(henv, cliRC);

/* connect to the database */

cliRC = SQLConnect(hdbc,

(SQLCHAR *)dbAlias, SQL_NTS,

(SQLCHAR *)user, SQL_NTS,

(SQLCHAR *)pswd, SQL_NTS);

DBC_HANDLE_CHECK(hdbc, cliRC);

/* allocate one or more statement handles */

cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

DBC_HANDLE_CHECK(hdbc, cliRC);