Re: Automatic transactions in SELECT - Mailing list pgsql-jdbc

After review I do not see a problem with the way pgJDBC handles transactions
appropriately during the failure in your test case. As indicated by others
replies this is an architicture decision to properly handle failure.

Basically as indicated you can continue to use SquirrelSQL and modify the
code to handle the behavior or convice the project to do so. Or move on
to another application or your own to handle the behavior you desire.

Your code to properly handle the exception and continue processing. I'm
sure this is not what may be pertinent to your situation but demostrates
how an application could possibly handle the situation.

danap.

try
       {
          conn.setAutoCommit(false);

          Statement st1 = conn.createStatement();
          String sqlStatementString = "create table example ( " +
                               "       name varchar(255), " +
                               "       primary key(name) " +
                               ")";
          System.out.println(sqlStatementString);
          st1.execute(sqlStatementString);
          st1.close();
          conn.commit();

          try
          {
             Statement query = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
             sqlStatementString = "select nam from example";
             System.out.println(sqlStatementString);
             query.executeQuery(sqlStatementString);
             query.close();
          }
          catch(Exception e)
          {
             System.out.println("Exception Inner Try");
             conn.rollback(); // Added
             e.printStackTrace();
          }

          Statement query2 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
          sqlStatementString = "select name from example";
          System.out.println(sqlStatementString);
          query2.executeQuery(sqlStatementString);
          query2.close();
          conn.commit();
          //conn.rollback(); << Change

          Statement st3 = conn.createStatement();
          sqlStatementString = "drop table example";
          System.out.println(sqlStatementString);
          st3.execute(sqlStatementString);
          st3.close();
          conn.commit();

          System.out.println("Finished.");

          conn.setAutoCommit(true); // Return autocommit
       }
       catch (Exception sqle)
       {
          System.out.println("Exception Outer Try");
          System.out.println("Exeception" + sqle);
          // Roll back.
          try
          {
             conn.rollback();
          }
          catch (Exception ee)
          {
             System.out.println("Failed to Rollback");
          }
       }

Joseph Pravato wrote:
>
>> Hello Joseph,
>>
>> If you can provide some kind of test case to duplicate the problem I
>> would happy to take a look to see if there is an issue with the pgJDBC.
> Thank you for the response, we have gone ahead and provided a Java test case for you.
>
> Basically we have run the queries outside of SquirrelSQL and we still happen to get the same error when we directly
accessthe database 
> through the JDBC driver. At this time we do not believe that this is a SquirrelSQL problem since we get this
transactionerror directly through 
> java.
>
> http://pastebin.com/vg8J0kZi
>
> Your input would be appreciated.
>