Re: Problem with transactions

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