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(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
>