PostgreSQL getKey is null

[bobmanc]

I converted from MySQL to PostgreSQL. My inserts no longer work on tables with an autogenerated key.
The data gets created but the KeyHolder getKey call returns null. I am using PostgreSQL 8 and postgresql-8.1dev-402.jdbc3.jar. Anyone able to get the generated key from PostgreSQL?

[Costin Leau]

How do you work with Postgres? Note that postgresql needs a sequence table to use when generating key (MySQL doesn't). Check out the postgres documentation or your framework documentation for configuration details.(HB forums for example contain tons of examples in this case).

[bobmanc]

The db is working fine. The row is created and the key is being generated. The problem is the KeyHolder returns a null when I do a kh.getKey();

-Bob

[Costin Leau]

Have you tried the STABLE branch of Postgres? Btw, what is 'KeyHolder'?

[trisberg]

Can you post your PostgreSQL table definitions. How are you autogenerating the key in Postgres? Last I checked Postgres did not support the auto generated key feature of JDBC, have they added that functionality?

[bobmanc]

postgresql-8.0-313.jdbc3.jar does the same thing.

This is the KeyHolder
http://www.springframework.org/docs/...KeyHolder.html

[davejunk78]

I have the same problem:

SQL state [0A000]; error code [0]; Returning autogenerated keys is not supported.; nested exception is org.postgresql.util.PSQLException: Returning autogenerated keys is not supported.

[jackcholt]

I also have the same problem. From the posts so far, it sounds like PostgreSQL doesn't support the KeyHolder feature, but just to fully document the issue...

My project that uses MySQL does get a key back when doing an insert using the following code:

public int addComment(final int recipeId, final String author,
            final String comment) {
        
        SqlUpdate sql = new SqlUpdate(ds,
                "INSERT INTO comment " +
                "(author, comment, recipe_id) " +
                "VALUES (?,?,?)",
                new int[] {Types.VARCHAR,Types.VARCHAR,Types.INTEGER});
        
        KeyHolder keyHold = new GeneratedKeyHolder();
        sql.update(new Object[] {author, comment, recipeId}, keyHold);
        
        return keyHold.getKey().intValue();
}

In fact, when I run that code I get a debug level log message that says, "[org.springframework.jdbc.core.JdbcTemplate] - <SQL update affected 1 rows and returned 1 keys>"

In comparison, in my project that uses PostgreSQL 8, I have the following code:

public void insertParking(final Parking park) {
        SqlUpdate sql = new SqlUpdate(ds, 
                "INSERT INTO parking " +
                "(source,location_number,name,street_address,city,state," +
                "county,zip_code,facility_telephone,country,number_of_spaces," +
                "valet_spaces,monthly_spaces,latitude," +
                "longitude,route_name) " +
                "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                new int[] {Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,
                Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,
                Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,
                Types.INTEGER,Types.INTEGER,Types.INTEGER,Types.DOUBLE,
                Types.DOUBLE,Types.VARCHAR,});
        
        KeyHolder keyHold = new GeneratedKeyHolder();
        
        sql.update(new Object[] {
                    park.getSource(),
                    park.getLocationNumber(),
                    park.getName(),
                    park.getAddress(),
                    park.getCity(),
                    park.getState(),
                    park.getCountry(),
                    park.getZipCode(),
                    park.getFacilityTelephone(),
                    park.getCountry(),
                    park.getNumberOfSpaces(),
                    park.getValetSpaces(),
                    park.getMonthlyNumberOfSpaces(),
                    park.getLatitude(),
                    park.getLongitude(),
                    park.getRouteName()
            }, keyHold);
        
        return keyHold.getKey().intValue();
}

When I run this code, I get a NullPointerException at the line that contains the return statement and a debug level log message that says, "[org.springframework.jdbc.core.JdbcTemplate] - <SQL update affected 1 rows and returned 0 keys>"

I think I know of a workaround but it involves converting this to a PostgreSQL function and using either the nextval() or currval() PostgreSQL functions to return the generated key from the function.

That sounds like a lot of work.

Please tell me that there is a way to use the KeyHolder mechanism with PostgreSQL!!!

[trisberg]

You might want to keep on eye on the following question posted on the PostgreSQL mailinglist:
http://www.nabble.com/getGeneratedKeys%28%29-support--tf4280343.html

[trisberg]

There is currently no way of using the KeyHolder with Postgres. This will of course change if the PostgreSQL JDBC project implements this feature.