db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Erik Steen Jakobsen" <...@logodata.dk>
Subject RE: updatable selects
Date Tue, 20 Nov 2007 08:32:58 GMT

I've investigated further ... 
Documentation says (as I read it! ) that type_scroll_insensitive  should
give updatable selects (ie : resultset which is updatable).

But only ResultSet.TYPE_FORWARD_ONLY (!) gives the functionality I seek
but without the ease. 
And even though I can use it... it is a logical pain to me Since model
of derby/jdbc/swing seems to be :  

database -> resultset -> modeldata 
datebase <- resultset <- modeldata

then this picture gives a nice possibility ... given that I can lock the
rows in the database.

1: You present a "view" to a kill  ... 
2: the user updates freely while you are listening to the actions, you
move the cursor in the resultset and updates it accordingly .

3:Use ends action , you move the cursor to the updated rows and you
or in case of user regrets ... you just don't.-

That would be beautiful and elegant programming ...KISS!
But I have been away from sql programming for a very long time.
So I guess I read the documentation wrong:-) 
Or seek the wrong solution 

Test code 
---- CLIP ---
//ps this code is only a slightly changed version of the documentation.
// You'll need to define a databse("testdb") with a table "tab1" with
id, empname, addr1, addr2,account where account is an int 
 * testit.java
 * Created on 19. november 2007, 22:12
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.

package testjavadb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

 * @author esj
public class testit

    /** Creates a new instance of testit */
    public static void main(String[] args)
//            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        } catch (ClassNotFoundException ex)

//            Connection conn =
DriverManager.getConnection("jdbc:derby:C:/Documents and

            Connection conn =


// Create the statement with concurrency mode CONCUR_UPDATABLE
// to allow result sets to be updatable
// only forward_only CAN make the code work!
//            Statement stmt =

            Statement stmt =

// this will NOT WORK ... ONLY ResultSet.TYPE_FORWARD_ONLY
// Updatable statements have some requirements
// for example, select must be on a single table

            ResultSet uprs = stmt.executeQuery(
                        "SELECT * " +
                        "FROM \"tab1\" FOR UPDATE of \"account\""); //
only account can be updated (according to docs)
            String theDept="viby";

// lOOk out this is not legal with forward only.-) 

            while (uprs.next())
                String empname = uprs.getString("empname");
                String dep = uprs.getString("addr2");
                int no = uprs.getInt("id");
                int account  = uprs.getInt("account");
                if (dep.equals(theDept))
// if the current row meets our criteria,
// update the updatable column in the row
                    account+=(no +1);
                    uprs.updateInt("account",account );
                    System.out.println("Updating account for employee:"
                                empname +";" + dep + " to " + account);
            conn.commit(); // commit the transaction
// close object
// Close connection if the application does not need it any more
        } catch (SQLException ex)

--- end clip ---
-----Original Message-----
From: Dag.Wanvik@Sun.COM [mailto:Dag.Wanvik@Sun.COM] 
Sent: 19. november 2007 16:45
To: Derby Discussion
Subject: Re: updatable selects

Erik Steen Jakobsen <esj@logodata.dk> writes:

> All I get from derby is resultsets that are read_only when I ask for
> concurrent updatable....

You can update through JDBC result sets if you specify
ResultSet.CONCUR_UPDATABLE. You do not even need for specify "FOR
UPDATE" in that case in the underlying query (default if you use

But not all queries are updatable, cf. this section of the reference


Essentially, only simple, single table selects are updatable.

Hope this helps! If not, can you provide a repro?


View raw message