DatabaseDataSource fails to return the generated keys after an update with PostGRESQL

[GoogleCodeExporter]

What steps will reproduce the problem?
1.Create a simple postgresql db with a "serial" column and several others
2.Configure persevere to use this table as a datasource.
3.Try to insert a new object in the db

What is the expected output? What do you see instead?
The jdbc driver throws an exception.
In the executeAndGetGeneratedKey method in the databaseDataSource class,
the generated id is retrieved in the first column, whereas postgresql
returns all columns after the insert. 

A "quick'n'dirty" solution is attached as a patch.

Original issue reported on code.google.com by rdunk...@gmail.com on 24 Feb 2010 at 8:16

Attachments:

• patch

[GoogleCodeExporter]

I'll have to take a look to see if this still works properly in MySQL

Original comment by kris...@gmail.com on 24 Feb 2010 at 4:44

• Changed state: Accepted

[GoogleCodeExporter]

What exactly is the exception that is thrown? This fix won't work for MySQL.

Original comment by kris...@gmail.com on 24 Feb 2010 at 6:44

[GoogleCodeExporter]

When i try to insert the following object:
{"firstname":"John","lastname":"Doe","birthdate":"2010-02-24T23:00:00Z"}

into my postgresql table, i've got the following exception:

org.postgresql.util.PSQLException: Bad value for type long : John       
        at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(AbstractJdbc2ResultSet.java:2
796)
        at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2ResultSet.java:
2019)
        at
org.persvr.datasource.DatabaseDataSource.executeAndGetGeneratedKey(DatabaseDataS
ource.java:158)
        at
org.persvr.datasource.DatabaseTableDataSource$1.finished(DatabaseTableDataSource
.java:317)
        at org.persvr.data.PersistableObject.persistNewObject(PersistableObject.java:479)
        at org.persvr.data.NewObjectId.persistIfNeeded(NewObjectId.java:54)
        at org.persvr.data.Transaction.commit(Transaction.java:229)
        at org.persvr.remote.Client.commitTransaction(Client.java:54)
        at
org.persvr.remote.PersevereFilter$DefaultHandler.call(PersevereFilter.java:973)
        at org.persvr.remote.PersevereFilter.doFilter(PersevereFilter.java:363)
        at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.jav
a:1157)

Apparently, persevere tries to retrieve the generated value for the primary key
(column id, from type "serial"). Postgresql returns all values, and persevere 
uses
the following statement to retrieve the key from the returning values:
key = rs.getLong(1);

In my case, the first column contains the first name (John), which is obviously 
not 
long...

Maybe it would be useful to use something like hibernate Dialects to work 
around the
database quirks ?

Original comment by rdunk...@gmail.com on 25 Feb 2010 at 10:12