Обсуждение: updatable resultsets -> SQLException: -1

hallo all,

i have a problem getting updatable resultsets working
(Postgres 7.3.4, current pg73jdbc3.jar)

basically i do:

try {
    Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
    Resultset rs=stmt.executeQuery("Select * from sometable;");
    rs.first();//in my example it exists
    rs.updateInt("some_int",4);
    rs.updateString("some_string","some_value");
    rs.updateRow();
} catch ( SQLException e ) {
    e.printStackTrace();
}

In that case i get an SQLException "no primary keys" whereas sometable definitely has primary key!!!

>java.sql.SQLException: No Primary Keys
>    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpdateable(AbstractJdbc2ResultSet.java:1369)
>    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateRow(AbstractJdbc2ResultSet.java:996)

i tracked this down and found that using:

Resultset rs=stmt.executeQuery("Select * from sometable for update of sometable;");

let's me indeed update the values in the table (even if the resultset is opened not-CONCUR_UPDATABLE) !!,
however there is still an "SQLException: -1" - whatever that means...

>java.sql.SQLException: -1
>    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateRow(AbstractJdbc2ResultSet.java:1082)
>...

Any hints?

Guido

On Tue, 20 Jan 2004, Guido Fiala wrote:

>
> hallo all,
>
> i have a problem getting updatable resultsets working
> (Postgres 7.3.4, current pg73jdbc3.jar)
>
> basically i do:
>
> try {
>     Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
>     Resultset rs=stmt.executeQuery("Select * from sometable;");
>     rs.first();//in my example it exists
>     rs.updateInt("some_int",4);
>     rs.updateString("some_string","some_value");
>     rs.updateRow();
> } catch ( SQLException e ) {
>     e.printStackTrace();
> }
>
> In that case i get an SQLException "no primary keys" whereas sometable
> definitely has primary key!!!

This is a known problem with the query parser.  It has many deficiencies,
but the one manifesting itself here is that the trailing semi-colon is
picked up as part of the tablename.  It tries to find the primary key for
"sometable;" which doesn't work.  A workaround for this is to leave off
the semi-colon or put a space between it and the tablename.

> i tracked this down and found that using:
>
> Resultset rs=stmt.executeQuery("Select * from sometable for update of
> sometable;");
>
> let's me indeed update the values in the table (even if the resultset
> is opened not-CONCUR_UPDATABLE) !!, however there is still an
> "SQLException: -1" - whatever that means...
>
> >java.sql.SQLException: -1 > at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateRow(AbstractJdbc2ResultSet.java:1082)
> >...
>

I don't understand what you're saying here.  You claim that it works, but
it also throws an SQLException?  Could we see some more code to produce
this?  The attached file does not give me any errors.

Kris Jurka

Am Dienstag, 20. Januar 2004 09:13 schrieb Kris Jurka:
> > "SQLException: -1" - whatever that means...
> > >java.sql.SQLException: -1 > at
> > org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateRow(AbstractJdbc2Result
> >Set.java:1082)
> >
>
> I don't understand what you're saying here.  You claim that it works, but
> it also throws an SQLException?  Could we see some more code to produce
> this?  The attached file does not give me any errors.

Yes, exactly so.

The problem is a bit deeper and i have not fully tracked down it yet.

The main thing happening in my code where the problem appears is:

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM mytesttable FOR UPDATE OF mytesttable;");

rs.updateString(2,"abc");
rs.updateRow();
rs.previous();//show some other record
rs.next();//go back to modified one
//now the above record has strangely enough vanished from my ResultSet !!!
//how do i get it back? (the ResulSet gives me at that position the values of the previous-record instead)
rs.updateString(2,"xyz");
rs.updateRow();//now i get the SQLException -1

The main problem is to reproduce the fact, that the record has vanished from the ResultSet.
With my code it does, with yours not.
It has nothing to do with a special table as it happens with all i tried, i assume, that i somehow
misconfigure the Statement/ResultSet although i can not imagine where.

On Tue, 20 Jan 2004, Guido Fiala wrote:

> Am Dienstag, 20. Januar 2004 09:13 schrieb Kris Jurka:
> > > "SQLException: -1" - whatever that means...
> > > >java.sql.SQLException: -1 > at
> > > org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateRow(AbstractJdbc2Result
> > >Set.java:1082)
> > >
> >
> > I don't understand what you're saying here.  You claim that it works, but
> > it also throws an SQLException?  Could we see some more code to produce
> > this?  The attached file does not give me any errors.
>
> Yes, exactly so.
>
> The problem is a bit deeper and i have not fully tracked down it yet.
>
> The main thing happening in my code where the problem appears is:
>
> Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
> ResultSet rs = stmt.executeQuery("SELECT * FROM mytesttable FOR UPDATE OF mytesttable;");
>
> rs.updateString(2,"abc");
> rs.updateRow();
> rs.previous();//show some other record
> rs.next();//go back to modified one
> //now the above record has strangely enough vanished from my ResultSet !!!
> //how do i get it back? (the ResulSet gives me at that position the values of the previous-record instead)
> rs.updateString(2,"xyz");
> rs.updateRow();//now i get the SQLException -1
>

You have apparently not navigated back to the original row.  The attached
program shows the error you are getting.  The error message is completely
unhelpful here, but it is your code that isn't working.

Kris Jurka

Am Mittwoch, 21. Januar 2004 03:11 schrieben Sie:
> > rs.updateString(2,"abc");
> > rs.updateRow();
> > rs.previous();//show some other record
> > rs.next();//go back to modified one
> > //now the above record has strangely enough vanished from my ResultSet
> > !!! //how do i get it back? (the ResulSet gives me at that position the
> > values of the previous-record instead) rs.updateString(2,"xyz");
> > rs.updateRow();//now i get the SQLException -1
>
> You have apparently not navigated back to the original row.  The attached
> program shows the error you are getting.  The error message is completely
> unhelpful here, but it is your code that isn't working.

The question is for me - would have the above code navigate back to the same record?

For me it looks like this:

(see attached code, it throws no excpetion)

The result is with one next():
a    b
1    zzz
2    aaa
3    world

But i would expect it should be:
a    b
1    hello
2    zzz
3    world

Do i need to requery the ResultSet to get the updated record back?
I would have to remember the primary-key of the record i was editing, to be able to jump back to the correct record
afterrequery. 
Which would be going through a loop until i find my record again as i can not assume that the records are ordered by
primarykey. 
What a waste of CPU!

Am i supposed to actually do that?

Guido

On Wed, 21 Jan 2004, Guido Fiala wrote:

> Am Mittwoch, 21. Januar 2004 03:11 schrieben Sie:
> > > rs.updateString(2,"abc");
> > > rs.updateRow();
> > > rs.previous();//show some other record
> > > rs.next();//go back to modified one
> > > //now the above record has strangely enough vanished from my ResultSet
> > > !!! //how do i get it back? (the ResulSet gives me at that position the
> > > values of the previous-record instead) rs.updateString(2,"xyz");
> > > rs.updateRow();//now i get the SQLException -1
> >
> > You have apparently not navigated back to the original row.  The attached
> > program shows the error you are getting.  The error message is completely
> > unhelpful here, but it is your code that isn't working.
>
> The question is for me - would have the above code navigate back to the same record?
>
> For me it looks like this:
>
> (see attached code, it throws no excpetion)
>
> The result is with one next():
> a    b
> 1    zzz
> 2    aaa
> 3    world
>
> But i would expect it should be:
> a    b
> 1    hello
> 2    zzz
> 3    world
>
> Do i need to requery the ResultSet to get the updated record back?
> I would have to remember the primary-key of the record i was editing, to be able to jump back to the correct record
afterrequery. 
> Which would be going through a loop until i find my record again as i can not assume that the records are ordered by
primarykey. 
> What a waste of CPU!
>

This is a bug, and it has been fixed.  If you use the 7.4 series jar file
from http://jbdc.postgresql.org/download.html you should see the correct
behavior.

Kris Jurka

Am Mittwoch, 21. Januar 2004 10:44 schrieb Kris Jurka:
> This is a bug, and it has been fixed.  If you use the 7.4 series jar file
> from http://jbdc.postgresql.org/download.html you should see the correct
> behavior.

Many thanks Kris - this solved the problem!

Guido

-------------------------------------------------------------------------------------------
just one day i'am at this list and do already receive spam-bounces -
can't we do something against such an abuse?

On Wed, 21 Jan 2004, Guido Fiala wrote:

> just one day i'am at this list and do already receive spam-bounces -
> can't we do something against such an abuse?
>

That's the problem with any list that archives its messages including
email addresses.  There is some obfuscation attemted, but that really
doesn't work because every address is obfuscated the same way.  Spam is
simply one of the costs of open development.

Kris Jurka