Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Lesson 5: Query the database

In order to process query result sets, ODBC requires that statements be prepared before they are executed. In this lesson you prepare and execute a statement, and print out the results.

Write code to query the database

1. Add prepare, execute, and fetch functions to sample.cpp:

   static SQLHANDLE prepare( SQLHANDLE hcon ){
       SQLRETURN retn;
       SQLHANDLE hstmt;
       static const ul_char * sql = 
           UL_TEXT( "SELECT id, fname, lname FROM customer" );
       retn = SQLAllocHandle( SQL_HANDLE_STMT, hcon, &hstmt );
       retn = SQLPrepare( hstmt, (SQLTCHAR*)sql, SQL_NTS );
       if( retn == SQL_SUCCESS ) {
           _tprintf( "success in prepare.\n" );
       } else {
           _tprintf( "error in prepare: %d.\n", retn );
           retn = SQLFreeHandle( SQL_HANDLE_STMT, hstmt );
           hstmt = 0;
       }
       return hstmt;
   }

   The prepare function does not execute the SQL statement.

   static ul_bool execute( SQLHANDLE hstmt )
   {
       SQLRETURN retn;
       retn = SQLExecute( hstmt );
       if( retn == SQL_SUCCESS ) {
           _tprintf( "success in execute.\n" );
       } else {
           _tprintf( "error in execute: %d.\n", retn );
       retn = SQLFreeHandle( SQL_HANDLE_STMT, hstmt );
       hstmt = 0;
       }
       return retn == SQL_SUCCESS;
   }

   The execute function executes the query, but does not make the result set directly available to the client application. Your application must explicitly fetch the rows it needs from the result set.

   static ul_bool fetch( SQLHANDLE hstmt )
   {
   #define NAME_LEN 20
       SQLCHAR      fName[NAME_LEN], lName[NAME_LEN];
       SQLINTEGER   id;
       SQLINTEGER   cbID = 0, cbFName = SQL_NTS, cbLName = SQL_NTS;
       SQLRETURN    retn;
   
       SQLBindCol( hstmt, 1, SQL_C_ULONG, &id, 0, &cbID );
       SQLBindCol( hstmt, 2, SQL_C_CHAR, 
                   fName, sizeof(fName), &cbFName );
       SQLBindCol( hstmt, 3, SQL_C_CHAR, 
                   lName, sizeof(lName), &cbLName );
       while ( ( retn = SQLFetch( hstmt ) ) != SQL_NO_DATA ){
           if (retn == SQL_SUCCESS || retn == SQL_SUCCESS_WITH_INFO){
               fName[ cbFName ] = '\0';
               lName[ cbLName ] = '\0';
               _tprintf( "%20s %d %20s\n", fName, id, lName );
           } else {
               _tprintf ( "error while fetching: %d.\n", retn );
               break;
           }
       }
       return retn == SQL_SUCCESS;
   }

   The values are fetched into variables that have been bound to the column. String variables are not returned with a null terminator, and so the null terminator is added for printing purposes. The length of the actual string that was returned is available in the final parameter of SQLBindCol.

2. Call prepare, execute, and fetch from the main function.

   Alter your main function in sample.cpp so that it reads as follows:

   int main() {
       SQLHANDLE henv;
       SQLHANDLE hcon;
       SQLHANDLE hstmt;
   
       henv = opendb();
       hcon = connect( henv );
       insert( hcon );
       hstmt = prepare( hcon ); 
       execute( hstmt );
       fetch( hstmt );
       SQLFreeHandle( SQL_HANDLE_STMT, hstmt );
       disconnect( hcon );
       closedb( henv );
       return 0;
   }

3. Compile, link, and run your application to confirm that the application builds properly.

   You should see that the application displays the data from the row you inserted.

For more information about the functions called in this procedure, see:

• SQLPrepare function
• SQLExecute function
• SQLBindCol function
• SQLFetch function

This completes the tutorial.