What's the problem in the next sequence of Java/SQL code?

Question

Hi!

I made an update to a table in access via Java code, and it doesn't work. But, when I print to the console the result of executeUpdate(), it shows me 1, but in the database, no change. Can you tell me where's the problem, please?

System.out.println("here");
PreparedStatement st = conn.prepareStatement(
    "UPDATE StocProduseCuFactura SET cantitate = " +
    "cantitate - ? WHERE nume = ? and um = 'buc'");
st.setInt(1, cant);
st.setString(2, p.getNume());
System.out.println(st.executeUpdate());
st.close();

I forgot to say, if i run that SQL code in access, it's working.

Answer 1

Have you ensured the transaction is commited?

Answer 2

Is it possible that the statement executes correctly, but there may be no match for your where clause, So nothing would change on the database? Or Canidate may not be changing at all, for example when

cant = 0.

Are you getting any errors when trying to execute this?

Answer 3

As others have told, transaction is the area you might need to focus upon. Try setting the autoCommit to true and false explicitly and try the update query.

conn.setAutoCommit(true);

Moreover, check for locks on the database. Some other connection might have acquired a lock and waiting for it to be committed, but then in that case, your update query would have not gotten fired at all. But just try restarting the database.

Answer 4

people, sorry for bothering you, but I realized it was my stupid mistake. There is another method after this sequence of code, that made that table right as it was before the update, that why I didn't see any changes.

Thank you very much.

Answer 5

Johnny Keys is probably on the right trail. Most likely your connection isn't set to commit the transaction automatically, so when you close it the transaction is rolled back and your change is removed.

You might try putting

conn.setAutocommit(true); 

at the top to see if it makes a difference. The other possibility is "cant" is always zero. In that case you will be setting cantitate = cantitate (i.e. no change), but update will still report every row the "where" clause was satisfied.