Erik Steen Jakobsen <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: 19. november 2007 16:45
> To: Derby Discussion
> Subject: Re: updatable selects
>
> Erik Steen Jakobsen <[EMAIL PROTECTED]> 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?
