[JDBC] Problem with prepareStatement and Statement.RETURN_GENERATED_KEYS in PostgreSQL JDBC driver 8.4

[Heikki Hiltunen]

When using 8.4 JDBC drivers, calling prepareStatement(String sql, int
autoGeneratedKeys) with Statement.RETURN_GENERATED_KEYS seems to add
"RETURNING *" to the end of the SQL even with select statements.
According to Javadoc for prepareStatement(String sql, int
autoGeneratedKeys) in java.sql.Connection:

"The given constant tells the driver whether it should make
auto-generated keys available for retrieval. This parameter is ignored
if the SQL statement is not an INSERT statement, or an SQL statement
able to return auto-generated keys (the list of such statements is
vendor-specific). "

Here's a simple test class to demonstrate this problem:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class PostgrePrepareStatementTest {

public static void main(String[] args) throws
InstantiationException, IllegalAccessException, ClassNotFoundException,
SQLException {
Class.forName("org.postgresql.Driver").newInstance();
String url = "jdbc:postgresql://localhost:5432/testdb";
Connection conn = DriverManager.getConnection(url, "user",
"password");
PreparedStatement pStmt = conn.prepareStatement("select * from
test_table", Statement.RETURN_GENERATED_KEYS);
System.out.println(pStmt.toString());
pStmt.execute();
}
}

When run this prints "select * from test_table RETURNING *" - which is
invalid - and an exception is thrown:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
syntax error at or near "RETURNING"
Position: 26
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:360)
at
com.smilehouse.PostgrePrepareStatementTest.main(PostgrePrepareStatementTest.java:17)

I have tested this with driver version 8.4-701 (both JDBC3 and JDBC4).
The PostgreSQL version was 8.4.

-Heikki Hiltunen

--
Sent via pgsql-jdbc mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

[Heikki Linnakangas]

Heikki Hiltunen wrote:
> When using 8.4 JDBC drivers, calling prepareStatement(String sql, int
> autoGeneratedKeys) with Statement.RETURN_GENERATED_KEYS seems to add
> "RETURNING *" to the end of the SQL even with select statements.
> According to Javadoc for prepareStatement(String sql, int
> autoGeneratedKeys) in java.sql.Connection:
>
> "The given constant tells the driver whether it should make
> auto-generated keys available for retrieval. This parameter is ignored
> if the SQL statement is not an INSERT statement, or an SQL statement
> able to return auto-generated keys (the list of such statements is
> vendor-specific). "

Yeah, the driver just blindly tacks a " RETURNING *" to the end of the
SQL string if you specify RETURN_GENERATED_KEYS. I'm tempted to do
something like this:

*** AbstractJdbc3Connection.java 23 Dec 2009 10:28:40 +0200 1.21
--- AbstractJdbc3Connection.java 23 Apr 2010 18:49:44 +0300
***************
*** 359,364 ****
--- 359,381 ----
throws SQLException
{
checkClosed();
+
+ /*
+ * We implement fetching auto-generated keys by tacking a
+ * " RETURNING *" to the end of the string. Don't try to do that
+ * with other statements than INSERT/UPDATE/DELETE.
+ *
+ * XXX this gets fooled by comments at the beginning of the SQL string
+ */
+ if (autoGeneratedKeys != Statement.NO_GENERATED_KEYS)
+ {
+ String trimmedSql = sql.trim();
+ if (!trimmedSql.regionMatches(true, 0, "INSERT", 0, 6) &&
+ !trimmedSql.regionMatches(true, 0, "UPDATE", 0, 6) &&
+ !trimmedSql.regionMatches(true, 0, "DELETE", 0, 6))
+ autoGeneratedKeys = Statement.NO_GENERATED_KEYS;
+ }
+
if (autoGeneratedKeys != Statement.NO_GENERATED_KEYS)
sql = AbstractJdbc3Statement.addReturning(this, sql, new
String[]{"*"}, false);

But that's not very bullet-proof, and will fail to detect the statement
as an INSERT if it e.g begins with a comment. We could add a mini-parser
to detect comments too, but it's not a very robust approach.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

[ig...@getrailo.org]

hi,

is there a bug ticket for this issue? and if not, can I open one?

according to the jdbc spec:

"This parameter is ignored if the SQL statement is not an INSERT statement, or an SQL statement able to return auto-generated keys (the list of such statements is vendor-specific)."

http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String,%20int)

we have users who use PostgreSQL and this makes all drivers > 8.3 unusable for them. https://issues.jboss.org/browse/RAILO-2019

we are debating whether to downgrade the driver to 8.3 or work out a dirty patch but it looks like a patch in the PostgresSQL driver would be the right way to go.

does the 8.3 driver work with PostgresSQL 9.x? would downgrading have major consequences?

thank you,


Igal
Railo Core Developer