db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Developer Dude" <developer.d...@comcast.net>
Subject RE: updatable selects
Date Tue, 20 Nov 2007 18:39:38 GMT
Or client/server based period.

My current use of Derby is as a Swing client side cache for server objects
that can number in the millions for any given view (multiple views may be
open) and that I don't want to hold in memory in order to show in a view. So
I use the embedded version solely (the server I am hitting uses SQLServer,
or Oracle, depending on the clients needs/wants).

Still, I don't hold open a connection for any longer than I need to run a
CRUD operation (single or batch). I do all the DB/data store ops in the
background and notify the model when the ops change it. The notify methods
use invokeLater() so that I can keep the Swing thread and the background
threads separate. I have no problem opening and closing many connections
(one connection per thread, I use a multi-reader/single writer lock in the

I try to keep the model and the view as disconnected from the backing data
store as possible via a processor interface that handle all CRUD operations
(there are background processors to populate the data store and model from
the server). It is much cleaner and I can swap in a different caching store
as needed without changing a lot of model code, or having users of the model
worry about the complexities of the data store. The data store can be reused
for different models and views.

-----Original Message-----
From: msegel@dbrack01.segel.com [mailto:msegel@dbrack01.segel.com] 
Sent: Tuesday, November 20, 2007 9:33 AM
To: Derby Discussion
Subject: Re: updatable selects

Depends on the app.

Not everything is web based.
Especially if you've embedded the engine in your app you'll want to keep the
connection for a long time.

Sent via BlackBerry by AT&T

-----Original Message-----
From: "Developer Dude" <developer.dude@comcast.net>

Date: Tue, 20 Nov 2007 09:33:59
To:"'Derby Discussion'" <derby-user@db.apache.org>
Subject: RE: updatable selects

Do you really want to hold a connection open for that long? I believe
connections can time out.

Who knows how long the user may let that screen sit there in edit mode? 

-----Original Message-----
From: Erik Steen Jakobsen [mailto:esj@logodata.dk] 
Sent: Tuesday, November 20, 2007 12:33 AM
To: Derby Discussion
Subject: RE: updatable selects

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 commit.
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 //OBS OBS OBS // 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

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

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