Re: problem with new autocommit config parameter and jdbc

On Mon, 9 Sep 2002, Tom Lane wrote:

> snpe <snpe@snpe.co.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@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC