1320-1323

Previous Table of Contents Next

Page 1320

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 57.3 demonstrate the ODBC API call to set a connection option.

Listing 57.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.

Page 1321

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 datatype of the parameter.
• The ODBC SQL datatype 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 .)

NOTE

For full descriptions of C datatypes, ODBC SQL datatypes, 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 are 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).

Page 1322

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

Listing 57.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, ?, ?) 
 /* 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); 
 ",                               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); 

Page 1323

 if (iError == SQL_SUCCESS)           iError = SQLFreeStmt(hStmt, SQL_DROP);     return(iError); } 

Listing 57.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)                                                  continues 

Previous Table of Contents Next