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: neutral (nike.apache.org: local policy)
Content-class: urn:content-classes:message
Subject: RE: updatable selects
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Date: Wed, 21 Nov 2007 08:15:39 +0100
Message-ID: <4C4F91EAA81028429D01BCA40BDB438D0454D7@exch2003.Logo.local>
Thread-Topic: updatable selects
Thread-Index: AcgqwyxJx1R3HV0USGipfuiRQ8G2rAAgPsQAABXO7nAAHKXf8A==
From: "Erik Steen Jakobsen" <esj@logodata.dk>
To: "Derby Discussion" <derby-user@db.apache.org>

You are pointing to a weak spot in my idea. =20
I better accept the fact and put in a layer between the program and the
dbm.

Thank you

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

-----Original Message-----
From: Erik Steen Jakobsen [mailto:esj@logodata.dk]=20
Sent: Tuesday, November 20, 2007 12:33 AM
To: Derby Discussion
Subject: RE: updatable selects


I've investigated further ...=20
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.=20
And even though I can use it... it is a logical pain to me Since model
of
derby/jdbc/swing seems to be : =20

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  ...=20
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=20

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=20
=20
   /*
 * 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 =3D
DriverManager.getConnection("jdbc:derby:C:/Documents and
Settings/esj/.netbeans/5.5.1/derby/testdb");

            Connection conn =3D
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 =3D
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,

            Statement stmt =3D
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 =3D stmt.executeQuery(
                        "SELECT * " +
                        "FROM \"tab1\" FOR UPDATE of \"account\""); //
only
account can be updated (according to docs)
            String theDept=3D"viby";

// lOOk out this is not legal with forward only.-)=20
            uprs.absolute(2);
//=20

            while (uprs.next())
            {
                String empname =3D uprs.getString("empname");
                String dep =3D uprs.getString("addr2");
                int no =3D uprs.getInt("id");
                int account  =3D uprs.getInt("account");
                if (dep.equals(theDept))
                {
// if the current row meets our criteria, // update the updatable column
in
the row
                    account+=3D(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=20
> 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