Re: question about rollback and SQLException - Mailing list pgsql-jdbc

The following patch fixes this problem.  The QueryExecutor was detecting
this error halfway through the sendQuery process without resetting the
query state.  I have moved this check to before any processing occurs.

Kris Jurka




On Fri, 24 Jan 2003, Andy Kriger wrote:

> Here you go - sample code. You'll need to set the url/usr/pass/query to make
> sense for your db (a dummy table with 3 int cols will do the trick).
>
> The key is the setInt methods. One is commented out, this will trigger the
> exceptions. You can comment any of them out and get the same exception (it
> doesn't just have to be the last one).
>
> No value specified for parameter 2
>         at
> org.postgresql.core.QueryExecutor.sendQuery(QueryExecutor.java:148)
>         at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:70)
>         at
> org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
> .java:505)
>         at
> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.j
> ava:320)
>         at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
> ava:48)
>         at
> org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1State
> ment.java:197)
>         at DriverBug.main(DriverBug.java:26)
> java.sql.SQLException: ERROR:  parser: parse error at or near "qrollback"
>         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.rollback(AbstractJdbc1Connectio
> n.java:1031)
>         at DriverBug.main(DriverBug.java:31)
>
> ---TEST CODE---
>
> import java.sql.*;
> public class DriverBug
> {
>     public static void main(String[] args)
>     {
>         String url = "";
>         String user = "";
>         String pass = "";
>         String query = "insert into table (col1,col2,col3) values (?, ?, ?)";
>
>         try {
>             Class.forName("org.postgresql.Driver");
>         } catch(Exception e) {
>             e.printStackTrace();
>             System.exit(1);
>         }
>
>         Connection cnx = null;
>         PreparedStatement stmt = null;
>         ResultSet rs = null;
>         try {
>             cnx = DriverManager.getConnection(url,user,pass);
>             cnx.setAutoCommit(false);
>             System.out.println("connection obtained");
>             stmt = cnx.prepareStatement(query);
>             stmt.setInt(1,3);
>             //stmt.setInt(2,53);
>             stmt.setInt(3,10);
>             System.out.println("statement initialized");
>             stmt.executeUpdate();
>             System.out.println("update done");
>         } catch(SQLException se) {
>             try {
>                 se.printStackTrace();
>                 cnx.rollback();
>             } catch(Exception e) {
>                 e.printStackTrace();
>             }
>         } finally {
>             try {
>                 if(rs != null) rs.close();
>                 if(stmt != null) stmt.close();
>                 if(cnx != null) cnx.close();
>             } catch(Exception e) {
>                 e.printStackTrace();
>             }
>         }
>         System.out.println("FIN");
>     }
> }
>
> -----Original Message-----
> From: Dave Cramer [mailto:Dave@micro-automation.net]
> Sent: Friday, January 24, 2003 11:44
> To: Barry Lind
> Cc: Andy Kriger; Pgsql-Jdbc; Kevin Tung
> Subject: Re: [JDBC] question about rollback and SQLException
>
>
> Barry,
>
> I haven't looked at the code, but it appears that the driver is not
> dealing well with a ill formed prepared statement.
>
> There are 3 variables, and 4 ? marks.
>
> Dave
> On Fri, 2003-01-24 at 00:45, Barry Lind wrote:
> > Andy,
> >
> > Now that you have a reproducable test case, can you send a code sample
> > that shows the problem.  After reading this thread, I am still not sure
> > when the error message is being given.  A code example would help a lot.
> >
> > Also, what version of the driver are you using?
> >
> > --Barry
> >
> > Andy Kriger wrote:
> > > [ moving this off the Resin mailing list as it is not about Resin ]
> > >
> > > I think I figured out what is going on. Below is the pgsql log (at level
> 2)
> > > You can see the problem in line 2: the query is being mangled.
> > >
> > > The original query was
> > > insert into purchase (customer_id,item_id,quantity) values (?,?,?,?);
> > >
> > > I realize this query is bad since there aren't as many columns as
> values. It
> > > was only meant to trigger a SQLException so I could verify rollback.
> > > However, my PreparedStatment was setting only 3 of the 4 values. And
> doing
> > > that caused the mangling. If I set all 4 values, I get a SQLException
> > > (INSERT has more expressions than target columns) followed by a
> successful
> > > rollback. If I use the same number of values and columns but don't set
> the
> > > last value, I get a SQLException (No value specified for parameter 3)
> > > followed by an unsucessful rollback (the original problem).
> > >
> > > So, it looks like the JDBC driver is not handling the error condition
> where
> > > the last value is not set. And this is mangling the rollback query
> (though
> > > I'm guessing it would mangle the next query regardless of what it was).
> I
> > > was not able to isolate it to whether you will get the same problem if
> any
> > > value is not set (the 1st or 2nd, for example).
> > >
> > > Hopefully, one of the JDBC driver developers can take it from here.
> > >
> > > -a
> > >
> > > === PGSQL log ===
> > >
> > > 2003-01-23 13:52:43 DEBUG:  StartTransactionCommand
> > > 2003-01-23 13:52:43 DEBUG:  query: insert into purchase
> > > (customer_id,item_id,quantity) values (8,2,2Qrollback; begin;
> > > 2003-01-23 13:52:43 ERROR:  parser: parse error at or near "qrollback"
> > > 2003-01-23 13:52:43 DEBUG:  AbortCurrentTransaction
> > > 2003-01-23 13:52:43 DEBUG:  StartTransactionCommand
> > > 2003-01-23 13:52:43 DEBUG:  query: rollback; begin;
> > > 2003-01-23 13:52:43 DEBUG:  ProcessUtility: rollback; begin;
> > > 2003-01-23 13:52:43 DEBUG:  CommitTransactionCommand
> > > 2003-01-23 13:52:43 DEBUG:  StartTransactionCommand
> > > 2003-01-23 13:52:43 DEBUG:  ProcessUtility: rollback; begin;
> > > 2003-01-23 13:52:43 DEBUG:  CommitTransactionCommand
> > > 2003-01-23 13:52:43 DEBUG:  StartTransactionCommand
> > > 2003-01-23 13:52:43 DEBUG:  query: end
> > > 2003-01-23 13:52:43 DEBUG:  ProcessUtility: end
> > > 2003-01-23 13:52:43 DEBUG:  CommitTransactionCommand
> > > 2003-01-23 13:52:43 DEBUG:  proc_exit(0)
> > > 2003-01-23 13:52:43 DEBUG:  shmem_exit(0)
> > > 2003-01-23 13:52:43 DEBUG:  exit(0)
> > > 2003-01-23 13:52:43 DEBUG:  reaping dead processes
> > > 2003-01-23 13:52:43 DEBUG:  child process (pid 31273) exited with exit
> code
> > > 0
> > >
> > > -----Original Message-----
> > > From: pgsql-jdbc-owner@postgresql.org
> > > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
> > > Sent: Thursday, January 23, 2003 13:06
> > > To: Andy Kriger
> > > Cc: Resin-Interest; Pgsql-Jdbc
> > > Subject: Re: [JDBC] question about rollback and SQLException
> > >
> > >
> > > Andy,
> > >
> > > The logs from the server would be good, I just tested rollback and it
> > > works, but if there was something some how left in the query buffer,
> > > this would be a problem
> > >
> > > DAve
> > > On Thu, 2003-01-23 at 12:44, Andy Kriger wrote:
> > >
> > >>I don't really have something I can easily reduce out of my code.
> > >>
> > >>I started a transaction with Connection.setAutoCommit(false)
> > >>Ran some SQL queries using PreparedStatements
> > >>Ran a query that I knew would fail, throwing a SQLException
> > >>Then I rolledback the transaction (just to be sure) with
> > >>Connection.rollback()
> > >>
> > >>It was during Connection.rollback() that I received the exception that's
> > >
> > > in
> > >
> > >>my first email. The stack trace picks up from my code as it enters the
> > >
> > > Resin
> > >
> > >>& driver code.
> > >>
> > >>-----Original Message-----
> > >>From: Dave Cramer [mailto:Dave@micro-automation.net]
> > >>Sent: Thursday, January 23, 2003 12:32
> > >>To: Andy Kriger
> > >>Cc: Resin-Interest; Pgsql-Jdbc
> > >>Subject: Re: [JDBC] question about rollback and SQLException
> > >>
> > >>
> > >>can you send us logs from the server?
> > >>
> > >>or a test case which demonstrates this?
> > >>
> > >>Dave
> > >>
> > >>On Thu, 2003-01-23 at 12:07, Andy Kriger wrote:
> > >>
> > >>>I received this exception when trying to rollback a transaction through
> > >>
> > >>the
> > >>
> > >>>Postgres JDBC driver (build106) used by a webapp running in Resin
> > >
> > > (2.0.6).
> > >
> > >>I
> > >>
> > >>>have tried setting up the connection pool in my web.xml to use both
> > >>>javax.sql.DataSource and javax.sql.XADataSource, but both give me this
> > >>>exception:
> > >>>
> > >>>java.sql.SQLException: ERROR:  parser: parse error at or near
> > >
> > > "qrollback"
> > >
> > >>>        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.rollback(AbstractJdbc1Connectio
> > >
> > >>>n.java:1031)
> > >>>        at
> > >>>
> > >>
> > >
> com.caucho.sql.UserConnectionAdapter.rollback(UserConnectionAdapter.java:470
> > >
> > >>>)
> > >>>        at com.gto.db.SqlTransaction.rollback(SqlTransaction.java:132)
> > >>>
> > >>>Any idea what that means and how I can prevent it from occuring?
> > >>>
> > >>>thx
> > >>>andy kriger
> > >>>
> > >>>
> > >>>
> > >>>---------------------------(end of
> broadcast)---------------------------
> > >>>TIP 6: Have you searched our list archives?
> > >>>
> > >>>http://archives.postgresql.org
> > >>
> > >>--
> > >>Dave Cramer <Dave@micro-automation.net>
> > >>
> > >>
> > >>
> > >>---------------------------(end of broadcast)---------------------------
> > >>TIP 3: if posting/reading through Usenet, please send an appropriate
> > >>subscribe-nomail command to majordomo@postgresql.org so that your
> > >>message can get through to the mailing list cleanly
> > >
> > > --
> > > Dave Cramer <Dave@micro-automation.net>
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> --
> Dave Cramer <Dave@micro-automation.net>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>