Error Handling

PostgreSQL 7.3.4 Documentation
Prev | Chapter 4. ECPG - Embedded SQL in C | Next

# 4.6. Error Handling

The embedded SQL interface provides a simplistic and a complex way to handle exceptional conditions in a program. The first method causes a message to printed automatically when a certain condition occurs. For example:

    EXEC SQL WHENEVER sqlerror sqlprint;

or

    EXEC SQL WHENEVER not found sqlprint;

This error handling remains enabled throughout the entire program.

> Note: This is not an exhaustive example of usage for the `EXEC SQL WHENEVER` statement. Further examples of usage may be found in SQL manuals (e.g., The LAN TIMES Guide to SQL by Groff and Weinberg).

For a more powerful error handling, the embedded SQL interface provides a `struct` and a variable with the name sqlca as follows:

    struct sqlca
    {
        char sqlcaid[8];
        long sqlabc;
        long sqlcode;
        struct
        {
            int sqlerrml;
            char sqlerrmc[70];
        } sqlerrm;
        char sqlerrp[8];

        long sqlerrd[6];
        /* 0: empty                                         */
        /* 1: OID of processed tuple if applicable          */
        /* 2: number of rows processed in an INSERT, UPDATE */
        /*    or DELETE statement                           */
        /* 3: empty                                         */
        /* 4: empty                                         */
        /* 5: empty                                         */

        char sqlwarn[8];
        /* 0: set to 'W' if at least one other is 'W'       */
        /* 1: if 'W' at least one character string          */
        /*    value was truncated when it was               */
        /*    stored into a host variable.                  */
        /* 2: empty                                         */
        /* 3: empty                                         */
        /* 4: empty                                         */
        /* 5: empty                                         */
        /* 6: empty                                         */
        /* 7: empty                                         */

        char sqlext[8];
    } sqlca;

(Many of the empty fields may be used in a future release.)

If no error occurred in the last SQL statement, sqlca.sqlcode will be 0 (`ECPG_NO_ERROR`). If sqlca.sqlcode is less that zero, this is a serious error, like the database definition does not match the query. If it is greater than zero, it is a normal error like the table did not contain the requested row.

sqlca.sqlerrm.sqlerrmc will contain a string that describes the error. The string ends with the line number in the source file.

These are the errors that can occur:

`-12, Out of memory in line %d.`

  Should not normally occur. This indicates your virtual memory is exhausted.

`-200 (ECPG_UNSUPPORTED): Unsupported type %s on line %d.`

  Should not normally occur. This indicates the preprocessor has generated something that the library does not know about. Perhaps you are running incompatible versions of the preprocessor and the library.

`-201 (ECPG_TOO_MANY_ARGUMENTS): Too many arguments line %d.`

  This means that the server has returned more arguments than we have matching variables. Perhaps you have forgotten a couple of the host variables in the `INTO :var1,:var2` list.

`-202 (ECPG_TOO_FEW_ARGUMENTS): Too few arguments line %d.`

  This means that the server has returned fewer arguments than we have host variables. Perhaps you have too many host variables in the `INTO :var1,:var2` list.

`-203 (ECPG_TOO_MANY_MATCHES): Too many matches line %d.`

  This means the query has returned several rows but the variables specified are not arrays. The `SELECT` command was not unique.

`-204 (ECPG_INT_FORMAT): Not correctly formatted int type: %s line %d.`

  This means the host variable is of type `int` and the field in the PostgreSQL database is of another type and contains a value that cannot be interpreted as an `int`. The library uses `strtol()` for this conversion.

`-205 (ECPG_UINT_FORMAT): Not correctly formatted unsigned type: %s line %d.`

  This means the host variable is of type `unsigned int` and the field in the PostgreSQL database is of another type and contains a value that cannot be interpreted as an `unsigned int`. The library uses `strtoul()` for this conversion.

`-206 (ECPG_FLOAT_FORMAT): Not correctly formatted floating-point type: %s line %d.`

  This means the host variable is of type `float` and the field in the PostgreSQL database is of another type and contains a value that cannot be interpreted as a `float`. The library uses `strtod()` for this conversion.

`-207 (ECPG_CONVERT_BOOL): Unable to convert %s to bool on line %d.`

  This means the host variable is of type `bool` and the field in the PostgreSQL database is neither 't' nor 'f'.

`-208 (ECPG_EMPTY): Empty query line %d.`

  The query was empty. (This cannot normally happen in an embedded SQL program, so it may point to an internal error.)

`-209 (ECPG_MISSING_INDICATOR): NULL value without indicator in line %d.`

  A null value was returned and no null indicator variable was supplied.

`-210 (ECPG_NO_ARRAY): Variable is not an array in line %d.`

  An ordinary variable was used in a place that requires an array.

`-211 (ECPG_DATA_NOT_ARRAY): Data read from backend is not an array in line %d.`

  The database returned an ordinary variable in a place that requires array value.

`-220 (ECPG_NO_CONN): No such connection %s in line %d.`

  The program tried to access a connection that does not exist.

`-221 (ECPG_NOT_CONN): Not connected in line %d.`

  The program tried to access a connection that does exist but is not open.

`-230 (ECPG_INVALID_STMT): Invalid statement name %s in line %d.`

  The statement you are trying to use has not been prepared.

`-240 (ECPG_UNKNOWN_DESCRIPTOR): Descriptor %s not found in line %d.`

  The descriptor specified was not found. The statement you are trying to use has not been prepared.

`-241 (ECPG_INVALID_DESCRIPTOR_INDEX): Descriptor index out of range in line %d.`

  The descriptor index specified was out of range.

`-242 (ECPG_UNKNOWN_DESCRIPTOR_ITEM): Descriptor %s not found in line %d.`

  The descriptor specified was not found. The statement you are trying to use has not been prepared.

`-243 (ECPG_VAR_NOT_NUMERIC): Variable is not a numeric type in line %d.`

  The database returned a numeric value and the variable was not numeric.

`-244 (ECPG_VAR_NOT_CHAR): Variable is not a character type in line %d.`

  The database returned a non-numeric value and the variable was numeric.

`-400 (ECPG_PGSQL): Postgres error: %s line %d.`

  Some PostgreSQL error. The message contains the error message from the PostgreSQL backend.

`-401 (ECPG_TRANS): Error in transaction processing line %d.`

  PostgreSQL signaled that we cannot start, commit, or rollback the transaction.

`-402 (ECPG_CONNECT): Could not connect to database %s in line %d.`

  The connect to the database did not work.

`100 (ECPG_NOT_FOUND): Data not found line %d.`

  This is a "normal" error that tells you that what you are querying cannot be found or you are at the end of the cursor.

Prev | Home | Next
Passing Data | Up | Including Files