HSQLDB意外令牌:? [英] HSQLDB unexpected token:?

问题描述

我有一个使用HSQLDB的JAVAFX项目.尝试设置表的源时，我想我理解了一个异常，但是由于无法修复它，所以我想我不理解它. 我的SQL是:

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语句在这里对我不起作用，现在这应该不是问题.我将上面的sql拆分为Strings的ArrayList，每一行都是一个元素.所以我得到了以下Java代码:

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);
  }
}

我的应用程序在第PreparedStatement ps = c.prepareStatement(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

此输出之前为:

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

我知道ps.setString(1, path.toUri().toString() + ";encoding=UTF-8;ignore_first=false;fs=\\n\\r");的语义可能不是完全正确的，但是在语法上它应该起作用，并且由于错误早于此，因此它不应成为此错误的原因.当我在没有该行的情况下运行应用程序时，会发生相同的错误.

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.

所以我的问题是:SET TABLE temp SOURCE ?;怎么了?为什么我不能将其用作Java中的PreparedStatement?据我从文档了解，语法为SET TABLE <tablename> SOURCE <quoted_filename_and_options>，其中<quoted_filename_and_options>是一个字符串.难道我用Java准备了吗?

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?

推荐答案

PreparedStatement发送到基础SQL引擎进行编译.允许使用参数的位置取决于驱动程序和引擎.通常，仅在非常特定的位置支持它们，否则将无法真正编译语句.

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.

考虑一个仅包含?"的PreparedStatement，然后提供一个参数:

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.

因此，大多数SQL数据库仅在通常会出现简单值的位置中支持INSERT/UPDATE/SELECTS中的参数.它们不能用于字段名称，表名称等.

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.

这篇关于HSQLDB意外令牌:?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！