Client 2 - Adding Error Checking

Client 2 ”Adding Error Checking

Now let's move on to see how you can detect and respond to errors. When you create an application that works by calling API functions, you can usually tell whether an operation succeeded or failed by examining the return value. In an ecpg application, your program is not calling PostgreSQL functions (at least at the source code level), so you can't just examine a return code.

The sqlca Structure

Instead, the ecpg library uses a special data structure, the sqlca , to communicate failure conditions. Here is the definition of the sqlca structure (from sqlca.h ):

 struct sqlca {    char      sqlcaid[8];    long      sqlabc;    long      sqlcode;    struct    {       int   sqlerrml;       char   sqlerrmc[SQLERRMC_LEN];    } sqlerrm;    char      sqlerrp[8];    long      sqlerrd[6];    char      sqlwarn[8];    char      sqlext[8]; }; 

You don't #include this file as you would with most header files. The ecpg preprocessor offers a special directive that you should use ^[2] :

^[2] Starting with PostgreSQL release 7.2, sqlca is automatically included in every ecpg program. You don't have to include it yourself.

 EXEC SQL INCLUDE sqlca; 

The difference between a #include and an EXEC SQL INCLUDE is that the ecpg preprocessor can see files that are included using the second form ”ecpg ignores #include s. That doesn't mean that you can't use #include files, just remember that the inclusion occurs after the ecpg preprocess has finished its work.

The contents of the sqlca structure might seem a bit weird. Okay, they don't just seem weird ”they are weird.

Let's walk through the members of the sqlca structure. PostgreSQL won't use many of the fields in the sqlca structure ”that structure was inherited from the SQL standard.

First, we'll look at the fields that never change. The sqlaid array always contains the string 'SQLCA' . Why? I don't know ”history, I suppose. The sqlabc member always contains the size of the sqlca structure. sqlerrp always contains the string 'NOT SET' .

Now let's look at the interesting parts of a sqlca .

The sqlcode member is an error indicator. If the most recent (ecpg library) operation was completely successful, sqlcode will be set to zero. If the most recent operation succeeded, but it was a query that returned no data, sqlcode will contain the value ECPG_NOT_FOUND ^[3] (or 100 ). sqlcode will also be set to ECPG_NOT_FOUND if you execute an UPDATE , INSERT , or DELETE that affects zero rows. If an error occurs, sqlcode will contain a negative number.

^[3] The symbolic names for sqlcode values (such as ECPG_NOT_FOUND ) are automatically #defined for you by the ecpg preprocessor.

If sqlca.sqlcode contains a non-zero value, the sqlerrm structure will contain a printable error message. sqlerrm.sqlerrmc will contain the null- terminated text of the message and sqlerrm.sqlerrml will contain the length of the error message.

The sqlerrd array also contains useful information. After executing a SELECT statement, sqlerrd[2] will contain the number of rows returned by the query. After executing an INSERT , UPDATE , or DELETE statement, sqlerrd[1] will contain the oid (object ID) of the most recently affected row, and sqlerrd[2] will contain the number of rows affected.

The sqlwarn array is used to tell you about warnings. When you retrieve data from PostgreSQL, sqlwarn[1] will be set to W if any of the data has been truncated. Truncation can occur, for example, when you retrieve a varchar column into a buffer too small to contain the actual value. sqlwarn[2] is set to W whenever a non-fatal error (such as executing a COMMIT outside of the context of a transaction) occurs. If any member of the sqlwarn array contains a W , sqlwarn[0] will contain a W .

I've modified the previous client application ( client1b.pgc ) so that it prints an error message if the connection attempt fails. Here is client2a.pgc :

 1  /* client2a.pgc */ 2 3  EXEC SQL INCLUDE sqlca; 4 5  #include <stdio.h> 6 7  int main( ) 8  { 9      EXEC SQL CONNECT TO movies; 10 11     if( sqlca.sqlcode == 0 ) 12         printf( "Connected to 'movies'\n" ); 13     else 14         printf( "Error: %s\n", sqlca.sqlerrm.sqlerrmc ); 15 16     EXEC SQL DISCONNECT; 17 18     return( 0 ); 19 } 

At line 11, check sqlca.sqlcode . If it contains a zero, your connection attempt was successful. If sqlca.sqlcode contains any other value, an error has occurred and you find the error message in sqlca.sqlerrm.sqlerrmc . If you want to try this code, you can induce an error by shutting down your PostgreSQL server and then running client2a .

Now let's modify this client slightly so that you can experiment with different error-processing scenarios:

 1 /* client2b.pgc */  2  3 EXEC SQL INCLUDE sqlca;  4  5 #include <stdio.h>  6  7 void dump_sqlca( void )  8 {  9     int     i; 10 11     printf("sqlca\n" ); 12     printf("sqlaid                  - %s\n",sqlca.sqlcaid ); 13     printf("sqlabc                  - %d\n",sqlca.sqlabc ); 14     printf("sqlcode                 - %d\n",sqlca.sqlcode ); 15     printf("sqlerrml                - %d\n",sqlca.sqlerrm.sqlerrml); 16     printf("sqlerrmc                - %s\n",sqlca.sqlerrm.sqlerrmc); 17     printf("sqlerrp                 - %s\n",sqlca.sqlerrp ); 18     printf("sqlerrd[1] (oid)        - %d\n",sqlca.sqlerrd[1] ); 19     printf("sqlerrd[2] (rows)       - %d\n",sqlca.sqlerrd[2] ); 20     printf("sqlwarn[0]              - %c\n",sqlca.sqlwarn[0] ); 21     printf("sqlwarn[1] (truncation) - %c\n",sqlca.sqlwarn[1] ); 22     printf("sqlwarn[2] (non-fatal)  - %c\n",sqlca.sqlwarn[2] ); 23 } 24 25 int main( int argc, char * argv[] ) 26 { 27     EXEC SQL BEGIN DECLARE SECTION; 28     char * url; 29     EXEC SQL END DECLARE SECTION; 30 31     url = argv[1] ? argv[1] : ""; 32 33     EXEC SQL CONNECT TO :url; 34 35     if( sqlca.sqlcode == 0 ) 36         printf( "Connected to '%s'\n", url ); 37     else 38     { 39         printf( "Error: %s\n", sqlca.sqlerrm.sqlerrmc ); 40         dump_sqlca( ); 41     } 42 43     EXEC SQL DISCONNECT; 44 45     return( 0 ); 46 } 

In client2b.pgc , I've added a new function, dump_sqlca() , which simply prints the contents of the sqlca structure. I've also changed the main() function so that you can include a connection URL on the command line. We haven't talked about the EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION directives yet, so don't worry if they aren't familiar ”I'll cover that topic in a moment. I'll also show you how to refer to host variables (that :url thing in line 33) in EXEC SQL statements.

Compile this program and run it a few times, feeding it connection URLs that will result in errors. Here is an example of what you might see:

 $ ./client2b foo Error: Could not connect to database foo in line 32. sqlca sqlaid                  - SQLCA   O sqlabc                  - 140 sqlcode                 - -402 sqlerrml                - 45 sqlerrmc                - Could not connect to database foo in line 32. sqlerrp                 - NOT SET sqlerrd[1] (oid)        - 0 sqlerrd[2] (rows)       - 0 sqlwarn[0]              - sqlwarn[1] (truncation) - sqlwarn[2] (non-fatal)  - 

Table 11.2 shows some of the error messages you might encounter. This list is not exhaustive. Some of the messages in this table may not make sense to you until later in this chapter.

Table 11.2. EPCG Runtime Errors

Error	Explanation
ECPG_NOT_FOUND	No data found
ECPG_OUT_OF_MEMORY	Out of memory
ECPG_UNSUPPORTED	Unsupported type typename
ECPG_TOO_MANY_ARGUMENTS	Too many arguments
ECPG_TOO_FEW_ARGUMENTS	Too few arguments
ECPG_TOO_MANY_MATCHES	You selected more rows than will fit into the space you allocated
ECPG_INT_FORMAT	Incorrectly formatted int type typename
ECPG_UINT_FORMAT	Incorrectly formatted unsigned type typename
ECPG_FLOAT_FORMAT	Incorrectly formatted floating point type typename
ECPG_CONVERT_BOOL	Unable to convert to bool
ECPG_EMPTY	Empty query
ECPG_MISSING_INDICATOR	NULL value without indicator
ECPG_NO_ARRAY	Variable is not an array
ECPG_DATA_NOT_ARRAY	Data read from backend is not an array
ECPG_NO_CONN	No such connection connection_name
ECPG_NOT_CONN	Not connected to 'database'
ECPG_INVALID_STMT	Invalid statement name statement_name
ECPG_UNKNOWN_DESCRIPTOR	Descriptor name not found
ECPG_INVALID_DESCRIPTOR_INDEX	Descriptor index out of range
ECPG_UNKNOWN_DESCRIPTOR_ITEM	Unknown descriptor item item
ECPG_VAR_NOT_NUMERIC	Variable is not a numeric type
ECPG_VAR_NOT_CHAR	Variable is not a character type
ECPG_TRANS	Error in transaction processing
ECPG_CONNECT	Could not connect to database database_name
ECPG_PSQL	Generic PostgreSQL error

The ecpg preprocessor provides an alternative method for detecting and handling errors: the EXEC SQL WHENEVER directive. The general form for a WHENEVER directive is

 EXEC SQL WHENEVER  condition action  ; 

where condition can be any of the following:

• SQLERROR ” Occurs whenever sqlca.sqlcode is less than zero

• SQLWARNING ” Occurs whenever sqlca.sqlwarn[0] contains W

• NOT FOUND ” Occurs whenever sqlca.sqlcode is ECPG_NOT_FOUND (that is, when a query returns no data)

When you use the EXEC SQL WHENEVER directive, you are telling the ecpg preprocessor to insert extra code into your program. Each time ecpg emits an ecpg library call that might raise a condition (at runtime), it follows that function call with code to detect and handle the condition that you specify. The exact format of the error-handling code depends on the action that you use. You can specify any of the following action s:

• SQLPRINT ” Calls the sqlprint() function to display an error message to the user ; the sqlprint() function simply prints "sql error " followed by the contents of the sqlca.sqlerrm.sqlerrmc string

• STOP ” Calls exit(1) ; this will cause your application to terminate whenever the specified condition arises

• GOTO label-name ” Causes your application to goto the label specified by label-name whenever the specified condition arises

• GO TO label-name ” Same as GOTO

• CALL function-name ( arguments ) ” Causes your application to call the given function-name with the given arguments whenever the specified condition arises

• DO function-name( arguments ) ” Same as CALL

• CONTINUE ” Causes your application to execute a continue statement whenever the specified condition arises; this should be used only inside of a loop

• BREAK ” Causes your application to execute a break statement whenever the specified condition arises; this should be used only inside loops or a switch statement

You may find it useful to examine the sqlca structure, even when you use EXEC SQL WHENEVER to intercept errors or warnings. EXEC SQL WHENEVER is a convenient way to detect error conditions, but sometimes you will find it overly broad ”different error conditions can produce the same result. By interrogating the sqlca structure, you can still use EXEC SQL WHENEVER to trap the errors, but treat each condition differently.

Here is client2c.pgc . I've modified the first client in this section (client2a.pgc) so that it uses the EXEC SQL WHENEVER directive to intercept a connection error.

 1  2 /* client2c.pgc */  3  4 EXEC SQL INCLUDE sqlca;  5  6 #include <stdio.h>  7  8 int main( int argc, char * argv[] )  9 { 10     EXEC SQL BEGIN DECLARE SECTION; 11       char * url; 12     EXEC SQL END DECLARE SECTION; 13     url = argv[1] ? argv[1] : ""; 14 15     EXEC SQL WHENEVER SQLERROR SQLPRINT; 16 17     EXEC SQL CONNECT TO :url; 18 19     EXEC SQL DISCONNECT; 20 21     return( 0 ); 22 } 

Let's run this program in such a way that a connection error occurs:

 $ ./client2c foo sql error Could not connect to database foo in line 17. sql error No such connection CURRENT in line 19. 

Notice that I received two error messages. The first error occurred when my connection attempt failed; the second occurred when I tried to tear down a nonexistent connection. That's an important thing to remember ”the EXEC SQL WHENEVER directive continues to affect your epcg code until you change the action associated with a given condition .

It's important to understand that EXEC SQL WHENEVER is a preprocessor directive, not a true statement. A directive affects the actions of the ecpg preprocessor from the point at which it is encountered in the source code. This means, for example, that if you include an EXEC SQL WHENEVER directive within an if statement, you probably won't get the results you were hoping for. Consider the following code:

 if( TRUE ) { EXEC SQL WHENEVER SQLERROR SQLPRINT; } else { EXEC SQL WHENEVER SQLERROR STOP; } EXEC SQL CONNECT TO movies; 

Looking at this code, you might expect that a connection failure would result in a call to the sqlprint() function. That's not what you'll get. Instead, the ecpg preprocessor will arrange for the exit() function to be called if the connection attempt fails. Preprocessor directives are not executable statements; they affect the code produced by the preprocessor. As the preprocessor reads through your source code, it keeps track of the action that you choose for each condition . Each time the preprocessor encounters an EXEC SQL WHENEVER directive, it remembers the new action and applies it to any EXEC SQL statements further down the source code. So, with EXEC SQL WHENEVER , the order of appearance (within the source file) is important, but the order of execution is not.

I recommend compiling a few ecpg programs that include the various EXEC SQL WHENEVER directives and then examining the resulting C code to better understand how they will affect your programs.