SQL on CSV using HSQLDB JDBC Driver

I have a pregenerated CSV file on which I need to run SQL queries. I've been looking at different open source solutions (e.g. CsvJdbc, xlSQL etc) but haven't really found anything satisfactory.

CsvJdbc couldn't recognize ORDER BY, GROUP BY etc xlSQL only runs on XLS files, not CSV (or I haven't been able to get it to run with CSV. Anybody know how?). Plus it's not in development or supported anymore.

I read somewhere that HSQLDB supports querying on CSV files, but I haven't been able to work it correctly. Here's what I've done till now:

If I create a CSV File using HSQLDB, then it's able to execute queries successfully. Here's the code for that:

String driver = "org.hsqldb.jdbcDriver";
Driver d = (Driver) Class.forName(driver).newInstance();
String protocol = "jdbc:hsqldb:file";
final String url = "jdbc:hsqldb:file:/C:/Users/varun.achar/Documents";

final StringBuilder createTable = new StringBuilder();
createTable.append("CREATE TEXT TABLE currency (");
createTable.append("id INT PRIMARY KEY, name VARCHAR)");
final StringBuilder linkTable = new StringBuilder();
linkTable.append("SET TABLE currency SOURCE ");
linkTable.append("\"/currencies.csv");
linkTable.append(";ignore_first=true;all_quoted=true\"");
Connection conn = DriverManager.getConnection(url, "sa", "");
Statement stm = conn.createStatement();
stm.execute(createTable.toString());
stm.execute(linkTable.toString());
ResultSet resultSet = stm.executeQuery("SELECT * FROM CURRENCY");
if (resultSet != null) {
    while (resultSet.next()) {
        System.out.println("CURRENCY = " + resultSet.getString(2));
    }
}
conn.close();

But the same thing doesn't work when I delete the file and run it a开发者_运维知识库gain! I get the error

Table already exists: CURRENCY in statement [CREATE TEXT TABLE currency]

Also, If I have a pre-existing csv file ( the formatting is correct since I was able to run a simple select statement using CsvJDBC) then I get the error

Table not found: RMS in statement [SET TABLE rms]

Code for this

final StringBuilder linkTable = new StringBuilder();
linkTable.append("SET TABLE rms SOURCE ");
linkTable.append("\"C:/myreports/temp/user/1316083232009/rms.csv");
linkTable.append(";ignore_first=true;all_quoted=true\"");
Driver d = (Driver) Class.forName(driver).newInstance();
System.out.println("Driver was successfully loaded.");
String protocol = "jdbc:hsqldb:file";
String database = "C:\\myreports\\temp\\user\\1316083232009\\rms.csv";
String url = protocol + ":" + database;
con = DriverManager.getConnection(url);
stm = con.createStatement();
stm.execute(linkTable.toString());
resultSet = stm.executeQuery(testSQLStatement());
if (resultSet != null) {
    while (resultSet.next()) {
        System.out.println("FULL NAME = "+ resultSet.getString("usr_FULL_NAME"));
    }
}

Can somebody throw some light on this?

Thanks

---

A single HSQLDB database can have many tables, including several TEXT tables. It seems you think there must be one database per each text table, which is not the case.

The database path is not a directory. It is not a CSV file either. In your first example you should specify a database name such as this:

final String url = "jdbc:hsqldb:file:/C:/Users/varun.achar/Documents/mydb";

The same in your second example.

The database consists of a few files beginning with the names you sepcified. In this example, you will have mydb.properties, mydb.script, etc.

The CREATE TEXT TABLE ... statement creates the metadata for the table. This table metadata is persisted in the database.

The first example will then work. If you delete the CSV file, then open the database, the 'CREATE TEXT TABLE' statement is still stored in the database, therefore it complains when you attempt to create the same table again.

If you have a preexisting CVS file, you still need to start with CREATE TEXT TABLE ..., then link it with the CVS using the SET TABLE statement, the same way as the first example.