Re: problem with new autocommit config parameter and jdbc

On Mon, 9 Sep 2002, Tom Lane wrote:

> snpe <snpe(at)snpe(dot)co(dot)yu> writes:
>
> > snpe> select * from org_ba;
> > ERROR: relation org_ba does not exists
> > snpe> select * from org_ban;
> > ERROR: current transactions is aborted, queries ignored until end of
> > transaction block
>
> Um, what's wrong with that?
>
> It seems to me that an application that is using autocommit=off will
> expect the first SELECT to start a transaction block.

Yup. In fact, the standard (at least, insofar as I have information
relating to it), specifies that the first SELECT statement above
*must* start a transaction.

From Date's _A Guide to the SQL Standard_ (Fourth Edition):

An SQL-transaction is initiated when the relevant SQL-agent executes
a "transaction-initiating" SQL Statement (see below) and the
SQL-agent does not already have an SQL-transaction in progress.
...
The following SQL statements are _not_ transaction-initiating:

CONNECT
SET CONNECTION
DISCONNECT
SET SESSION AUTHORIZATION
SET CATALOG
SET SCHEMA
SET NAMES
SET TIME ZONE
SET TRANSACTION
SET CONSTRAINTS
COMMIT
ROLLBACK
GET DIAGNOSTICS

Nor, of course, are the nonexecutable statements DECLARE CURSOR,
DECLAR LOCAL TEMPORARY TABLE, BEGIN DECLARE SECTION, SEND DECLARE
SECTIONS, and WHENEVER.

So SELECT ought always to initiate a transaction, if one is not already
in progress. If auto-commit is enabled, of course, that statement may
be committed immediately after execution, if it doesn't fail.

As far as the JDBC driver goes, I'm not too sure of the issues here, but
it should certainly be ensuring that autocommit is enabled, as per the
JDBC specification, when a new connection is created. I see no reason
this couldn't be done with a "SET AUTOCOMMIT TO OFF" or whatever, if
that's necessary to override a possible configuration file setting.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC