Обсуждение: getXXX throws SQLException while on insert row
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.
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.
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.