SQLSTATE Variable

The SQLSTATE variable is a five-character string whose value indicates the outcome of the most recently executed SQL statement. SQLSTATE can contain only digits and uppercase letters. The first two characters of SQLSTATE indicate a class; the following three characters indicate a subclass. Class codes are unique, but subclass codes are not; the meaning of a subclass code depends on the class code in which it is contained.

Class codes starting with 0-4 or A-H indicate error conditions and exceptions that are defined in the latest ANSI SQL Standard (ANSI X3.135-1992). Subclass codes for conditions specified in this same standard also start with 0-4 and A-H.

Class codes starting with 5-9 or I-Z indicate Progress-specific error conditions and exceptions. There are no subclasses for these class codes.

Table 2–8 shows the SQLSTATE return code values that the ESQL entry points return to the application under the listed conditions.

Table 2–8: SQLSTATE Error Code Values
Error Code
Condition
00 000
The request completed with unconditional success.
02 000
Returned when a FETCH returns no row because the cursor was already at the end of the set, when a SELECT produces an empty table, or the condition specified in an INSERT, searched DELETE, or searched UPDATE is not satisfied.
07 001
Using clause does not match dynamic parameters. Occurs when the SQLDA supplied to sqldynexec() is bad because of one of the following: The COLCNT is less than the number of dynamic parameter markers but greater than zero, a TYPE field contains an unknown value, or a DATA field contains a NULL pointer.
07002
Using clause does not match target specification. Occurs when the SQLDA supplied to sqldynftch() is bad because of one of the following: The CONLCNT is less than the number of result columns but greater than zero, a TYPE field contains an unknown value, or a DATA field contains a NULL pointer.
07 004
Using clause required for dynamic parameters. The prepared request contained dynamic parameter markers, but either the SQLDA pointer supplied to sqldynexec() was NULL, or the COLCNT was zero.
07 006
Restricted data type attribute violation. An attempt was made to store a value to a data type that is incompatible with the data type of the source (for example, an attempt to store an SQLDATE to an SQL integer).
07 008
Invalid descriptor count. The COLCNT field of an SQLDATE supplied to sqldynexec() or sqldynftch() contained a value greater than the SQLN field.
07 009
Invalid descriptor index. The parameter supplied to sqlald() was invalid because it was either less than zero or greater than the system-defined maximum number of SQLDATE indexes.
08 001
Client unable to establish connection to database; sqlconn() failed.
08 003
Connection does not exist; sqldisconn() failed because the database was not currently connected.
21 000
Cardinality violation. A SELECT... INTO or a subquery returned more than one result row.
22 002
Null value, no indicator parameter. The application tried to fetch a null value but did not supply an indicator variable in which to indicate that the value is null.
22 003
A numeric value is too large to be stored in a destination data type without losing significant digits. Currently, this state may not be returned if the value results from calculating an expression.
22 005
An error occurred while trying to convert a value to the data type of the destination (for example, trying to store the string “ABCD” to an integer).
22 012
Division by zero.
22 024
An unterminated string value was supplied to ESQL from the application. Either a host language variable declared as character[n] contained n non-null characters, or an SQLDA was supplied with a LENGTH field of n and there were n+1 non-null characters to which the DATA field pointed.
23 000
Integrity constraint violation. A statement attempted to change a table in such a way that the resulting table would violate the constraints on the table. Either a column declared as NOT NULL was set to NULL, or a column declared as UNIQUE was set to a duplicate value.
24 000
An attempt was made to do something to a cursor that is invalid given the current state of the cursor. All of the following attempts result in this state: OPEN an already OPEN cursor, CLOSE an unopened cursor, FETCH from an unopened cursor, DELETE from an unopened or unpositioned cursor, or UPDATE an unopened or unpositioned cursor.
26 000
Invalid SQL statement identifier. This is returned from sqldynexec() or sqldyndesc() if it is called with a stale request handle. There is no prepared request associated with the request handle; the statement identifier is invalid.
33 000
Invalid SQL descriptor name. Returned from sqldynftch(), sqldyndesc(), and sqldyndescinp() if the SQLDA pointer supplied to the entry point is null or invalid.
37 000
Syntax error or access violation in PREPAREd request. See state 42 000. Same as state 42 000 except this state is returned for statements that were PREPAREd, while 42000 is returned for all other statements.
40 000
Indicates that the current transaction has been rolled back. This may be returned by any function that accesses a database.
42 000
Indicates that there was either a syntax error in the request, or the request could not be carried out for one of these reasons: the table, view, or index does not exist or the user does not have sufficient privilege to carry out the operation. The state is used for several different error conditions for security reasons. The user should not be able to differentiate between a request that failed because there was a syntax error or a request that failed because a table does not exist.
50 000
Indicates that a statement failed but does not indicate the reason for the failure. It is a default error state, used when none of the more specific error states are appropriate.
UN SET
Unset state. At the beginning of every entry point, the ESQL sets SQLSTATE to UNSET. Before ESQL returns control to the application, it sets the SQLSTATE to the correct value. The application should never be returned to this state. If so, it indicates that ESQL failed to properly set SQLSTATE. However, the value of SQLCODE is still reliable.
ZZ 000
A fatal, unrecoverable error occurred. No more calls to the ESQL entry points can be made. Currently returned by sqllogout() only.

Using SQLSTATE in an Application

SQLSTATE is the preferred status reporting mechanism, although the SQL preprocessor does not require it. If you use SQLSTATE with the SQL preprocessor, declare SQLSTATE as follows within the SQL DECLARE SECTION in the scope of any function that contains any Progress/ESQL statements except the WHENEVER, BEGIN DECLARE SECTION, or END DECLARE SECTION statements:

char SQLSTATE[6]; 

The SQL preprocessor uses the address of this declared variable as an argument to an ESQL-LIB function, sqlcdbind(). By placing this function call prior to many of the calls to the ESQL-LIB interface in the generated output, the SQL preprocessor notifies the interface where to deposit the status information. If the SQLSTATE variable declaration is not found in the SQL DECLARE SECTION, then the SQL preprocessor does not pass any SQLSTATE value to the sqlcdbind() function.

If you are not using the SQL preprocessor, or if you are using it and are accessing some of the ESQL-LIB functions that have no counterpart in Embedded SQL (especially the dynamic ESQL-LIB functions), you may still use SQLSTATE to retrieve status information about the ESQL-LIB function calls being made. In this case, declare the 6-byte character array in your application and pass the address of that variable to the sqlcdbind() function. You do not have to declare the variable with the name SQLSTATE.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095