Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <257124352.1242756885540.JavaMail.jira@brutus>
Date: Tue, 19 May 2009 11:14:45 -0700 (PDT)
From: "Daniel White (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Created: (DERBY-4237) scrollable updatable ResultSets should
 show rows added with insertRow()
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

scrollable updatable ResultSets should show rows added with insertRow()
-----------------------------------------------------------------------

                 Key: DERBY-4237
                 URL: https://issues.apache.org/jira/browse/DERBY-4237
             Project: Derby
          Issue Type: Bug
    Affects Versions: 10.5.1.1
         Environment: Windows XP Pro
Java Version:    1.6.0_13
Java Vendor:     Sun Microsystems Inc.
JRE - JDBC: Java SE 6 - JDBC 4.0
[C:\Program Files\Apache Software Foundation\db-derby-10.5.1.1-bin\lib\derby.jar] 10.5.1.1 - (764942)

            Reporter: Daniel White


I have a database table with two fields: ID (integer, primary key, identity) and F1 (varchar, default '').

In my Java/JDBC program, I select 2 records from this table into a ResultSet that is scrollable and updatable.

I do rs.moveToInsertRow() and rs.insertRow() to add a record to the ResultSet.

The record gets added to the database, but my ResultSet still has only 2 records in it.  It doesn't show the new record unless I open a new ResultSet by querying the database again.  I need to be able to see the record that I added as soon as I have added it, without having to requery the database.

I ran the same test with a different database platform (MySQL), and it worked just fine.  I'd rather use Derby because it can be embedded into the application, but I won't be able to if this doesn't work.

If I'm doing something wrong, please let me know.

Here is the full code that I am using:

------------------------------

database:

CREATE TABLE APP.TEST_TABLE_1
(
	ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
	F1 VARCHAR(25) DEFAULT '',
	PRIMARY KEY (ID)
);

-----------------------------

DerbyGetRowTest.java:

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.derby.tools.sysinfo;

/**
 *
 * @author Daniel
 */
public class DerbyGetRowTest {

    // loop through the result set and output the records
    static void showRs(ResultSet rs) {
        try {
            rs.beforeFirst();
            if (rs.isBeforeFirst()) {
                System.out.println("before first record");
            }
            while (rs.next()) {
                System.out.println("row " + rs.getRow() + ": ID = " + rs.getInt("ID") + "; F1 = " + rs.getString("F1"));
            }
            if (rs.isAfterLast()) {
                System.out.println("after last record");
            }
        } catch (SQLException ex) {
            while (ex != null) {
                System.err.println(ex);
                ex = ex.getNextException();
            }
        }
        System.out.println();
    }

    // run this test once for MySQL and once for Derby
    static void runTest(String url) {
        try {
            // get connection to database
            Connection connection = DriverManager.getConnection(url);
            System.out.println("connected to " + url);
            System.out.println();

            // set up the initial records
            Statement statement = connection.createStatement();
            statement.executeUpdate("Delete from test_table_1");
            statement.executeUpdate("Insert into test_table_1 (F1) values ('a')");
            statement.executeUpdate("Insert into test_table_1 (F1) values ('b')");

            // get result set; it doesn't seem to matter whether I use
            // TYPE_SCROLL_SENSITIVE or TYPE_SCROLL_INSENSITIVE
            Statement stmt = connection.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = stmt.executeQuery("select id, f1 from test_table_1");

            // make sure we got something
            showRs(rs); // there are 2 records

            // insert a new row with the result set
            rs.moveToInsertRow();
            // ID is an auto-increment field
            // F1 has a default value of ''
            rs.insertRow();

            System.out.println("inserted new row");

            // see what we now have
            showRs(rs); // MySQL shows 3 records; Derby only shows 2

            // refetch the records from the database
            rs = stmt.executeQuery("select id, f1 from test_table_1");
            showRs(rs); // there are 3 records
        } catch (SQLException ex) {
            while (ex != null) {
                System.err.println(ex);
                ex = ex.getNextException();
            }
        }

        System.out.println();
    }

    public static void main(String[] args) {
        sysinfo.main(args);

        String mysqlUrl = "jdbc:mysql://localhost:3306/test";
        String derbyUrl = "jdbc:derby:testdb";

        runTest(mysqlUrl);
        runTest(derbyUrl);
    }
}

--------------------------------------------

output:

run:
------------------ Java Information ------------------
Java Version:    1.6.0_13
Java Vendor:     Sun Microsystems Inc.
Java home:       C:\Program Files\Java\jdk1.6.0_13\jre
Java classpath:  C:\Program Files\NetBeans 6.5.1\ide10\modules\ext\mysql-connector-java-5.1.6-bin.jar;C:\Program Files\Apache Software Foundation\db-derby-10.5.1.1-bin\lib\derby.jar;C:\Documents and Settings\Daniel\My Documents\PRG\Java\Test\build\classes;C:\Documents and Settings\Daniel\My Documents\PRG\Java\Test\src
OS name:         Windows XP
OS architecture: x86
OS version:      5.1
Java user name:  Daniel
Java user home:  C:\Documents and Settings\Daniel
Java user dir:   C:\Documents and Settings\Daniel\My Documents\PRG\Java\Test
java.specification.name: Java Platform API Specification
java.specification.version: 1.6
--------- Derby Information --------
JRE - JDBC: Java SE 6 - JDBC 4.0
[C:\Program Files\Apache Software Foundation\db-derby-10.5.1.1-bin\lib\derby.jar] 10.5.1.1 - (764942)
------------------------------------------------------
----------------- Locale Information -----------------
Current Locale :  [English/United States [en_US]]
Found support for locale: [cs]
         version: 10.5.1.1 - (764942)
Found support for locale: [de_DE]
         version: 10.5.1.1 - (764942)
Found support for locale: [es]
         version: 10.5.1.1 - (764942)
Found support for locale: [fr]
         version: 10.5.1.1 - (764942)
Found support for locale: [hu]
         version: 10.5.1.1 - (764942)
Found support for locale: [it]
         version: 10.5.1.1 - (764942)
Found support for locale: [ja_JP]
         version: 10.5.1.1 - (764942)
Found support for locale: [ko_KR]
         version: 10.5.1.1 - (764942)
Found support for locale: [pl]
         version: 10.5.1.1 - (764942)
Found support for locale: [pt_BR]
         version: 10.5.1.1 - (764942)
Found support for locale: [ru]
         version: 10.5.1.1 - (764942)
Found support for locale: [zh_CN]
         version: 10.5.1.1 - (764942)
Found support for locale: [zh_TW]
         version: 10.5.1.1 - (764942)
------------------------------------------------------
connected to jdbc:mysql://localhost:3306/test

before first record
row 1: ID = 45; F1 = a
row 2: ID = 46; F1 = b
after last record

inserted new row
before first record
row 1: ID = 45; F1 = a
row 2: ID = 46; F1 = b
row 3: ID = 47; F1 = 
after last record

before first record
row 1: ID = 45; F1 = a
row 2: ID = 46; F1 = b
row 3: ID = 47; F1 = 
after last record


connected to jdbc:derby:testdb

before first record
row 1: ID = 31; F1 = a
row 2: ID = 32; F1 = b
after last record

inserted new row
before first record
row 1: ID = 31; F1 = a
row 2: ID = 32; F1 = b
after last record

before first record
row 1: ID = 31; F1 = a
row 2: ID = 32; F1 = b
row 3: ID = 33; F1 = 
after last record


BUILD SUCCESSFUL (total time: 3 seconds)


-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.