You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Daniel White (JIRA)" <ji...@apache.org> on 2009/05/19 08:30:45 UTC
[jira] Updated: (DERBY-4235) scrollable updatable ResultSets don't
show rows added with insertRow()
[ 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.