- 51

- ODBC Applications

- Components of ODBC
- Configuring an ODBC Data Source
- Connecting to an ODBC Data Source using the ODBC API
- Setting Connection Options
- Applying SQL Transactions
- Retrieving Result Sets
- Handling Errors
- Calling Stored Procedures and Functions
- Disconnecting and Freeing Resources
- Debugging ODBC Applications
- Limitations of ODBC
- Summary

#
51

## ODBC Applications

ODBC (Open Database Connectivity) is an industry standard programming interface that enables applications to access a variety of database management systems, residing on many different platforms. ODBC provides a large degree of database independence through a standard SQL syntax, which can be translated by database-specific drivers to the native SQL of the DBMS.

Database independence and ease of use are the primary advantages to using ODBC. It is supported by many popular development tools, including Visual Basic, PowerBuilder, Delphi, and SQLWindows. These tools and numerous others provide their own interfaces to ODBC, making ODBC easier to use by insulating the developer from many of the complexities of the ODBC API.

### Components of ODBC

ODBC software is made up of several distinct components. The application layer contains embedded SQL and logic for data entry, preparing transactions, and displaying result sets. It calls API functions exported by the driver manager to connect to the data source, apply SQL, and retrieve results and error codes. The driver manager provides the common ODBC interface, loads database-specific drivers as requested by the application, performs call-level validations, and maps ODBC calls to functions exported by the database-specific driver. The database-specific driver processes the ODBC function calls, optionally converting SQL and data types to the native syntax of the DBMS, and formats DBMS error codes into a standard format. It also returns result sets and error codes to the driver manager. The data source consists of the DBMS itself, in addition to any network or operating system software required to connect to it. Figure 51.1 illustrates these layers.

Figure 51.1. This is a visual representation of the components of ODBC.

For local desktop databases, the data source might simply consist of the name of local server or database file. When the DBMS resides on a remote server, however, the data source includes any network software required to access the remote host. For example, if you attempt to access Oracle on a remote server, SQL*Net must be installed and properly configured. Although this software is not actually part of ODBC, it is considered part of the data source because it is required by ODBC to connect to the database.

### Configuring an ODBC Data Source

The process of configuring an ODBC data source is simply a matter of providing some information to the driver manager and the DBMS-specific driver. The driver manager uses entries in ODBC.INI to determine what driver to load for a particular data source name. The specific driver may use ODBC.INI to determine the server name and the values of any database-specific parameters required to connect.

The ODBC administration program ships with nearly all Windows development tools that support ODBC and is typically installed as part of the Windows Control Panel applet. This program, ODBCADM.EXE, and a DLL, ODBCINST.DLL, are used to install specific drivers and configure data sources. The following instructions on how to use the driver manager are based on version 1.02 of ODBCADM.EXE. This application may vary slightly from version to version, but the functions provided are essentially the same.

Always use the ODBC administration program to install drivers and configure data sources. ODBC.INI and ODBCINST.INI should not be edited manually, unless it becomes absolutely necessary because of corruption or other extreme problems. If this situation arises, the files should be backed up prior to editing.

When the administration application is started, a list of defined data sources is presented, as shown in Figure 51.2.

Figure 51.2. The Data Sources dialog displays a list of defined data sources.

The Close and Help buttons should be self-explanatory, and the Options button will be discussed later, in the section on debugging. Add is used to define a new data source for one of the installed drivers. Delete is used to delete an existing data source, but does not delete the driver. The configuration of the selected data source can be edited by clicking on the Setup button. The Drivers button is used to install additional DBMS-specific ODBC drivers.

The following examples illustrate the installation of the Oracle ODBC driver and the configuration of an Oracle data source. This process begins with the installation of the Oracle ODBC driver. First, select Drivers from the Data Sources dialog. When this button is clicked, all installed drivers are displayed, as shown in Figure 51.3.

Figure 51.3. The Drivers dialog displays a list of installed drivers.

To install the Oracle ODBC driver, click the Add button. This will display a dialog requesting the location of the drivers. Select the drive and directory containing the ODBC.INF file and click OK. The dialog box shown in Figure 51.4 indicates that the Oracle ODBC driver was located.

Figure 51.4. The Install Drivers dialog displays a list of drivers available for installation.

The Advanced button displays a dialog that allows the user to specify installation of the driver manager and code page translators. The Versions button in the lower-right corner brings up a second dialog that can be used to view extended version information about each component available to install. These two dialogs are shown in Figure 51.5.

Figure 51.5. The Advanced Installation Options and Versions dialogs can be used to control the installation of the Oracle ODBC driver.

The Install selected driver(s) with version checking should be selected. This will cause the installation program to prompt before overwriting an existing driver if it is the same or a newer version of the driver being installed.

The version checking options should also be used for the installation of the Driver Manager and translators. Newer versions of these DLLs should not be overwritten if they exist.

Code page translators are used to translate between different character sets and languages. In some cases, they are used for encryption or data type conversion. Although a translator is not needed in most cases, you can install them for possible future use. After making your selections from these options, click the OK button to return to the Install Drivers dialog.

To complete the installation of the Oracle7 driver, make sure that Oracle7 is highlighted in the list box and click the OK button. The Drivers dialog should now appear as shown in Figure 51.6.

Figure 51.6. The Drivers dialog shows that the Oracle ODBC driver was successfully installed.

Click the Close button to complete driver installation.

Configuring the data source is simply a matter of specifying a driver, naming the data source, and providing some additional information for the driver to use when connecting to the database. Refer to the Data Sources dialog in Figure 51.2. From this dialog, click Add to configure a new Oracle data source. The next dialog, Add Data Sources, requires the selection of an installed driver. Select Oracle7 from the list and click the OK button. Next, the Oracle7 ODBC Setup dialog prompts the user for a data source name, description, and SQL*Net connect string, as illustrated in Figure 51.7.

Figure 51.7. This is the Oracle7 ODBC Setup dialog.

The connect string is specific to the network transport (if any), the hostname or address of the server, and the system ID of the database to be accessed (if more than one Oracle database exists on the host). The syntax of the connect string is

                       transport_code:host_name:database

The transport_code is a single character used to specify the SQL*Net driver to be used (T for TCP/IP, X for IPX/SPX, and so on). The host_name is the name, alias, or network address of the server. The database argument is necessary only if more than one Oracle database exists on the host. In this case, the argument should be the system name of the database, as specified when the database was created with the CREATE DATABASE command. Consult the Oracle ODBC driver release notes and the SQL*Net documentation for further information on the SQL*Net Connect String.

For the purposes of this example, assume that the database resides on a UNIX host and will be accessed from the Windows workstation using TCP/IP. Enter ORACLE for the data source name, Oracle 7.1 for the description, and T:ORACLE_SERVER for the SQL*Net connect string. The dialog should now look like the one shown in Figure 51.8.

Figure 51.8. The Oracle7 ODBC Setup dialog requires a name and a SQL*Net Connect String. The description is optional.

The Options button enables the user to select a code page translator, assuming that a translator was installed with the driver. In most cases, no translation is necessary. Click OK to complete the data source setup. The new data source should appear in the Data Sources dialog as shown in Figure 51.9.

Figure 51.9. The Data Sources dialog shows that the new Oracle7 Data Source was successfully added.

The data source is now fully configured and ready to be accessed by an application. Note that the setup routine in Figure 51.7 and Figure 51.8 is specific to the driver (Oracle 7.1 version 1.11.0002, in this case). This setup dialog will vary slightly from driver to driver, but will always require similar information.

### Connecting to an ODBC Data Source using the ODBC API

Before your application connects to the ODBC data source, some memory allocation and initialization must be performed. First, the application must call SQLAllocEnv, passing a pointer to memory allocated to store an environment handle. This handle will be used to establish connections and for transaction processing. The application might need to establish more than one environment handle, but a single environment handle is usually sufficient, except in multithreaded environments.

Next, the application should call SQLAllocConnect, passing the previously established environment handle and a pointer to storage allocated for the connection handle. The driver manager allocates storage for connection information and associates the resulting connection handle with the environment handle. Multiple connections can be established for a single environment handle, but each connection can only be associated with a single environment. The connection handle will be used to allocate statement handles and process embedded SQL transactions.

Finally, the application may call either SQLConnect or SQLDriverConnect, passing the connection handle instantiated by the call to SQLAllocConnect. The primary difference between these two functions is that SQLDriverConnect accepts a full connection string, rather than separate arguments for the data source name, userid, and password. This allows for additional database-specific parameters to be passed to the driver as part of the connection string. Additionally, SQLDriverConnect provides an argument used to define the behavior of the driver manager and a window handle to be used as the parent of the data sources dialog (if one will be presented). The arguments to SQLDriverConnect are, in order:

- An allocated connection handle.
- The handle of the parent window from the Data Sources dialog (or NULL, if no dialog will be presented).
- A connection string.
- The length of the connection string.
- A pointer to storage for the connection string actually used by the driver. (It may add information to the connection string it receives.)
- A pointer to storage to hold the length of the completed connection string.
- An integer constant used to control the behavior of the driver manager.

A typical connection string looks like this:
DSN=ORACLE;UID=scotty;PWD=tiger;
Additional database-specific parameters may be provided, or the connection string may be partial, or empty, in which case the driver will provide a dialog requesting the information required to connect. If SQL_DRIVER_NOPROMPT is passed as the completion constant, the application must provide all required information in the connection string.

C applications should include ODBC.H (or SQL.H and SQLEXT.H, depending on the compiler), which contains all function prototypes, data types, and constants available in the ODBC API. When using other development tools, the developer must provide prototypes for all ODBC functions used by the application. Listings 51.1 and 51.2 demonstrate connecting to an ODBC data source in C and Visual Basic, in a Microsoft Windows application context.

Listing 51.1. This C function establishes a single connection to a data source.

    int ConnectToDataSource(

    HENV *hEnv,     /* used to store the environment handle */

    HDBC *hDBc)     /* used to store the connection handle  */

    {

        UCHAR        *szConnect;

        UCHAR        szConnectOut[SQL_MAX_MESSAGE_LENGTH];

        SWORD        iConnectOutLen;

        RETCODE      iError;

        szConnect = strdup(ÒDSN=ORACLE;UID=scotty;PWD=tiger;Ó);

        iError = SQLAllocEnv(hEnv);

        if (iError == SQL_SUCCESS)

            iError = SQLAllocConnect(*hEnv, hDBc);

        if (iError == SQL_SUCCESS)    iError = SQLDriverConnect(*hDBc,

                                      NULL,

                                      szConnect,

                                      SQL_NTS,

                                      szConnectOut,

                                      (SQL_MAX_MESSAGE_LENGTH - 1),

                                      &iConnectOutLen,

                                      SQL_DRIVER_NOPROMPT);

        return(iError);

    }

Listing 51.2. This Visual Basic function establishes a connection to a data source.

    '  include these prototypes in the module:

    Declare Function SQLAllocEnv

                     Lib "odbc.dll" (hEnv As Long) As Integer

    Declare Function SQLAllocConnect

                     Lib "odbc.dll" (ByVal hEnv As Long,

                                     hDBc As Long) As Integer

    Declare Function SQLDriverConnect

                     Lib "odbc.dll" (ByVal hDBc As Long,

                                     ByVal hWnd As Integer,

                                     ByVal szCSin As String,

                                     ByVal iCSinLen As Integer,

                                     ByVal szCSOut As String,

                                     ByVal iCSOutMaxLen As Integer,

                                     iCSOutLen As Integer,

                                     ByVal iDriverComplete As Integer)

                                     As Integer

    '  also define these constants:

    Global Const SQL_SUCCESS = 0

    Global Const SQL_SUCCESS_WITH_INFO = 1

    Global Const SQL_STILL_EXECUTING = 2

    Global Const SQL_NEED_DATA = 99

    Global Const SQL_NO_DATA_FOUND = 100

    Global Const SQL_ERROR = 1

    Global Const SQL_INVALID_HANDLE = 2

    Global Const SQL_NTS = 3

    Global Const SQL_DRIVER_NOPROMPT = 0

    Global Const SQL_MAX_MESSAGE_LENGTH = 512

    Function ConnectToDataSource(hEnv As Long, hDBc As Long) As Integer

        Dim    szConnect As String

        Dim    szConnectOut As String

        Dim    iConnectOutLen As Integer

        Dim    iError As Integer

        szConnectOut = Space$(SQL_MAX_MESSAGE_LENGTH)

        szConnect = ÒDSN=ORACLE;UID=scotty;PWD=tiger;Ó

        iError = SQLAllocEnv(hEnv)

        If (iError = SQL_SUCCESS) Then

            iError = SQLAllocConnect(hEnv, hDBc)

        End If

        If (iError = SQL_SUCCESS) Then

            iError = SQLDriverConnect(hDBc, 0, szConnect, SQL_NTS,       szConnectOut,

                                      (SQL_MAX_MESSAGE_LENGTH - 1),

                                      iConnectOutLen,

                                      SQL_DRIVER_NOPROMPT)

        End If

        ConnectToDataSource = iError

    End Function

Although the preceding examples might be somewhat oversimplified, they should be sufficient to illustrate the steps necessary to connect to a data source using the ODBC API. For clarification of the data types and constants used in the examples, consult the ODBC.H header file included in the Microsoft ODBC SDK.

### Setting Connection Options

After you allocate a connection, options can be set to control the behavior of statements processed by the connection, using SQLSetConnectOption. There are numerous parameters available, the most significant of which is the SQL_AUTOCOMMIT option. By default, this option is enabled, which means that transactions are committed as sent, with no possibility of rollback. This can be very dangerous if the application uses multiple statements to process one logical transaction. For DBMSs that do not support stored procedures and triggers, this situation is nearly unavoidable. The function examples in Listing 51.3 demonstrates the ODBC API call to set a connection option.

Listing 51.3. This C function disables the AUTOCOMMIT option for a connection.

    /* not part of ODBC.H */

    enum ConnectOptionValues

    {

        OFF,

        ON

    );

    int DisableAutoCommit(

    HDBC hDBc)     /* connection handle created using SQLAllocConnect */

    {

        RETCODE iError;

        iError = SQLSetConnectOption(hDBc, SQL_AUTOCOMMIT, OFF);

        return(iError);

    }

Other connection options enable connections to be made read-only, to specify a translation DLL, to specify a trace file for debugging, and to set transaction isolation levels. In addition, SQLSetConnectOption can be called using any of the SQLSetStatementOption parameters. In this case, the option applies not to a specific statement handle, but to all statement handles processed by the connection.

### Applying SQL Transactions

Transaction control through ODBC is dependent on SQL_AUTOCOMMIT being set to OFF, as described in the previous section. Before applying SQL, the application must call SQLAllocStmt to create a statement handle. After allocating a statement handle, the application can then apply SQL using either prepared, or direct execution.

Prepared execution should be used when the statement to be processed is complex and will be called repeatedly. Under the prepared execution method, the statement is compiled and the access plan is bound before the SQL is executed. For each subsequent execution of the statement, the driver sends only an access plan identifier, instead of the entire statement, to the server. To implement prepared execution, the application calls SQLPrepare, passing an allocated statement handle, the SQL statement, and the length of the SQL statement. The application can then reuse the statement handle and the associated SQL statement using SQLExecute. The SQL statement can be parameterized, using ? as a placeholder for a parameter. Parameter values can be set with each execution through calls to SQLSetParam. The arguments to SQLSetParam are as follows:

- The statement handle for which the SQL will be executed.
- The number of the parameter to be set (position, starting at 1, within the prepared statement).
- The C data type of the parameter.
- The ODBC SQL data type of the parameter.
- The precision of the parameter value.
- The scale of the parameter value.
- A pointer to storage containing the parameter value.
- A pointer to storage that contains the length of the parameter value. (This might always be NULL, provided that any strings are NULL-terminated.)

For full descriptions of C data types, ODBC SQL data types, and their corresponding precisions and scales, refer to the Microsoft ODBC SDK documentation.

If a statement will be executed only once or is fairly simple, direct execution may be preferable. It requires fewer functions calls, so it is easier to implement. It should also be faster than the prepared method for the first execution of a statement. Direct execution requires only a single call to SQLExecDirect, passing an allocated statement handle, the SQL string, and the length of the SQL string.

Regardless of the type of execution used, the application should call SQLTransact to commit or roll back transactions based on the return code of the call to SQLExecute or SQLExecDirect. If SQL_AUTOCOMMIT is set to OFF and the application calls SQLTransact with SQL_ROLLBACK, all statements processed by the connection since the last commit will be rolled back. The statement handle can then be freed using SQLFreeStmt. If the statement handle is not freed, it is available to be reused or overwritten. The second parameter to SQLFreeStmt is an integer constant used to close an open cursor, release buffers for parameters and bound columns, or free all resources associated with the statement (invalidating the handle).

Listings 51.4 and 51.5 demonstrate how an application can insert values into a table, using either the prepared or the direct execution method.

Listing 51.4. This C function inserts records into a table using prepared execution.

    /* structure containing company information */

    typedef struct

    {

        long ID;

        char *Company;

        char *Notes;

    }

    COMPANY;

    int InsertCompanyRecords(

    HENV    hEnv,          /* pre-allocated environment handle */

    HDBC    hDBc,          /* pre-allocated connection handle  */

    COMPANY *Companies,    /* pointer to an array of COMPANYs  */

    int     iNumCompanies) /* number of COMPANYs in the array */

    {

        RETCODE iError;

        int     i;

        HSTMT   hStmt;

        iError = SQLAllocStmt(hDBc, &hStmt);

        if (iError == SQL_SUCCESS)

            iError = SQLPrepare(hStmt,

                                ÒINSERT INTO COMPANY VALUES (

                                CompanyIDs.NextVal, ?, ?) \0Ó,

                                SQL_NTS);

        if (iError == SQL_SUCCESS)

        {

            for (i = 0; i < iNumCompanies; i++)

            {

                SQLSetParam(hStmt, 1, SQL_C_CHAR, SQL_CHAR,

                            strlen(Companies[i].Company), 0,

                            Companies[i].Company, NULL);

                SQLSetParam(hStmt, 2, SQL_C_CHAR, SQL_C_CHAR,

                            strlen(Companies[i].Notes), 0,

                            Companies[i].Notes, NULL);

                iError = SQLExecute(hStmt);

                if (iError != SQL_SUCCESS)

                    break;

            }

        }

        if (iError == SQL_SUCCESS)

            iError = SQLTransact(hEnv, hDBc, SQL_COMMIT);

        else

            iError = SQLTransact(hEnv, hDBc, SQL_ROLLBACK);

        if (iError == SQL_SUCCESS)

            iError = SQLFreeStmt(hStmt, SQL_DROP);

        return(iError);

    }

Listing 51.5. This Visual Basic function inserts records into a table using direct execution.

    '  include these prototypes in the module:

    Declare Function SQLAllocStmt Lib "odbc.dll" (ByVal hStmt As Long)

                     As Integer

    Declare Function SQLExecDirect Lib "odbc.dll" (ByVal hStmt As Long,

                     ByVal szSQL As String, ByVal iSQLLen As Long)

                     As Integer

    Declare Function SQLFreeStmt Lib "odbc.dll" (ByVal hStmt As Long

                     ByVal iOption As Integer) As Integer

    Declare Function SQLTransact Lib "odbc.dll" (ByVal hEnv As Long,

                     ByVal hDBc As Long, ByVal iType As Integer)

                     As Integer

    '  also define these constants:

    Global Const SQL_CHAR = 1

    Global Const SQL_INTEGER = 4

    Global Const SQL_C_CHAR = 0

    Global Const SQL_C_LONG = 1

    Global Const SQL_COMMIT = 0

    Global Const SQL_ROLLBACK = 1

    Global Const SQL_CLOSE = 0

    Global Const SQL_DROP = 1

    Global Const SQL_UNBIND = 2

    Global Const SQL_RESET_PARAMS = 3

    Type COMPANY

        Dim ID      As Long

        Dim Company As String

        Dim Notes   As String

    End Type

    Function InsertCompanyRecords(ByVal hEnv As Long,

             ByVal hDBc As Long, Companies() As COMPANY,

             ByVal iNumCompanies As Integer) As Integer

        Dim iError As Integer

        Dim i      As Integer

        Dim hStmt  As Long

        Dim szSQL  As String

        iError = SQLAllocStmt(hDBc, hStmt)

        If (iError = SQL_SUCCESS) Then

            For i = 0 To (iNumCompanies - 1)

                szSQL = ÒINSERT INTO COMPANY VALUES (Ò

                szSQL = szSQL & ÓCompanyIDs.NextVal, ' Ó

                szSQL = szSQL & Companies(i).Company & Ò' , ' Ó

                szSQL = szSQL & Companies(i).Notes & Ò' )Ó

                iError = SQLExecDirect(hStmt, szSQL, SQL_NTS)

                If (iError <> SQL_SUCCESS) Then

                    Exit For

                End If

            Next i

        End If

        If (iError = SQL_SUCCESS) Then

            iError = SQLTransact(hEnv, hDBc, SQL_COMMIT)

        Else

            iError = SQLTransact(hEnv, hDBc, SQL_ROLLBACK)

        End If

        If (iError = SQL_SUCCESS) Then

            iError = SQLFreeStmt(hStmt, SQL_DROP)

        End If

        InsertCompanyRecords = iError

    End Function

There are several variations on the prepared execution method. These include setting multiple values for each parameter, and providing parameter values after the call to SQLExecute. For information on these methods, consult the ODBC SDK documentation for the SQLParamData and SQLPutData functions.

### Retrieving Result Sets

The prepared execution and direct execution methods also apply to SQL SELECT statements. The additional methods available to retrieve results through ODBC, however, are almost too numerous to mention. SQLSetStmtOption can be used to enable asynchronous processing, which allows single-threaded environments such as Windows 3.x to process multiple statements simultaneously. When used with SQLSetScrollOptions, SQLSetStmtOption can enable multiple rows to be fetched with a single call to SQLExtendedFetch. SQLSetScrollOptions and SQLExtendedFetch can also be used to create cursors that scroll in both directions; and when used with SQLSetPos, the record pointer can be placed at a specific row in the result set.

Unfortunately, these extended functions are not part of the core ODBC standard and are currently unsupported by the Oracle ODBC driver. Although third-party driver vendors might supply some of these functions, the code examples in this section will focus on bound and unbound fetches using prepared and direct execution. These examples use core functions and level 1 extensions, all of which are supported by the current Oracle ODBC driver available from Oracle Corporation.

Although the SQL to retrieve result sets can be executed in exactly the same manner as SQL to-process transactions, the application must take additional steps to bind result set columns to application variables. Columns may be prebound using SQLBindCol, or bound after execution, using SQLGetData.

In order to bind columns prior to execution, SQLBindCol must be called once for each column to be bound. The arguments to SQLBindCol are somewhat similar to those for SQLSetParam, as enumerated here:

- The statement handle for the executed SQL.
- The number of the column to be bound (position, starting at 1, within the result set).
- The C data type of the variable to be bound.
- A pointer to storage allocated for the variable
- The maximum length of the variable, in bytes.
- A pointer for storage to receive the full length of the column in the result set. This can be used to determine if data was truncated.

Alternatively, the application can bind variables to result set columns after execution, using SQLGetData. Its arguments are identical to those for SQLBindCol. The difference between these two methods is essentially a matter of when the application variables are bound to result set columns.

SQLDescribeCol can be called prior to SQLGetData to obtain information about a column, including its name, data type, and length. This information can be used to ensure that no data is truncated, among other things. For example, when used with SQLNumResultCols, a result set generated by a SQL statement such as
SELECT * FROM view_name
can be bound to application variables completely dynamically at runtime.

Regardless of when the columns are bound to variables, the application calls SQLFetch, with the statement handle as the only argument. This positions the cursor at the next row in the result set. If columns are prebound, data is placed in variables at this time. Otherwise, the call to SQLFetch simply scrolls the cursor one row forward.

An application can use SQLFetch with SQLGetData to locate a specific row or set of rows in the result set, based on the value of one or more columns. Although this can be accomplished with bound columns, it should be more efficient to use SQLGetData to do comparisons on a single column when there are many rows and columns in the result set.

The code examples in Listings 51.6 and 51.7 demonstrate the fetching of bound and unbound columns, using direct execution.

Listing 51.6. This C function retrieves the first record in a result set using column-wise binding.

    int GetCompanyInfo(

    HENV    hEnv,          /* preallocated environment handle */

    HDBC    hDBc,          /* preallocated connection handle  */

    COMPANY *Company,      /* pointer to a  COMPANY            */

    char    *szName)       /* Company Name to find             */

    {

        RETCODE iError;

        HSTMT   hStmt;

        long    tempID;

        long    iLenOut1, iLenOut2, iLenOut3;

        char    tempName[80];

        char    tempNotes[255];

        char    szSQL[255];

        iError = SQLAllocStmt(hDBc, &hStmt);

        if (iError == SQL_SUCCESS)

        {

            sprintf (szSQL,

                    "SELECT ID, Company, Notes FROM Company WHERE

                     Company LIKE '%");

            strcat(szSQL, szName);

            strcat(szSQL, "%'");

            iError = SQLExecDirect(hStmt, szSQL, SQL_NTS);

            if (iError == SQL_SUCCESS)

            {

                iError = SQLBindCol(hStmt, 1, SQL_C_LONG, &tempID, 0

                                   , &iLenOut1);

                iError = SQLBindCol(hStmt, 2, SQL_C_CHAR, tempName

                                   , 80, &iLenOut2);

                iError = SQLBindCol(hStmt, 3, SQL_C_CHAR, tempNotes

                                   , 255, &iLenOut3);

                iError = SQLFetch(hStmt);

                if (iError >= SQL_SUCCESS)

                {

                    Company>ID = tempID;

                    Company>Company = strdup(tempName);

                    Company>Notes = strdup(tempNotes);

                }

            }

           SQLFreeStmt(hStmt, SQL_CLOSE);

           SQLFreeStmt(hStmt, SQL_DROP);

        }

        return(iError);

    }

Listing 51.7. This Visual Basic function retrieves multiple records with unbound columns.

    '  include these prototypes in the module:

    Declare Function SQLFetch Lib "odbc.dll" (ByVal hStmt As Long)

                     As Integer

    Declare Function SQLGetData Lib "odbc.dll" (ByVal hStmt As Long,

                     ByVal iColNum As Integer,

                     ByVal iDataType As Integer, ByVal hBuffer As Any,

                     ByVal iBuffLen As Long, iLenOut As Long)

                     As Integer

    Function GetCompanies (ByVal hDBc As Long, Companies() As Company,

                           iNumCoOut As Integer) As Integer

        Dim iError    As Integer

        Dim hStmt     As Long

        Dim szSQL     As String

        Dim iOut1     As Long

        Dim iOut2     As Long

        Dim iTemp     As Integer

        iNumCoOut = 0

        ReDim Companies(iNumCoOut)

        iError = SQLAllocStmt(hDBc, hStmt)

        If (iError = SQL_SUCCESS) Then

            szSQL = "SELECT COMPANY, NOTES FROM COMPANY ORDER BY 2"

            iError = SQLExecDirect(hStmt, szSQL, SQL_NTS)

            If (iError = SQL_SUCCESS) Then

                While (iError >= SQL_SUCCESS)

                    ReDim Preserve Companies(iNumCoOut)

                    iError = SQLFetch(hStmt)

                    iError = SQLGetData(hStmt, 1, SQL_C_CHAR,

                    Companies(iNumCoOut).Company,

                            80, iOut1)

    iError = SQLGetData(hStmt, 2, SQL_C_CHAR,

                            Companies(iNumCoOut).Notes, 255, iOut2)

                    If (iError >= SQL_SUCCESS) Then

                        iNumCoOut = iNumCoOut + 1

                    End If

                Wend

            End If

        End If

        iTemp = SQLFreeStmt(hStmt, SQL_CLOSE)

        iTemp = SQLFreeStmt(hStmt, SQL_DROP)

        GetCompanies = iError

    End Function

The examples in Listings 51.6 and 51.7 also demonstrate dynamic SQL building by the application. Although prepared execution could be used to parameterize the SQL statements, prepared execution is not always the best choice for dynamic SQL. When retrieving result sets, the SQL is typically not executed repeatedly, and in these cases, direct execution is often preferable.

### Handling Errors

The return values of ODBC functions should always be checked to determine whether an error has occurred. The return code SQL_SUCCESS is defined as 0. Error codes are defined as negative numbers, whereas positive numbers are used to indicate that additional information is required or is being provided by the driver. How these error and informational return codes are handled within a program is entirely application-specific. The ODBC API provides a function to retrieve standard ODBC error codes, DBMS-specific error codes, and error and informational text from the driver. This function, SQLError, has the following arguments:

- The environment handle in which the error occurred.
- The connection handle in which the error occurred.
- The statement handle in which the error occurred.
- A pointer to storage to receive a null-terminated string containing SQL state information. This will inform the application that data was truncated, among other things.
- A pointer to storage for a long integer that will receive the DBMS's native error code.
- A pointer to storage that will receive error text.
- A pointer to storage that will receive the length of error text, in bytes. This can be used to determine whether error text was truncated.

The error code SQL_INVALID_HANDLE (-2), does not provide additional SQL state or error information. It indicates that an environment, connection, or statement handle was invalid. These errors are commonly the result of indirection or scope problems within the application.

The code example in Listing 51.8 displays and retrieves SQL state and/or error text and displays it to the user.

Listing 51.8. This Visual Basic function displays ODBC SQL states and error messages.

    '  include this prototype in the module:

    Declare Function SQLError Lib "odbc.dll" (ByVal hEnv As Long,

                     ByVal hDBc As Long, ByVal hStmt As Long,

                     ByVal szSQLState As String, iNativeError As Long,

                     ByVal szBuffer As String,

                     ByVal iBufLen As Integer, iLenOut As Integer)

                     As Integer

    Sub ODBCError (ByVal hEnv As Long, ByVal hDBc As Long,

                   ByVal hStmt As Long)

        Dim iError          As Integer

        Dim szSQLState      As String * 10

        Dim iNativeError    As Long

        Dim szErrorMsg      As String * 511

        Dim iMsgLength      As Integer

        Dim szODBCMsg       As String

        iError = SQLError(hEnv, hDBc, hStmt, szSQLState, iNativeError,

                 szErrorMsg, SQL_MAX_MESSAGE_LENGTH  1, iMsgLength)

        If (iError = 0) Then

         szODBCMsg = Left(szErrorMsg, iMsgLength)

         Beep

         MsgBox "ODBC Error: " & szODBCMsg

        Else

         Beep

         MsgBox "Undetermined ODBC Error."

        End If

    End Sub

The error and informational data may be stored in the environment handle, the connection handle, or the statement handle, depending on the nature of the error or information. The application might supply NULL arguments for two of the three handles to retrieve information specific to the supplied handle. This error information is stored until the handle is reused.

### Calling Stored Procedures and Functions

Support for stored procedures and functions is highly DBMS-specific. Oracle-stored procedures and functions are accessible through ODBC in much the same way as embedded SQL. In most cases, it is preferable to use stored procedures or functions to process inserts, updates, and deletes. This simplifies client-side development and allows for greater control over transactions by allowing database objects to handle errors, and to commit or rollback work, as needed.

Oracle-stored procedures and functions can be executed using either the prepared or direct execution methods, with the following SQL syntax:

    procedures: {call proc_name(' an example of direct execution' , ' param2')}

    functions:  {?=call proc_name(' an example of prepared execution' , ?, ?)}

Note that the entire statement must be enclosed in curly braces, and that when calling functions, a placeholder must be supplied for the return value. (Use prepared execution when calling functions.)

Listings 51.9 and 51.10 demonstrate the prepared and direct execution methods for calling Oracle procedures.

Listing 51.9. This C function inserts records into a table using an Oracle stored procedure with prepared execution.

    int InsertCompanySP(

    HENV    hEnv,          /* preallocated environment handle */

    HDBC    hDBc,          /* preallocated connection handle  */

    COMPANY *Companies,    /* pointer to an array of COMPANYs  */

    int     iNumCompanies) /* number of COMPANYs in the array */

    {

        RETCODE iError;

        int     i;

        HSTMT   hStmt;

        iError = SQLAllocStmt(hDBc, &hStmt);

        if (iError == SQL_SUCCESS)

            iError = SQLPrepare(hStmt,

                                "{call sp_insert_company(?, ?)}\0",

                                SQL_NTS);

        {

            if (iError == SQL_SUCCESS)

            {

                for (i = 0; i < iNumCompanies; I++)

                {

                    SQLSetParam(hStmt, 1, SQL_C_CHAR, SQL_CHAR,                                                    strlen(Companies[i].Company), 0,

                                Companies[i].Company, NULL);

                    SQLSetParam(hStmt, 2, SQL_C_CHAR, SQL_C_CHAR,

                                strlen(Companies[i].Notes), 0,

                                Companies[i].Notes, NULL);

                    iError = SQLExecute(hStmt);

                    if (iError != SQL_SUCCESS)

                        break;

                }

           }

            iError = SQLFreeStmt(hStmt, SQL_DROP);

        }

        return(iError);

    }

Listing 51.10. This Visual Basic function inserts records into a table using an Oracle-stored procedure with direct execution.

    Function InsertCompanySP (ByVal hEnv As Long, ByVal hDBc As Long,

             Companies() As Company, ByVal iNumCompanies As Integer)

             As Integer

        Dim iError      As Integer

        Dim iTemp       As Integer

        Dim i           As Integer

        Dim hStmt       As Long

        Dim szSQL       As String

        Dim szCompany   As String

        Dim szNotes     As String

        iError = SQLAllocStmt(hDBc, hStmt)

        If (iError = SQL_SUCCESS) Then

            For i = 0 To (iNumCompanies  1)

                szSQL = "{CALL sp_insert_company('"

                szSQL = szSQL & Companies(i).Company & "', '"

                szSQL = szSQL & Companies(i).Notes & "')}"

                iError = SQLExecDirect(hStmt, szSQL, SQL_NTS)

                If (iError <> SQL_SUCCESS) Then

                    Call ODBCError(hEnv, hDBc, hStmt)

                    Exit For

                End If

            Next I

            iTemp = SQLFreeStmt(hStmt, SQL_DROP)

        End If

        InsertCompanySP = iError

    End Function

The application might not have to call SQLTransact when accessing stored procedures and functions. In most cases, it is the stored objects that should handle commits and rollbacks internally.

Currently, support for stored procedures and functions does not extend to packaged objects, even if they are declared publicly. In order to call a packaged procedure or function, an external stub must be created. ODBC can then access the packaged object through an external function, which calls into the package. This solution is clearly less than ideal, and provides somewhat of a deterrent to using packages with ODBC applications. This should not, however, prevent an application from using procedures and functions to handle transaction-based SQL. In terms of reliability and performance, procedures and functions are typically better suited to this task than embedded SQL when using ODBC.

### Disconnecting and Freeing Resources

The application should free all ODBC resources and cleanly disconnect before exiting. First, all statement handles should be freed by calling SQLFreeStmt.

Next, the application should call SQLDisconnect, passing the active connection handle. Then, the application should call SQLFreeConnect, passing the connection handle as the argument. Finally, the application should use SQLFreeEnv to free all resources allocated for the environment handle.

The order in which these functions are called is very important. All statement handles for a connection should be freed prior to passing the connection handle to SQLDisconnect. Each connection handle for an environment should be disconnected and freed prior to passing the environment handle to SQLFreeEnv. Freeing allocated memory and disconnecting in the proper order ensures that the connection will not remain active on the server. Listing 51.11 illustrates these steps.

Listing 51.11. This C function disconnects from the data source and frees all resources allocated to the connection and environment handles.

    int Disconnect(

    HENV hEnv,

    HDBC hDBc)

    {

        int iRetVal;

        iRetVal = SQLDisconnect(hDBc);

        if (iRetVal != SQL_SUCCESS)

            ReportError(hEnv, hDBc, SQL_NULL_HSTMT);

        iRetVal = SQLFreeConnect(hDBc);

        if (iRetVal != SQL_SUCCESS)

            ReportError(hEnv, hDBc, SQL_NULL_HSTMT);

        iRetVal = SQLFreeEnv(hEnv);

        return(iRetVal);

    }

The code example in Listing 51.11 assumes that all statement handles have been freed. SQL_DROP should be passed as the second parameter to SQLFreeStmt when an application is exiting to ensure that all resources are freed.

### Debugging ODBC Applications

The ODBC API provides the developer with most of the tools needed to debug an ODBC application. The SQLError function, for example, is arguably more useful in debugging mode than in production code. The application can include debug code to display information from SQLError after every call to the ODBC API that results in a return value other than SQL_SUCESS. This is especially helpful in the initial phases of development, because it assists not only in locating programming bugs but also in determining what errors are likely to occur in a production environment. Steps should be taken to simplify the task of invalidating or removing debug code when it is no longer needed. An easy way to do this is to define a constant in the application and call it DEBUG_MODE, for example. The constant can be used to conditionally branch to debug code or to ignore it, depending on the value of the constant. By simply changing the value of the constant, the developer can then enable or disable debug code. This also simplifies the process of removing this code at a later date, by providing a single value to search for that will exist wherever debug code exists.

The ODBC API also gives the developer the ability to trace ODBC function calls and parameters. The application can call SQLSetConnectOption to enable tracing and to specify a trace file. Alternatively, the developer can enable tracing for all connections to a data source using the ODBC Administration applet. Refer to the Data Sources dialog in Figure 51.2. Clicking the Options button on this dialog brings up the ODBC Options dialog, which lets the user enable tracing for a data source and specify a log file. Tracing is simply logging ODBC function calls and arguments, providing a history of the application's interaction with ODBC.

Debugging time can be minimized by some investigation prior to design and development. Select a driver and determine what functions it supports before writing any code. When developing ODBC applications, never assume that a level 1 or level 2 extension will be supported by the driver. Purchase a utility (or write one yourself) that queries the driver for the functions that it supports. SQLGetFunctions can be used to accomplish this task. It accepts a connection handle, an integer constant specifying the function, and a pointer to storage to receive an integer value of TRUE (a nonzero value) if the function is supported, or FALSE (0) if it is not supported. This information is critical to the design of most applications. However, in some cases, it is desirable to use SQLGetFunctions at runtime to determine how the application will interact with the database.

### Limitations of ODBC

One of the primary goals of the ODBC standard is to provide a DBMS-independent standard interface to relational databases. This, in turn, should enable the development of database-independent client applications. In theory, an application can be developed using embedded SQL that will work regardless of the DBMS being accessed, whether it is Oracle, Sybase, DB2, or another DBMS. The primary limitation to this approach is that the standard ODBC SQL syntax reduces the language to the least common denominator. Outer join syntax, for example, is not currently supported by the ODBC standard.

In many cases, using ODBC precludes the use of the most powerful features of the database. When accessing Oracle through ODBC, packaged constructs are completely unavailable, and support for Oracle functions is awkward, at best. If outer joins are needed, they must be accomplished through the creation of views at design-time, which is a hindrance to applications requiring the construction of dynamic SQL.

Using ODBC might also demand a sacrifice in terms of performance. Although performance is not always sacrificed when using ODBC, most high-performance drivers are fairly expensive third-party products that are licensed on a per-workstation basis. Typically, a custom interface to the DBMS-specific network software can provide greater performance and flexibility. A custom interface to SQL*Net, however, would require additional time to design and develop. Because the ODBC standard is so widely supported and its API is relatively easy to use, minor deficiencies in performance can often be accepted in favor of shorter development cycles and more portable code.

### Summary

Despite its limitations, ODBC is one of the most popular means of communicating with a database and has become the de facto standard for Microsoft Windows client development. Many of the Windows development tools provide interfaces that completely abstract the underlying API from the developer, making ODBC extremely easy to use. Regardless of the development tools used or the level of abstraction, a thorough understanding of the API is invaluable in making the most of an ODBC application. When greater control is required or when performance is an issue, there is no substitute for using the API directly.