Description:
Transaction is not working for this version of Mysql Connector/J with mysql-4.1.7-win.
i tried with a very simple scenario like this:
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password=admin");
String query = "Select ID, UnitPrice from Stock where ItemName=? and Quantity=?";
String updateQuery = "Update Stock Set UnitPrice=? Where ID=?";
PreparedStatement queryStmt = conn.prepareStatement(query);
PreparedStatement updateStmt = conn.prepareStatement(updateQuery);
conn.setAutoCommit(false);
queryStmt.setString(1,"M.U");
queryStmt.setInt(2,325);
ResultSet queryResultSet = queryStmt.executeQuery();
System.out.print("ID:\t\t");
System.out.print("OldPrice:\t\t");
System.out.println("NewPrice:");
while(queryResultSet.next()){
int ID = queryResultSet.getInt("ID");
int oldPrice = queryResultSet.getInt("UnitPrice");
int newPrice = oldPrice + (int) (oldPrice * 0.1f);
updateStmt.setInt(1,newPrice);
updateStmt.setInt(2,ID);
updateStmt.executeUpdate();
System.out.print(ID+"\t\t\t\t");
System.out.print(oldPrice+"\t\t\t");
System.out.println(newPrice);
}
conn.rollback();
conn.setAutoCommit(true);
} catch (ClassNotFoundException e) {
System.out.println("Could not load driver");
} catch (SQLException e) {
System.out.println("\n---------SQL Exception Caught----------\n");
e.printStackTrace();
while (e != null) {
System.out.println("Message: " + e.getMessage());
System.out.println("SQL State: " + e.getSQLState());
System.out.println("Error Code: " + e.getErrorCode());
e = e.getNextException();
}
} finally {
System.out.println("Finally block");
if (conn != null) {
conn = null;
System.gc();
}
}
}
The above code always update the values in the table. However, i m rolling back my transaction. it should not change values in data base. Please chk it and tell either i m doing some thing wrong or this is any issue of the driver
How to repeat:
I m providing the code of my issue
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password=admin");
String query = "Select ID, UnitPrice from Stock where ItemName=? and Quantity=?";
String updateQuery = "Update Stock Set UnitPrice=? Where ID=?";
PreparedStatement queryStmt = conn.prepareStatement(query);
PreparedStatement updateStmt = conn.prepareStatement(updateQuery);
conn.setAutoCommit(false);
queryStmt.setString(1,"M.U");
queryStmt.setInt(2,325);
ResultSet queryResultSet = queryStmt.executeQuery();
System.out.print("ID:\t\t");
System.out.print("OldPrice:\t\t");
System.out.println("NewPrice:");
while(queryResultSet.next()){
int ID = queryResultSet.getInt("ID");
int oldPrice = queryResultSet.getInt("UnitPrice");
int newPrice = oldPrice + (int) (oldPrice * 0.1f);
updateStmt.setInt(1,newPrice);
updateStmt.setInt(2,ID);
updateStmt.executeUpdate();
System.out.print(ID+"\t\t\t\t");
System.out.print(oldPrice+"\t\t\t");
System.out.println(newPrice);
}
conn.rollback();
conn.setAutoCommit(true);
} catch (ClassNotFoundException e) {
System.out.println("Could not load driver");
} catch (SQLException e) {
System.out.println("\n---------SQL Exception Caught----------\n");
e.printStackTrace();
while (e != null) {
System.out.println("Message: " + e.getMessage());
System.out.println("SQL State: " + e.getSQLState());
System.out.println("Error Code: " + e.getErrorCode());
e = e.getNextException();
}
} finally {
System.out.println("Finally block");
if (conn != null) {
conn = null;
System.gc();
}
}
}