Re: [HACKERS] Practical sets of SQLSTATE values?
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 [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Practical sets of SQLSTATE values?
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Practical sets of SQLSTATE values?
Joe Conway [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Practical sets of SQLSTATE values?
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 [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Practical sets of SQLSTATE values?
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Practical sets of SQLSTATE values?
Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Practical sets of SQLSTATE values?
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly