Thread: Problem with prepareStatement and Statement.RETURN_GENERATED_KEYS in PostgreSQL JDBC driver 8.4
Problem with prepareStatement and Statement.RETURN_GENERATED_KEYS in PostgreSQL JDBC driver 8.4
From
Heikki Hiltunen
Date:
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
Re: Problem with prepareStatement and Statement.RETURN_GENERATED_KEYS in PostgreSQL JDBC driver 8.4
From
Heikki Linnakangas
Date:
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
Re: Problem with prepareStatement and Statement.RETURN_GENERATED_KEYS in PostgreSQL JDBC driver 8.4
From
Alvaro Herrera
Date:
Heikki Linnakangas wrote: > 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. It will also fail to work with WITH clauses, alvherre=# create table r (f int); CREATE TABLE alvherre=# insert into r values (42), (142857); INSERT 0 2 alvherre=# with t as (select 1) update r set f = f + t."?column?" from t returning r.*; f -------- 43 142858 (2 filas) UPDATE 2 -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: Problem with prepareStatement and Statement.RETURN_GENERATED_KEYS in PostgreSQL JDBC driver 8.4
From
David G Johnston
Date:
Alvaro Herrera-9 wrote > Heikki Linnakangas wrote: > >> 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. > > It will also fail to work with WITH clauses, > > alvherre=# create table r (f int); > CREATE TABLE > alvherre=# insert into r values (42), (142857); > INSERT 0 2 > alvherre=# with t as (select 1) update r set f = f + t."?column?" from t > returning r.*; > f > -------- > 43 > 142858 > (2 filas) > > UPDATE 2 Check for insert/update/delete (IUD) at query start (ignoring whitespace) or immediately following a close parenthesis, and for added measure check for into/set/from appropriately subsequent to (i.e, need to account for relation name in the set case)? Can anchor off of */ for block comment detection and ignoring anything following a -- should be straight forward. Nested block comments will be a problem though...does the parser even effectively allow them? Given the end result of a bad guess is a parser error getting it 98% accurate is acceptable. Even if it is somewhat expensive I'm less inclined to worry about punishing those who blindly set the option. I imagine a typical IUD statement should be able to quickly match any regex we use so the typical use case should still be performant. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-prepareStatement-and-Statement-RETURN-GENERATED-KEYS-in-PostgreSQL-JDBC-driver-8-4-tp2172722p5807018.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.