1

When I run the following script in HSQLDB 2.2.5 using the command java -jar sqltool.jar --rcFile sqltool.rc db_file q.sql, I got the following error message:

Error message:

SEVERE  Error at 'q3.sql' line 16:
"\xq SELECT "T1"."C1", "T1"."C2" FROM "PUBLIC"."TABLE1" "T1""
Export syntax:  "\x table_or_view_name "  OR  "\x SELECT statement".
(Do not end with ';', since the \x command itself is not SQL).
SEVERE  Rolling back SQL transaction.
org.hsqldb.cmdline.SqlTool$SqlToolException

HSQLDB script:

--q.sql
DROP TABLE "PUBLIC"."TABLE1" IF EXISTS;

CREATE TABLE "PUBLIC"."TABLE1" (
  "C1" VARCHAR(10),
  "C2" VARCHAR(10),
  "C3" VARCHAR(10)
);

INSERT INTO "PUBLIC"."TABLE1" ("C1", "C2", "C3") VALUES (',', 'b', 'c');
INSERT INTO "PUBLIC"."TABLE1" ("C1", "C2", "C3") VALUES ('d', 'e', 'f');

* *DSV_COL_DELIM=,
* *DSV_ROW_DELIM=\n
* *DSV_TARGET_FILE=results.csv

\xq SELECT "T1"."C1", "T1"."C2" FROM "PUBLIC"."TABLE1" "T1"

Note that the above HSQLDB script runs successfully if I use \x instead of \xq. But, the \x command does not escape the "," character from the data. However, according to the HSQLDB manual, the \xq should escape delimiters in input.

How can I export my HSQLDB table to a CSV file that escapes delimiters whenever needed?

reprogrammer
  • 14,298
  • 16
  • 57
  • 93

1 Answers1

1

As the error message says, it is expecting "\x something", not "\xq something".

You should use the Utilities Guide from the distribution you are using. The docs on the web site are for 2.2.6. I apologize that the online docs are too far ahead, since 2.2.6 has not been released publicly.

2.2.5 does not support \xq.

Blaine
  • 1,577
  • 2
  • 17
  • 15
  • I got the pre-release snapshot of HSQLDB 2.2.6 from hsqldb.org/support/hsqldb_16.zip and it successfully ran my script. Thanks! – reprogrammer Nov 17 '11 at 16:59