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.

Part I: General PostgreSQL Use

Introduction to PostgreSQL and SQL

Working with Data in PostgreSQL

PostgreSQL SQL Syntax and Use

Performance

Part II: Programming with PostgreSQL

Introduction to PostgreSQL Programming

Extending PostgreSQL

PL/pgSQL

The PostgreSQL C APIlibpq

A Simpler C APIlibpgeasy

The New PostgreSQL C++ APIlibpqxx

Embedding SQL Commands in C Programsecpg

Using PostgreSQL from an ODBC Client Application

Using PostgreSQL from a Java Client Application

Using PostgreSQL with Perl

Using PostgreSQL with PHP

Using PostgreSQL with Tcl and Tcl/Tk

Using PostgreSQL with Python

Npgsql: The .NET Data Provider

Other Useful Programming Tools

Part III: PostgreSQL Administration

Introduction to PostgreSQL Administration

PostgreSQL Administration

Internationalization and Localization

Security

Replicating PostgreSQL Data with Slony

Contributed Modules

Index



PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
ISBN: 735712573
EAN: N/A
Year: 2004
Pages: 261

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net