Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Date: Wed, 21 Nov 2007 00:09:22 +0100
From: Dag.Wanvik@Sun.COM (Dag H. Wanvik)
Subject: Re: updatable selects
In-reply-to: <4C4F91EAA81028429D01BCA40BDB438D0454B6@exch2003.Logo.local>
Sender: Dag.Wanvik@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <x1oxk5ocy0nx.fsf@sun.com>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
References: <4C4F91EAA81028429D01BCA40BDB438D0454B6@exch2003.Logo.local>
User-Agent: Gnus/5.1008 (Gnus v5.10.8) Emacs/22.1 (usg-unix-v)

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

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

Your code uses SCROLL_SENSITIVE. But Derby only has updatable result
sets for SCROLL_INSENSITIVE. Hope that help :)

Thanks,
Dag


>
>
> 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?