Thread: setAutoCommit(false)
Hi, I'm a developer on a JDO implementation and we support PostgreSQL as a database. When obtaining a connection for the DB we do a setAutoCommit(false). Nothing controversial there. I've used PostgreSQL 7.3.* with no problems and also 7.4.1-2 with no problems. I do however have a user saying that he is using latest PostgreSQL (postgresql 7.4.2 with pg74.213.jdbc3 is what he told me) and our call to setAutoCommit(false) apparently returns java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer supported at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:482) at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:461) at org.postgresql.jdbc1.AbstractJdbc1Connection.setAutoCommit(AbstractJdbc1Connection.java:957) at org.jpox.store.adapter.DatabaseAdapter.getConnection(DatabaseAdapter.java:702) Is there any good reason why he is getting this ? If so, what are client applications supposed to do to not have things auto-committed ? TIA ===== ____________________________________________________________ Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html
On Wed, 12 May 2004, [iso-8859-1] Andy Jefferson wrote: > I do however have a user saying that he is using latest PostgreSQL > (postgresql 7.4.2 with pg74.213.jdbc3 is what he told me) and our call > to setAutoCommit(false) apparently returns > > java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer > supported > > at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) The user is not using a 7.4 series JDBC driver. They likely have the 7.3 driver hidden somewhere in their classpath which is being used. This error and associated stack trace are not from a 7.4 driver. Kris Jurka
Andy Jefferson wrote: > Hi, > > I'm a developer on a JDO implementation and we support PostgreSQL as a > database. When obtaining a connection for the DB we do a > setAutoCommit(false). Nothing controversial there. I've used PostgreSQL > 7.3.* with no problems and also 7.4.1-2 with no problems. I do however > have > a user saying that he is using latest PostgreSQL (postgresql 7.4.2 with > pg74.213.jdbc3 is what he told me) and our call to setAutoCommit(false) > apparently returns > > java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer > supported This happens if you use a 7.3-series driver against a 7.4-series database. The most likely cause is that your user is not using the driver they think they are; perhaps there is an older version of the driver earlier in the classpath or in the JVM's extension search path. -O
Kris Jurka schrieb: >>I do however have a user saying that he is using latest PostgreSQL >>(postgresql 7.4.2 with pg74.213.jdbc3 is what he told me) and our call >>to setAutoCommit(false) apparently returns >> >>java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer >>supported >> > > The user is not using a 7.4 series JDBC driver. They likely have the 7.3 > driver hidden somewhere in their classpath which is being used. This > error and associated stack trace are not from a 7.4 driver. > I'm seeing the same error with the 7.4 driver and the dev driver against the current Win32 builds... Thomas
Thomas,
This is very definitely an old driver.
The best way to find this is to write a single line program
with a main and the following line.
Class.forName("org.posgtgresql.Driver")
then run java -verbose to see where it is loading the driver from.
Dave
On Wed, 2004-05-12 at 11:48, Thomas Kellerer wrote:
> Kris Jurka schrieb:
> >>I do however have a user saying that he is using latest PostgreSQL
> >>(postgresql 7.4.2 with pg74.213.jdbc3 is what he told me) and our call
> >>to setAutoCommit(false) apparently returns
> >>
> >>java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer
> >>supported
> >>
> >
> > The user is not using a 7.4 series JDBC driver. They likely have the 7.3
> > driver hidden somewhere in their classpath which is being used. This
> > error and associated stack trace are not from a 7.4 driver.
> >
>
> I'm seeing the same error with the 7.4 driver and the dev driver against
> the current Win32 builds...
>
> Thomas
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
> !DSPAM:40a24b75281861655120010!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561
Hi:
this is normal or it's a bug?
please, rewrite the line 98 to fit your database:
DriverManager.getConnection("jdbc:postgresql://yourIp:yourPort/yourDataBaseName?charSet=yourCharSet",
"yourUser", "yourPassword");
import java.sql.*;
import java.io.IOException;
import javax.swing.JOptionPane;
class Problem {
public static void main(String [] arguments) {
Connection conn = null;
try {
conn = getConn();
Statement stat = conn.createStatement();
String sequenceTest = "CREATE SEQUENCE sequence_test";
String table1 = "CREATE TABLE test (id INTEGER DEFAULT NEXTVAL('sequence_test') NOT NULL, value VARCHAR(100))";
//create sequence and table for testing purposes
stat.executeUpdate(sequenceTest);
stat.executeUpdate(table1);
//let's go with the problem
boolean autoCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
String insert1 = "INSERT INTO test(value) VALUES('this will have id1')";
String insert2 = "INSERT INTO test(value) VALUES('this will have id2')";
String insert3 = "INSERT INTO test(value, other) VALUES('this will have id3', 'this will cause an error')";
//<=error
stat.executeUpdate(insert1);
stat.executeUpdate(insert2);
stat.executeUpdate(insert3);
conn.setAutoCommit(autoCommit);
} catch(SQLException e) {
try {
conn.rollback();
conn.close();
System.out.println("rollback done");
} catch(SQLException SQLe) {
e.printStackTrace();
SQLe.printStackTrace();
System.out.println("rollback isn't done :'(");
}
System.out.println("Error code: " + e.getErrorCode());
System.out.println("Error messague: " + e.getMessage());
System.out.println("Localized messague: " + e.getLocalizedMessage());
System.out.println("Description: " + e);
System.out.println();
System.out.println("<- ->");
System.out.println();
e.printStackTrace();
System.out.println();
System.out.println("<- ->");
System.out.println();
} catch(IOException ioe) {
ioe.printStackTrace();
System.out.println("Imput/output error");
}
try {
conn = getConn();
Statement stat = conn.createStatement();
String insert1 = "INSERT INTO test(value) VALUES('this will have id1')";
stat.executeUpdate(insert1);
stat.close();
PreparedStatement pstmt = conn.prepareStatement("SELECT CURRVAL('sequence_test') AS CURRVAL");
ResultSet rs = pstmt.executeQuery();
if (rs.first()) {
System.out.println("Current sequence value is: " + rs.getInt("CURRVAL"));
System.out.println("According with the code, rollback was called, so this new row should have 1");
} else {
System.out.println("FATAL");
System.out.println("FATAL");
System.out.println("FATAL");
System.out.println("cannot reach the sequence");
return;
}
rs.close();
pstmt.close();
conn.close();
} catch(SQLException e) {
e.printStackTrace();
} catch(IOException ioe) {
ioe.printStackTrace();
}
}
public static Connection getConn() throws SQLException, IOException {
try {
Class.forName("org.postgresql.Driver").newInstance();
//return DriverManager.getConnection("jdbc:postgresql://yourIp:yourPort/yourDataBaseName?charSet=yourCharSet",
"yourUser","yourPassword");
} catch(ClassNotFoundException e) {
e.printStackTrace();
} catch(IllegalAccessException ee) {
ee.printStackTrace();
} catch(InstantiationException eee) {
eee.printStackTrace();
}
catch(SQLException e) {
JOptionPane.showMessageDialog(null,
"can't connect with database");
}
return null;
}
}
Sequences do not rollback, so it is not a bug
Dave
On Wed, 2004-05-12 at 15:22, Marcos Truchado wrote:
> Hi:
>
> this is normal or it's a bug?
>
> please, rewrite the line 98 to fit your database:
>
> DriverManager.getConnection("jdbc:postgresql://yourIp:yourPort/yourDataBaseName?charSet=yourCharSet",
> "yourUser", "yourPassword");
>
>
> !DSPAM:40a27f82120464098919018!
>
> ______________________________________________________________________
> import java.sql.*;
> import java.io.IOException;
> import javax.swing.JOptionPane;
>
> class Problem {
>
> public static void main(String [] arguments) {
> Connection conn = null;
> try {
> conn = getConn();
>
> Statement stat = conn.createStatement();
>
> String sequenceTest = "CREATE SEQUENCE sequence_test";
> String table1 = "CREATE TABLE test (id INTEGER DEFAULT NEXTVAL('sequence_test') NOT NULL, value
VARCHAR(100))";
>
> //create sequence and table for testing purposes
> stat.executeUpdate(sequenceTest);
> stat.executeUpdate(table1);
>
> //let's go with the problem
>
> boolean autoCommit = conn.getAutoCommit();
> conn.setAutoCommit(false);
>
> String insert1 = "INSERT INTO test(value) VALUES('this will have id1')";
> String insert2 = "INSERT INTO test(value) VALUES('this will have id2')";
> String insert3 = "INSERT INTO test(value, other) VALUES('this will have id3', 'this will cause an error')";
//<=error
>
> stat.executeUpdate(insert1);
> stat.executeUpdate(insert2);
> stat.executeUpdate(insert3);
>
> conn.setAutoCommit(autoCommit);
>
> } catch(SQLException e) {
> try {
> conn.rollback();
> conn.close();
> System.out.println("rollback done");
> } catch(SQLException SQLe) {
> e.printStackTrace();
> SQLe.printStackTrace();
> System.out.println("rollback isn't done :'(");
> }
> System.out.println("Error code: " + e.getErrorCode());
> System.out.println("Error messague: " + e.getMessage());
> System.out.println("Localized messague: " + e.getLocalizedMessage());
> System.out.println("Description: " + e);
> System.out.println();
> System.out.println("<- ->");
> System.out.println();
> e.printStackTrace();
> System.out.println();
> System.out.println("<- ->");
> System.out.println();
> } catch(IOException ioe) {
> ioe.printStackTrace();
> System.out.println("Imput/output error");
> }
>
>
> try {
> conn = getConn();
> Statement stat = conn.createStatement();
> String insert1 = "INSERT INTO test(value) VALUES('this will have id1')";
> stat.executeUpdate(insert1);
> stat.close();
>
> PreparedStatement pstmt = conn.prepareStatement("SELECT CURRVAL('sequence_test') AS CURRVAL");
> ResultSet rs = pstmt.executeQuery();
> if (rs.first()) {
> System.out.println("Current sequence value is: " + rs.getInt("CURRVAL"));
> System.out.println("According with the code, rollback was called, so this new row should have 1");
> } else {
> System.out.println("FATAL");
> System.out.println("FATAL");
> System.out.println("FATAL");
> System.out.println("cannot reach the sequence");
> return;
> }
> rs.close();
> pstmt.close();
> conn.close();
> } catch(SQLException e) {
> e.printStackTrace();
> } catch(IOException ioe) {
> ioe.printStackTrace();
> }
> }
>
> public static Connection getConn() throws SQLException, IOException {
>
> try {
>
> Class.forName("org.postgresql.Driver").newInstance();
>
> //return
DriverManager.getConnection("jdbc:postgresql://yourIp:yourPort/yourDataBaseName?charSet=yourCharSet","yourUser",
"yourPassword");
>
> } catch(ClassNotFoundException e) {
> e.printStackTrace();
> } catch(IllegalAccessException ee) {
> ee.printStackTrace();
> } catch(InstantiationException eee) {
> eee.printStackTrace();
> }
>
> catch(SQLException e) {
> JOptionPane.showMessageDialog(null,
> "can't connect with database");
>
> }
>
> return null;
> }
>
> }
>
>
> !DSPAM:40a27f82120464098919018!
>
> ______________________________________________________________________
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>
> !DSPAM:40a27f82120464098919018!
--
Dave Cramer
519 939 0336
ICQ # 14675561