Re: insertRow and updateable resultset

Joel,

Thanks for the additional information.  I wanted to point out that not
all tables have an OID column, thus any general solution can't rely on
the existance of an OID column.  So I beleive the oid is only used if
the select statement explicitly included the oid in the select list.

thanks,
--Barry


Joel Hock wrote:
> 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
>>
>
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>