Thread: Practical sets of SQLSTATE values?

Practical sets of SQLSTATE values?

From
Tom Lane
Date:
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


Re: Practical sets of SQLSTATE values?

From
Rod Taylor
Date:
> 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@rbt.ca>

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

Re: Practical sets of SQLSTATE values?

From
Joe Conway
Date:
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



Re: Practical sets of SQLSTATE values?

From
Tom Lane
Date:
Joe Conway <mail@joeconway.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


Re: Practical sets of SQLSTATE values?

From
Jeff
Date:
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@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/




Re: Practical sets of SQLSTATE values?

From
Peter Eisentraut
Date:
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@gmx.net



Re: Practical sets of SQLSTATE values?

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.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
9075to, provide subcodes for        exception condition syntax error or access rule violation that        distinguish
betweenthe 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


Re: Practical sets of SQLSTATE values?

From
Michael Brusser
Date:
>> 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