Using HSQL to parse CSV files

I have a flat file data source, but I want to be able to query the data in the CSV files using HSQL. I can easily create the HSQL data source, which creates a new HSQL database on the fly (I need it to happen on the fly), but the new database instance needs to be told to use the CSV files. HSQL supports this with the "SET TABLE tablename SOURCE filename.csv" command. My problem is that I don't know if there's a way to run SQL statements from BIRT other than in the Data Set where it wants a query, not a "SET TABLE" statement. I've tried having the query run multiple statements like the following:

SET TABLE mytable SOURCE mytable.csv;SELECT id FROM mytable

...But I get the following error:

A BIRT exception occurred.
Plug-in Provider:Eclipse.org
Plug-in Name:BIRT Data Engine
Plug-in ID:org.eclipse.birt.data
Version:2.5.2.v20100209
Error Code:data.engine.fail.prepareExecution
Error Message:Failed to prepare the query execution for the data set: MY_DATASET
Failed to prepare the following query for the data set type (org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet).
[SET TABLE mytable SOURCE mytable.csv;SELECT id FROM mytable]
Error preparing SQL statement.
SQL error #1: user lacks privilege or object not found: MYTABLE

I don't think this is a file permissions issue or a database privilege issue.

Just to head off an obvious question: Can I upgrade birt to something more recent than 2.5.2? No, 2.5.2 is bundled with another app and I am required to use it.

Any ideas on how I can slip in the "SET TABLE" command somewhere?

Any help is appreciated.

Find more posts tagged with

Comments

elbeau

I ran a "create text table" command before the "set table" command, which is the right way to do it.

It still has the same error. The HSQL parser requires that the table be created in a different execution from the set table command or it fails with the same error you see above.

Any way to run SQL commands from scripts without needing to make manual JDBC connections perhaps?

Any other ideas?

Tubal

<blockquote class='ipsBlockquote' data-author="'elbeau'" data-cid="99561" data-time="1335213635" data-date="23 April 2012 - 01:40 PM">
I ran a "create text table" command before the "set table" command, which is the right way to do it. 
 
It still has the same error. The HSQL parser requires that the table be created in a different execution from the set table command or it fails with the same error you see above. 
 
Any way to run SQL commands from scripts without needing to make manual JDBC connections perhaps? 
 
Any other ideas? </blockquote>
 
I'm not familiar with HSQL, but I do something like this with PostgreSQL functions. I'm able to import csv into a table, do what I want with it, then spit out what I want, all in one query. I basically do a "select * from mycustomfunction();" in a dataset, bind it to a table, and it does everything I need. 
 
<a class='bbc_url' href='http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#N12DFD'>http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#N12DFD</a> 
 
If you can't do custom functions in HSQL, then manual connections may be your only option. Set up your database and build your table in your beforeFactory event, then access your database in your report. 
 
Thanks.