Database ~ Committing a Transaction

[charon]

Hi,

A we know that we are using transaction when we do not want one statement to take effect unless another one completes. Either all of the statements are executed, or none of the statements is executed.

Normally I wil do as below:

<%

int result = 0;
int result2 = 0;

PreparedStatement updateSales = con.prepareStatement(
"UPDATE MYTABLEA SET STATUS = ? WHERE MYTABLEA_NUMBER = 'PM7610100'");
//updateSales.setInt(1, 50);
updateSales.setString(1, "N");
result = updateSales.executeUpdate();

PreparedStatement updateTotal = con.prepareStatement(
"UPDATE MYTABLEB SET STATUS= ? WHERE MYTABLEB_NUMBER = 'G761010'");
//updateTotal.setInt(1, 50);
updateTotal.setString(1, "Y");
result2 = updateTotal.executeUpdate();

if (result < 1){

con.rollback();
}

con.commit();

It will be difficult for me to use this approach of it involved more than two tables, Is there any better way of doing these?

pld advise.......

[Lallo]

you cannot do update of 2 or more tables in one update statement so you'll have to make new statement for every single table that you have to make new statement. you can split it to methods so it would be easier and more manageable!

regards!

[Lallo]

NEW IDEA!!!!!!!!!

Make the statement dynamic!

so when you call the function sent attributes - updateSales(status, table_number)

like this you will have only one statement one function and one connection!

the statement will be something like this:
"UPDATE MYTABLE SET STATUS = " + status + " WHERE MYTABLEA_NUMBER = '" + table_number + "'");

:thumbsup:

[charon]

Hi...
Ok..thanks...
Refer to the tutorial in http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html

as I can understand is that both update statements must be executed, if either one not row updated, then will be rollback. For instance:

if updateSales stmt not successfully updated due to cannot find the 'PM7610100 in MYTABLEA_NUMBER, so the second stmt updateTotal should not be updated.

I tried the example and found that the second stmt was updated with the status value ="Y". Why?? how it the transaction work with above example.

pls advise.......