Jump to content

SQLSTATE

From Wikipedia, the free encyclopedia

Programs calling a database which accords to the SQL standard receive an indication about the success or failure of the call. This return code - which is called SQLSTATE - consists of 5 bytes. They are divided into two parts: the first and second bytes contain a class and the following three a subclass. Each class belongs to one of four categories: "S" denotes "Success" (class 00), "W" denotes "Warning" (class 01), "N" denotes "No data" (class 02) and "X" denotes "Exception" (all other classes).

  • Real DBMSs are free to define additional values for SQLSTATE to handle those features which are beyond the standard. Such values must use one of the characters [I-Z] or [5-9] as the first byte of class (first byte of SQLSTATE) or subclass (third byte of SQLSTATE).
  • In addition to SQLSTATE the SQL command GET DIAGNOSTICS offers more details about the last executed SQL command.
  • In very early versions of the SQL standard the return code was called SQLCODE and used a different coding schema.

The following table lists the standard-conforming values - based on SQL:2011.[1] The table's last column shows the part of the standard that defines the row. If it is empty, the definition originates from part 2 Foundation.

SQLSTATE Cat. Class Class Text Subclass Subclass Text SQL part
00000 S 00 successful completion 000 (no subclass)
01000 W 01 warning 000 (no subclass)
01001 W 01 warning 001 cursor operation conflict
01002 W 01 warning 002 disconnect error
01003 W 01 warning 003 null value eliminated in set function
01004 W 01 warning 004 string data, right truncation
01005 W 01 warning 005 insufficient item descriptor areas
01006 W 01 warning 006 privilege not revoked
01007 W 01 warning 007 privilege not granted
01009 W 01 warning 009 search condition too long for information schema
0100A W 01 warning 00A query expression too long for information schema
0100B W 01 warning 00B default value too long for information schema
0100C W 01 warning 00C result sets returned
0100D W 01 warning 00D additional result sets returned
0100E W 01 warning 00E attempt to return too many result sets
0100F W 01 warning 00F statement too long for information schema
01010 W 01 warning 010 column cannot be mapped SQL/XML
01011 W 01 warning 011 SQL-Java path too long for information schema SQL/JRT
01012 W 01 warning 012 invalid number of conditions
0102F W 01 warning 02F array data, right truncation
02000 N 02 no data 000 (no subclass)
02001 N 02 no data 001 no additional result sets returned
07000 X 07 dynamic SQL error 000 (no subclass)
07001 X 07 dynamic SQL error 001 using clause does not match dynamic parameter specifications
07002 X 07 dynamic SQL error 002 using clause does not match target specifications
07003 X 07 dynamic SQL error 003 cursor specification cannot be executed
07004 X 07 dynamic SQL error 004 using clause required for dynamic parameters
07005 X 07 dynamic SQL error 005 prepared statement not a cursor specification
07006 X 07 dynamic SQL error 006 restricted data type attribute violation
07007 X 07 dynamic SQL error 007 using clause required for result fields
07008 X 07 dynamic SQL error 008 invalid descriptor count
07009 X 07 dynamic SQL error 009 invalid descriptor index
0700B X 07 dynamic SQL error 00B data type transform function violation
0700C X 07 dynamic SQL error 00C undefined DATA value
0700D X 07 dynamic SQL error 00D invalid DATA target
0700E X 07 dynamic SQL error 00E invalid LEVEL value
0700F X 07 dynamic SQL error 00F invalid DATETIME_INTERVAL_CODE
08000 X 08 connection exception 000 (no subclass)
08001 X 08 connection exception 001 SQL-client unable to establish SQL-connection
08002 X 08 connection exception 002 connection name in use
08003 X 08 connection exception 003 connection does not exist
08004 X 08 connection exception 004 SQL-server rejected establishment of SQL-connection
08006 X 08 connection exception 006 connection failure
08007 X 08 connection exception 007 transaction resolution unknown
09000 X 09 triggered action exception 000 (no subclass)
0A000 X 0A feature not supported 000 (no subclass)
0A001 X 0A feature not supported 001 multiple server transactions
0D000 X 0D invalid target type specification 000 (no subclass)
0E000 X 0E invalid schema name list specification 000 (no subclass)
0F000 X 0F locator exception 000 (no subclass)
0F001 X 0F locator exception 001 invalid specification
0K000 X 0K resignal when handler not active 000 (no subclass) SQL/PSM
0L000 X 0L invalid grantor 000 (no subclass)
0M000 X 0M invalid SQL-invoked procedure reference 000 (no subclass)
0N000 X 0N SQL/XML mapping error 000 (no subclass) SQL/XML
0N001 X 0N SQL/XML mapping error 001 unmappable XML name SQL/XML
0N002 X 0N SQL/XML mapping error 002 invalid XML character SQL/XML
0P000 X 0P invalid role specification 000 (no subclass)
0S000 X 0S invalid transform group name specification 000 (no subclass)
0T000 X 0T target table disagrees with cursor specification 000 (no subclass)
0U000 X 0U attempt to assign to non-updatable column 000 (no subclass)
0V000 X 0V attempt to assign to ordering column 000 (no subclass)
0W000 X 0W prohibited statement encountered during trigger execution 000 (no subclass)
0W001 X 0W prohibited statement encountered during trigger execution 001 modify table modified by data change delta table
0X000 X 0X invalid foreign server specification 000 (no subclass) SQL/MED
0Y000 X 0Y pass-through specific condition 000 (no subclass) SQL/MED
0Y001 X 0Y pass-through specific condition 001 invalid cursor option SQL/MED
0Y002 X 0Y pass-through specific condition 002 invalid cursor allocation SQL/MED
0Z000 X 0Z diagnostics exception 000 (no subclass)
0Z001 X 0Z diagnostics exception 001 maximum number of stacked diagnostics areas exceeded
0Z002 X 0Z diagnostics exception 002 stacked diagnostics accessed without active handler SQL/PSM
10000 X 10 XQuery error 000 (no subclass) SQL/XML
20000 X 20 case not found for case statement 000 (no subclass) SQL/PSM
21000 X 21 cardinality violation 000 (no subclass)
22000 X 22 data exception 000 (no subclass)
22001 X 22 data exception 001 string data, right truncation
22002 X 22 data exception 002 null value, no indicator parameter
22003 X 22 data exception 003 numeric value out of range
22004 X 22 data exception 004 null value not allowed
22005 X 22 data exception 005 error in assignment
22006 X 22 data exception 006 invalid interval format
22007 X 22 data exception 007 invalid datetime format
22008 X 22 data exception 008 datetime field overflow
22009 X 22 data exception 009 invalid time zone displacement value
2200B X 22 data exception 00B escape character conflict
2200C X 22 data exception 00C invalid use of escape character
2200D X 22 data exception 00D invalid escape octet
2200E X 22 data exception 00E null value in array target
2200F X 22 data exception 00F zero-length character string
2200G X 22 data exception 00G most specific type mismatch
2200H X 22 data exception 00H sequence generator limit exceeded
2200J X 22 data exception 00J nonidentical notations with the same name SQL/XML
2200K X 22 data exception 00K nonidentical unparsed entities with the same name SQL/XML
2200L X 22 data exception 00L not an XML document SQL/XML
2200M X 22 data exception 00M invalid XML document SQL/XML
2200N X 22 data exception 00N invalid XML content SQL/XML
2200P X 22 data exception 00P interval value out of range
2200Q X 22 data exception 00Q multiset value overflow
2200R X 22 data exception 00R XML value overflow SQL/XML
2200S X 22 data exception 00S invalid comment SQL/XML
2200T X 22 data exception 00T invalid processing instruction SQL/XML
2200U X 22 data exception 00U not an XQuery document node SQL/XML
2200V X 22 data exception 00V invalid XQuery context item SQL/XML
2200W X 22 data exception 00W XQuery serialization error SQL/XML
22010 X 22 data exception 010 invalid indicator parameter value
22011 X 22 data exception 011 substring error
22012 X 22 data exception 012 division by zero
22013 X 22 data exception 013 invalid preceding or following size in window function
22014 X 22 data exception 014 invalid argument for NTILE function
22015 X 22 data exception 015 interval field overflow
22016 X 22 data exception 016 invalid argument for NTH_VALUE function
22017 X 22 data exception 017 invalid data specified for datalink SQL/MED
22018 X 22 data exception 018 invalid character value for cast
22019 X 22 data exception 019 invalid escape character
2201A X 22 data exception 01A null argument passed to datalink constructor SQL/MED
2201B X 22 data exception 01B invalid regular expression
2201C X 22 data exception 01C null row not permitted in table
2201D X 22 data exception 01D datalink value exceeds maximum length SQL/MED
2201E X 22 data exception 01E invalid argument for natural logarithm
2201F X 22 data exception 01F invalid argument for power function
2201G X 22 data exception 01G invalid argument for width bucket function
2201H X 22 data exception 01H invalid row version
2201J X 22 data exception 01J XQuery sequence cannot be validated SQL/XML
2201K X 22 data exception 01K XQuery document node cannot be validated SQL/XML
2201L X 22 data exception 01L no XML schema found SQL/XML
2201M X 22 data exception 01M element namespace not declared SQL/XML
2201N X 22 data exception 01N global element not declared SQL/XML
2201P X 22 data exception 01P no XML element with the specified QName SQL/XML
2201Q X 22 data exception 01Q no XML element with the specified namespace SQL/XML
2201R X 22 data exception 01R validation failure SQL/XML
2201S X 22 data exception 01S invalid Query regular expression
2201T X 22 data exception 01T invalid Query option flag
2201U X 22 data exception 01U attempt to replace a zero-length string
2201V X 22 data exception 01V invalid Query replacement string
2201W X 22 data exception 01W invalid row count in fetch first clause
2201X X 22 data exception 01X invalid row count in result offset clause
22021 X 22 data exception 021 character not in repertoire
22022 X 22 data exception 022 indicator overflow
22023 X 22 data exception 023 invalid parameter value
22024 X 22 data exception 024 unterminated C string
22025 X 22 data exception 025 invalid escape sequence
22026 X 22 data exception 026 string data, length mismatch
22027 X 22 data exception 027 trim error
22029 X 22 data exception 029 noncharacter in UCS string
2202A X 22 data exception 02A null value in field reference SQL/PSM
2202D X 22 data exception 02D null value substituted for mutator subject parameter
2202E X 22 data exception 02E array element error
2202F X 22 data exception 02F array data, right truncation
2202G X 22 data exception 02G invalid repeat argument in a sample clause
2202H X 22 data exception 02H invalid sample size
23000 X 23 integrity constraint violation 000 (no subclass)
23001 X 23 integrity constraint violation 001 restrict violation
24000 X 24 invalid cursor state 000 (no subclass)
25000 X 25 invalid transaction state 000 (no subclass)
25001 X 25 invalid transaction state 001 active SQL-transaction
25002 X 25 invalid transaction state 002 branch transaction already active
25003 X 25 invalid transaction state 003 inappropriate access mode for branch transaction
25004 X 25 invalid transaction state 004 inappropriate isolation level for branch transaction
25005 X 25 invalid transaction state 005 no active SQL-transaction for branch transaction
25006 X 25 invalid transaction state 006 read-only SQL-transaction
25007 X 25 invalid transaction state 007 schema and data statement mixing not supported
25008 X 25 invalid transaction state 008 held cursor requires same isolation level
26000 X 26 invalid SQL statement name 000 (no subclass)
27000 X 27 triggered data change violation 000 (no subclass)
27001 X 27 triggered data change violation 001 modify table modified by data change delta table
28000 X 28 invalid authorization specification 000 (no subclass)
2B000 X 2B dependent privilege descriptors still exist 000 (no subclass)
2C000 X 2C invalid character set name 000 (no subclass)
2D000 X 2D invalid transaction termination 000 (no subclass)
2E000 X 2E invalid connection name 000 (no subclass)
2F000 X 2F SQL routine exception 000 (no subclass)
2F002 X 2F SQL routine exception 002 modifying SQL-data not permitted
2F003 X 2F SQL routine exception 003 prohibited SQL-statement attempted
2F004 X 2F SQL routine exception 004 reading SQL-data not permitted
2F005 X 2F SQL routine exception 005 function executed no return statement
2H000 X 2H invalid collation name 000 (no subclass)
30000 X 30 invalid SQL statement identifier 000 (no subclass)
33000 X 33 invalid SQL descriptor name 000 (no subclass)
34000 X 34 invalid cursor name 000 (no subclass)
35000 X 35 invalid condition number 000 (no subclass)
36000 X 36 cursor sensitivity exception 000 (no subclass)
36001 X 36 cursor sensitivity exception 001 request rejected
36002 X 36 cursor sensitivity exception 002 request failed
38000 X 38 external routine exception 000 (no subclass)
38001 X 38 external routine exception 001 containing SQL not permitted
38002 X 38 external routine exception 002 modifying SQL-data not permitted
38003 X 38 external routine exception 003 prohibited SQL-statement attempted
38004 X 38 external routine exception 004 reading SQL-data not permitted
39000 X 39 external routine invocation exception 000 (no subclass)
39004 X 39 external routine invocation exception 004 null value not allowed
3B000 X 3B savepoint exception 000 (no subclass)
3B001 X 3B savepoint exception 001 invalid specification
3B002 X 3B savepoint exception 002 too many
3C000 X 3C ambiguous cursor name 000 (no subclass)
3D000 X 3D invalid catalog name 000 (no subclass)
3F000 X 3F invalid schema name 000 (no subclass)
40000 X 40 transaction rollback 000 (no subclass)
40001 X 40 transaction rollback 001 serialization failure
40002 X 40 transaction rollback 002 integrity constraint violation
40003 X 40 transaction rollback 003 statement completion unknown
40004 X 40 transaction rollback 004 triggered action exception
42000 X 42 syntax error or access rule violation 000 (no subclass)
44000 X 44 with check option violation 000 (no subclass)
45000 X 45 unhandled user-defined exception 000 (no subclass) SQL/PSM
46000 X 46 OLB-specific error 000 (no subclass) SQL/OLB
46001 X 46 Java DDL 001 invalid URL SQL/JRT
46002 X 46 Java DDL 002 invalid JAR name SQL/JRT
46003 X 46 Java DDL 003 invalid class deletion SQL/JRT
46005 X 46 Java DDL 005 invalid replacement SQL/JRT
4600A X 46 Java DDL 00A attempt to replace uninstalled JAR SQL/JRT
4600B X 46 Java DDL 00B attempt to remove uninstalled JAR SQL/JRT
4600C X 46 Java DDL 00C invalid JAR removal SQL/JRT
4600D X 46 Java DDL 00D invalid path SQL/JRT
4600E X 46 Java DDL 00E self-referencing path SQL/JRT
46102 X 46 Java DDL 102 invalid JAR name in path SQL/JRT
46103 X 46 Java DDL 103 unresolved class name SQL/JRT
46110 X 46 OLB-specific error 110 unsupported feature SQL/OLB
46120 X 46 OLB-specific error 120 invalid class declaration SQL/OLB
46121 X 46 OLB-specific error 121 invalid column name SQL/OLB
46122 X 46 OLB-specific error 122 invalid number of columns SQL/OLB
46130 X 46 OLB-specific error 130 invalid profile state SQL/OLB
HW000 X HW datalink exception 000 (no subclass) SQL/MED
HW001 X HW datalink exception 001 external file not linked SQL/MED
HW002 X HW datalink exception 002 external file already linked SQL/MED
HW003 X HW datalink exception 003 referenced file does not exist SQL/MED
HW004 X HW datalink exception 004 invalid write token SQL/MED
HW005 X HW datalink exception 005 invalid datalink construction SQL/MED
HW006 X HW datalink exception 006 invalid write permission for update SQL/MED
HW007 X HW datalink exception 007 referenced file not valid SQL/MED
HV000 X HV FDW-specific condition 000 (no subclass) SQL/MED
HV001 X HV FDW-specific condition 001 memory allocation error SQL/MED
HV002 X HV FDW-specific condition 002 dynamic parameter value needed SQL/MED
HV004 X HV FDW-specific condition 004 invalid data type SQL/MED
HV005 X HV FDW-specific condition 005 column name not found SQL/MED
HV006 X HV FDW-specific condition 006 invalid data type descriptors SQL/MED
HV007 X HV FDW-specific condition 007 invalid column name SQL/MED
HV008 X HV FDW-specific condition 008 invalid column number SQL/MED
HV009 X HV FDW-specific condition 009 invalid use of null pointer SQL/MED
HV00A X HV FDW-specific condition 00A invalid string format SQL/MED
HV00B X HV FDW-specific condition 00B invalid handle SQL/MED
HV00C X HV FDW-specific condition 00C invalid option index SQL/MED
HV00D X HV FDW-specific condition 00D invalid option name SQL/MED
HV00J X HV FDW-specific condition 00J option name not found SQL/MED
HV00K X HV FDW-specific condition 00K reply handle SQL/MED
HV00L X HV FDW-specific condition 00L unable to create execution SQL/MED
HV00M X HV FDW-specific condition 00M unable to create reply SQL/MED
HV00N X HV FDW-specific condition 00N unable to establish connection SQL/MED
HV00P X HV FDW-specific condition 00P no schemas SQL/MED
HV00Q X HV FDW-specific condition 00Q schema not found SQL/MED
HV00R X HV FDW-specific condition 00R table not found SQL/MED
HV010 X HV FDW-specific condition 010 function sequence error SQL/MED
HV014 X HV FDW-specific condition 014 limit on number of handles exceeded SQL/MED
HV021 X HV FDW-specific condition 021 inconsistent descriptor information SQL/MED
HV024 X HV FDW-specific condition 024 invalid attribute value SQL/MED
HV090 X HV FDW-specific condition 090 invalid string length or buffer length SQL/MED
HV091 X HV FDW-specific condition 091 invalid descriptor field identifier SQL/MED
HY000 X HY CLI-specific condition 000 (no subclass) SQL/CLI
HY??? X HY CLI-specific condition dynamic parameter value needed SQL/CLI
HY??? X HY CLI-specific condition invalid handle SQL/CLI
HY001 X HY CLI-specific condition 001 memory allocation error SQL/CLI
HY003 X HY CLI-specific condition 003 invalid data type in application descriptor SQL/CLI
HY004 X HY CLI-specific condition 004 invalid data type SQL/CLI
HY007 X HY CLI-specific condition 007 associated statement is not prepared SQL/CLI
HY008 X HY CLI-specific condition 008 operation canceled SQL/CLI
HY009 X HY CLI-specific condition 009 invalid use of null pointer SQL/CLI
HY010 X HY CLI-specific condition 010 function sequence error SQL/CLI
HY011 X HY CLI-specific condition 011 attribute cannot be set now SQL/CLI
HY012 X HY CLI-specific condition 012 invalid transaction operation code SQL/CLI
HY013 X HY CLI-specific condition 013 memory management error SQL/CLI
HY014 X HY CLI-specific condition 014 limit on number of handles exceeded SQL/CLI
HY017 X HY CLI-specific condition 017 invalid use of automatically allocated descriptor handle SQL/CLI
HY018 X HY CLI-specific condition 018 server declined the cancellation request SQL/CLI
HY019 X HY CLI-specific condition 019 non-string data cannot be sent in pieces SQL/CLI
HY020 X HY CLI-specific condition 020 attempt to concatenate a null value SQL/CLI
HY021 X HY CLI-specific condition 021 inconsistent descriptor information SQL/CLI
HY024 X HY CLI-specific condition 024 invalid attribute value SQL/CLI
HY055 X HY CLI-specific condition 055 non-string data cannot be used with string routine SQL/CLI
HY090 X HY CLI-specific condition 090 invalid string length or buffer length SQL/CLI
HY091 X HY CLI-specific condition 091 invalid descriptor field identifier SQL/CLI
HY092 X HY CLI-specific condition 092 invalid attribute identifier SQL/CLI
HY093 X HY CLI-specific condition 093 invalid datalink value SQL/MED
HY095 X HY CLI-specific condition 095 invalid FunctionId specified SQL/CLI
HY096 X HY CLI-specific condition 096 invalid information type SQL/CLI
HY097 X HY CLI-specific condition 097 column type out of range SQL/CLI
HY098 X HY CLI-specific condition 098 scope out of range SQL/CLI
HY099 X HY CLI-specific condition 099 nullable type out of range SQL/CLI
HY103 X HY CLI-specific condition 103 invalid retrieval code SQL/CLI
HY104 X HY CLI-specific condition 104 invalid LengthPrecision value SQL/CLI
HY105 X HY CLI-specific condition 105 invalid parameter mode SQL/CLI
HY106 X HY CLI-specific condition 106 invalid fetch orientation SQL/CLI
HY107 X HY CLI-specific condition 107 row value out of range SQL/CLI
HY108 X HY CLI-specific condition 108 invalid cursor position SQL/CLI
HYC00 X HY CLI-specific condition C00 optional feature not implemented SQL/CLI
HZ??? ? HZ Reserved for ISO9579 (RDA) ?

References

[edit]
  1. ^ "SQL:2008 draft" (Zip). Whitemarsh Information Systems Corporation. {{cite journal}}: Cite journal requires |journal= (help)