Thread: getGeneratedKeys() problem

Hello,

I've encountered a strange problem with the Statement.getGeneratedKeys()
method. In our project we use Hibernate with PostgreSQL, the issue was
detected with Hibernate 3.5.0-Final and 3.6.0.Final, PostgreSQL server
9.0.0-1 and PostgreSQL JDBC driver 9.0-801.jdbc3/4 on Windows 7 32 bit. The
issue is that for some reason the call to
PreparedStatement.getGeneratedKeys() returns all columns of the table and
not just the one that has the generated key, and since Hibernate expects the
identity column in case of the long IDs to be the first one in the list and
the schema is generated automatically and thus the column order is
undefined, we got an org.postgresql.util.PSQLException: Bad value for type
long: <whatever column value is the first one>. In other words, Hibernate
expects getGeneratedKeys() to return only the column which contains the long
ID, but instead the driver returns all of them, and if the first one isn't
the ID column then Hibernate crashes.

If I understand correctly the prepared statement is generated by
AbstractJdbc3Connection.prepareStatement (String sql, int autoGeneratedKeys)
which results in a query like "INSERT blah-blah RETURNING *". Could it be
the case that this "RETURNING *" statement is what actually causes all
columns to be returned by getGeneratedKeys()?

Here's a really simple test case:

CREATE TABLE users
(
  id bigserial NOT NULL,
  creation_date timestamp without time zone NOT NULL,
  modification_date timestamp without time zone NOT NULL,
  email character varying(255) NOT NULL,
  "login" character varying(255) NOT NULL,
  "password" character varying(255) NOT NULL
)

public static void main(String[] args) throws SQLException {
        Connection conn=DriverManager.getConnection(dbUrl);
        PreparedStatement ps=conn.prepareStatement("INSERT INTO USERS
(creation_date,modification_date,email,\"login\",\"password\") VALUES
(?,?,?,?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
        ps.setDate(1, new Date(System.currentTimeMillis()));
        ps.setDate(2,new Date(System.currentTimeMillis()));
        ps.setString(3, "a@b.c.d");
        ps.setString(4,"testjdbc");
        ps.setString(5,"test");
        ps.executeUpdate();
        System.out.println("Column count:
"+ps.getGeneratedKeys().getMetaData().getColumnCount());
    }
It returns 6 columns instead of 1.

So, let me sum it up: in the case of
  PreparedStatement ps = connection.prepareStatement( sql,
PreparedStatement.RETURN_GENERATED_KEYS );
  ps.executeUpdate();
  ResultSet rs = rs.getGeneratedKeys();
the result set contains all columns of the inserted record instead of
containing the ID column(s) only. Is it a bug in the JDBC driver or is there
something wrong in the way the statement is prepared? How could it be
resolved in the latter case?

Thanks,
Viktor Pravdin

Viktor,

I asked about this issue a few months ago and got no response.  I worked
around it with code to discern the table generating the keys (it does
not handle comments in-line in the query, sadly), determines which
columns have auto-generated values AND are parts of the primary key,
then grabs those values from all of the values returned by
getGeneratedKeys().  This works for us but is expensive (extra query)
and clunky.

I too am very interested in a resolution to this.

-Adam

On 10/26/2010 10:19 AM, Viktor Pravdin wrote:
> Hello,
>
> I've encountered a strange problem with the Statement.getGeneratedKeys()
> method. In our project we use Hibernate with PostgreSQL, the issue was
> detected with Hibernate 3.5.0-Final and 3.6.0.Final, PostgreSQL server
> 9.0.0-1 and PostgreSQL JDBC driver 9.0-801.jdbc3/4 on Windows 7 32 bit. The
> issue is that for some reason the call to
> PreparedStatement.getGeneratedKeys() returns all columns of the table and
> not just the one that has the generated key, and since Hibernate expects the
> identity column in case of the long IDs to be the first one in the list and
> the schema is generated automatically and thus the column order is
> undefined, we got an org.postgresql.util.PSQLException: Bad value for type
> long:<whatever column value is the first one>. In other words, Hibernate
> expects getGeneratedKeys() to return only the column which contains the long
> ID, but instead the driver returns all of them, and if the first one isn't
> the ID column then Hibernate crashes.
>
> If I understand correctly the prepared statement is generated by
> AbstractJdbc3Connection.prepareStatement (String sql, int autoGeneratedKeys)
> which results in a query like "INSERT blah-blah RETURNING *". Could it be
> the case that this "RETURNING *" statement is what actually causes all
> columns to be returned by getGeneratedKeys()?
>
> Here's a really simple test case:
>
> CREATE TABLE users
> (
>    id bigserial NOT NULL,
>    creation_date timestamp without time zone NOT NULL,
>    modification_date timestamp without time zone NOT NULL,
>    email character varying(255) NOT NULL,
>    "login" character varying(255) NOT NULL,
>    "password" character varying(255) NOT NULL
> )
>
> public static void main(String[] args) throws SQLException {
>          Connection conn=DriverManager.getConnection(dbUrl);
>          PreparedStatement ps=conn.prepareStatement("INSERT INTO USERS
> (creation_date,modification_date,email,\"login\",\"password\") VALUES
> (?,?,?,?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
>          ps.setDate(1, new Date(System.currentTimeMillis()));
>          ps.setDate(2,new Date(System.currentTimeMillis()));
>          ps.setString(3, "a@b.c.d");
>          ps.setString(4,"testjdbc");
>          ps.setString(5,"test");
>          ps.executeUpdate();
>          System.out.println("Column count:
> "+ps.getGeneratedKeys().getMetaData().getColumnCount());
>      }
> It returns 6 columns instead of 1.
>
> So, let me sum it up: in the case of
>    PreparedStatement ps = connection.prepareStatement( sql,
> PreparedStatement.RETURN_GENERATED_KEYS );
>    ps.executeUpdate();
>    ResultSet rs = rs.getGeneratedKeys();
> the result set contains all columns of the inserted record instead of
> containing the ID column(s) only. Is it a bug in the JDBC driver or is there
> something wrong in the way the statement is prepared? How could it be
> resolved in the latter case?
>
> Thanks,
> Viktor Pravdin
>
>