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:13:04 GMT


-----Original Message-----
From: Developer Dude [mailto:developer.dude@comcast.net] 
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: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