Thread: getXXX throws SQLException while on insert row

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

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
>
>

--
View this message in context:
http://www.nabble.com/getXXX-throws-SQLException-while-on-insert-row-tp13476706p15403292.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

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

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....



--
View this message in context:
http://www.nabble.com/getXXX-throws-SQLException-while-on-insert-row-tp13476706p15407079.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

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! :-)


--
View this message in context:
http://www.nabble.com/getXXX-throws-SQLException-while-on-insert-row-tp13476706p15407063.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.