Re: question about rollback and SQLException

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
>