Skip site navigation (1) Skip section navigation (2)

autocommit and stored procedures

From: roehm(at)it(dot)usyd(dot)edu(dot)au
To: pgsql-jdbc(at)postgresql(dot)org
Subject: autocommit and stored procedures
Date: 2007-08-15 11:21:09
Message-ID: D3A9FBB1-6304-4B25-878D-E0F1C06280BA@it.usyd.edu.au (view raw or flat)
Thread:
Lists: pgsql-jdbc
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



Responses

pgsql-jdbc by date

Next:From: Markus SchaberDate: 2007-08-15 11:58:46
Subject: Re: Install two different versions of postgres which should run in parallel
Previous:From: Loredana CurugiuDate: 2007-08-14 07:37:47
Subject: Re: [NOVICE] Install two different versions of postgres which should run in parallel

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group