Re: question about rollback and SQLException

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(at)micro-automation(dot)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(at)postgresql(dot)org
> > > [mailto:pgsql-jdbc-owner(at)postgresql(dot)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(at)micro-automation(dot)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(at)micro-automation(dot)net>
> > >>
> > >>
> > >>
> > >>---------------------------(end of broadcast)---------------------------
> > >>TIP 3: if posting/reading through Usenet, please send an appropriate
> > >>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > >>message can get through to the mailing list cleanly
> > >
> > > --
> > > Dave Cramer <Dave(at)micro-automation(dot)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(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> --
> Dave Cramer <Dave(at)micro-automation(dot)net>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>