Re: updatable resultsets -> SQLException: -1

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