CODEWITHSUNDEEP
javasqloracle-databasejdbctransactions
Michael
Michael

Reputation: 1164

Oracle - How to execute transaction correctly?

My statement:

statement.addBatch("START TRANSACTION;" +
                "UPDATE FIRST_TABLE SET FIRST_FIELD = 1;" +
                "UPDATE SECOND_TABLE SET SECOND_FIELD = 2;" +
                "UPDATE THIRD_TABLE SET THIRD_FIELD = 3;" +
                "COMMIT;");

And exception throws in next line:

statement.executeBatch();

The same thing happens if I use the method execute:

statement.execute(myTransaction);

The exception:

java.sql.BatchUpdateException: ORA-00900: invalid SQL statement

I use Oracle. I don't have any exception if I execute this statement in command line or SQL IDE. It works properly in this case. There is this exception only in java code. What is wrong?

Upvotes: 0

Views: 2383

Answers (4)

Razvan
Razvan

Reputation: 10093

As a side note,in order to create a batch of SQLs you have to separately add each statement to the batch.

So, instead of: 

     statement.addBatch("START TRANSACTION;" +
            "UPDATE FIRST_TABLE SET FIRST_FIELD = 1;" +
            "UPDATE SECOND_TABLE SET SECOND_FIELD = 2;" +
            "UPDATE THIRD_TABLE SET THIRD_FIELD = 3;" +
            "COMMIT;");

Use: 

     statement.addBatch("START TRANSACTION;");
     statement.addBatch("UPDATE FIRST_TABLE SET FIRST_FIELD = 1;");
     ....

But, this is not the standard way of running transactions. To run a transaction:

    con.setAutoCommit(false); //so that a transaction is not committed after each
                              //statement

    //run you queries
    statement.executeUpdate(); //or statement.executeBatch();
    ...
    //in the end commit
    con.commit();

Upvotes: 0

KV Prajapati
KV Prajapati

Reputation: 94625

Try,

connection.setAutoCommit(false);
statement.addBatch("UPDATE FIRST_TABLE SET FIRST_FIELD = 1");
statement.addBatch("UPDATE SECOND_TABLE SET SECOND_FIELD = 2");
statement.addBatch("UPDATE THIRD_TABLE SET THIRD_FIELD = 3");
int [] counts = statement.executeBatch();
connection.commit();

Upvotes: 3

CloudyMarble
CloudyMarble

Reputation: 37566

Try to add each Update Statement in a separated addBatch()

Upvotes: 1

Vikdor
Vikdor

Reputation: 24124

Generally, you would using the connection's setAutoCommit() method to start a transaction, run the statements, and finally call commit() or rollback() appropriately. This is kind of a DB-agnostic way to work in transactions. Not sure, why you want to construct SQL for the same! Could you elaborate on your use case?

Connection con = dataSource.getConnection();
try {
    con.setAutoCommit(false);
    Statement stmt = con.prepareStatement("<your update statement with placeholders for  parameters");
    stmt.set(1, <Value1>);
    stmt.set(2, <Value2>);
    stmt.execute();
    con.commit();
}
catch (SQLException sqle) {
    // Handle the exception
    con.rollback();
}
finally {
    con.close();
}

Note: There are many corner cases to handle in the above code while obtaining connection and while closing it, left out to make this look simple for now!

Upvotes: 0

Related Questions