Subject Re: [Firebird-Architect] Proposal to add SQLState to Vulcan
Author Dmitry Yemanov
Bill Oliver wrote:
> I look forward to your response!

A few questions:

1) Am I right in understanding that the only purpose of
isc_arg_sql_state being added to the status vector is to remove an
ambiguity for some GDSCODEs for a proper mapping into SQLSTATEs? In
other words, we generally rely on the mapping table, but sometimes force
the necessary SQLSTATE via isc_arg_sql_state?

While I would accept that as an intermediate solution, I'd really would
like to avoid the engine internals being SQLSTATE aware, as it requires
us to permanently keep SQLSTATEs in mind when throwing GDS errors.

Could we think of any alternative solution? For example, implement a
multi-level mapping table and solve the ambiguity issues using secondary
GDS codes?

- "String data, right-truncated"
ERR_post(isc_arith_except, isc_arg_gds, isc_string_truncation, 0);
mapping: SQLSTATE 22001 <-> {isc_arith_except, isc_string_truncation}

- "Division by zero"
ERR_post(isc_arith_except, isc_arg_gds, isc_division_by_zero, 0);
mapping: SQLSTATE 22012 <-> {isc_arith_except, isc_division_by_zero}

and so on.

This doesn't solve the DYN problem, so those errors should be
de-randomized (i.e. redefined using proper GDS codes in the DYN facility
group). But you seem to suggesting that anyway (defining symbols for DYN


2) The standard declares the list of SQLSTATEs separated by class and
subclass. PGSQL also documents its error the same way. Thinking about
that, does it really make sense to replace columns SQL_CLASS and
SQL_SUBCLASS with a single one SQL_STATE? Also, with these columns being
separated, we could allow subclass values to be omitted (and implicitly
treated as '000' -- exactly how the standard defines that).

3) Is the SQLSTATE value 'HY000' widely used as a generic state code
among other RDBMS? The only place where it's mentioned in the SQL
specification is related to CLI_SPECIFIC_CONDITION_NO_SUBCLASS. But I
see 'HY000' being used by MySQL and DB2. Also, I cannot find other
subclasses of the 'HY' class in the SQL:2003 document.

4) Please prefix the new API call with "fb_" rather then with "isc_" :-)
Also, the "gds__" counterpart is not required, as there's no legacy
interface to care about in this case.

5) I don't think there's a real need to hide SQLSTATE in ISQL by
default, so perhaps we could live without the SET SQLSTATE command.
Although I'd expect the guys maintaining the TCS and QMDB scripts to
argue here :-)

> I think it would be better to make these database changes also to FB2
> head.
> [...]
> If the message database file changes are pushed to FB2, the code
> changes could wait until the FB3 timeframe.

Sounds reasonable.