CachedRowSet Support

102 views
Skip to first unread message

da...@dandymadeproductions.com

unread,
Mar 31, 2014, 3:40:15 PM3/31/14
to h2-da...@googlegroups.com
Hello,

In the Roadmap, Priority 1, is listed Server side cursors. Does the
com.sun.rowset.CachedRowSetImpl, javax.sql.rowset.CachedRowSet classes
fall into this category?

I have been testing these classes with the MyJSQLView GUI client and the
H2 database and have seen some issues.

Dana M. Proctor
MyJSQLView Project Manager
http://code.google.com/p/myjsqlview/

Thomas Mueller

unread,
Apr 1, 2014, 1:48:13 AM4/1/14
to h2-da...@googlegroups.com
Hi,

No, server side cursors are regular result sets. Server side cursors will be much easier to implement with the MVStore engine, so this will be done first.

H2 does not implement row sets.

But anyway, what issues did you see? If the issues are not fully related to H2, I guess it makes sense to ask at StackOverflow.com.

Regards,
Thomas
--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

da...@dandymadeproductions.com

unread,
Apr 1, 2014, 12:06:32 PM4/1/14
to h2-da...@googlegroups.com
Hello,

Actually the regular JdbcRowSet, essentially a connected result set, seems to
work fine. The CachedRowSet also works fine for a query, but when a
acceptChange()
is applied back to the database table the following error occurs.

SQLException: Column "NAME" not found; SQL statement:
INSERT INTO KEY_TABLES.PUBLIC."key_table5" (name, color, price) VALUES (?,
?, ?) [42122-175]
SQLState: null
VendorError: 0
org.h2.jdbc.JdbcSQLException: Column "NAME" not found; SQL statement:
INSERT INTO KEY_TABLES.PUBLIC."key_table5" (name, color, price) VALUES (?,
?, ?) [42122-175]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
at org.h2.message.DbException.get(DbException.java:172)
at org.h2.message.DbException.get(DbException.java:149)
at org.h2.table.Table.getColumn(Table.java:626)
at org.h2.command.Parser.parseColumn(Parser.java:826)
at org.h2.command.Parser.parseColumnList(Parser.java:811)
at org.h2.command.Parser.parseInsert(Parser.java:1004)
at org.h2.command.Parser.parsePrepared(Parser.java:394)
at org.h2.command.Parser.parse(Parser.java:298)
at org.h2.command.Parser.parse(Parser.java:270)
at org.h2.command.Parser.prepareCommand(Parser.java:235)
at org.h2.engine.Session.prepareLocal(Session.java:436)
at org.h2.engine.Session.prepareCommand(Session.java:379)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:70)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:267)
at com.sun.rowset.internal.CachedRowSetWriter.writeData(Unknown Source)
at com.sun.rowset.CachedRowSetImpl.acceptChanges(Unknown Source)
at com.sun.rowset.CachedRowSetImpl.acceptChanges(Unknown Source)
at
com.dandymadeproductions.dbtofilememorydb.DB_To_FileMemoryDB.executeCachedJDBCRowSet(DB_To_FileMemoryDBPanel.java:831)

*** 831 - cachedRowSet.acceptChanges(dbConnection);

The columns in the table are defined in lowercase so without being quoted
in the insert
there is an error. Offhand I would say this seems to be an issue with the
CachedRowSetWriter
and perhaps can be overidden by a local implemenating class.

I have included H2 as an library to the MyJSQLView desktop application and
have chosen
it to be one of the database options for creating local file/memory tables
for analysis.

It has been a pleasure to include H2 as a supported database by
MyJSQLView. I have found
its workings to be quite SQL compliant and easy to work with.

Thomas Mueller

unread,
Apr 2, 2014, 1:42:30 AM4/2/14
to h2-da...@googlegroups.com
Hi,

Some identifiers are quoted (surrounded with double quotes) and others are not. Depending on how the table was created, that's wrong. I would try:

    INSERT INTO KEY_TABLES.PUBLIC."key_table5" ("name", "color", "price") VALUES (?, ?, ?) 

I would consistently _not_ use double quotes, but then the "create table" statement also needs to be changed.

Regards,
Thomas

da...@dandymadeproductions.com

unread,
Apr 2, 2014, 5:03:30 PM4/2/14
to h2-da...@googlegroups.com
Hello,

Actually the table was created by quoting all identifiers. I have to test
such
cases in the MyJSQLView application because I never know how someone will
define
a table.

In testing CachedRowSets for the various databases that support them, MySQL,
PostgreSQL, etc. the same testing passed without the error.

Now I have produced a self-contained test case that can be found at the
location
identified below. By using no quotes H2 does work properly. Quoting will
give the
error described when the identifiers are not all uppercase I suspect.

This is of no consequence since after testing I decided CachedRowSets are not
the way to go for creating data sets for recurring analysis.

Test Case: http://dandymadeproductions.com/temp/H2_CachedRowSet.java

Thomas Mueller

unread,
Apr 6, 2014, 8:32:36 AM4/6/14
to H2 Google Group
Hi,

Actually the table was created by quoting all identifiers

Yes. In fact, all column and table names are quoted in *all* statements, except in the statement that fails. Why don't you quote the column names there as well? Or don't quote the column names everywhere?

DROP TABLE IF EXISTS "key_table5"

CREATE TABLE "key_table5" ("name" VARCHAR(30) DEFAULT '' NOT NULL, "color" VARCHAR(10) NOT NULL, "price" DOUBLE DEFAULT NULL, PRIMARY KEY ("color"));

INSERT INTO "key_table5" ("name", "color", "price") VALUES ('bike', 'blue', 100.0)

Select Query: SELECT "name", "color", "price" FROM "key_table5"

org.h2.jdbc.JdbcSQLException: Column "NAME" not found; SQL statement:

INSERT INTO TEST.PUBLIC."key_table5" (name, color, price) VALUES (?, ?, ?) [42122-176]


Regards,
Thomas




Reply all
Reply to author
Forward
0 new messages