Re: question about rollback and SQLException

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>