Re: insertRow and updateable resultset

Hi,

There was a typo in the original email; the last line should be:
getString("login_id");
Imagine a simple schema:
CREATE SEQUENCE login_seq;
CREATE TABLE login (
    login_id INT PRIMARY KEY DEFAULT nextval('login_seq'),
    email TEXT NOT NULL
);

I realize that the driver doesn't support the getGeneratedKeys()-type
functionality, but I was hoping a refreshRow after an insertRow would
retrieve the generated key.  The reason I think it should work is that the
driver stores the oid of the inserted row.  From
AbstractJdbc2ResultSet.java's insertRow() line 639:

long insertedOID = ((AbstractJdbc2Statement) insertStatement).getLastOID();
updateValues.put("oid", new Long(insertedOID) );

Furthermore, AbstractJdbc2ResultSet.java's isUpdateable(), which is called
at the beginning of most functions dealing with updateable resultsets,
should be adding the oid to the 'PrimaryKey' Vector (around line 1319).  So,
I thought that would enable the refreshRow() to work, since the oid would
act as a primary key.

Thanks,
Joel

-----Original Message-----
From: Barry Lind [mailto:blind@xythos.com]
Sent: Friday, January 17, 2003 2:56 PM
To: Joel Hock
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] insertRow and updateable resultset

Joel,

To better understand your problem, can you please include your schema
definition?  (what is the table structure, the PK columns, and the
column you are trying to access the generated key of).

But in general, I don't see how this is going to work in postgres, if my
assumptions about your schema are accurate.  In general the driver does
not support retrieving generated keys in an automated fashion (and the
DatabaseMetaData object correctly reports that).

In the case at hand, the query build to refresh the row, uses the
primary key to fetch the data, but you don't have the primary key since
it is autogenerated and the driver doesn't know what the value was.  So
it is going to issue the refresh using null for the primary key and thus
not get any results.

thanks,
--Barry


Joel Hock wrote:
> All of this pertains to PostgreSQL 7.1.3 and the jdbc3 development
> driver that I downloaded today and also the stable driver.
>
>
>
> I am using an updateable ResultSet and insertRow() to insert a row,
> which works fine.
>
> I then do a refreshRow() and try to retrieve the auto-generated primary
> key, which doesn't work.  I just get back a null value.  I am currently
> using the oid to re-select the row as a workaround, but want a
> database-independent way of getting the key.
>
>
>
> Sample code:
>
>
>
> ResultSet uprs = stmt.executeQuery("SELECT * FROM login WHERE 1=0");
>
> uprs.moveToInsertRow();
>
> uprs.updateObject("email", email);
>
> uprs.insertRow();
>
> uprs.next();
>
> uprs.refreshRow();
>
> String loginId = uprs.getString();    // returns null
>
>
>
> Note that the same thing happens if I leave out the 'uprs.next()'.  (As
> an aside, this is a bug because the refreshRow() should fail if next()
> is not called; the java docs state that refreshRow() should fail on the
> insert row).
>
>
>
>
>
> Can anyone confirm that the code I'm using should return the generated
> primary key from the database?  I've looked at the driver code
> (AbstractJdbc2ResultSet.java) and couldn't see why this wasn't working.
>
>
>
> Thanks,
>
> Joel
>