You are pointing to a weak spot in my idea. I better accept the fact and put in a layer between the program and the dbm.
Thank you -----Original Message----- From: Developer Dude [mailto:[EMAIL PROTECTED] Sent: 20. november 2007 18:46 To: 'Derby Discussion' 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:[EMAIL PROTECTED] 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: [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? Thanks, Dag
