db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik" <dag.wan...@sun.com>
Subject possible bug: "FOR UPDATE" required for updateable result set?
Date Mon, 18 Apr 2005 12:35:25 GMT

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