Error Handling v10
ECPGPlus provides two methods to detect and handle errors in embedded SQL code:
- A client application can examine the
sqlcadata structure for error messages, and supply customized error handling for your client application.
- A client application can include
EXEC SQL WHENEVERdirectives to instruct the ECPGPlus compiler to add error-handling code.
sqlca (SQL communications area) is a global variable used by
ecpglib to communicate information from the server to the client application. After executing a SQL statement (for example, an
SELECT statement) you can inspect the contents of
sqlca to determine if the statement has completed successfully or if the statement has failed.
sqlca has the following structure:
Use the following directive to implement
If you include the
ecpg directive, you do not need to
sqlca.h file in the client application's header declaration.
The Advanced Server
sqlca structure contains the following members:
sqlcaid contains the string:
sqlabc contains the size of the
sqlcode member has been deprecated with SQL 92; Advanced Server supports
sqlcode for backward compatibility, but you should use the
sqlstate member when writing new code.
sqlcode is an integer value; a positive
sqlcode value indicates that the client application has encountered a harmless processing condition, while a negative value indicates a warning or error.
If a statement processes without error,
sqlcode will contain a value of
0. If the client application encounters an error (or warning) during a statement's execution,
sqlcode will contain the last code returned.
The SQL standard defines only a positive value of 100, which indicates that he most recent SQL statement processed returned/affected no rows. Since the SQL standard does not define other
sqlcode values, please be aware that the values assigned to each condition may vary from database to database.
sqlerrm is a structure embedded within
sqlca, composed of two members:
sqlerrml contains the length of the error message currently stored in
sqlerrmc contains the null-terminated message text associated with the code stored in
sqlstate. If a message exceeds 149 characters in length,
ecpglib will truncate the error message.
sqlerrp contains the string
sqlerrd is an array that contains six elements:
sqlerrdcontains the OID of the processed row (if applicable).
sqlerrdcontains the number of processed or returned rows.
sqlerrd, sqlerrd, sqlerrdand
sqlwarn is an array that contains 8 characters:
sqlwarncontains a value of
'W'if any other element within
sqlwarnis set to
sqlwarncontains a value of
'W'if a data value was truncated when it was stored in a host variable.
sqlwarncontains a value of
'W'if the client application encounters a non-fatal warning.
sqlwarn, sqlwarn, sqlwarn, sqlwarn, and
sqlstate is a 5 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 will contain a value of
00000. Please note that
sqlstate is not a null-terminated string.
sqlstate codes are assigned in a hierarchical scheme:
- The first two characters of
sqlstateindicate the general class of the condition.
- The last three characters of
sqlstateindicate a specific status within the class.
If the client application encounters multiple errors (or warnings) during an SQL statement's execution
sqlstate will contain the last code returned.
The following table lists the
sqlcode values, as well as the symbolic name and error description for the related condition:
|sqlstate||sqlcode (Deprecated)||Symbolic Name||Description|
|Virtual memory is exhausted.|
|The preprocessor has generated an unrecognized item. Could indicate incompatibility between the preprocessor and the library.|
|The program specifies more variables than the command expects.|
|The program specified fewer variables than the command expects.|
|The SQL command has returned multiple rows, but the statement was prepared to receive a single row.|
|The host variable (defined in the C code) is of type INT, and the selected data is of a type that cannot be converted into an INT. |
|The host variable (defined in the C code) is an unsigned INT, and the selected data is of a type that cannot be converted into an unsigned INT. |
|The host variable (defined in the C code) is of type FLOAT, and the selected data is of a type that cannot be converted into an FLOAT. |
|The host variable (defined in the C code) is of type BOOL, and the selected data cannot be stored in a BOOL.|
|The statement sent to the server was empty.|
|A NULL indicator variable has not been supplied for the NULL value returned by the server (the client application has received an unexpected NULL value).|
|The server has returned an array, and the corresponding host variable is not capable of storing an array.|
|The server has returned a value that is not an array into a host variable that expects an array value.|
|The client application has attempted to use a non-existent connection.|
|The client application has attempted to use an allocated, but closed connection.|
|The statement has not been prepared.|
|The specified descriptor is not found.|
|The descriptor index is out-of-range.|
|The client application has requested an invalid descriptor item (internal error).|
|A dynamic statement has returned a numeric value for a non-numeric host variable.|
|A dynamic SQL statement has returned a CHAR value, and the host variable is not a CHAR.|
|The server has returned an error message; the resulting message contains the error text.|
|The server cannot start, commit or rollback the specified transaction.|
|The client application's attempt to connect to the database has failed.|
|The last command retrieved or processed no rows, or you have reached the end of a cursor.|
EXEC SQL WHENEVER directive to implement simple error handling for client applications compiled with ECPGPlus. The syntax of the directive is:
This directive instructs the ECPG compiler to insert error-handling code into your program.
The code instructs the client application that it should perform a specified action if the client application detects a given condition. The condition may be one of the following:
SQLERROR condition exists when
sqlca.sqlcode is less than zero.
SQLWARNING condition exists when
sqlca.sqlwarn contains a
NOT FOUND condition exists when
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 to instruct the client application to continue processing, ignoring the current
CONTINUE is the default action.
An action of
DO CONTINUE will generate a
CONTINUE statement in the emitted C code that if it encounters the condition, skips the rest of the code in the loop and continues with the next iteration. You can only use it within a loop.
GO TO label
Use a C
goto statement to jump to the specified
Print an error message to
stderr (standard error), using the
sqlprint() function. The
sqlprint() function prints
sql error, followed by the contents of
exit(1) to signal an error, and terminate the program.
Execute the C
break statement. Use this action in loops, or
Invoke the C function specified by the name
parameter, using the parameters specified in the
The following code fragment prints a message if the client application encounters a warning, and aborts the application if it encounters an error:
The ECPGPlus compiler processes your program from top to bottom, even though the client application may 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 within your program is not top-to-bottom, you should consider adding error-handling directives to any parts of the program that may be inadvertently missed during compilation.