HSQLDB unexpected token:?

问题:

I have a JAVAFX project using HSQLDB. When trying to set the SOURCE of a table I get an exception I think I understand, but since I cant fix it I guess I dont understand it. My SQL is:

DROP TABLE temp IF EXISTS;
CREATE TEXT TABLE temp(text_data LONGVARCHAR(10000));
SET TABLE temp SOURCE ?;
INSERT INTO log(typ, json) SELECT SUBSTRING(text_data, 3, LOCATE('"', text_data, 3)-3),text_data FROM temp WHERE text_data <> '';
DROP TABLE temp IF EXISTS;

Mutliple Statements somehow do not work for me here, and this should not be a problem for now. Im splitting the sql above into an ArrayList of Strings, with each line being one element. So I got this Java Code:

s = c.createStatement();
for (String sql : sqls) {
  System.out.println("sql: " + sql);
  if (sql.contains("?")) {
    System.out.println("in ? part");
    PreparedStatement ps = c.prepareStatement(sql);

    ps.setString(1, path.toUri().toString() + ";encoding=UTF-8;ignore_first=false;fs=\\n\\r");
    System.out.println("ps prepared" + ps.toString());
    ps.execute();
  } else {
    s.execute(sql);
  }
}

And my application is failing at line PreparedStatement ps = c.prepareStatement(sql); with the following exception:

java.sql.SQLSyntaxErrorException: unexpected token: ? in statement [SET TABLE temp SOURCE ?;]
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
    at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
    at myfile in the line I pointed out above
    at anotherofmyfiles
    at javafx.concurrent.Task$TaskCallable.call(Task.java:1423)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.hsqldb.HsqlException: unexpected token: ?
    at org.hsqldb.error.Error.parseError(Unknown Source)
    at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
    at org.hsqldb.ParserBase.checkIsValue(Unknown Source)
    at org.hsqldb.ParserBase.readQuotedString(Unknown Source)
    at org.hsqldb.ParserCommand.compileTableSource(Unknown Source)
    at org.hsqldb.ParserCommand.compileSetTable(Unknown Source)
    at org.hsqldb.ParserCommand.compileSet(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
    at org.hsqldb.Session.compileStatement(Unknown Source)
    at org.hsqldb.StatementManager.compile(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 7 more

with this output before:

sql: DROP TABLE temp IF EXISTS;
sql: CREATE TEXT TABLE temp(text_data LONGVARCHAR(10000));
sql: SET TABLE temp SOURCE ?;
in ? part

I am aware that ps.setString(1, path.toUri().toString() + ";encoding=UTF-8;ignore_first=false;fs=\\n\\r"); may not be completely correct in semantics, but syntaxwise it should work and since the error is before that it should not be a cause to this error. When I am running the application without that line the same error occurs.

So my question is: What is wrong with SET TABLE temp SOURCE ?;? Why cant I use this as a PreparedStatement in Java? As I understand from the documentation the syntax is SET TABLE <tablename> SOURCE <quoted_filename_and_options> where <quoted_filename_and_options> is a string. Cant I prepare that in Java?

回答1:

PreparedStatements are sent to underlying SQL engine for compilation. The location where you are allowed to use parameters depends on the driver and engine. Usually they're only supported in very specific places as otherwise a statement cannot really be compiled.

Consider a PreparedStatement that only contains "?", and you supply a parameter:

 ps.setString(1, "SELECT * FROM myTable");

This can't be compiled, so it gets rejected.

Therefore most SQL database only support parameters in INSERT/UPDATE/SELECTS in positions where a simple value would normally appear. They can't be used for field names, table names, etc.