Re: Transactions in JDBC with Oracle

From: Bob Withers <bwit_at_pobox.com>
Date: Sun, 26 Sep 1999 12:46:47 GMT
Message-ID: <MPG.1257cf511de7578798969f@news.mciworld.com>

[This followup was posted to comp.databases and a copy was sent to scott_at_transactionengines.com if this address didn't require editing.]

In article <7sishk$3gp$1_at_bgtnsc02.worldnet.att.net>, scott_at_transactionengines.com says...
> How do you specify a transaction in jdbc connecting to an oracle dbms?
> Let's say I want to have a unique number generated through sql. So, I have
> a table with a field called Count. I want to have one transaction with two
> sql statements. The first one is update count = count + 1 and the second
> one retrieves the count. This way every call gets a unique number.
>
> How do I specify this? I've tried resultSet =
> statement.executeQuery("UPDATE etc;SELECT etc");
> but this gives me an error..
>
> Thanks for any help with this. If anybody wants to become rich through a
> company with SOLID technology, we're located in downtown miami. Transaction
> Engines needs java programmers and oracle dbas. Very generous
> options/salary/benifiits for the right people!
>

int getCount(Connection con)
{

    bool ac = con.getAutoCommit();
    con.setAutoCommit(false);
    try
    {

        Statement stmt = con.createStatement();
        stmt.executeUpdate("update counttab set count = count + 1");
        ResultSet rs = stmt.executeQuery("select count from counttab");
        rs.next();
        int cnt = rs.getInt(1);
        con.commit();
        return(cnt);

    }
    catch (SQLException e)
    {

        con.rollback();
        throw e;

    }
    finally
    {

        con.setAutoCommit(ac);
    }
}

Or if you don't mind being Oracle specific you could just use an Oracle sequence.

Regards,
Bob

--

---

Bob Withers               Do or do not, there is no try
bwit_at_pobox.com                                  Yoda

http://www.pobox.com/~bwit

---

Received on Sun Sep 26 1999 - 07:46:47 CDT