Обсуждение: Updateable ResultSet doesn't fetch sequence-generated values

Hello,

I am using a library which is using the JDBCRowSet API, in order to
bind a user-interface with a SQL database.
The library relies on auto generated primary keys (I am using
sequences for this task), however after inserting, the key-column with
the server-side generated value is set to 0.
I am porting the application from MySQL-5.0 to PostgreSQL-8.3, and
MySQL sets the key-column to the value generated by auto_increment.

Am I doing something wrong here, or is it a bug? Is that behaviour
specified at all?

The following code outputs "Hallo has key: 0" although it should
output "Hallo has key: 10000".

> CREATE TABLE customer (key SERIAL PRIMARY KEY NOT NULL, name VARCHAR(255));
> ALTER SEQUENCE customer_key_seq START WITH 10000;

> JdbcRowSet rowset = new JdbcRowSetImpl(instance.ssConnection.getConnection());
> rowset.setType(ResultSet.TYPE_SCROLL_INSENSITIVE);
> rowset.setConcurrency(ResultSet.CONCUR_UPDATABLE);
> rowset.setCommand("SELECT * FROM customer");
> rowset.execute();
> rowset.moveToInsertRow();

> rowset.updateString("name", "Hello");
> rowset.insertRow();
> System.out.println(rowset.getString("name")+" has key: "+rowset.getInt("key"));

Thank you in advance, Clemens

On Sun, 26 Jul 2009, Clemens Eisserer wrote:

> I am using a library which is using the JDBCRowSet API, in order to bind
> a user-interface with a SQL database. The library relies on auto
> generated primary keys (I am using sequences for this task), however
> after inserting, the key-column with the server-side generated value is
> set to 0. I am porting the application from MySQL-5.0 to PostgreSQL-8.3,
> and MySQL sets the key-column to the value generated by auto_increment.
>
> Am I doing something wrong here, or is it a bug? Is that behaviour
> specified at all?

I don't know whether it's unspecified or a bug, but the PG driver doesn't
do this at the moment.

http://pgfoundry.org/tracker/index.php?func=detail&aid=1010520&group_id=1000224&atid=857

Kris Jurka

Hi Kris,

> I don't know whether it's unspecified or a bug, but the PG driver doesn't do
> this at the moment.

Ugh, thats really bad news, I really need that functionality :-/
Seems I have to stay with mysql for now.

Thanks again, Clemens

PS: Seems the pgfoundry authentication is broken - tried to register
twice, but when I try to login in after registration it says my
credentials are not valid.