Re: insertRow and updateable resultset

Thanks for all your help; unfortunately it still isn't working for me.

I tried using "SELECT oid,* FROM login WHERE 1=0", which didn't seem to
help.  So, then I added
updateNull("login_id");
before the insertRow(), but that made postgres think I wanted a null value
for the primary key, which isn't allowed and gives the appropriate
exception:
java.sql.SQLException: ERROR:  ExecAppend: Fail to add null value in not
null attribute login_id
        at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)


Again, thanks for all your help-do you have any more ideas?

Joel

-----Original Message-----
From: Dave Cramer [mailto:Dave@micro-automation.net]
Sent: Friday, January 17, 2003 4:28 PM
To: Joel Hock
Cc: Barry Lind; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] insertRow and updateable resultset

Joel,

the oid stuff in updateable result sets require that you do

select oid, * from foo

in order to work

So I think what you want to do is possible,

do the select as above, and do a updateNull("login_id")

then do the update row

However as I write this, I think you are correct the insertRow should
set the primary key to null to invoke the serial default value.

Dave
On Fri, 2003-01-17 at 16:51, 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)
--
Dave Cramer <Dave@micro-automation.net>