db-derby-dev mailing list archives

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

    [ https://issues.apache.org/jira/browse/DERBY-4235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12710814#action_12710814
] 

Dag H. Wanvik commented on DERBY-4235:
--------------------------------------

Yes, Knut is correct. The "may" here is because, a priori, scrollable, insensitive
result sets (in contrast to scrollable, sensitive result sets, which Derby do not presently
support)
provide no guarantees that inserted rows are seen. Cf the metadata method
ResultSet.ownInsertsAreVisible, which returns "false" for Derby.

 In reality, whether an inserted row will be seen depends on 
where the row ends up in the sequence of rows being read from the table the first time.
Re-reads if we scroll back and forth will not "see" any inserted rows.

Feel free to post an improvement request for this capability, though!



> 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