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

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: JDBC: The raise of an SQLException force the user to call either commit() or rollback() on the connection
Date: 2001-07-12 14:55:47
Message-ID: 200107121455.f6CEtlZ11431@hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Denis (dforveille(at)zaq(dot)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 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

Browse pgsql-bugs by date

  From Date Subject
Next Message pgsql-bugs 2001-07-12 21:54:58 JDBC - DatabaseMetaData.getTables() null pointer exception
Previous Message Bruce Momjian 2001-07-12 00:08:00 Re: COPY TO looses on view