getXXX throws SQLException while on insert row

[prasanth-3]

Hi,

Should the below code throw an SQLException?

rs.moveToInsertRow();
rs.updateInt("account_id", 1023);
rs.getInt("account_id");      - shouldn't this return 1023 rather than throwing SQLException?

Below is the exception:
org.postgresql.util.PSQLException: ResultSet not positioned properly, perhaps you need to call next.
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkResultSet(AbstractJdbc2ResultSet.java:2473)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:1903)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getFixedString(AbstractJdbc2ResultSet.java:2404)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2012)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2226)



Postgres Server version: 8.1
JDBC Driver version: 8.1-410 JDBC 3

Thanks,
-Prasanth.

[Kris Jurka]

On Mon, 29 Oct 2007, Prasanth Reddy wrote:

> Should the below code throw an SQLException?
>
> rs.moveToInsertRow();
> rs.updateInt("account_id", 1023);
> rs.getInt("account_id");      - shouldn't this return 1023 rather than
> throwing SQLException?
>
> Below is the exception:
> org.postgresql.util.PSQLException: ResultSet not positioned properly,
> perhaps you need to call next.

It's not clear.  Reading the JDBC spec it doesn't seem to say anything
about fetching data from a partially updated ResultSet.  Also note that
this doesn't work as you might expect for an update of a regular ResultSet
row.

int i = rs.getInt(1);
rs.updateInt(1, i+1);
// here j will be == i, not i+1.
int j = rs.getInt(1);

We store the pending values in a separate area because it makes things
like cancelRowUpdates or just navigating away from the updated row without
issuing updateRow easy.  While I can see the utility of being able to
fetch the updated values, it would complicate the driver, so I'm not
excited about it unless you can point to some documentation that says we
should.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[andreasfreyer]

Hello,

according to the Java Documentation form SUN ResultSet.moveToInsertRow is defined as:

    Moves the cursor to the insert row. The current cursor position is remembered while the cursor is positioned on the insert row. The insert row is a special row associated with an updatable result set. It is essentially a buffer where a new row may be constructed by calling the updater methods prior to inserting the row into the result set. Only the updater, getter, and insertRow methods may be called when the cursor is on the insert row. All of the columns in a result set must be given a value each time this method is called before calling insertRow. An updater method must be called before a getter method can be called on a column value.


I would like to draw our attention to the the last sentence:
"An updater method must be called before a getter method can be called on a column value."

That means after setting a value [rs.updateXXX(abc)] it is possible to get it back [rs.getXXX()] without doing anything else in between [so without rs.insertRow() or rs.updateRow()].

best regards,
 Andreas Freyer

PS: To my knowledge atleast the JDBC Drivers for the following Databases work like this: MySQLDB, HSQLDB, Derby

Kris Jurka wrote:

On Mon, 29 Oct 2007, Prasanth Reddy wrote:

> Should the below code throw an SQLException?
>
> rs.moveToInsertRow();
> rs.updateInt("account_id", 1023);
> rs.getInt("account_id");      - shouldn't this return 1023 rather than
> throwing SQLException?
>
> Below is the exception:
> org.postgresql.util.PSQLException: ResultSet not positioned properly,
> perhaps you need to call next.

It's not clear.  Reading the JDBC spec it doesn't seem to say anything
about fetching data from a partially updated ResultSet.  Also note that
this doesn't work as you might expect for an update of a regular ResultSet
row.

int i = rs.getInt(1);
rs.updateInt(1, i+1);
// here j will be == i, not i+1.
int j = rs.getInt(1);

We store the pending values in a separate area because it makes things
like cancelRowUpdates or just navigating away from the updated row without
issuing updateRow easy.  While I can see the utility of being able to
fetch the updated values, it would complicate the driver, so I'm not
excited about it unless you can point to some documentation that says we
should.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Kris Jurka]

On Sun, 10 Feb 2008, andreasfreyer wrote:

> I would like to draw our attention to the the last sentence:
> "An updater method must be called before a getter method can be called on a
> column value."
>
> That means after setting a value [rs.updateXXX(abc)] it is possible to get
> it back [rs.getXXX()] without doing anything else in between [so without
> rs.insertRow() or rs.updateRow()].
>

OK, I read that the same way you do (the pg driver is currently wrong).
I'll take a look to see how hard this will be to fix.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@... so that your
       message can get through to the mailing list cleanly

[andreasfreyer]

Hi Kris,

maybe it is not so hard to fix because only if an updateXXX (or updateNull) has already been performed onto a certain column the getter method needs to return an other result as the current implementation of the pg-jbdc driver does.

I think if you would have a list of the rows that have been recently updated a getter-method could check this list and if the column is in this list it returns the value of the insertRow  .... I mean the separate area that you mentioned in an earlier post.

Best regards,

Andreas Freyer

PS: Have Fun! :-)

[andreasfreyer]

sorry...typo:

The last sentence of my last post should start with...

I think if you would have a list of the coulumns that have....