Re: Practical sets of SQLSTATE values?

I've been starting to look at assigning SQLSTATE values to all the
backend elog() calls, and have realized that the set of values defined
by the spec is very, how you say, uneven. They have conditions as
specific as "data exception/invalid time zone displacement value"
(22009) and yet nothing for cases as obvious as "no such function"
or "out of disk space". We're going to need a lot of implementation-
defined SQLSTATE codes if we want the facility to be as useful as it
should be.

What do other DBMSes do about this? Seems like it would make sense to
borrow as many SQLSTATE codes as we can from Oracle or DB2 or some other
big player ... especially if there's any commonality in their
extensions. Anyone have lists of implementation-defined SQLSTATEs for
the big commercial DBs?

regards, tom lane

> extensions. Anyone have lists of implementation-defined SQLSTATEs for
> the big commercial DBs?

http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2m0db2m002.htm#ToC

Chapter 12 has SQLState information. It's very short in most cases
(aside from 'Warning').

DB2 seems to use an SQLCode (Chapter 11) which catalogues all of the
error messages, the error code, and SQLState applied to it.

The entire book has to do with DB2 messages and their meaning.
--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Tom Lane wrote:
> What do other DBMSes do about this? Seems like it would make sense to
> borrow as many SQLSTATE codes as we can from Oracle or DB2 or some other
> big player ... especially if there's any commonality in their
> extensions. Anyone have lists of implementation-defined SQLSTATEs for
> the big commercial DBs?

Does this help?
http://www.csis.gvsu.edu/GeneralInfo/Oracle/appdev.920/a97269/pc_09err.htm#3174

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> Tom Lane wrote:
>> Anyone have lists of implementation-defined SQLSTATEs for
>> the big commercial DBs?

> Does this help?
> http://www.csis.gvsu.edu/GeneralInfo/Oracle/appdev.920/a97269/pc_09err.htm#3174

Some, but the mapping table is mostly pretty vague --- for instance,
it's quite unclear whether they provide subclasses in the 42xxx series
errors, or if they all come out as 42000. If there are subclasses,
which subclass codes correspond to which ORA-foo codes? A more complete
mapping table would help.

The same site has a copy of the complete Oracle 9i error message book:
http://www.csis.gvsu.edu/GeneralInfo/Oracle/server.920/a96525/toc.htm
but I couldn't find any mention at all of SQLSTATE codes in it. It's
pretty clear that Oracle regards SQLSTATE as an ugly stepchild. (Which
might be a fair assessment ;-), but it's at least somewhat standard ...)
We might do better following DB2's lead.

regards, tom lane

On Fri, 30 May 2003, Tom Lane wrote:

> What do other DBMSes do about this? Seems like it would make sense to
> borrow as many SQLSTATE codes as we can from Oracle or DB2 or some other
> big player ... especially if there's any commonality in their
> extensions. Anyone have lists of implementation-defined SQLSTATEs for
> the big commercial DBs?
>

On informix SQLSTATE is mostly for getting the oh.. lets call it the
"genre" of the error. They use a separate error code which contains the
specific error.

--
Jeff Trout <jeff(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

Tom Lane writes:

> I've been starting to look at assigning SQLSTATE values to all the
> backend elog() calls, and have realized that the set of values defined
> by the spec is very, how you say, uneven. They have conditions as
> specific as "data exception/invalid time zone displacement value"
> (22009) and yet nothing for cases as obvious as "no such function"
> or "out of disk space". We're going to need a lot of implementation-
> defined SQLSTATE codes if we want the facility to be as useful as it
> should be.

In my mind, distinct error codes are only useful if the application can
react differently to the condition. Hence, "no such function" can be
equated to "no such <anything>" or a general "syntax error", because the
action of the application in all those cases is likely the same (perhaps
show error text to user and make him fix the command). Similarly, "out of
disk space" can be put into a general "internal server error" class
because in all those cases the action is the same (show error text to
administrator and make him fix the problem). How this extends to "invalid
time zone displacement value" is a little beyond my reach right now, but
in general we should be able to get away with relatively few distinct
error codes.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> In my mind, distinct error codes are only useful if the application can
> react differently to the condition.

Agreed, we do not want to divide the error codes too finely. However,
we had a request on the lists just today for an error-code-based way to
detect whether the server failed because of running out of disk space,
and that wasn't by any means the first such request. So "out of disk
space" definitely deserves its own SQLSTATE, IMHO.

A nice property of the SQLSTATE design is that even if an application
doesn't recognize the exact code, it probably can recognize the
category (the first two characters), and the category is usually enough
to give it an idea of whether it can do anything useful or not. So for
example, as long as "no such function" is under the 42xxx (syntax error
or access rule violation) category, it shouldn't be a big problem for
applications to understand it well enough for their purposes. This is
specifically intended by the spec writers, I think, in view of this
note in SQL99:

NOTE 356 - One consequence of this is that an SQL-implementation
may, but is not required by ISO/IEC 9075 to, provide subcodes for
exception condition syntax error or access rule violation that
distinguish between the syntax error and access rule violation
cases.

We should probably expend more care on making sure we have the
categories right than on worrying about which errors deserve their
own subcodes. I also wonder whether we shouldn't explicitly document
someplace "if you don't recognize an XXYYY SQLSTATE, you may treat it
as XX000 instead".

regards, tom lane

>> Anyone have lists of implementation-defined SQLSTATEs for
>> the big commercial DBs?

This points to the Oracle docs.

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a87540/ch2.htm

Table 2-2 SQLSTATE Status Codes