Thread: Update ResultSet for JDBC

Update ResultSet for JDBC

From
Zengfa Gao
Date:
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

Re: Update ResultSet for JDBC

From
Dave Cramer
Date:
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
>
>




Re: Update ResultSet for JDBC

From
Zengfa Gao
Date:
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

Proper state after insertRow

From
Dave Cramer
Date:
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


Re: Proper state after insertRow

From
"Paul Stead"
Date:
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


Re: Proper state after insertRow

From
Dave Cramer
Date:
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
>
>



Re: Proper state after insertRow

From
"Paul Stead"
Date:
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 >>>


Re: Proper state after insertRow

From
Dave Cramer
Date:
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
>
>