1324-1327

Previous Table of Contents Next

Page 1324

Listing 57.5. continued

 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 enables 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 focus on bound and unbound

Page 1325

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

TIP

SQLDescribeCol can be called prior to SQLGetData to obtain information about a column, including its name , datatype, and length. This information can be used to ensure that no data is truncated, among other things. When used with SQLNumResultCols, for example, a result set generated by a SQL statement such as

 SELECT * FROM view_name 

can be completely bound dynamically to application variables 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.

Page 1326

TIP

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 57.6 and 57.7 demonstrate the fetching of bound and unbound columns, using direct execution.

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

Page 1327

 Company>Company = strdup(tempName);                   Company>Notes = strdup(tempNotes);             }         }        SQLFreeStmt(hStmt, SQL_CLOSE);        SQLFreeStmt(hStmt, SQL_DROP);     }        return(iError); } 

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

Previous Table of Contents Next