Re: autocommit and stored procedures

Thanks for the prompt answer.
I had a look into the source code in the meanwhile and it seems as
you say:
Autocommit(true) means no explicit BEGIN transaction from the JDBC
driver,
which at the server side means an implicit transaction around the
command sent.
If that is a stored procedure call, the whole stored procedure runs
as one transaction.

Uwe

On 15 Aug 2007, at 23:50, Mark Lewis wrote:

> On Wed, 2007-08-15 at 21:21 +1000, roehm(at)it(dot)usyd(dot)edu(dot)au wrote:
>> Hi,
>>
>> Which command granularity does the JDBC driver's autocommit have?
>> Does it commit after each client-side JDBC statement, or does it
>> commit
>> each individual SQL statement on the server-side?
>> In particular, does an JDBC autocommit around the call to a stored
>> procedure
>> commit each statement within that stored procedure, or does it commit
>> the
>> procedure as a whole?
>>
>> Example:
>>
>> Stored Procedure
>> ----------------
>> CREATE PROCEDURE Test (n varying character,val REAL)
>> DECLARE
>> cid INTEGER;
>> BEGIN
>> SELECT custid INTO cid
>> FROM account
>> WHERE name=n;
>>
>> UPDATE checking
>> SET balance=balance-val
>> WHERE custid=cid;
>> END;
>>
>> JDBC Code
>> ---------
>> Connection conn;
>> CallableStatement cstmt;
>> conn.setAutoCommit(true);
>> cstmt = conn.prepareCall("{call Test(?,?)}");
>> cstmt.setString(1, name);
>> cstmt.setString(2, value);
>> cstmt.execute();
>>
>>
>> Does PostgreSQL execute one commit after the execution of Test(),
>> i.e. do select and update run in one transaction?
>> Or will PostgreSQL commit after the select and then again after the
>> update
>> inside the Test() procedure?
>>
>> Does anyone know what the specified behaviour is for JDBC AutoCommit?
>>
>> Many thanks
>>
>> Uwe
>
>
> The JDBC driver manages the autocommit flag by not beginning a new
> transaction at all, since in PostgreSQL all statements execute in
> their
> own private transactions unless an explicit transaction is started
> with
> the BEGIN statement.
>
> Therefore, you would expect each individual statement (as
> recognized by
> the PG back-end, not by your Java code) to execute and commit
> individually.
>
> I do not know whether or not JDBC specifies a behavior for this case,
> but given the intentional vagueness of the spec in several areas, I
> would be very surprised if the the spec weren't either silent or
> answered, "Executing multiple SQL commands in a single JDBC command is
> unsupported and may result in unspecified, driver-specific behavior".
>
> -- Mark
>

--
Dr. Uwe Roehm
School of Information Technologies
University of Sydney, NSW 2006, Australia