db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel White (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4235) scrollable updatable ResultSets don't show rows added with insertRow()
Date Tue, 19 May 2009 06:30:45 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4235?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Daniel White updated DERBY-4235:
--------------------------------

    Priority: Major  (was: Blocker)

Changed priority from blocker to major; I may be able to use a different database engine.

> scrollable updatable ResultSets don't show rows added with insertRow()
> ----------------------------------------------------------------------
>
>                 Key: DERBY-4235
>                 URL: https://issues.apache.org/jira/browse/DERBY-4235
>             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.


Mime
View raw message