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

[pgsql-bugs]

Denis ([EMAIL PROTECTED]) 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 database wthout 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()or a 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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])