Error handling v16

ECPGPlus provides two methods to detect and handle errors in embedded SQL code. A client application can:

  • Examine the sqlca data structure for error messages and supply customized error handling for your client application.
  • Include EXEC SQL WHENEVER directives to instruct the ECPGPlus compiler to add error-handling code.

Error handling with sqlca

The SQL communications area (sqlca) is a global variable used by ecpglib to communicate information from the server to the client application. After executing a SQL statement such as an INSERT or SELECT statement, you can inspect the contents of sqlca to determine if the statement completed successfully or if the statement failed.

sqlca has the following structure:

struct
{
    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];

} sqlca;

Use the following directive to implement sqlca functionality:

EXEC SQL INCLUDE sqlca;

If you include the ecpg directive, you don't need to #include the sqlca.h file in the client application's header declaration.

The EDB Postgres Advanced Server sqlca structure contains the following members:

  • sqlcaid Contains the string: "SQLCA".

  • sqlabc sqlabc contains the size of the sqlca structure.

  • sqlcode The sqlcode member was deprecated with SQL 92. EDB Postgres Advanced Server supports sqlcode for backward compatibility. Use the sqlstate member when writing new code.

    sqlcode is an integer value. A positive sqlcode value indicates that the client application encountered a harmless processing condition. A negative value indicates a warning or error.

    If a statement processes without error, sqlcode contains a value of 0. If the client application encounters an error or warning during a statement's execution, sqlcode contains the last code returned.

    The SQL standard defines only a positive value of 100, which indicates that the most recent SQL statement processed returned or affected no rows. Since the SQL standard doesn't define other sqlcode values, be aware that the values assigned to each condition can vary from database to database.

sqlerrm is a structure embedded in sqlca, composed of two members:

  • sqlerrml Contains the length of the error message currently stored in sqlerrmc.

  • sqlerrmc Contains the null-terminated message text associated with the code stored in sqlstate. If a message exceeds 149 characters, ecpglib truncates the error message.

  • sqlerrp Contains the string "NOT SET".

sqlerrd is an array that contains six elements:

  • sqlerrd[1] Contains the OID of the processed row (if applicable).

  • sqlerrd[2] Contains the number of processed or returned rows.

  • sqlerrd[0], sqlerrd[3], sqlerrd[4] and sqlerrd[5] are unused.

sqlwarn is an array that contains 8 characters:

  • sqlwarn[0] Contains a value of 'W' if any other element in sqlwarn is set to 'W'.

  • sqlwarn[1] Contains a value of 'W' if a data value was truncated when it was stored in a host variable.

  • sqlwarn[2] Contains a value of 'W' if the client application encounters a nonfatal warning.

  • sqlwarn[3], sqlwarn[4], sqlwarn[5], sqlwarn[6], and sqlwarn[7] are unused.

sqlstate is a five-character array that contains a SQL-compliant status code after the execution of a statement from the client application. If a statement processes without error, sqlstate contains a value of 00000. sqlstate isn't a null-terminated string.

sqlstate codes are assigned in a hierarchical scheme:

  • The first two characters of sqlstate indicate the general class of the condition.
  • The last three characters of sqlstate indicate a specific status within the class.

If the client application encounters multiple errors (or warnings) during an SQL statement's execution, sqlstate contains the last code returned.

List of sqlstate and sqlcode values

The following table lists the sqlstate and sqlcode values, as well as the symbolic name and error description for the related condition.

sqlstatesqlcode (deprecated)Symbolic nameDescription
YE001-12ECPG_OUT_OF_MEMORYVirtual memory is exhausted.
YE002-200ECPG_UNSUPPORTEDThe preprocessor generated an unrecognized item. Might indicate incompatibility between the preprocessor and the library.
07001, or 07002-201ECPG_TOO_MANY_ARGUMENTSThe program specifies more variables than the command expects.
07001, or 07002-202ECPG_TOO_FEW_ARGUMENTSThe program specified fewer variables than the command expects.
21000-203ECPG_TOO_MANY_MATCHESThe SQL command returned multiple rows, but the statement was prepared to receive a single row.
42804-204ECPG_INT_FORMATThe host variable (defined in the C code) is of type INT, and the selected data is of a type that can't be converted into an INT. ecpglib uses the strtol() function to convert string values into numeric form.
42804-205ECPG_UINT_FORMATThe host variable (defined in the C code) is an unsigned INT, and the selected data is of a type that can't be converted into an unsigned INT. ecpglib uses the strtoul() function to convert string values into numeric form.
42804-206ECPG_FLOAT_FORMATThe host variable (defined in the C code) is of type FLOAT, and the selected data is of a type that can't be converted into a FLOAT. ecpglib uses the strtod() function to convert string values into numeric form.
42804-211ECPG_CONVERT_BOOLThe host variable (defined in the C code) is of type BOOL, and the selected data can't be stored in a BOOL.
YE002-2-1ECPG_EMPTYThe statement sent to the server was empty.
22002-213ECPG_MISSING_INDICATORA NULL indicator variable wasn't supplied for the NULL value returned by the server. (The client application received an unexpected NULL value.).
42804-214ECPG_NO_ARRAYThe server returned an array, and the corresponding host variable can't store an array.
42804-215ECPG_DATA_NOT_ARRAYThe server returned a value that isn't an array into a host variable that expects an array value.
08003-220ECPG_NO_CONNThe client application attempted to use a nonexistent connection.
YE002-221ECPG_NOT_CONNThe client application attempted to use an allocated but closed connection.
26000-230ECPG_INVALID_STMTThe statement wasn't prepared.
33000-240ECPG_UNKNOWN_DESCRIPTORThe specified descriptor isn't found.
07009-241ECPG_INVALID_DESCRIPTOR_INDEXThe descriptor index is out of range.
YE002-242ECPG_UNKNOWN_DESCRIPTOR_ITEMThe client application requested an invalid descriptor item (internal error).
07006-243ECPG_VAR_NOT_NUMERICA dynamic statement returned a numeric value for a non-numeric host variable.
07006-244ECPG_VAR_NOT_CHARA dynamic SQL statement returned a CHAR value, and the host variable isn't a CHAR.
-400ECPG_PGSQLThe server returned an error message. The resulting message contains the error text.
08007-401ECPG_TRANSThe server can't start, commit, or roll back the specified transaction.
08001-402ECPG_CONNECTThe client application's attempt to connect to the database failed.
02000100ECPG_NOT_FOUNDThe last command retrieved or processed no rows, or you reached the end of a cursor.

Implementing simple error handling for client applications

Use the EXEC SQL WHENEVER directive to implement simple error handling for client applications compiled with ECPGPlus. The syntax of the directive is:

EXEC SQL WHENEVER <condition> <action>;

This directive instructs the ECPG compiler to insert error-handling code into your program.

The code instructs the client application to perform a specified action if the client application detects a given condition. The condition can be one of the following:

SQLERROR

A SQLERROR condition exists when sqlca.sqlcode is less than zero.

SQLWARNING

A SQLWARNING condition exists when sqlca.sqlwarn[0] contains a 'W'.

NOT FOUND

A NOT FOUND condition exists when sqlca.sqlcode is ECPG_NOT_FOUND (when a query returns no data).

You can specify that the client application perform one of the following actions if it encounters one of the previous conditions:

CONTINUE

Specify CONTINUE to instruct the client application to continue processing, ignoring the current condition. CONTINUE is the default action.

DO CONTINUE

An action of DO CONTINUE generates a CONTINUE statement in the emitted C code. If it encounters the condition, it skips the rest of the code in the loop and continues with the next iteration. You can use it only in a loop.

GOTO label or GO TO label

Use a C goto statement to jump to the specified label.

SQLPRINT

Print an error message to stderr (standard error), using the sqlprint() function. The sqlprint() function prints sql error followed by the contents of sqlca.sqlerrm.sqlerrmc.

STOP

Call exit(1) to signal an error and terminate the program.

DO BREAK

Execute the C break statement. Use this action in loops or switch statements.

CALL name(args) or DO name(args)

Invoke the C function specified by the name parameter, using the parameters specified in the args parameter.

Example

The following code fragment prints a message if the client application encounters a warning and aborts the application if it encounters an error:

EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL WHENEVER SQLERROR STOP;
Note

The ECPGPlus compiler processes your program from top to bottom, even though the client application might not execute from top to bottom. The compiler directive is applied to each line in order and remains in effect until the compiler encounters another directive. If the control of the flow in your program isn't top to bottom, consider adding error-handling directives to any parts of the program that might be missed during compiling.