Embedding SQL Commands in C Programsecpg

Client 2Adding 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];
};

The sqlca structure differs slightly in newer versions of PostgreSQL:

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 sqlstate[5];
};

Notice that the last member of the original structure has been replaced by sqlstate[]. The (obsolete) sqlext[] member was never used. The new member, sqlstate[], provides a modern, standard-compliant way for ecpg to report error conditions to your application. In this chapter, we'll show you how to use both the old and new error-handling schemes. If you're writing an ecpg application that needs to run with older PostgreSQL servers, use the old scheme (which stores error codes in the sqlcode member). The sqlstate[] scheme is more powerful, more flexible, and complies with SQL standards. The new scheme was introduced to PostgreSQL in version 7.4.

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. The C preprocessor can see header files imported by #include or EXEC SQL INCLUDE, but the ecpg preprocessor can only see headers imported by EXEC SQL INCLUDE. That doesn't mean that you can't use #include files, but if you need to import a structure (or other definition) that the ecpg preprocessor must see, you should EXEC SQL INCLUDE it instead. Just remember that the #include 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 weirdthey are weird.

Let's walk through the members of the sqlca structure. PostgreSQL won't use many of the fields in the sqlca structurethat 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 knowhistory, 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 (sqlcode is the basis of the older error handling scheme). 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. The sqlstate member (not present in versions of PostgreSQL older than 7.4) also functions as an error indicator. If the most recent (ecpg library) operation was completely successful, sqlstate will contain the characters 00000. Note that sqlstate is not a null-terminated string; it's an array containing five ASCII characters. That means that you cannot use strcmp() to test the value of sqlstate; you have to use strncmp() instead. If sqlstate contains a value other than 00000, an error may have occurred. Appendix A of the PostgreSQL reference documentation contains a list of the values you may find in the sqlstate member. Most sqlstate values indicate that an error has occurred, but two values indicate success: 00000 means that the previous operation succeeded and 02000 means that the previous command returned zero rows. The ecpg library sets sqlcode every time it puts a value into sqlstate so you can use either structure member to check for errorsjust remember that sqlstate is designed to be standards-compliant and sqlcode is specific to PostgreSQL.

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

If sqlca.sqlstate contains a value other than 00000 (or 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 
6
7 int main( )
8 {
9 EXEC SQL CONNECT TO movies;
10
11 if( strncmp( sqlca.sqlstate, "00000", sizeof( sqlca.sqlstate )) == 0 )
12 printf( "Connected to 'movies'
" );
13 else
14 printf( "Error: %s
", sqlca.sqlerrm.sqlerrmc );
15
16 EXEC SQL DISCONNECT;
17
18 return( 0 );
19 }

At line 11, this application checks sqlca.sqlstate. If it contains 00000, your connection attempt was successful. If sqlca.sqlstate 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 
 6
 7 void dump_sqlca( void )
 8 {
 9 int i;
10
11 printf( "sqlca
" );
12 printf( "sqlaid - %s
", sqlca.sqlcaid );
13 printf( "sqlabc - %d
", sqlca.sqlabc );
14 printf( "sqlcode - %d
", sqlca.sqlcode );
15 printf( "sqlstate - %5.5s
", sqlca.sqlstate );
16 printf( "sqlerrml - %d
", sqlca.sqlerrm.sqlerrml );
17 printf( "sqlerrmc - %s
", sqlca.sqlerrm.sqlerrmc );
18 printf( "sqlerrp - %s
", sqlca.sqlerrp );
19 printf( "sqlerrd[1] (oid) - %d
", sqlca.sqlerrd[1] );
20 printf( "sqlerrd[2] (rows) - %d
", sqlca.sqlerrd[2] );
21 printf( "sqlwarn[0] - %c
", sqlca.sqlwarn[0] );
22 printf( "sqlwarn[1] (truncation) - %c
", sqlca.sqlwarn[1] );
23 printf( "sqlwarn[2] (non-fatal) - %c
", sqlca.sqlwarn[2] );
24 }
25
26 int main( int argc, char * argv[] )
27 {
28 EXEC SQL BEGIN DECLARE SECTION;
29 char * url;
30 EXEC SQL END DECLARE SECTION;
31
32 url = argv[1] ? argv[1] : "";
33
34 EXEC SQL CONNECT TO :url;
35
36 if( sqlca.sqlcode == 0 )
37 printf( "Connected to '%s'
", url );
38 else
39 {
40 printf( "Error: %s
", sqlca.sqlerrm.sqlerrmc );
41 dump_sqlca( );
42 }
43
44 EXEC SQL DISCONNECT;
45
46 return( 0 );
47 }

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 familiarI'll cover that topic in a moment. I'll also show you how to refer to host variables (that :url thing in line 34) 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 in line 34.
sqlca
sqlaid - SQLCA
sqlabc - 140
sqlcode - -402
sqlstate - 08001
sqlerrml - 42
sqlerrmc - Could not connect to database in line 34.
sqlerrp - NOT SET
sqlerrd[1] (oid) - 0
sqlerrd[2] (rows) - 0
sqlwarn[0] -
sqlwarn[1] (truncation) -
sqlwarn[2] (non-fatal) -

Table 11.1 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.1. ECPG Runtime Errors

Error	SQLSTATE	Explanation
ECPG_NOT_FOUND	02000	No data found
ECPG_OUT_OF_MEMORY	YE001	Out of memory
ECPG_UNSUPPORTED	YE000	Unsupported type typename
ECPG_TOO_MANY_ARGUMENTS	07001 or 07002	Too many arguments
ECPG_TOO_FEW_ARGUMENTS	07001 or 07002	Too few arguments
ECPG_TOO_MANY_MATCHES	21000	You selected more rows than will fit into the space you allocated
ECPG_INT_FORMAT	42804	Incorrectly formatted int type typename
ECPG_UINT_FORMAT	42804	Incorrectly formatted unsigned int type typename
ECPG_FLOAT_FORMAT	42804	Incorrectly formatted floating point type typename
ECPG_CONVERT_BOOL	42804	Unable to convert to bool
ECPG_EMPTY	YE000	Empty query
ECPG_MISSING_INDICATOR	22002	NULL value encountered without a corresponding indicator
ECPG_NO_ARRAY	42804	Variable is not an array
ECPG_DATA_NOT_ARRAY	42804	Data read from server is not an array
ECPG_NO_CONN	08003	No such connection connection_name
ECPG_NOT_CONN	YE000	Not connected to 'database'
ECPG_INVALID_STMT	26000	Invalid statement name statement_name
ECPG_UNKNOWN_DESCRIPTOR	33000	Descriptor name not found
ECPG_INVALID_DESCRIPTOR_INDEX	07009	Descriptor index out of range
ECPG_UNKNOWN_DESCRIPTOR_ITEM	YE000	Unknown descriptor item
ECPG_VAR_NOT_NUMERIC	07006	Variable is not a numeric type
ECPG_VAR_NOT_CHAR	07006	Variable is not a character type
ECPG_TRANS	08007	Error in transaction processing
ECPG_CONNECT	08001	Could not connected to database database_name
ECPG_PSQL	no equivalent	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 actions:

• 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 broaddifferent 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 
 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 rememberthe 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 C 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.