CODEWITHSUNDEEP
sqldatabasehsqldb
reprogrammer
reprogrammer

Reputation: 14718

How can I use a multi-line SELECT statement in HSQLDB to export data?

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 get the error message: "user lacks privilege or object not found: SELECT". If I make the \x command a single-line statement, HSQLDB will execute the script successfully. I'd like to break my \x into multiple lines for better readability. How can I use a multi-line \x command in an 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 ('a', 'b', 'c');
INSERT INTO "PUBLIC"."TABLE1" ("C1", "C2", "C3") VALUES ('d', 'e', 'f');

DROP TABLE "PUBLIC"."TABLE2" IF EXISTS;

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

INSERT INTO "PUBLIC"."TABLE2" ("C1", "C2") VALUES ('a', 'b');
INSERT INTO "PUBLIC"."TABLE2" ("C1", "C2") VALUES ('u', 'v');

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

\x SELECT
( SELECT TOP 1 "T2"."C1" FROM "PUBLIC"."TABLE2" "T2" WHERE "T2"."C1" = "T1"."C1") AS "T2C1",
"T1"."C2" AS "T1C2"
FROM "PUBLIC"."TABLE1" "T1"
ORDER BY "T2C1", "T1C2"

UPDATE: Per @Blaine's suggestion, I switched to HSQLDB 2.2.6 and replaced the \x command in the above script with the following:

\.

SELECT
(SELECT TOP 1 "T2"."C1" FROM "PUBLIC"."TABLE2" "T2"
 WHERE "T2"."C1" = "T1"."C1") AS "T2C1",
"T1"."C2" AS "T1C2"
FROM "PUBLIC"."TABLE1" "T1"
ORDER BY "T2C1", "T1C2"

.;

\x :

Upvotes: 2

Views: 2177

Answers (1)

Blaine
Blaine

Reputation: 1586

This requires version 2.2.6.

Once you have 2.2.6, \x? will show you everything you need to know.

Since you want this to work from a script, you would use raw mode to enter your multi-line SQL statement into SqlTool's command buffer, then use \x :. Mnemonic: ":" indicates SqlTool's edit buffer.

Upvotes: 2

Related Questions