Subject SQLSTATE and Firebird
Author Tom Cole
What is the current state of thinking on support for SQLSTATE in Firebird/Vulcan? Since I work on an application that must be able to connect to multiple databases (including Vulcan!), I think that a SQLSTATE value that is part of the return status block is very useful in attempting to consolidate programmatic response to error states.

SQLSTATE is not uniformly implemented across all databases, of course. That is, there's a fairly small subset of states that could be considered truly common across all databases. However, I think it would be very desirable to support at least that subset. I should think that the data could be encoded in the return status vector, and simply skipped for the purposes of text messaging to the user. However, it would be available in the vector for programmatic interpretation.

I imagine we'd want something like a new return code stack entry of isc_arg_sqlstate which takes as it's argument a pointer to a five-character string which contains the SQLSTATE value. We would then be able to document the subset of SQLSTATE values that we return that are relatively uniform with respect to other databases. Since there are probably some pretty fringe database implementations, I'd be happy with supporting something that plays well with DB2 and ORACLE.

For the most part, I think this _could_ be done in OSRIException as a "post processing" of the signaled exception to add the return code (with a nice default of something like "42000" which is a generic access violation) for unrecognized or unsupported error states.

If this has value, then we'd need follow-up discussion on selecting a subset of states we'd support, a mechanism for returning that information, and agreements on how to maintain the relationship between return codes and SQLSTATE as the product evolves.

My question for the architectural community is to see if there interest in a SQLSTATE return code mechanism? I think that if we only expect users to connect to Firebird/Vulcan then it's not important; if we expect user applications to live in the larger enterprise where client connections to multiple providers is more common, then SQLSTATE has significant value. If it's not clear, I think we need SQLSTATE... :-)

Tom Cole, Platform R&D, SAS Institute Inc.
Tom's Cartesian Corollary: "I am nothing, if not considerate."