JDBC: The raise of an SQLException force the user to call either commit() or rollback() on the connection - Mailing list pgsql-bugs

Denis (dforveille@zaq.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
JDBC: The raise of an SQLException force the user to call either commit() or rollback() on the connection

Long Description
Using JDBC, after an SQLException is raised as a result of either a call to executeQuery() or executeUpdate() on a
java.sql.Statement,the connection is in a strange quite unusable state. 
It seems that the only possible action that can be issued is either to issue a commit() or a rollback() on the
connection.
Any use of executeQuery() or executeUpdate() against the same Statement or another one will not perform anything in the
databasewthout any exception, even if it should. 
Raising an SQLException (for example because of a duplicate key) should not force the caller to issue immediately a
commit()ora rollback(), but instead, the caller could issue any other Statement.  
This is a major difference with the JDBC Driver implementation of other DBMS (At least DB2 and Oracle)
(driver jdbc7.0-1.2.jar on NT, server postgres 7.1 on Linux Red Hat 7.0)

Sample Code
import java.sql.*;

Connection con;
Statement stmt;
ResultSet rs;

try {
   Class.forName(<driver>);
   con = DriverManager.getConnection(<url>,<user>,<password>);
   con.setAutoCommit(false);
   stmt = con.createStatement();
} catch (Exception e) {...}

try{
   rs = stmt.executeUpdate("<any SQL Statement that fails>");
}
// This Exception block is called: Correct
catch (SQLException se) {se.printStackTrace();}

try{
   rs = stmt.executeUpdate("<other SQL Statement that fails>");
}
// This Exception Blok is never called as it should: Incorrect
// Closing/Opening and/or using another Statement does not change anything
catch (SQLException se) {se.printStackTrace();}

try{
   con.rollback();
   rs = stmt.executeUpdate("<another SQL Statement that fails>");
}
// This exception block will be called because of the previous call to rollback()
catch (SQLException se) {se.printStackTrace();}



No file was uploaded with this report