The fields SQLSTATE and SQLCODE are two different schemes that provide error codes. Both are derived from the SQL standard, but SQLCODE has been marked deprecated in the SQL-92 edition of the standard and has been dropped in later editions. Therefore, new applications are strongly encouraged to use SQLSTATE.
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. A successful state is indicated by the code 00000. The SQLSTATE codes are for the most part defined in the SQL standard. The PostgreSQL server natively supports SQLSTATE error codes; therefore a high degree of consistency can be achieved by using this error code scheme throughout all applications.
See
Refer to "PostgreSQL Error Codes" in "Appendixes" in the PostgreSQL Documentation for further information.
SQLCODE, the deprecated error code scheme, is a simple integer. A value of 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. Therefore, this scheme can only achieve poor portability and does not have a hierarchical code assignment. Historically, the embedded SQL processor for PostgreSQL has assigned some specific SQLCODE values for its use, which are listed below with their numeric value and their symbolic name. Remember that these are not portable to other SQL implementations. To simplify the porting of applications to the SQLSTATE scheme, the corresponding SQLSTATE is also listed. There is, however, no one-to-one or one-to-many mapping between the two schemes (indeed it is many-to-many), so you should consult the global SQLSTATE in each case.
See
Refer to "PostgreSQL Error Codes" in "Appendixes" in the PostgreSQL Documentation.
These are the assigned SQLCODE values:
Indicates no error. (SQLSTATE 00000)
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. (SQLSTATE 02000)
When processing a cursor in a loop, you could use this code as a way to detect when to abort the loop, like this:
PERFORM NO LIMIT
EXEC SQL FETCH ... END-EXEC
IF SQLCODE = 100 THEN
GO TO FETCH-END
END-IF
END-PERFORM.But WHENEVER NOT FOUND GOTO ... effectively does this internally, so there is usually no advantage in writing this out explicitly.
Indicates that your virtual memory is exhausted. The numeric value is defined as -ENOMEM. (SQLSTATE YE001)
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. (SQLSTATE YE002)
This means that the command specified more host variables than the command expected. (SQLSTATE 07001 or 07002)
This means that the command specified fewer host variables than the command expected. (SQLSTATE 07001 or 07002)
This means a query has returned multiple rows but the statement was only prepared to store one result row (for example, because the specified variables are not arrays). (SQLSTATE 21000)
The host variable is of type singed int and the datum in the database is of a different type and contains a value that cannot be interpreted as an signed int. The library uses strtol() for this conversion. (SQLSTATE 42804)
The host variable is of type unsigned int and the datum 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. (SQLSTATE 42804)
The host variable is of type float and the datum in the database is of another type and contains a value that cannot be interpreted as a float. The library uses strtod() for this conversion. (SQLSTATE 42804)
The host variable is of type DECIMAL and the datum in the database is of another type and contains a value that cannot be interpreted as a DECIMAL or DISPLAY value. For the case of DISPLAY, this error happens if values in the database is too large for converting to DISPLAY value. (SQLSTATE 42804)
The host variable is of type interval and the datum in the database is of another type and contains a value that cannot be interpreted as an interval value. (SQLSTATE 42804)
The host variable is of type date and the datum in the database is of another type and contains a value that cannot be interpreted as a date value. (SQLSTATE 42804)
The host variable is of type timestamp and the datum in the database is of another type and contains a value that cannot be interpreted as a timestamp value. (SQLSTATE 42804)
This means the host variable is of type bool and the datum in the database is neither 't' nor 'f'. (SQLSTATE 42804)
The statement sent to the PostgreSQL server was empty. (This cannot normally happen in an embedded SQL program, so it might point to an internal error.) (SQLSTATE YE002)
A null value was returned and no null indicator variable was supplied. (SQLSTATE 22002)
An ordinary variable was used in a place that requires an array. (SQLSTATE 42804)
The database returned an ordinary variable in a place that requires array value. (SQLSTATE 42804)
The program tried to access a connection that does not exist. (SQLSTATE 08003)
The program tried to access a connection that does exist but is not open. (This is an internal error.) (SQLSTATE YE002)
The statement you are trying to use has not been prepared. (SQLSTATE 26000)
The descriptor specified was not found. The statement you are trying to use has not been prepared. (SQLSTATE 33000)
The descriptor index specified was out of range. (SQLSTATE 07009)
An invalid descriptor item was requested. (This is an internal error.) (SQLSTATE YE002)
During the execution of a dynamic statement, the database returned a numeric value and the host variable was not numeric. (SQLSTATE 07006)
During the execution of a dynamic statement, the database returned a non-numeric value and the host variable was numeric. (SQLSTATE 07006)
Some error caused by the PostgreSQL server. The message contains the error message from the PostgreSQL server.
The PostgreSQL server signaled that we cannot start, commit, or rollback the transaction. (SQLSTATE 08007)
The connection attempt to the database did not succeed. (SQLSTATE 08001)
Duplicate key error, violation of unique constraint. (SQLSTATE 23505)
A result for the subquery is not single row. (SQLSTATE 21000)
An invalid cursor name was specified. (SQLSTATE 34000)
Transaction is in progress. (SQLSTATE 25001)
There is no active (in-progress) transaction. (SQLSTATE 25P01)
An existing cursor name was specified. (SQLSTATE 42P03)