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.
