1328-1331

Previous Table of Contents Next

Page 1328

Listing 57.7. continued

 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 57.6 and 57.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 informs the application that data was truncated, among other things.
• A pointer to storage for a long integer that receives the DBMS's native error code.
• A pointer to storage that receives error text.
• A pointer to storage that receives the length of error text, in bytes. This can be used to determine whether error text was truncated.

Page 1329

NOTE

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 57.8 displays and retrieves SQL states and/or error text and displays them to the user .

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

Page 1330

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 57.9 and 57.10 demonstrate the prepared and direct execution methods for calling Oracle procedures.

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

Page 1331

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

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

Previous Table of Contents Next