Thread: Update ResultSet for JDBC
Hi, all:
From JDBC website, I got
Latest Java/JDBC news
Jun 23 Updated dev drivers that include callable
statement support and updatable result set support
Then I downloaded 7.3Dev version of JDBC driver and
try to use updatable result set.
When I run
ResultSet rs = stmt.executeQuery(selectStat);
rs.moveToInsertRow();
I got:
ERROR: Got exception: Result Set not
updateable. The query that gene
rated this result set must select only one table, and
must select all primary ke
ys from that table. See the JDBC 2.1 API
Specification, section 5.6 for more det
ails.
But I only select from one table, and I select all
columns from that table include primary key. Does
anyone know why it failed at here?
I used PostgreSQL 7.0.3 on Redhat system.
Thanks,
Zengfa
__________________________________________________
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com
It's possible it doesn't work with a 7.0 database. Why are you using such an old version? Dave On Thu, 2002-08-15 at 14:21, Zengfa Gao wrote: > Hi, all: > > >From JDBC website, I got > > Latest Java/JDBC news > Jun 23 Updated dev drivers that include callable > statement support and updatable result set support > > Then I downloaded 7.3Dev version of JDBC driver and > try to use updatable result set. > > When I run > > ResultSet rs = stmt.executeQuery(selectStat); > > rs.moveToInsertRow(); > > I got: > > > ERROR: Got exception: Result Set not > updateable. The query that gene > rated this result set must select only one table, and > must select all primary ke > ys from that table. See the JDBC 2.1 API > Specification, section 5.6 for more det > ails. > > > But I only select from one table, and I select all > columns from that table include primary key. Does > anyone know why it failed at here? > > I used PostgreSQL 7.0.3 on Redhat system. > > Thanks, > > Zengfa > > > __________________________________________________ > Do You Yahoo!? > HotJobs - Search Thousands of New Jobs > http://www.hotjobs.com > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
I used postgresql 7.21 version on Redhat 7.1 system, got same error. I used JDBC development version. ERROR: Got exception: Result Set not updateable. The query that gene rated this result set must select only one table, and must select all primary ke ys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details. But I only select one table, and I select all columns include primary key. How can I get "Updateable ResultSet" works? Thanks! Zengfa --- Dave Cramer <Dave@micro-automation.net> wrote: > It's possible it doesn't work with a 7.0 database. > Why are you using > such an old version? > > Dave > On Thu, 2002-08-15 at 14:21, Zengfa Gao wrote: > > Hi, all: > > > > >From JDBC website, I got > > > > Latest Java/JDBC news > > Jun 23 Updated dev drivers that include callable > > statement support and updatable result set support > > > > > Then I downloaded 7.3Dev version of JDBC driver > and > > try to use updatable result set. > > > > When I run > > > > ResultSet rs = stmt.executeQuery(selectStat); > > > > rs.moveToInsertRow(); > > > > I got: > > > > > > ERROR: Got exception: Result Set not > > updateable. The query that gene > > rated this result set must select only one table, > and > > must select all primary ke > > ys from that table. See the JDBC 2.1 API > > Specification, section 5.6 for more det > > ails. > > > > > > But I only select from one table, and I select all > > columns from that table include primary key. Does > > anyone know why it failed at here? > > > > I used PostgreSQL 7.0.3 on Redhat system. > > > > Thanks, > > > > Zengfa > > > > > > __________________________________________________ > > Do You Yahoo!? > > HotJobs - Search Thousands of New Jobs > > http://www.hotjobs.com > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > __________________________________________________ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com
Does anyone know what the state of the result set should be after an insertRow Does the result set stay on the "insertRow", does the inserted row become the current row should we force the user to do a refreshRow after an insert to pick up the results of sequences, etc. after the user executes insertRow, can they then update this row without moving? Dave
Dave, No expert here, but according to JDBC API Tutorial and Reference, 2nd Ed, from the Java Series: (pg 596 - top) ".....the application calls the method insertRow. This method adds the insert row to both the result set and the underlying database simultaneously. Finally, the application neeeds to position the cursor on a row back in the result set." So I would think the cursor stays on the insert row, which is "a special row, associated with the result set but *not a part of it*", and the application must move the cursor where it wants from there. As it says "the application needs to position the cursor on a row back in the result set". and then (pg 596 - bottom) "....a result set keeps track of where its cursor was positioned when the cursor moved to the insert row. As a result, a call to the method ResultSet.moveToCurrentRow will return the cursor to the row that was the current row immediately before the method moveToInsertRow was called." So IMHO while the cursor remains on the insert row, the inserted row itself does *not* become the current row, since "the method ResultSet.moveToCurrentRow will return the cursor to the row that was the current row immediately before the method moveToInsertRow was called". Once the application has moved the cursor back to the result set, I don't think a refreshRow would be needed since insertRow "adds the insert row to both the result set and underlying database simultaneously." And finally, once the application has executed insertRow, I don't think they can update that particular row without moving. I think the insert row stays a staging area and perhaps you could "stay there" (i.e. leave the cursor there) and do some more updateInt updateString calls etc., but once you call insertRow again it will insert yet another new row into the result set and database providing you have updated all the necessary columns. It would seem that the insert row should be "cleared" when insertRow is called, so that if someone didn't treat it like starting over they'd get an exception. Otherwise it might be a little dangerous. Anywho, that's how I read all this..... Hope this helps! Paul -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer Sent: Friday, August 23, 2002 6:48 AM To: Zengfa Gao Cc: pgsql-jdbc@postgresql.org Subject: [JDBC] Proper state after insertRow Does anyone know what the state of the result set should be after an insertRow Does the result set stay on the "insertRow", does the inserted row become the current row should we force the user to do a refreshRow after an insert to pick up the results of sequences, etc. after the user executes insertRow, can they then update this row without moving? Dave ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Ok, So far this is consistent with what I understand. However there is a special case ( there always is ;) ). What if the underlying table has no data in it. In this case the cursor is nowhere! Dave On Fri, 2002-08-23 at 12:24, Paul Stead wrote: > Dave, > > No expert here, but according to JDBC API Tutorial and Reference, 2nd Ed, > from the Java Series: > (pg 596 - top) ".....the application calls the method insertRow. This > method adds the insert row to both the result set and the underlying > database simultaneously. Finally, the application neeeds to position the > cursor on a row back in the result set." > > So I would think the cursor stays on the insert row, which is "a special > row, associated with the result set but *not a part of it*", and the > application must move the cursor where it wants from there. As it says "the > application needs to position the cursor on a row back in the result set". > > and then > > (pg 596 - bottom) "....a result set keeps track of where its cursor was > positioned when the cursor moved to the insert row. As a result, a call to > the method ResultSet.moveToCurrentRow will return the cursor to the row that > was the current row immediately before the method moveToInsertRow was > called." > > So IMHO while the cursor remains on the insert row, the inserted row itself > does *not* become the current row, since "the method > ResultSet.moveToCurrentRow will return the cursor to the row that was the > current row immediately before the method moveToInsertRow was called". > > Once the application has moved the cursor back to the result set, I don't > think a refreshRow would be needed since insertRow "adds the insert row to > both the result set and underlying database simultaneously." > > And finally, once the application has executed insertRow, I don't think they > can update that particular row without moving. I think the insert row stays > a staging area and perhaps you could "stay there" (i.e. leave the cursor > there) and do some more updateInt updateString calls etc., but once you call > insertRow again it will insert yet another new row into the result set and > database providing you have updated all the necessary columns. It would > seem that the insert row should be "cleared" when insertRow is called, so > that if someone didn't treat it like starting over they'd get an exception. > Otherwise it might be a little dangerous. > > Anywho, that's how I read all this..... > > Hope this helps! > Paul > > > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer > Sent: Friday, August 23, 2002 6:48 AM > To: Zengfa Gao > Cc: pgsql-jdbc@postgresql.org > Subject: [JDBC] Proper state after insertRow > > > Does anyone know what the state of the result set should be after an > insertRow > > Does the result set stay on the "insertRow", does the inserted row > become the current row > > should we force the user to do a refreshRow after an insert to pick up > the results of sequences, etc. > > after the user executes insertRow, can they then update this row without > moving? > > Dave > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
Wouldn't it behave exactly the same as a cursor in a ResultSet before you call next() for the first time? Again from JDBC API Tutorial and Reference, "when a ResultSet object is first created, the cursor is positioned before the first row, so the first call to next() puts the cursor on the first row, making it the current row." So wouldn't moveToCurrentRow return the cursor to its position before the first row (which now exists, although it didn't before)? Paul -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer Sent: Friday, August 23, 2002 10:38 AM To: pstead@elementallogic.com Cc: Zengfa Gao; pgsql-jdbc@postgresql.org Subject: Re: [JDBC] Proper state after insertRow Ok, So far this is consistent with what I understand. However there is a special case ( there always is ;) ). What if the underlying table has no data in it. In this case the cursor is nowhere! Dave On Fri, 2002-08-23 at 12:24, Paul Stead wrote: > Dave, > > No expert here, but according to JDBC API Tutorial and Reference, 2nd Ed, > from the Java Series: <<< snip >>>
Ok, this makes sense, and is more or less what it does after I fixed it so you can insert into an empty result set. Thanks Dave On Fri, 2002-08-23 at 14:32, Paul Stead wrote: > Wouldn't it behave exactly the same as a cursor in a ResultSet before you > call next() for the first time? Again from JDBC API Tutorial and Reference, > "when a ResultSet object is first created, the cursor is positioned before > the first row, so the first call to next() puts the cursor on the first row, > making it the current row." So wouldn't moveToCurrentRow return the cursor > to its position before the first row (which now exists, although it didn't > before)? > > Paul > > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer > Sent: Friday, August 23, 2002 10:38 AM > To: pstead@elementallogic.com > Cc: Zengfa Gao; pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] Proper state after insertRow > > > Ok, > > So far this is consistent with what I understand. However there is a > special case ( there always is ;) ). What if the underlying table has no > data in it. In this case the cursor is nowhere! > > Dave > On Fri, 2002-08-23 at 12:24, Paul Stead wrote: > > Dave, > > > > No expert here, but according to JDBC API Tutorial and Reference, 2nd Ed, > > from the Java Series: > > <<< snip >>> > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >