Re: 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>