Thread: insertRow and updateable resultset
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
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
>
Joel,
Is the primary key a "serial" type, as Barry mentioned send us your
schema for that table.
It should work correctly. You may have to do a setNull on the primary
key column.
Dave
On Fri, 2003-01-17 at 13:09, 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
--
Dave Cramer <Dave@micro-automation.net>
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
>
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>
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)
>
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>