Client 2 - Adding Error Checking

Client 2 ”Adding Error Checking

In the previous example, I omitted a lot of code that would normally appear in a real-world application. In this section, I'll add some simple error-handling functions and show you how to properly free up the resources (handles) that you create. I'll also use a more complex and more flexible connection function: SQLDriverConnect() .

In the previous section, I mentioned that most ODBC functions return two different values to indicate a successful completion: SQL_SUCCESS and SQL_SUCCESS_WITH_INFO . To make your ODBC programming life a little easier, you can use the following function to check for success or failure:

 static bool SQL_OK( SQLRETURN result ) {   if( result == SQL_SUCCESS  result == SQL_SUCCESS_WITH_INFO )     return( TRUE );   else     return( FALSE ); } 

A typical call to SQL_OK() might look like this:

 if( SQL_OK( SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &handle )) {    ... } 

So what's the difference between SQL_SUCCESS and SQL_SUCCESS_WITH_INFO ? The simple answer is that SQL_SUCCESS implies that a function succeeded; SQL_SUCCESS_WITH_INFO also means that a function succeeded, but more information is available. For example, if you try to REVOKE a privilege from a user, but the user did not have the privilege to begin with, you'll get a SQL_SUCCESS_WITH_INFO result. The request is completed successfully, but you might want to know the extra information.

In an ODBC 2.x application, you call the SQLError() to retrieve any extended return information. If you call SQLError() after receiving a SQL_SUCCESS result, the SQLError() function will fail. Here is the function prototype for the SQLError() function:

 SQLRETURN SQLError(     SQLHENV       envHandle,     SQLHDBC       conHandle,     SQLHSTMT      stmtHandle,     SQLCHAR     * sqlState,     SQLINTEGER  * nativeError,     SQLCHAR     * messageText,     SQLSMALLINT   messageTextLength,     SQLSMALLINT * requiredLength ); 

Notice that the SQLError() function can accept three different handles ”when you call SQLError() , you provide only one of the three. For example, if you receive an error status on a statement handle, you would call SQLError() , as follows :

 SQLError( SQL_NULL_HENV, SQL_NULL_HDBC, stmtHandle, ... ); 

Table 12.2 shows how you would call SQLError() given each handle type.

Table 12.2. Handle Types and SQLError() Parameters

Handle Type	SQLError() Parameters
SQLHENV	envHandle , SQL_NULL_HDBC , SQL_NULL_HSTMT ,
SQLHDBC	SQL_NULL_HENV , conHandle , SQL_NULL_HSTMT ,
SQLHSTMT	SQL_NULL_HENV , SQL_NULL_HDBC , stmtHandle ,

If the SQLError() function succeeds ^[2] , it returns three pieces of status information.

^[2] The SQLError() will fail if you give it a bad handle or if there are no more messages to report to the application.

The first is called the SQLSTATE . The sqlState parameter should point to a six-byte SQLCHAR array. SQLError() will fill in the sqlState array with a five-character code (and a NULL-terminator). ODBC uses the SQLSTATE as a way to provide status information in a database-independent format. A SQLSTATE code is composed of a two-character class followed by a three-character subclass . SQLSTATE code ' 00000 ' means 'successful completion' and is equivalent to SQL_SUCCESS . SQLSTATE values that begin with the class '01' are warnings. Any other SQLSTATE class indicates an error. Table 12.3 shows a few common SQLSTATE values.

Table 12.3. Common SQLState Values

SQLState	Meaning
00000	Successful completion
01004	Warning-string data, right truncation (that is, you tried to select 20 bytes into a 10-byte buffer)
23000	Integrity constraint violation (for example, you tried to add a duplicate key value into a unique index)
42000	Syntax error or access rule violation
HY010	Function sequence error
42S02	Base table (or view) not found

The second piece of information returned by SQLError() is a native error number. The driver returns the native error number ”you have to know what kind of database your application is connected to before you can make sense of the native error numbers. Not all drivers return native error numbers .

The most useful information returned by SQLError() is the text of an error message. The last three parameters to SQLError() are used to retrieve the error message. The messageText parameter points to an array of SQLCHAR s. This array should be SQL_MAX_MESSAGE_LENGTH+1 bytes long. messageTextLength tells SQLError() how many bytes it can write into *messageText . SQLError() writes the number of bytes required to contain the message text into the SQLSMALLINT pointed to by the requiredLength ^[3] parameter.

^[3] Many API functions need to return variable-length information ”somehow, the caller must know how much space to allocate for the return information. A common solution to this problem is to call a function twice. When you make the first call, you tell the function that you allocated 0 bytes for the variable-length information. The function tells you how much space is required by setting something like the requiredLength parameter described previously. After you know how much space is required, you allocate the required number of bytes and call the function a second time. In the case of SQLError() , the requiredLength parameter is pretty pointless. We can't call SQLError() more than once per diagnostic because the diagnostic is discarded as soon as SQLError() retrieves it from the given handle.

Listing 12.2 shows the client1.c example, fleshed out with some error-handling code.

Listing 12.2 odbc/client2.c

 1 /* client2.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 static bool SQL_OK( SQLRETURN result ) 11 { 12   if( result == SQL_SUCCESS  result == SQL_SUCCESS_WITH_INFO ) 13     return( TRUE ); 14   else 15     return( FALSE ); 16 } 17 

You've already seen the SQL_OK() function ”it simply checks for the two success codes returned by ODBC.

 18 static bool printErrors( SQLHENV  envHandle, 19                          SQLHDBC  conHandle, 20                          SQLHSTMT stmtHandle ) 21 { 22   SQLRETURN   result; 23   SQLCHAR     sqlState[6]; 24   SQLINTEGER  nativeError; 25   SQLSMALLINT requiredLength; 26   SQLCHAR     messageText[SQL_MAX_MESSAGE_LENGTH+1]; 27 28   do 29   { 30     result = SQLError( envHandle, 31                        conHandle, 32                        stmtHandle, 33                        sqlState, 34                        &nativeError, 35                        messageText, 36                        sizeof( messageText ), 37                        &requiredLength ); 38 39     if( SQL_OK( result )) 40       { 41         printf( "SQLState     = %s\n", sqlState ); 42         printf( "Native error = %d\n", nativeError ); 43         printf( "Message text = %s\n", messageText ); 44       } 45   } while( SQL_OK( result )); 46 } 47 

The printErrors() function is new. You call SQLError() until it returns a failure code. Why would you call SQLError() multiple times? Because each ODBC function can return multiple errors. Remember, each time SQLError() returns successfully, it removes a single diagnostic from the given handle. If you don't retrieve all the errors from a handle, they will be discarded (and lost) the next time you use that handle.

 48 int main( int argc, char * argv[] )  49 {  50   SQLRETURN   res;  51   SQLHENV     env;  52   SQLHDBC     con;  53   SQLCHAR     fullConnectStr[SQL_MAX_OPTION_STRING_LENGTH];  54   SQLSMALLINT requiredLength;  55  56   res = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env );  57  58   if( SQL_OK( res ))  59   {  60     res = SQLSetEnvAttr( env,  61                          SQL_ATTR_ODBC_VERSION,  62                          (SQLPOINTER)SQL_OV_ODBC2,  63                          0 );  64     if( !SQL_OK( res ))  65     {  66       printErrors( env, SQL_NULL_HDBC, SQL_NULL_HSTMT );  67       exit( -1 );  68     }  69  70     res = SQLAllocHandle( SQL_HANDLE_DBC, env, &con );  71     if( !SQL_OK( res ))  72     {  73       printErrors( env, SQL_NULL_HDBC, SQL_NULL_HSTMT );  74       exit( -2 );  75     }  76  77     res = SQLDriverConnect( con,  78                             (SQLHWND)NULL,  79                             argv[1], SQL_NTS,  80                             fullConnectStr,  81                             sizeof( fullConnectStr ),  82                             &requiredLength,  83                             SQL_DRIVER_NOPROMPT );  84  85  86     if( !SQL_OK( res ))  87     {  88       printErrors( SQL_NULL_HENV, con, SQL_NULL_HSTMT );  89       exit( -3 );  90     }  91  92     printf( "connection ok...disconnecting\n" );  93  94     res = SQLDisconnect( con );  95     if( !SQL_OK( res ))  96     {  97       printErrors( SQL_NULL_HENV, con, SQL_NULL_HSTMT );  98       exit( -4 );  99     } 100 101     res = SQLFreeHandle( SQL_HANDLE_DBC, con ); 102     if( !SQL_OK( res )) 103     { 104       printErrors( SQL_NULL_HENV, con, SQL_NULL_HSTMT ); 105       exit( -5 ); 106     } 107 108     res = SQLFreeHandle( SQL_HANDLE_ENV, env ); 109     if( !SQL_OK( res )) 110     { 111       printErrors( env, SQL_NULL_HDBC, SQL_NULL_HSTMT ); 112       exit( -6 ); 113     } 114   } 115 116   exit( 0 ); 117 118 } 

There are three new features in the main() function.

First, you'll notice that I have littered the code with calls to printErrors() . You call printErrors() any time an ODBC function returns a failure status. You could also call printErrors() when you get a SQL_SUCCESS_WITH_INFO status, but in most cases, the extra information is uninteresting.

Notice that you exit as soon as an error is encountered . Each call to exit() specifies a different value: If the program succeeds, you return ; in all other cases, you return a unique negative number. The return value is given to the calling program (usually a shell) and is used to check for success or failure.

The other thing that's different between this version of main() and the version that I included in client1.c is that you use the SQLDriverConnect() function instead of SQLConnect() . The SQLDriverConnect() function is a more powerful version of SQLConnect() . Whereas SQLConnect() allows you to specify three connection properties (the data source name , user id, and password), SQLDriverConnect() can accept an arbitrary number of properties. In fact, the following two calls are ( roughly ) equivalent:

 SQLConnect( con, "MoviesDSN", SQL_NTS, "korry", SQL_NTS, "cows", SQL_NTS ); SQLDriverConnect( con, (SQLHWND)NULL,                   "DSN=MoviesDSN;UID=korry;PWD=cows", SQL_NTS, ... ); 

Here is the function prototype for SQLDriverConnect() :

 SQLRETURN SQLDriverConnect(     SQLHDBC       connectionHandle,     SQLHWND       windowHandle,     SQLCHAR     * connectStrIn,     SQLCHAR     * connectStrOut,     SQLSMALLINT   connectStrOutMax,     SQLSMALLINT * requiredBytes,     SQLUSMALLINT  driverCompletion ) 

The purpose of the first argument is pretty obvious ”you provide the connection handle that you want to connect.

The second argument might seem a bit mysterious ”what's a SQLHWND , and why would I need one to connect to a database? One of the differences between SQLDriverConnect() and SQLConnect() is that SQLDriverConnect() can prompt the user for more connection parameters. If you are running a graphical client application, you would expect to see a pop-up dialog if the database that you are connecting to requires more information. The SQLHWND parameter is used to provide a parent window handle that the driver can use to display a dialog. Under Windows, a SQLHWND is really a window handle (that is, a HWND ). There is no clear winner in the Unix GUI wars, so there is no standard data type that represents a window handle. The driver manager ignores the windowHandle parameter and just passes it along to the driver. Very few Unix-hosted ODBC drivers support a connection dialog when using SQLDriverConnect() . One driver that does support a connection dialog is the IBM DB2 driver If you are calling SQLDriverConnect() to connect to a DB2 database, you would pass in a Motif widget handle as the windowHandle parameter (if you are connecting to a DB2 database under Windows, you would pass in a HWND). Drivers that don't provide a connection dialog return an error if the connectStrIn parameter doesn't contain all the required information.

The third argument to SQLDriverConnect() is an ODBC connection string (this is not the same as a libpq connection string). An ODBC connection string is a semicolon-delimited collection of keyword=value properties. The ODBC driver manager looks for the DSN property to determine which data source you want to connect to. After the driver is loaded, the driver manager passes all the properties to the driver. The PostgreSQL driver understands the following properties shown in Table 12.4.

Table 12.4. PostgreSQL/ODBC Connection String Properties

Property	Description
DSN	Data source name
UID	User ID
PWD	Password
SERVER	Server's IP address or hostname
PORT	TCP port number on server
DATABASE	PostgreSQL database name

(The PostgreSQL ODBC driver supports other connection properties. See the documentation that comes with the driver for a complete list.)

The next three arguments ( connectStrOut , connectStrOutMax , and requiredBytes ) are used to return a complete connection string to the client application. If you successfully connect to a database, the driver will populate *connectStrOut with a null- terminated string that contains all the connection properties that the driver used. For example, if you call SQLDriverConnect() with the following connection string:

 "DSN=MoviesDSN; UID=korry; PWD=cows" 

the driver will return a string such as

 DSN=MoviesDsn; DATABASE=movies; SERVER=localhost; PORT=5432; UID=korry; PWD=; READONLY=No; PROTOCOL=6.4; FAKEOIDINDEX=No; SHOWOIDCOLUMN=No; ROWVERSIONING=No; SHOWSYSTEMTABLES=No; CONNSETTINGS='; 

This is assuming that the video-store data source uses a PostgreSQL driver. You may have noticed that the complete connection string is composed from the set of connection properties that this driver understands ”most of the properties are defaulted from the data source.

If the buffer that you provide is too short for the entire connection string, SQLDriverConnect() will truncate the string and will return the required length in *requiredBytes .

You use the final parameter to SQLDriverConnect() to indicate how much assistance you want if the connection string is incomplete. Acceptable values for driverCompletion are shown in Table 12.5.

Table 12.5. Values for SQLDriverConnect().driverCompletion

Value	Description
SQL_DRIVER_PROMPT	The user sees a connection dialog, even if it is not required.
SQL_DRIVER_COMPLETE	The user sees a connection dialog if the connection string does not contain all required information. The connection dialog prompts the user for required and optional connection properties.
SQL_DRIVER_COMPLETE_REQUIRED	The user sees a connection dialog if the connection string does not contain all required information. The connection dialog only prompts the user for required connection properties.
SQL_DRIVER_NOPROMPT	If the connection string does not contain all required information, SQLDriverConnect() will return SQL_ERROR , and the user will not be prompted (by the driver).

Most open -source ODBC drivers support only the SQL_DRIVER_NOPROMPT option. If you ask for a different completion type, it will be treated like SQL_DRIVER_NOPROMPT .

The last thing that I'll explain about this client is the teardown code. To properly clean up the client application, you have to disconnect the connection handle (using SQLDisconnect() ) and then free the connection and environment handles using SQLFreeHandle() . The order in which you tear down connections is important. You won't be able to free the connection handle until you disconnect it. You won't be able to free an environment handle until all the connection handles have been disconnected and freed.

If you want to run this program, the single command-line argument is a SQLDriverConnect() connection string. For example:

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

In the next section, I'll introduce a new handle type ”the SQLHSTMT statement handle. The parent of a SQLHSTMT is a connection handle. You must free all child statement handles before you can free a connection handle.

This section was rather long, but now you know how to connect to a database, how to detect errors, and how to properly tear down an ODBC connection. The next section describes how to process a simple query in an ODBC client.