javahsqldbhypersql

Caused by: org.hsqldb.HsqlException: invalid statemnet - text table required when importing CSV data

I am trying to import CSV data to HSSQL database using java and this SQL sentence:

statement.execute("set TABLE data_source source 'data.csv;ignore_first=true;fs=\\semi'");

But I am getting this error:

Exception in thread "main" java.sql.SQLException: invalid statemnet - text table required in statement [set TABLE data_source source 'data.csv;ignore_first=true;fs=\semi']
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
    at com.test.Application.main(Application.java:53)
Caused by: org.hsqldb.HsqlException: invalid statemnet - text table required
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.StatementCommand.getResult(Unknown Source)
    at org.hsqldb.StatementCommand.execute(Unknown Source)
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 3 more

P.S. using this in HSSQL client works fine:

set TABLE data_source source 'data.csv;ignore_first=true;fs=\semi'
Solution

  • You need to escape the backslash twice. The following code doesn't show any exceptions.

    public static void main(String[] args) throws Exception {
    Connection connection = DriverManager.getConnection("jdbc:hsqldb:file:~/swdev/hsqldb/testdb", "SA", "");
    PreparedStatement statement = connection.prepareCall("create text TABLE data_source (id INTEGER)");
    statement.execute();
    statement.close();
    statement = connection.prepareCall("set TABLE data_source source 'data.csv;ignore_first=true;fs=\\\\semi'");
    statement.execute();
    statement.close();
    connection.close();
}