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 Wed, 21 Nov 2007 07:29:25 GMT
And for a "real" time app this could be everything 
Thank you 	ALL for making this point visible! 
It makes a difference when you choose your design


Erik

-----Original Message-----
From: Michael Segel [mailto:msegel@segel.com] On Behalf Of
derby@segel.com
Sent: 20. november 2007 22:16
To: 'Derby Discussion'
Subject: RE: updatable selects

Again, it depends on the application.

If you're embedding derby, then you'll want to hold a connection open as
long as your application is alive. The reason being that it costs about
100ms to open a JDBC connection to a database compared with 10ms for an
execute stmt. (YMMV but the order of magnitude exists in setting up the
connection, and that's the point.)

There are other reasons why you'd want to maintain the connection.
Caching
of prepared statements as an example...

So, I'd rethink your design. The cost of the connection object isn't
always
that expensive vs some of the underlying objects held/referenced by the
connection object. 


> -----Original Message-----
> From: Developer Dude [mailto:developer.dude@comcast.net]
> Sent: Tuesday, November 20, 2007 12:40 PM
> To: 'Derby Discussion'
> Subject: RE: updatable selects
> 
> 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
> methods).
> 
> 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)
>     {
>         try
>         {
> //            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
>             Class.forName("org.apache.derby.jdbc.ClientDriver");
>         } catch (ClassNotFoundException ex)
>         {
>             ex.printStackTrace();
>         }
> 
>         try
>         {
> //            Connection conn =
> DriverManager.getConnection("jdbc:derby:C:/Documents and
> Settings/esj/.netbeans/5.5.1/derby/testdb");
> 
>             Connection conn =
> DriverManager.getConnection("jdbc:derby://localhost:1527/testdb");
> 
>             conn.setAutoCommit(false);
> 
> // 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 =
> conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
> 
>             Statement stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
>                         ResultSet.CONCUR_UPDATABLE,
>                         ResultSet.CLOSE_CURSORS_AT_COMMIT);
> 
> // 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.-)
>             uprs.absolute(2);
> //
> 
>             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 );
>                     uprs.updateRow();
>                     System.out.println("Updating account for
employee:"
> +
>                                 empname +";" + dep + " to " +
account);
>                 }
>             }
>             conn.commit(); // commit the transaction // close object
>             uprs.close();
>             stmt.close();
> // Close connection if the application does not need it any more
>             conn.close();
>         } catch (SQLException ex)
>         {
>             ex.printStackTrace();
>         }
>     }
> }
> 
> --- 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
> manual:
> 
>
http://db.apache.org/derby/docs/dev/ref/rrefsqlj41360.html#rrefsqlj41360
> __sqlj15384
> 
> Essentially, only simple, single table selects are updatable.
> 
> Hope this helps! If not, can you provide a repro?
> 
> Thanks,
> Dag
> 
> 
> 
> 
> 







Mime
View raw message