Hi Thomas, On Apr 28, 4:44 pm, Thomas Mueller <[email protected]> wrote: > Are SQL states really standardized to the last digit? When searching > for "sqlstate23503" in Google, I find IBM DB2 documentation, but no > link to any standard.
I managed to get my hands on the SQL:2008 draft. I finally have a conclusive answer to your question: ------quote------- The character string value returned in an SQLSTATE parameter comprises a 2-character class value followed by a 3-character subclass value, each with an implementation-defined character set that has a one-octet character encoding form and is restricted to <digit>s and <simple Latin upper case letter>s. Table 33, “SQLSTATE class and subclass values”, specifies the class value for each condition and the subclass value or values for each class value. Class values that begin with one of the <digit>s '0', '1', '2', '3', or '4' or one of the <simple Latin upper case letter>s 'A', 'B', 'C', 'D', 'E', 'F', 'G', or 'H' are returned only for conditions defined in ISO/IEC 9075 or in any other International Standard. The range of such class values are called standard-defined classes. Some such class codes are reserved for use by specific International Standards, as specified elsewhere in this Clause. Subclass values associated with such classes that also begin with one of those 13 characters are returned only for conditions defined in ISO/IEC 9075 or some other International Standard. The range of such class values are called standard- defined classes. Subclass values associated with such classes that begin with one of the <digit>s '5', '6', '7', '8', or '9' or one of the <simple Latin upper case letter>s 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', or 'Z' are reserved for implementation-specified conditions and are called implementation-defined subclasses. Class values that begin with one of the <digit>s '5', '6', '7', '8', or '9' or one of the <simple Latin upper case letter>s 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', or 'Z' are reserved for implementation-specified exception conditions and are called implementation-defined classes. All subclass values except '000', which means no subclass, associated with such classes are reserved for implementation-specified conditions and are called implementation-defined subclasses. An implementation-defined completion condition shall be indicated by returning an implementation-defined subclass in conjunction with one of the classes successful completion, warning, or no data. If a subclass value is not specified for a condition, then either subclass '000' or an implementation-defined subclass is returned. ------quote------- So if I understand correctly, you are allowed to define implementation- specific SQLStates for standard categories (such as 23xxx) so long as the sub-class begins with the implementation-specific characters. In other words, it should be legal to use define a implementation- specific code 23053. The standard goes on to define specific SQLStates (down to the last 5 characters). I can point you to the full document if you wish to read further. Gili -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
