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.
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 resultFields 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 ", sqlState ); 58 printf( "Native error = %d ", nativeError ); 59 printf( "Message text = %s ", 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( " " ); 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( " " ); 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 0—so, the loop goes from 1 to columnCount rather than the usual 0 to columnCount-1; I won't use fields[0] for the sake of 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 retrieving and storing the metadata for a column, this function allocates a buffer large enough to hold the data for the column in the form of a null-terminated string. It also computes 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 TypesSo 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.7.
You can use SQLGetInfo(..., SQL_TXN_CAPABLE, ...) to find out about the transaction—processing capabilities of a database. |
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 " ); 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.