CODEWITHSUNDEEP
sqldatabaserelational-databasehsqldb
reprogrammer
reprogrammer

Reputation: 14718

How to enable escaping in the HSQLDB export command?

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?

Upvotes: 1

Views: 730

Answers (1)

Blaine
Blaine

Reputation: 1586

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.

Upvotes: 1

Related Questions