RE: updatable selects

[Erik Steen Jakobsen]

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