Map SQLCODE Values to SQLSTATE Values | Teradata Vantage - Mapping SQLCODE Values to SQLSTATE Values - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
vqj1592443206677.ditamap
dita:ditavalPath
vqj1592443206677.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantageā„¢

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.

The following rules apply to SQLCODE status variables:
  • 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.

The following rules apply to SQLSTATE status variables:
  • Status code values can be integers or a mix of integers with Latin uppercase characters.
  • Unless otherwise specified, CLI/TDP and 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 database errors have classes of T1 through T9, with the digit in the class corresponding to the first digit of the database error code.

    The subclass contains the remaining 3 digits of the 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.