db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: possible bug: "FOR UPDATE" required for updateable result set?
Date Mon, 18 Apr 2005 21:10:54 GMT
Hi Dag,

Derby implements the JDBC updatable resultset by using the existing
updatable cursor implementation. And in order to do that, it requires
that the SELECT statement should include the FOR UPDATE clause. One
can change the Derby implementation so that it does not require FOR
UPDATE clause to piggyback on updatable cursor implementation. So,
yes, please go ahead and add a JIRA entry for this so we don't loose
track of the issue.

thanks,
Mamta

On 4/18/05, Dag H. Wanvik <dag.wanvik@sun.com> wrote:
> 
> Hi,
> 
> I see the following error message which I suspect to be wrong, I will
> file a JIRA issue if this is the case, but I would like your opinion first.
> 
> When I try to update a row via an updateable cursor (see code sample
> below), I still get a complaint:
> 
>    Exception in thread "main" SQL Exception: 'updateInt' not allowed \
>           because the ResultSet is not an updatable ResultSet.
>    at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:79)
>    at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:86)
>    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:165)
>    at org.apache.derby.impl.jdbc.EmbedResultSet.checksBeforeUpdateOrDelete(EmbedResultSet.java:2062)
>    at org.apache.derby.impl.jdbc.EmbedResultSet.checksBeforeUpdateXXX(EmbedResultSet.java:2041)
>    at org.apache.derby.impl.jdbc.EmbedResultSet.getDVDforColumnToBeUpdated(EmbedResultSet.java:2080)
>    at org.apache.derby.impl.jdbc.EmbedResultSet.updateInt(EmbedResultSet.java:2208)
>    at resultset1.Main.main(Main.java:81)
> 
> I need to modify the SELECT statement to include a "FOR UPDATE" clause
> for the update via cursor to work.
> 
> In section 14.2.4.1 "Updating a row" in the JDBC 3.0 specification,
> the example shown does not specify "FOR UPDATE" in the SELECT. Neither
> does my ODBC book in a similar case.
> 
> "FOR UPDATE" is intended for SQL cursors and should *not* be required
> in this case. (It is a <dynamic select statement> == <cursor
> specification> in the SQL standard).
> 
> Thanks,
> 
> Dag
> 
>        String createString = "create table mytab (i integer primary key, j integer)";
>        PreparedStatement pstmt = conn.prepareStatement(createString);
>        pstmt.executeUpdate();
> 
>        // Insert tuples
>        String insertString = "insert into mytab values (?,?)";
>        pstmt = conn.prepareStatement(insertString);
>        for (int i=0; i<1000; i++) {
>           pstmt.setInt(1, i);
>           pstmt.setInt(2, 2*i);
>           pstmt.executeUpdate();
>        }
> 
>        // String execstring = "select * from mytab for update";
>        String execstring = "select * from mytab";
>        pstmt = conn.prepareStatement(execstring,
>                                      ResultSet.TYPE_FORWARD_ONLY,
>                                      ResultSet.CONCUR_UPDATABLE);
>        ResultSet rs = pstmt.executeQuery();
>        while (rs.next()) {
>           int i = rs.getInt("i");
>           int j = rs.getInt("j");
>           System.out.println("  " + i + " " + j);
>           if ((i % 10) == 0) {
>              rs.updateInt(2, -1);
>              rs.updateRow();
>           } else if ((i%3) == 0) {
>               rs.deleteRow(); // next() re-establishes cursor
>               // rs.moveToInsertRow(); (not yet implemented)
>           }
>        }
> 
> Dag H. Wanvik
> Sun Microsystems, Web Services, Database Technology Group
> Haakon VII gt. 7b, N-7485 Trondheim, Norway
> Tel: x43496/+47 73842196, Fax:  +47 73842101
> 
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> NOTICE: This email message is for the sole use of the intended
> recipient(s) and may contain confidential and privileged
> information. Any unauthorized review, use, disclosure or distribution
> is prohibited. If you are not the intended recipient, please contact
> the sender by reply email and destroy all copies of the original
> message.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>

Mime
View raw message