Skip site navigation (1) Skip section navigation (2)

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

From: Heikki Hiltunen <heikki(dot)hiltunen(at)smilehouse(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Problem with prepareStatement and Statement.RETURN_GENERATED_KEYS in PostgreSQL JDBC driver 8.4
Date: 2010-04-23 12:46:44
Message-ID: 4BD196B4.3040607@smilehouse.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

Responses

pgsql-jdbc by date

Next:From: Heikki LinnakangasDate: 2010-04-23 15:57:11
Subject: Re: Problem with prepareStatement and Statement.RETURN_GENERATED_KEYS in PostgreSQL JDBC driver 8.4
Previous:From: Kris JurkaDate: 2010-04-21 18:00:53
Subject: Re: SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group