Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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


pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group