Re: Problem with transactions - Mailing list pgsql-jdbc

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