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>

On Thu, Jan 23, 2003 at 12:07:31PM -0500, 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"

"qrollback"? Sure looks like a typo in your code somewhere, to me.

Ross

That's what I thought too, except I'm not sending the rollback cmd as a
String, I'm using JDBC's Connection.rollback(), which would seem to suggest
that the error is in the driver (which is why I'm posting to pgsql-jdbc ->
hoping a driver developer can comment on this).

-----Original Message-----
From: Ross J. Reedstrom [mailto:reedstrm@rice.edu]
Sent: Thursday, January 23, 2003 12:32
To: Andy Kriger
Cc: Pgsql-Jdbc
Subject: Re: [JDBC] question about rollback and SQLException

On Thu, Jan 23, 2003 at 12:07:31PM -0500, 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"

"qrollback"? Sure looks like a typo in your code somewhere, to me.

Ross

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>

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>

The "q" in "qrollback" is part of the fronted/backend protocol that is
saying, "here's a query". How it got into the query string itself is a
driver bug in all likelihood, probably the driver got confused and put two
q's into the upstream message. I have seen this problem before with batch
prepared statements, but I thought those cases had been fixed.

Andy, can you give us an idea of what your actually doing? In code form
ideally.

Kris Jurka

On 23 Jan 2003, Dave Cramer 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

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:

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>

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@micro-automation.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@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>

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:

Kris,

This has been applied to HEAD, and 7.2.3

Dave

On Mon, 2003-01-27 at 13:29, Kris Jurka wrote:

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@micro-automation.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@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>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

______________________________________________________________________

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Dave Cramer <Dave@micro-automation.net>