Re: JDBC / stored procedures / transaction/ rollback capability

From: <dhagberg_at_millibits.com>
Date: Tue, 14 Dec 1999 19:45:26 GMT
Message-ID: <8366ok$ilk$1@nnrp1.deja.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.18574_at_news1.alsv1.occa.home.com>,   "CaboDog" <cabodog_at_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. Received on Tue Dec 14 1999 - 13:45:26 CST