[kepler-users] Problem with queries on HSQLDB database

[Jackey Wall]

I've recently built a work flow that requires numerous parameters that vary
during the run. I would like to use a lightweight database to store and then
fetch those parameters as the processes require. Towards that end I've
created a simple HSQL database (version 1.8.0.10). This is all on Windows XP
Pro.

The HSQLDB is launched as ../lib/hsqldb.jar org.hsqldb.Server and seems to
work fine from several different consoles and GUI admin tools. I can insert
and query as one would expect.

In Kepler 1.0 I have a simple test work flow: SDF Director, Open Database
Connection, to Database Query, to a display sink (text window).

The database connection seems to work fine as I can see the connection from
the HSQL log and on the display sink when I do not have the Database Query
Actor connected and in place; for example, the following is output from the
Database Connection actor to the display sink with the Database Query Actor
out-of-the-loop.

dbconnection(org.hsqldb.jdbc.jdbcConnection at faf9c1)

And, I can use a Database Writer and actually insert data to a table in my
database.

INSERT INTO PRODUCT (product_id, product_time, product_timemean,
product_timestdev, product_xreflist) VALUES ('3116-SB',15.1, 7.6, 1.51,
'0R-XXXX 0R-ABCD') ;

So, I think the connection is fine.

However, upon any query I receive an exception as follows:

exception in SQL in .TestDBConnect.Database Query
Because:
SQL executeQuery exception in .TestDBConnect.Database Query
Because:
Connection is broken: Transfer corrupted

The full stack trace is provided below.

Also, the Open Actor returns with an empty Query Builder window, except for
an error message: There was a problem loading the schema or it was empty.

So, after looking through the source for org.geon.DatabaseQuery, I suspect
that the connection is closed when the Database Query actor requests the
database schema and perhaps that fails, then that's it. Has anyone had a
similar experience? Ideas or suggestions?

-- 
Best regards,

Jackey Wall, PMP

(309) 636-5944 Office
(309) 397-4727 Mobile
(425) 663-2277 Fax

"The beginnings and endings of all human undertakings are untidy, the
building of a house, the writing of a novel, the demolition of a bridge,
and,
eminently, the finish of a voyage."
— John Galsworthy - Over the River, 1933

Stack trace:

ptolemy.kernel.util.IllegalActionException: exception in SQL
  in .TestDBConnect.Database Query
Because:
SQL executeQuery exception
  in .TestDBConnect.Database Query
Because:
Connection is broken: Transfer corrupted
    at org.geon.DatabaseQuery.fire(DatabaseQuery.java:338)
    at ptolemy.actor.AtomicActor.iterate(AtomicActor.java:398)
    at
ptolemy.actor.sched.StaticSchedulingDirector.fire(StaticSchedulingDirector.java:170)
    at ptolemy.actor.CompositeActor.fire(CompositeActor.java:400)
    at ptolemy.actor.Manager.iterate(Manager.java:688)
    at ptolemy.actor.Manager.execute(Manager.java:332)
    at ptolemy.actor.Manager.run(Manager.java:1071)
    at ptolemy.actor.Manager$3.run(Manager.java:1112)
Caused by: ptolemy.kernel.util.IllegalActionException: SQL executeQuery
exception
  in .TestDBConnect.Database Query
Because:
Connection is broken: Transfer corrupted
    at org.geon.DatabaseQuery.fire(DatabaseQuery.java:306)
    ... 7 more
Caused by: java.sql.SQLException: Connection is broken: Transfer corrupted
    at org.hsqldb.jdbc.jdbcUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
    at org.geon.DatabaseQuery.fire(DatabaseQuery.java:304)
    ... 7 more
Caused by: ptolemy.kernel.util.IllegalActionException: SQL executeQuery
exception
  in .TestDBConnect.Database Query
Because:
Connection is broken: Transfer corrupted
    at org.geon.DatabaseQuery.fire(DatabaseQuery.java:306)
    at ptolemy.actor.AtomicActor.iterate(AtomicActor.java:398)
    at
ptolemy.actor.sched.StaticSchedulingDirector.fire(StaticSchedulingDirector.java:170)
    at ptolemy.actor.CompositeActor.fire(CompositeActor.java:400)
    at ptolemy.actor.Manager.iterate(Manager.java:688)
    at ptolemy.actor.Manager.execute(Manager.java:332)
    at ptolemy.actor.Manager.run(Manager.java:1071)
    at ptolemy.actor.Manager$3.run(Manager.java:1112)
Caused by: java.sql.SQLException: Connection is broken: Transfer corrupted
    at org.hsqldb.jdbc.jdbcUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
    at org.geon.DatabaseQuery.fire(DatabaseQuery.java:304)
    ... 7 more
Caused by: java.sql.SQLException: Connection is broken: Transfer corrupted
    at org.hsqldb.jdbc.jdbcUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
    at org.geon.DatabaseQuery.fire(DatabaseQuery.java:304)
    at ptolemy.actor.AtomicActor.iterate(AtomicActor.java:398)
    at
ptolemy.actor.sched.StaticSchedulingDirector.fire(StaticSchedulingDirector.java:170)
    at ptolemy.actor.CompositeActor.fire(CompositeActor.java:400)
    at ptolemy.actor.Manager.iterate(Manager.java:688)
    at ptolemy.actor.Manager.execute(Manager.java:332)
    at ptolemy.actor.Manager.run(Manager.java:1071)
    at ptolemy.actor.Manager$3.run(Manager.java:1112)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mercury.nceas.ucsb.edu/ecoinformatics/pipermail/kepler-users/attachments/20080602/85fe8ac5/attachment.htm