Client 3 - Processing Queries

Client 3 ”Processing Queries

When you execute a query using ODBC, your client will first send the query to the server, and then process the results.

The ODBC result-processing model is more complex than other PostgreSQL APIs. In the libpq, libpq++, and libpgeasy APIs, you send a query to the server and then call a function to access each field (in each row) in the result set.

An ODBC application generally uses a different scheme. After you send the query to the server, you bind each field in the result set to a variable in your application. After all the result fields are bound, you can fetch the individual rows in the result set ”each time you fetch a new row, the bound variables are populated by ODBC.

Listing 12.3 shows you how to execute a query and display the results.

Listing 12.3 odbc/client3.c

 1 /* client3.c */  2  3 #include <sql.h>  4 #include <sqlext.h>  5 #include <sqltypes.h>  6 #include <stdio.h>  7  8 typedef enum { FALSE, TRUE } bool;  9 10 typedef struct 11 { 12   char          name[128+1]; 13   SQLSMALLINT   nameLength; 14   SQLSMALLINT   dataType; 15   SQLUINTEGER   fieldLength; 16   SQLSMALLINT   scale; 17   SQLSMALLINT   nullable; 18   SQLINTEGER    displaySize; 19   int           headerLength; 20   SQLINTEGER    resultLength; 21   char        * value; 22 } resultField; 23 24 static void printResultSet( SQLHSTMT stmt ); 25 

The only thing that is new here is the resultField structure. I'll use an array of resultField s to process the result set. A note on terminology here: PostgreSQL documentation makes a minor distinction between a field and a column. Column refers to a column in a database, whereas field can refer to a column or a computed value. ODBC does not make this distinction. I tend to use the terms interchangeably.

 26 static bool SQL_OK( SQLRETURN result ) 27 { 28   if( result == SQL_SUCCESS  result == SQL_SUCCESS_WITH_INFO ) 29     return( TRUE ); 30   else 31     return( FALSE ); 32 } 33 34 static bool printErrors( SQLHENV  envHandle, 35                          SQLHDBC  conHandle, 36                          SQLHSTMT stmtHandle ) 37 { 38   SQLRETURN   result; 39   SQLCHAR     sqlState[6]; 40   SQLINTEGER  nativeError; 41   SQLSMALLINT requiredLength; 42   SQLCHAR     messageText[SQL_MAX_MESSAGE_LENGTH+1]; 43 44   do 45   { 46     result = SQLError( envHandle, 47                        conHandle, 48                        stmtHandle, 49                        sqlState, 50                        &nativeError, 51                        messageText, 52                        sizeof( messageText ), 53                        &requiredLength ); 54 55     if( SQL_OK( result )) 56       { 57         printf( "SQLState     = %s\n", sqlState ); 58         printf( "Native error = %d\n", nativeError ); 59         printf( "Message text = %s\n", messageText ); 60       } 61   } while( SQL_OK( result )); 62 } 63 

You've already seen SQL_OK() and printErrors() in the previous example, so I won't bother explaining them here.

 64 static void executeStmt( SQLHDBC con, char * stmtText ) 65 { 66   SQLHSTMT  stmt; 67 68   SQLAllocHandle( SQL_HANDLE_STMT, con, &stmt ); 69 70   if( SQL_OK( SQLExecDirect( stmt, stmtText, SQL_NTS ))) 71     printResultSet( stmt ); 72   else 73     printErrors( SQL_NULL_HENV, SQL_NULL_HDBC, stmt ); 74 } 

The executeStmt() function is responsible for sending a query to the server. You start by allocating a new type of handle ”a SQLHSTMT . A SQLHSTMT is a statement handle. The parent of a statement handle is always a connection handle (or a SQLHDBC ).

After you have a statement handle, send the query to the server using SQLExecDirect() . SQLExecDirect() is pretty simple ”you provide a statement handle, the text of the query that you want to send to the server, and the length of the query string (or SQL_NTS to indicate that the query text is a null- terminated string).

If SQLExecDirect() returns a success value, you call printResultSet() to process the result set.

 75  76 static void printResultSet( SQLHSTMT stmt )  77 {  78   SQLSMALLINT   i;  79   SQLSMALLINT   columnCount;  80   resultField * fields;  81  82   //  First, examine the metadata for the  83   //  result set so that we know how many  84   //  fields we have and how much room we need for each.  85  86   SQLNumResultCols( stmt, &columnCount );  87  88   fields = (resultField *)calloc( columnCount+1,  89               sizeof( resultField ));  90  91   for( i = 1; i <= columnCount; i++ )  92   {  93     SQLDescribeCol( stmt,  94           i,  95           fields[i].name,  96           sizeof( fields[i].name ),  97           &fields[i].nameLength,  98           &fields[i].dataType,  99           &fields[i].fieldLength, 100           &fields[i].scale, 101           &fields[i].nullable ); 102 103     SQLColAttribute( stmt, 104            i, 105            SQL_DESC_DISPLAY_SIZE, 106            NULL, 107            0, 108            NULL, 109            &fields[i].displaySize ); 110 111 112     fields[i].value = (char *)malloc( fields[i].displaySize + 1 ); 113 114     if( fields[i].nameLength > fields[i].displaySize ) 115       fields[i].headerLength = fields[i].nameLength; 116     else 117       fields[i].headerLength = fields[i].displaySize; 118   } 119 120   //  Now print out the column headers 121 122   for( i = 1; i <= columnCount; i++ ) 123   { 124     printf( "%-*s ", fields[i].headerLength, fields[i].name ); 125   } 126   printf( "\n" ); 127 128   //  Now fetch and display the results... 129 130   while( SQL_OK( SQLFetch( stmt ))) 131   { 132     for( i = 1; i <= columnCount; i++) 133     { 134       SQLRETURN result; 135 136       result = SQLGetData( stmt, 137            i, 138            SQL_C_CHAR, 139            fields[i].value, 140            fields[i].displaySize, 141            &fields[i].resultLength ); 142 143       if( fields[i].resultLength == SQL_NULL_DATA ) 144          printf( "%-*s ", fields[i].headerLength, "" ); 145       else 146          printf( "%-*s ", fields[i].headerLength, fields[i].value ); 147     } 148     printf( "\n" ); 149   } 150 151   for( i = 1; i <= columnCount; i++ ) 152     free( fields[i].value ); 153 154   free( fields ); 155 156 } 157 

The printResultSet() function is somewhat complex. It starts by building up an array of resultField structures to keep track of the metadata for the query that was just executed.

You first call SQLNumResultCols() to determine how many fields (or columns ) will appear in the result set. After you know how many fields you will be processing, you allocate an array of resultField structures ”one structure for each field (and one extra to simplify the code).

Next , you call two metadata functions so that you know what kind of information is being returned for each field. The SQLDescribeCol() function returns the column name, data type, binary field length, scale (used for numeric data types), and nullability for a given field. Notice that field indexes start with 1 , not ”so, the loop goes from 1 to columnCount rather than the usual to columnCount-1 ; you don't use fields[0] for simplicity.

The SQLColAttribute() function returns a specific metadata attribute for the given column ( i ). You will retrieve each field in the form of a null-terminated string, so you need to know the maximum display length for each field. The SQL_DESC_ DISPLAY_SIZE attribute is just what you need.

The SQLDescribeCol() and SQLColAttribute() functions both return column- related metadata. SQLDescribeCol() is a convenient function that returns the most commonly used metadata properties. Calling SQLDescribeCol() is equivalent to

 SQLColAttribute( stmt, column, SQL_DESC_NAME, ... ); SQLColAttribute( stmt, column, SQL_DESC_TYPE, ... ); SQLColAttribute( stmt, column, SQL_DESC_LENGTH, ... ); SQLColAttribute( stmt, column, SQL_DESC_SCALE, ... ); SQLColAttribute( stmt, column, SQL_DESC_NULLABLE, ... ); 

After you have retrieved and stored the metadata for a column, you allocate a buffer large enough to contain the data for the column in the form of a null-terminated string. You also compute the header length. You want to print each column in a horizontal space large enough to hold either the column name or the column contents, whichever is longer.

After printing out the column headings (lines 122 “126), we start processing the contents of the result set. The SQLFetch() function will fetch the next row within the result set associated with the given SQLHSTMT . SQLFetch() will return the value SQL_NO_DATA when you have exhausted the result set.

ODBC Metadata Types

So far, we have looked only at metadata that describes a result set. Because ODBC is designed as a portability layer between your application and the backend database, ODBC provides a rich set of metadata functions. First, you can retrieve a list of the data sources defined on your system using the SQLDataSources() function. The SQLDrivers() function will retrieve a list of installed drivers.

After you have connected to a data source, you can retrieve a list of supported data types by calling SQLGetTypeInfo() . This function returns the list as a result set ”you use SQLFetch() and SQLGetData() (described later) to obtain the list.

You can use SQLFunctions() to determine which of the ODBC API functions are supported by a given driver. The PostgreSQL ODBC Driver is (currently) an ODBC 2.5 driver and does not directly support ODBC 3.0 functions. The PostgreSQL driver does not support a few of the ODBC 2.5 functions (such as SQLProcedures() , SQLProcedureColumns() , and SQLBrowseConnect( )).

You can also ask the driver whether it supports various SQL syntax features. For example, if you call SQLGetInfo( ..., SQL_CREATE_TABLE, ... ) , you can determine which CREATE TABLE clauses are supported by the database's CREATE TABLE statement. The SQLGetInfo() function also returns version information, as shown in Table 12.6.

Table 12.6. Version Information Returned by SQLGetInfo()

SQLGetInfo() InfoType Argument	Return Information
SQL_DBMS_VER	Database version (for example, PostgreSQL 7.1.3)
SQL_DM_VER	Driver manager version
SQL_DRIVER_NAME	Driver name
SQL_DRIVER_ODBC_VER	ODBC version that driver conforms to
SQL_DRIVER_VER	Driver version
SQL_SERVER_NAME	Name of server

You can use SQLGetInfo(..., SQL_TXN_CAPABLE, ...) to find out about the transaction-processing capabilities of a database.

By my count, SQLGetInfo() can return more than 150 different pieces of information about a data source!

If SQLFetch() succeeds, you retrieve each column in the current row using the SQLGetData() function, which has the following prototype:

 SQLRETURN SQLGetData( SQLHSTMT stmtHandle,                       SQLUSMALLINT columnNumber,                       SQLSMALLINT  desiredDataType,                       SQLPOINTER   destination,                       SQLINTEGER   destinationLength,                       SQLINTEGER * resultLength ); 

When you call SQLGetData() , you want ODBC to put the data into your fields[i].value buffer so you pass that address (and the displaySize ). Passing in a desiredDataType of SQL_C_CHAR tells ODBC to return each column in the form of a null-terminated string. SQLGetData() returns the actual field length in fields[i].resultLength ”if the field is NULL , you will get back the value SQL_NULL_DATA.

Lines 143 “146 print each field (left-justified within a fields[i].headerLength space).

Finally, clean up after yourself by freeing the value buffers and then the resultField array:

 158 int main( int argc, char * argv[] ) 159 { 160   SQLRETURN   res; 161   SQLHENV     env; 162   SQLHDBC     con; 163   SQLCHAR     fullConnectStr[SQL_MAX_OPTION_STRING_LENGTH]; 164   SQLSMALLINT requiredLength; 165 166   res = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env ); 167 168   if( SQL_OK( res )) 169   { 170     res = SQLSetEnvAttr( env, 171                          SQL_ATTR_ODBC_VERSION, 172                          (SQLPOINTER)SQL_OV_ODBC2, 173                          0 ); 174     if( !SQL_OK( res )) 175     { 176       printErrors( env, SQL_NULL_HDBC, SQL_NULL_HSTMT ); 177       exit( -1 ); 178     } 179 180     res = SQLAllocHandle( SQL_HANDLE_DBC, env, &con ); 181     if( !SQL_OK( res )) 182     { 183       printErrors( env, SQL_NULL_HDBC, SQL_NULL_HSTMT ); 184       exit( -2 ); 185     } 186 187     res = SQLDriverConnect( con, 188                             (SQLHWND)NULL, 189                             argv[1], SQL_NTS, 190                             fullConnectStr, 191                             sizeof( fullConnectStr ), 192                             &requiredLength, 193                             SQL_DRIVER_NOPROMPT ); 194 195 196     if( !SQL_OK( res )) 197     { 198       printErrors( SQL_NULL_HENV, con, SQL_NULL_HSTMT ); 199       exit( -3 ); 200     } 201 202     printf( "connection ok\n" ); 203 204     executeStmt( con, argv[2] ); 205 206     res = SQLDisconnect( con ); 207     if( !SQL_OK( res )) 208     { 209       printErrors( SQL_NULL_HENV, con, SQL_NULL_HSTMT ); 210       exit( -4 ); 211     } 212 213     res = SQLFreeHandle( SQL_HANDLE_DBC, con ); 214     if( !SQL_OK( res )) 215     { 216       printErrors( SQL_NULL_HENV, con, SQL_NULL_HSTMT ); 217       exit( -5 ); 218     } 219 220     res = SQLFreeHandle( SQL_HANDLE_ENV, env ); 221     if( !SQL_OK( res )) 222     { 223       printErrors( env, SQL_NULL_HDBC, SQL_NULL_HSTMT ); 224       exit( -6 ); 225     } 226   } 227 228   exit( 0 ); 229 230 } 

The main() function for client3.c is identical to that in client2.c .

When you run this program, the single command-line argument should be a SQLDRIVERCONNECT() connection string:

 $ ./client3 "DSN=MoviesDSN; UID=korry; PWD=cows" 

This example has shown you the easiest way to execute a query and process results in an ODBC application, but using SQLExecDirect() and SQLGetData() will not always give you the best performance. The next client shows a method that is more complex, but performs better.