SQLSTATE

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.

SQLSTATECat.ClassClass TextSubclassSubclass TextSQL part
00000S00successful completion000(no subclass)
01000W01warning000(no subclass)
01001W01warning001cursor operation conflict
01002W01warning002disconnect error
01003W01warning003null value eliminated in set function
01004W01warning004string data, right truncation
01005W01warning005insufficient item descriptor areas
01006W01warning006privilege not revoked
01007W01warning007privilege not granted
01009W01warning009search condition too long for information schema
0100AW01warning00Aquery expression too long for information schema
0100BW01warning00Bdefault value too long for information schema
0100CW01warning00Cresult sets returned
0100DW01warning00Dadditional result sets returned
0100EW01warning00Eattempt to return too many result sets
0100FW01warning00Fstatement too long for information schema
01010W01warning010column cannot be mappedSQL/XML
01011W01warning011SQL-Java path too long for information schemaSQL/JRT
01012W01warning012invalid number of conditions
0102FW01warning02Farray data, right truncation
02000N02no data000(no subclass)
02001N02no data001no additional result sets returned
07000X07dynamic SQL error000(no subclass)
07001X07dynamic SQL error001using clause does not match dynamic parameter specifications
07002X07dynamic SQL error002using clause does not match target specifications
07003X07dynamic SQL error003cursor specification cannot be executed
07004X07dynamic SQL error004using clause required for dynamic parameters
07005X07dynamic SQL error005prepared statement not a cursor specification
07006X07dynamic SQL error006restricted data type attribute violation
07007X07dynamic SQL error007using clause required for result fields
07008X07dynamic SQL error008invalid descriptor count
07009X07dynamic SQL error009invalid descriptor index
0700BX07dynamic SQL error00Bdata type transform function violation
0700CX07dynamic SQL error00Cundefined DATA value
0700DX07dynamic SQL error00Dinvalid DATA target
0700EX07dynamic SQL error00Einvalid LEVEL value
0700FX07dynamic SQL error00Finvalid DATETIME_INTERVAL_CODE
08000X08connection exception000(no subclass)
08001X08connection exception001SQL-client unable to establish SQL-connection
08002X08connection exception002connection name in use
08003X08connection exception003connection does not exist
08004X08connection exception004SQL-server rejected establishment of SQL-connection
08006X08connection exception006connection failure
08007X08connection exception007transaction resolution unknown
09000X09triggered action exception000(no subclass)
0A000X0Afeature not supported000(no subclass)
0A001X0Afeature not supported001multiple server transactions
0D000X0Dinvalid target type specification000(no subclass)
0E000X0Einvalid schema name list specification000(no subclass)
0F000X0Flocator exception000(no subclass)
0F001X0Flocator exception001invalid specification
0K000X0Kresignal when handler not active000(no subclass)SQL/PSM
0L000X0Linvalid grantor000(no subclass)
0M000X0Minvalid SQL-invoked procedure reference000(no subclass)
0N000X0NSQL/XML mapping error000(no subclass)SQL/XML
0N001X0NSQL/XML mapping error001unmappable XML nameSQL/XML
0N002X0NSQL/XML mapping error002invalid XML characterSQL/XML
0P000X0Pinvalid role specification000(no subclass)
0S000X0Sinvalid transform group name specification000(no subclass)
0T000X0Ttarget table disagrees with cursor specification000(no subclass)
0U000X0Uattempt to assign to non-updatable column000(no subclass)
0V000X0Vattempt to assign to ordering column000(no subclass)
0W000X0Wprohibited statement encountered during trigger execution000(no subclass)
0W001X0Wprohibited statement encountered during trigger execution001modify table modified by data change delta table
0X000X0Xinvalid foreign server specification000(no subclass)SQL/MED
0Y000X0Ypass-through specific condition000(no subclass)SQL/MED
0Y001X0Ypass-through specific condition001invalid cursor optionSQL/MED
0Y002X0Ypass-through specific condition002invalid cursor allocationSQL/MED
0Z000X0Zdiagnostics exception000(no subclass)
0Z001X0Zdiagnostics exception001maximum number of stacked diagnostics areas exceeded
0Z002X0Zdiagnostics exception002stacked diagnostics accessed without active handlerSQL/PSM
10000X10XQuery error000(no subclass)SQL/XML
20000X20case not found for case statement000(no subclass)SQL/PSM
21000X21cardinality violation000(no subclass)
22000X22data exception000(no subclass)
22001X22data exception001string data, right truncation
22002X22data exception002null value, no indicator parameter
22003X22data exception003numeric value out of range
22004X22data exception004null value not allowed
22005X22data exception005error in assignment
22006X22data exception006invalid interval format
22007X22data exception007invalid datetime format
22008X22data exception008datetime field overflow
22009X22data exception009invalid time zone displacement value
2200BX22data exception00Bescape character conflict
2200CX22data exception00Cinvalid use of escape character
2200DX22data exception00Dinvalid escape octet
2200EX22data exception00Enull value in array target
2200FX22data exception00Fzero-length character string
2200GX22data exception00Gmost specific type mismatch
2200HX22data exception00Hsequence generator limit exceeded
2200JX22data exception00Jnonidentical notations with the same nameSQL/XML
2200KX22data exception00Knonidentical unparsed entities with the same nameSQL/XML
2200LX22data exception00Lnot an XML documentSQL/XML
2200MX22data exception00Minvalid XML documentSQL/XML
2200NX22data exception00Ninvalid XML contentSQL/XML
2200PX22data exception00Pinterval value out of range
2200QX22data exception00Qmultiset value overflow
2200RX22data exception00RXML value overflowSQL/XML
2200SX22data exception00Sinvalid commentSQL/XML
2200TX22data exception00Tinvalid processing instructionSQL/XML
2200UX22data exception00Unot an XQuery document nodeSQL/XML
2200VX22data exception00Vinvalid XQuery context itemSQL/XML
2200WX22data exception00WXQuery serialization errorSQL/XML
22010X22data exception010invalid indicator parameter value
22011X22data exception011substring error
22012X22data exception012division by zero
22013X22data exception013invalid preceding or following size in window function
22014X22data exception014invalid argument for NTILE function
22015X22data exception015interval field overflow
22016X22data exception016invalid argument for NTH_VALUE function
22017X22data exception017invalid data specified for datalinkSQL/MED
22018X22data exception018invalid character value for cast
22019X22data exception019invalid escape character
2201AX22data exception01Anull argument passed to datalink constructorSQL/MED
2201BX22data exception01Binvalid regular expression
2201CX22data exception01Cnull row not permitted in table
2201DX22data exception01Ddatalink value exceeds maximum lengthSQL/MED
2201EX22data exception01Einvalid argument for natural logarithm
2201FX22data exception01Finvalid argument for power function
2201GX22data exception01Ginvalid argument for width bucket function
2201HX22data exception01Hinvalid row version
2201JX22data exception01JXQuery sequence cannot be validatedSQL/XML
2201KX22data exception01KXQuery document node cannot be validatedSQL/XML
2201LX22data exception01Lno XML schema foundSQL/XML
2201MX22data exception01Melement namespace not declaredSQL/XML
2201NX22data exception01Nglobal element not declaredSQL/XML
2201PX22data exception01Pno XML element with the specified QNameSQL/XML
2201QX22data exception01Qno XML element with the specified namespaceSQL/XML
2201RX22data exception01Rvalidation failureSQL/XML
2201SX22data exception01Sinvalid Query regular expression
2201TX22data exception01Tinvalid Query option flag
2201UX22data exception01Uattempt to replace a zero-length string
2201VX22data exception01Vinvalid Query replacement string
2201WX22data exception01Winvalid row count in fetch first clause
2201XX22data exception01Xinvalid row count in result offset clause
22021X22data exception021character not in repertoire
22022X22data exception022indicator overflow
22023X22data exception023invalid parameter value
22024X22data exception024unterminated C string
22025X22data exception025invalid escape sequence
22026X22data exception026string data, length mismatch
22027X22data exception027trim error
22029X22data exception029noncharacter in UCS string
2202AX22data exception02Anull value in field referenceSQL/PSM
2202DX22data exception02Dnull value substituted for mutator subject parameter
2202EX22data exception02Earray element error
2202FX22data exception02Farray data, right truncation
2202GX22data exception02Ginvalid repeat argument in a sample clause
2202HX22data exception02Hinvalid sample size
23000X23integrity constraint violation000(no subclass)
23001X23integrity constraint violation001restrict violation
24000X24invalid cursor state000(no subclass)
25000X25invalid transaction state000(no subclass)
25001X25invalid transaction state001active SQL-transaction
25002X25invalid transaction state002branch transaction already active
25003X25invalid transaction state003inappropriate access mode for branch transaction
25004X25invalid transaction state004inappropriate isolation level for branch transaction
25005X25invalid transaction state005no active SQL-transaction for branch transaction
25006X25invalid transaction state006read-only SQL-transaction
25007X25invalid transaction state007schema and data statement mixing not supported
25008X25invalid transaction state008held cursor requires same isolation level
26000X26invalid SQL statement name000(no subclass)
27000X27triggered data change violation000(no subclass)
27001X27triggered data change violation001modify table modified by data change delta table
28000X28invalid authorization specification000(no subclass)
2B000X2Bdependent privilege descriptors still exist000(no subclass)
2C000X2Cinvalid character set name000(no subclass)
2D000X2Dinvalid transaction termination000(no subclass)
2E000X2Einvalid connection name000(no subclass)
2F000X2FSQL routine exception000(no subclass)
2F002X2FSQL routine exception002modifying SQL-data not permitted
2F003X2FSQL routine exception003prohibited SQL-statement attempted
2F004X2FSQL routine exception004reading SQL-data not permitted
2F005X2FSQL routine exception005function executed no return statement
2H000X2Hinvalid collation name000(no subclass)
30000X30invalid SQL statement identifier000(no subclass)
33000X33invalid SQL descriptor name000(no subclass)
34000X34invalid cursor name000(no subclass)
35000X35invalid condition number000(no subclass)
36000X36cursor sensitivity exception000(no subclass)
36001X36cursor sensitivity exception001request rejected
36002X36cursor sensitivity exception002request failed
38000X38external routine exception000(no subclass)
38001X38external routine exception001containing SQL not permitted
38002X38external routine exception002modifying SQL-data not permitted
38003X38external routine exception003prohibited SQL-statement attempted
38004X38external routine exception004reading SQL-data not permitted
39000X39external routine invocation exception000(no subclass)
39004X39external routine invocation exception004null value not allowed
3B000X3Bsavepoint exception000(no subclass)
3B001X3Bsavepoint exception001invalid specification
3B002X3Bsavepoint exception002too many
3C000X3Cambiguous cursor name000(no subclass)
3D000X3Dinvalid catalog name000(no subclass)
3F000X3Finvalid schema name000(no subclass)
40000X40transaction rollback000(no subclass)
40001X40transaction rollback001serialization failure
40002X40transaction rollback002integrity constraint violation
40003X40transaction rollback003statement completion unknown
40004X40transaction rollback004triggered action exception
42000X42syntax error or access rule violation000(no subclass)
44000X44with check option violation000(no subclass)
45000X45unhandled user-defined exception000(no subclass)SQL/PSM
46000X46OLB-specific error000(no subclass)SQL/OLB
46001X46Java DDL001invalid URLSQL/JRT
46002X46Java DDL002invalid JAR nameSQL/JRT
46003X46Java DDL003invalid class deletionSQL/JRT
46005X46Java DDL005invalid replacementSQL/JRT
4600AX46Java DDL00Aattempt to replace uninstalled JARSQL/JRT
4600BX46Java DDL00Battempt to remove uninstalled JARSQL/JRT
4600CX46Java DDL00Cinvalid JAR removalSQL/JRT
4600DX46Java DDL00Dinvalid pathSQL/JRT
4600EX46Java DDL00Eself-referencing pathSQL/JRT
46102X46Java DDL102invalid JAR name in pathSQL/JRT
46103X46Java DDL103unresolved class nameSQL/JRT
46110X46OLB-specific error110unsupported featureSQL/OLB
46120X46OLB-specific error120invalid class declarationSQL/OLB
46121X46OLB-specific error121invalid column nameSQL/OLB
46122X46OLB-specific error122invalid number of columnsSQL/OLB
46130X46OLB-specific error130invalid profile stateSQL/OLB
HW000XHWdatalink exception000(no subclass)SQL/MED
HW001XHWdatalink exception001external file not linkedSQL/MED
HW002XHWdatalink exception002external file already linkedSQL/MED
HW003XHWdatalink exception003referenced file does not existSQL/MED
HW004XHWdatalink exception004invalid write tokenSQL/MED
HW005XHWdatalink exception005invalid datalink constructionSQL/MED
HW006XHWdatalink exception006invalid write permission for updateSQL/MED
HW007XHWdatalink exception007referenced file not validSQL/MED
HV000XHVFDW-specific condition000(no subclass)SQL/MED
HV001XHVFDW-specific condition001memory allocation errorSQL/MED
HV002XHVFDW-specific condition002dynamic parameter value neededSQL/MED
HV004XHVFDW-specific condition004invalid data typeSQL/MED
HV005XHVFDW-specific condition005column name not foundSQL/MED
HV006XHVFDW-specific condition006invalid data type descriptorsSQL/MED
HV007XHVFDW-specific condition007invalid column nameSQL/MED
HV008XHVFDW-specific condition008invalid column numberSQL/MED
HV009XHVFDW-specific condition009invalid use of null pointerSQL/MED
HV00AXHVFDW-specific condition00Ainvalid string formatSQL/MED
HV00BXHVFDW-specific condition00Binvalid handleSQL/MED
HV00CXHVFDW-specific condition00Cinvalid option indexSQL/MED
HV00DXHVFDW-specific condition00Dinvalid option nameSQL/MED
HV00JXHVFDW-specific condition00Joption name not foundSQL/MED
HV00KXHVFDW-specific condition00Kreply handleSQL/MED
HV00LXHVFDW-specific condition00Lunable to create executionSQL/MED
HV00MXHVFDW-specific condition00Munable to create replySQL/MED
HV00NXHVFDW-specific condition00Nunable to establish connectionSQL/MED
HV00PXHVFDW-specific condition00Pno schemasSQL/MED
HV00QXHVFDW-specific condition00Qschema not foundSQL/MED
HV00RXHVFDW-specific condition00Rtable not foundSQL/MED
HV010XHVFDW-specific condition010function sequence errorSQL/MED
HV014XHVFDW-specific condition014limit on number of handles exceededSQL/MED
HV021XHVFDW-specific condition021inconsistent descriptor informationSQL/MED
HV024XHVFDW-specific condition024invalid attribute valueSQL/MED
HV090XHVFDW-specific condition090invalid string length or buffer lengthSQL/MED
HV091XHVFDW-specific condition091invalid descriptor field identifierSQL/MED
HY000XHYCLI-specific condition000(no subclass)SQL/CLI
HY???XHYCLI-specific conditionn/adynamic parameter value neededSQL/CLI
HY???XHYCLI-specific conditionn/ainvalid handleSQL/CLI
HY001XHYCLI-specific condition001memory allocation errorSQL/CLI
HY003XHYCLI-specific condition003invalid data type in application descriptorSQL/CLI
HY004XHYCLI-specific condition004invalid data typeSQL/CLI
HY007XHYCLI-specific condition007associated statement is not preparedSQL/CLI
HY008XHYCLI-specific condition008operation canceledSQL/CLI
HY009XHYCLI-specific condition009invalid use of null pointerSQL/CLI
HY010XHYCLI-specific condition010function sequence errorSQL/CLI
HY011XHYCLI-specific condition011attribute cannot be set nowSQL/CLI
HY012XHYCLI-specific condition012invalid transaction operation codeSQL/CLI
HY013XHYCLI-specific condition013memory management errorSQL/CLI
HY014XHYCLI-specific condition014limit on number of handles exceededSQL/CLI
HY017XHYCLI-specific condition017invalid use of automatically allocated descriptor handleSQL/CLI
HY018XHYCLI-specific condition018server declined the cancellation requestSQL/CLI
HY019XHYCLI-specific condition019non-string data cannot be sent in piecesSQL/CLI
HY020XHYCLI-specific condition020attempt to concatenate a null valueSQL/CLI
HY021XHYCLI-specific condition021inconsistent descriptor informationSQL/CLI
HY024XHYCLI-specific condition024invalid attribute valueSQL/CLI
HY055XHYCLI-specific condition055non-string data cannot be used with string routineSQL/CLI
HY090XHYCLI-specific condition090invalid string length or buffer lengthSQL/CLI
HY091XHYCLI-specific condition091invalid descriptor field identifierSQL/CLI
HY092XHYCLI-specific condition092invalid attribute identifierSQL/CLI
HY093XHYCLI-specific condition093invalid datalink valueSQL/MED
HY095XHYCLI-specific condition095invalid FunctionId specifiedSQL/CLI
HY096XHYCLI-specific condition096invalid information typeSQL/CLI
HY097XHYCLI-specific condition097column type out of rangeSQL/CLI
HY098XHYCLI-specific condition098scope out of rangeSQL/CLI
HY099XHYCLI-specific condition099nullable type out of rangeSQL/CLI
HY103XHYCLI-specific condition103invalid retrieval codeSQL/CLI
HY104XHYCLI-specific condition104invalid LengthPrecision valueSQL/CLI
HY105XHYCLI-specific condition105invalid parameter modeSQL/CLI
HY106XHYCLI-specific condition106invalid fetch orientationSQL/CLI
HY107XHYCLI-specific condition107row value out of rangeSQL/CLI
HY108XHYCLI-specific condition108invalid cursor positionSQL/CLI
HYC00XHYCLI-specific conditionC00optional feature not implementedSQL/CLI
HZ????HZReserved for ISO9579 (RDA)???

References

  1. "SQL:2008 draft" (Zip). Whitemarsh Information Systems Corporation. Cite journal requires |journal= (help)
This article is issued from Wikipedia. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.