Help Center/ GaussDB/ Developer Guide(Distributed_3.x)/ Application Development Guide/ ECPG-based Development/ Error Handling/ SQLSTATE and SQLCODE
Updated on 2024-05-07 GMT+08:00
View PDF
Share

SQLSTATE and SQLCODE

SQLSTATE is a five-character array. The five characters contain digits or upper-case letters that represent codes of various error and warning conditions. SQLSTATE has a hierarchical scheme: the first two characters indicate the general class of the condition, the last three characters indicate a subclass of the general condition. For example, the code 00000 indicates the success state.

SQLCODE is a simple integer. The value 0 indicates success, a positive value indicates success with additional information, a negative value indicates an error. The SQL standard only defines the positive value +100, which indicates that the last command returned or affected zero rows, and no specific negative values.

Table 1 Mapping between SQLSTATE and SQLCODE

SQLCODE Value

SQLSTATE Value

Description

0 (ECPG_NO_ERROR)

SQLSTATE 00000

Indicates no error.

100 (ECPG_NOT_FOUND)

SQLSTATE 02000

This is a harmless condition indicating that the last command retrieved or processed zero rows, or that you are at the end of the cursor.

When processing a cursor in a loop, you could use this code as a way to detect when to abort the loop. An example is as follows: 
while (1) 
{ 
    EXEC SQL FETCH ... ; 
    if (sqlca.sqlcode == ECPG_NOT_FOUND)  
        break; 
}

Actually, WHENEVER NOT FOUND DO BREAK effectively does this internally, so there is usually no advantage in writing this out explicitly.

-12 (ECPG_OUT_OF_MEMORY)

SQLSTATE YE001

Indicates that your virtual memory is exhausted. The numeric value is defined as –ENOMEM.

-200 (ECPG_UNSUPPORTED)

SQLSTATE YE000

Indicates that the preprocessor has generated something that the library does not know about.

-201 (ECPG_TOO_MANY_ARGUMENTS)

SQLSTATE 07001 or 07002

Indicates that the command specified more host variables than the command expected.

-202 (ECPG_TOO_FEW_ARGUMENTS)

SQLSTATE 07001 or 07002

Indicates that the command specified fewer host variables than the command expected.

-203 (ECPG_TOO_MANY_MATCHES)

SQLSTATE 21000

Indicates that a query has returned multiple rows, but the statement is ready to store only one result row.

-204 (ECPG_INT_FORMAT)

SQLSTATE 42804

The host variable is of the int type and the data in the database is of a different type and contains a value that cannot be interpreted as an int. The library uses strtol() for this conversion.

-205 (ECPG_UINT_FORMAT)

SQLSTATE 42804

The host variable is of the unsigned int type and the data in the database is of a different type and contains a value that cannot be interpreted as an unsigned int. The library uses strtoul() for this conversion.

-206 (ECPG_FLOAT_FORMAT)

SQLSTATE 42804

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

-207 (ECPG_NUMERIC_FORMAT)

SQLSTATE 42804

The host variable is of the numeric type and the data in the database is of another type and contains a value that cannot be interpreted as a numeric value.

-208 (ECPG_INTERVAL_FORMAT)

SQLSTATE 42804

The host variable is of the interval type and the data in the database is of another type and contains a value that cannot be interpreted as an interval value.

-209 (ECPG_DATE_FORMAT)

SQLSTATE 42804

The host variable is of the date type and the data in the database is of another type and contains a value that cannot be interpreted as a date value.

-210 (ECPG_TIMESTAMP_FORMAT)

SQLSTATE 42804

The host variable is of the timestamp type and the data in the database is of another type and contains a value that cannot be interpreted as a timestamp value.

-211 (ECPG_CONVERT_BOOL)

SQLSTATE 42804

The host variable is of the Boolean type, but the data in the database is neither 't' nor 'f'.

-212 (ECPG_EMPTY)

SQLSTATE YE000

The statement sent to the SQL server was empty. (This usually does not occur in an embedded SQL program, so it may point to an internal error.)

-213 (ECPG_MISSING_INDICATOR)

SQLSTATE 22002

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

-214 (ECPG_NO_ARRAY)

SQLSTATE 42804

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

-215 (ECPG_DATA_NOT_ARRAY)

SQLSTATE 42804

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

-216 (ECPG_ARRAY_INSERT)

SQLSTATE 42804

The value cannot be inserted into the array.

-220 (ECPG_NO_CONN)

SQLSTATE 08003

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

-221 (ECPG_NOT_CONN)

SQLSTATE YE000

The program tried to access a connection that does exist but is not open. (This is an internal error.)

-230 (ECPG_INVALID_STMT)

SQLSTATE 26000

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

-239 (ECPG_INFORMIX_DUPLICATE_KEY)

SQLSTATE 23505

Duplicate key error, violation of unique constraint.

-240 (ECPG_UNKNOWN_DESCRIPTOR)

SQLSTATE 33000

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

-241 (ECPG_INVALID_DESCRIPTOR_INDEX)

SQLSTATE 07009

The specified descriptor was out of range.

-242 (ECPG_UNKNOWN_DESCRIPTOR_ITEM)

SQLSTATE YE000

An invalid descriptor item was requested. (This is an internal error.)

-243 (ECPG_VAR_NOT_NUMERIC)

SQLSTATE 07006

During the execution of a dynamic statement, the database returned a numeric value and the host variable was not numeric.

-244 (ECPG_VAR_NOT_CHAR)

SQLSTATE 07006

During the execution of a dynamic statement, the database returned a non-numeric value and the host variable was numeric.

-284 (ECPG_INFORMIX_SUBSELECT_NOT_ONE)

SQLSTATE 21000

The result of the subquery was not a single row.

-400 (ECPG_PGSQL)

-

Some error caused by the SQL server. This message contains an error message from the SQL server.

-401 (ECPG_TRANS)

SQLSTATE 08007

The SQL server notified that the transaction cannot be started, committed, or rolled back.

-402 (ECPG_CONNECT)

SQLSTATE 08001

The connection attempt to the database did not succeed.

-403 (ECPG_DUPLICATE_KEY)

SQLSTATE 23505

Duplicate key error, violation of unique constraint.

-404 (ECPG_SUBSELECT_NOT_ONE)

SQLSTATE 21000

The result of the subquery was not a single row.

-602 (ECPG_WARNING_UNKNOWN_PORTAL)

SQLSTATE 34000

An invalid cursor name was specified.

-603 (ECPG_WARNING_IN_TRANSACTION)

SQLSTATE 25001

A transaction is in progress.

-604 (ECPG_WARNING_NO_TRANSACTION)

SQLSTATE 25P01

There is no active (in-progress) transaction.

-605 (ECPG_WARNING_PORTAL_EXISTS)

SQLSTATE 42P03

An existing cursor name was specified.
  • The SQLSTATE codes 22002, 07001, 07002, 07006, 07009, 33000, 42601, 42804, 42P03, YE000 and YE001 are newly added to ecpg for embedded SQL statements. Other SQLSTATE codes are inherited from the kernel SQLSTATE codes.
  • If the value of SQLSCODE is –400, ecpg detects that the kernel server returns an error. The error code of the kernel SQLSTATE is used.
Parent topic: Error Handling