JDBC / stored procedures / transaction/ rollback capability

[CaboDog]

From my Java program, I want to call stored procedures on an Oracle database
that perform inserts to tables. If I call more than one of these stored
procedures in succession (using JDBC callable statements) inside a
transaction wrapper (in other words, I execute 2 or more of these insert
stored procedures before committing) across the same connection in my Java
program, if I issue a rollback, will everything that those stored procedures
have done be rolled back, or is the only possible thing that can be rolled
back in JDBC dynamic sql?

Brad

[Thomas Kyte]

A copy of this was sent to "CaboDog" <cab...@home.com>
(if that email address didn't require changing)

As long as the connection is not set to autocommit (eg:

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
oracleConnection = DriverManager.getConnection(...);
oracleConnection.setAutoCommit(false);

)

and the stored procedures do not commit themselves, a rollback will rollback
both of them.

>Brad
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte tk...@us.oracle.com
Oracle Service Industries Reston, VA USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

[Brian Seery]

You wont loose the chages of the stored procedures if you commit as soon as
they have finished. If you call commit the rollback will only erase what was
done since the last commit!

Regards

Brian>
>

[Roman V. Yakovlev]

> if I issue a rollback, will everything that those stored procedures
> have done be rolled back, or is the only possible thing that can be rolled
> back in JDBC dynamic sql?

Usually, rollback is described and is done in a stored procedure. Then all is
OK.
By default rollback will roll away only before expression, in which there was an
error.
And as far as I understand, JDBC will not do rollback ...:))..

[dhag...@millibits.com]

JDBC Connection objects have the autoCommit property which can be
turned on and off via:

setAutoCommit(boolean);

By default, autoCommit is true, which means that each select/update/
insert/delete statement is wrapped in an implicit transaction.

When you set autoCommit to false, you must use commit() or rollback()
in your JDBC calls to end transactions. NOTE that this requires some
careful coding to deal with exceptions. Usually you want to use
try/finally blocks to ensure either commit or rollback occurred:

Connection c = ConnectionPool.checkout();
c.setAutoCommit(false);
CallableStatement cs = null;
boolean committed = false;
try {
cs = c.prepareCall("{call proc1(?,?)}");
cs.setInt(1,val);
cs.registerOutParam....
cs.executeUpdate();
cs.close();
cs = null;

cs = c.prepareCall("{call proc2(?)}");
cs.registerOut...
cs.executeUpdate();
cs.close();
cs = null;
c.commit();
committed = true;
}
finally {
if(cs != null) try {cs.close();} catch(SQLException ex1) {}
if(!committed) try {c.rollback();} catch(SQLException ex2) {}
ConnectionPool.checkin(c);
}

In article <A5F44.751$ds3....@news1.alsv1.occa.home.com>,

"CaboDog" <cab...@home.com> wrote:
> From my Java program, I want to call stored procedures on an Oracle
database
> that perform inserts to tables. If I call more than one of these
stored
> procedures in succession (using JDBC callable statements) inside a
> transaction wrapper (in other words, I execute 2 or more of these
insert
> stored procedures before committing) across the same connection in my
Java

> program, if I issue a rollback, will everything that those stored

procedures
> have done be rolled back, or is the only possible thing that can be
rolled
> back in JDBC dynamic sql?

Sent via Deja.com http://www.deja.com/
Before you buy.