SQLCODE Rules
SQLCODE is not defined by the ANSI/ISO SQL-99 standard. Its use was deprecated by the ANSI/ISO SQL-92 standard and abandoned by the SQL-99 standard.
- For precompiler validation purposes, SQLCODE must be defined as a 32-bit signed INTEGER value for embedded SQL applications.
- SQLCODE and SQLSTATE can both be specified in the same compilation unit. Both status variables subsequently contain valid status variable codes.
SQLSTATE Rules
SQLSTATE is defined by the ANSI/ISO SQL-99 standard as a 5-character string value. The value is logically divided into a 2-character class and a 3-character subclass.
- Status code values can be integers or a mix of integers with Latin uppercase characters.
- Unless otherwise specified, CLI/TDP and Teradata Database error messages always map into SQLSTATE values.
- Unmapped CLI/TDP errors have a class of T0 and a subclass containing a 3-digit CLI error code.
For example, a CLI error of 157 (invalid Use_Presence_Bits option) produces a class of T0 and a subclass of 157.
- Unmapped Teradata Database errors have classes of T1 through T9, with the digit in the class corresponding to the first digit of the Teradata Database error code.
The subclass contains the remaining 3 digits of the Teradata Database error code.
For example: An error code of 3776 (unterminated comment) maps to a class of T3 and a subclass of 776.
- For precompiler validation purposes, SQLSTATE must be defined as a fixed-length CHAR(5) array.
For C language programs, SQLSTATE must be defined as CHAR(6) to accommodate the C null terminator.
- SQLCODE and SQLSTATE can both be specified in the same compilation unit. Both status variables subsequently contain valid result codes.
SQLCODE to SQLSTATE Mapping Table
The following table maps SQLCODE values to SQLSTATE values for those SQLCODE values that are not generated as the result of a CLI, TDP, or Teradata SQL error.
SQLCODE | SQLSTATE | SQLCODE | SQLSTATE | ||
---|---|---|---|---|---|
Class | Subclass | Class | Subclass | ||
100 | 02 | 000 | -741 | 08 | 002 |
901 | 01 | 901 | -742 | 08 | 000 |
902 | 01 | 004 | -743 | 08 | 000 |
-101 | 54 | 001 | -744 | 08 | 000 |
-104(1) | 42(2) | 512 | -752 | 08 | 752 |
-302 | 22 | 024 | -804 | T0 | 804 |
-303 | 22 | 509 | -811 | 21 | 000 |
-304 | 22 | 003 | -822 | 51 | 004 |
-305 | 22 | 002 | -840 | 21 | 840 |
-413 | 22 | 003 | -901 | T0 | T10 |
-501 | 24 | 501 | -925 | 56 | 021 |
-502 | 24 | 502 | -926 | 56 | 021 |
-504 | 52 | 008 | -942 | T0 | T12 |
-508 | 24 | 508 | -943 | 24 | 000 |
-510 | 53 | 028 | -1001 | T0 | T13 |
-514 | 24 | 000 | -1002 | T0 | T14 |
-515 | 07 | 515 | -1003 | T0 | T15 |
-563 | 08 | 003 | -1005 | T0 | T16 |
-650 | 04 | 000 | -1006 | 07 | T17 |
-651 | 03 | 000 | -1007 | 22 | 007 |
-652 | 04 | 000 | -1009 | 22 | T04 |
-653 | 41 | 000 | -1010 | T0 | T18 |
-740 | 08 | 003 | -1013 | 22 | 023 |
(1) This code should be -84, not -104. (2) This usage is not consistent with IBM DB2. DB2 uses class 37, which applies only to dynamic SQL. Code 2A applies to static SQL. |