Re: JDBC SQLStatus for Dynamic SQL, unsupported feature, - Mailing list pgsql-patches

Barry Lind wrote:
> Fernando,
>
> Can you provide a reason these patches are necessary?  Are there some
> specific use cases that are trying to be addressed?
>

The most concrete case for the need of SQLState is the one I gave in the
first patch.  We have a GUI tool that connects to remotes databases and,
sometimes, due to a network problem or a backend problem (there was an
assertion failure in one occasion and the backend was aborted), the
connection goes away.  Without a proper SQLState, we have to parse the
error message to check if the connection is gone so that we can re-set
the GUI properly (i.e., indicate that we are disconnected, disable some
buttons etc.).  If we don't do this, the user can continue to try and
send things (as the GUI still reports being connected) and things get
really inconsistent (nothing hapens, the previous error message is
repeated when is not applicable anymore, stack traces show up in the
console etc.).


> I am very reluctant to apply all of this.  The reason being, that
> without a commitment on how error codes are going to be implemented in
> the backend, I don't want to partially implement them in the jdbc
> driver.

All the error codes I've been adding so far are driver generated ones,
i.e., conditions detected at the client side.  They are not going to
change regardless of whayt the backend does.  Please read on for my
thinking on the bacend part.

> There needs to be a consistent implementation between the jdbc
> driver and the backend (since the jdbc driver will end up passing the
> backend's codes to the client).

We have to be X/Open compliant anyway (for JDBC1 and JDBC2).  We do have
the option of using the ANSI codes for JDBC3, which would add a few
extra standard error codes when compared with the X/Open.  Most of the
codes are common to both specs.

>  Depending on what the backend
> implementation is, that might even mean that the jdbc driver will
> support error codes in a non-standard way.  I am much more concerned
> with consistency between the two sets of error codes than I am with
> following the standard at the moment.   I really want to avoid needing to
> have the reinterpret all the backend error codes to conform to how these
> patches think error codes should be handled.  That will be a maintenance
> nightmare.
>

The backend will either follow the ANSI or the X/Open standards, and
there are just a few extra ANSI codes that we would have to map back to
X/Open or use implementation-defined codes.  If the backend uses some
non standard coding (which I doubt it will do as we are trying to get as
SQL compliant as possible), it would not be advisable for the JDBC
driver to blindly pass those on.  Some of the value of the JDBC is to be
as standard as possible.

The codes that will have to be invented by the backend will follow the
ANSI conventions for implementation-defined error codes.  And here is
were we are lucky: the rules are the same for both ANSI and X/Open.  Any
  valid implementation-defined ANSI code is also a valid
implementation-defined X/Open code.  So, except for a few extra standard
codes defined in the SQL99, we can pass all others on irrespectively of
what we are using for JDBC3: X/Open or ANSI style SQLState.

P.S. We can always decide to use ANSI style SQLState for JDBC3 if we
want.  We would have top abstract the PSQLException class but it is not
difficult to support bothe the X/Open for JDBC1,2 and ANSI for JDBC3.
I just find it very confusing for application to handle both sets and
there is absolutely no advantage in switching to the ANSI one (just for
a few extra codes).


> I think this patch goes beyond what I am comfortable applying without
> any guidance on how/when/if error codes are going to be implemented in
> the backend.
>

I would say that it is independent of the backend.  We only have two
choices to be compliant with JDBC: X/Open and ANSI.  All codes in the
patch are both ANSI and X/Open standard (plus the de facto 08S01 one,
which is an implementation-defined one anyway).  The only real choice
made was to return XOpen in the getSQLStateType() routine, which can be
altered at any time (JDBC3 applications are supposed to check that anyway).



--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9