Base - Execute SQL command

[jcs94782]

I have been following a Base tutorial where it was possible to paste in multiple SQL statements into the Execute SQL Window separated by semicolon. However I always get an error when I try. The tutorial used version 4.x of LO and the previous SQL engine.

CREATE TABLE “Members” (
“ID” INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
“FirstName” VARCHAR(20) NOT NULL ,
“LastName” VARCHAR(20) NOT NULL,
“EmailName” VARCHAR(20),
“EmailDomain” VARCHAR(20),
“DateJoined” DATE );

INSERT INTO “Members” (“FirstName”, “LastName”, “EmailName”, “EmailDomain”)
VALUES (‘test’,‘user2’,‘testuser2’,‘soroban.co.uk’);

Error report

1: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 9, column 1
*INSERT
caused by
‘isc_dsql_prepare’

If I enter these SQL statements one at a time they work.

I am an experienced programmer (55+ years), very experienced with Excel including extensive use of macros, but I am attempting to use a relational database for the first time. This is just to enter my test data so I can work round it but is there a solution?

[mauricio]

In my system work fine.

Version: 6.3.6.2
Build ID: 6.3.6-1
CPU threads: 8; OS: Linux 5.6; UI render: default; VCL: gtk3; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded

[Ratslinger]

@mauricio,

I agree with your results when using HSQLDB embedded. However, and although not explicitly stated (see error noted), this is a problem using Firebird embedded. As mentioned in the question, this works when each statement is run independently. The possible cause may be something in the SBDC connector. Should be reported on Bugzilla

[mauricio]

With Firebird, yes, I get same error, but if execute one by one, work fine.

[jcs94782]

Bug reported - 132693

[jimk]

To me, it’s not surprising that this does not work in Firebird, but it is surprising that it does work in HSQLDB. The dialog says “Execute SQL Statement”, so how does it send more than one transaction? Or maybe HSQLDB combines it all into a single transaction.

[Ratslinger]

Also had no problem using PostgreSQL - tried with JDBC and SDBC connection.

Edit: Final test - MySQL using JDBC did not work.

[jimk]

Firebird does not allow executing a DDL statement followed by corresponding DML statements. That’s because the DDL transaction must be completed first. For more about this, see database - Why temporary table is not allowed in stored procedure in Firebird? - Stack Overflow.

What it does allow is multiple DML statements using EXECUTE BLOCK.

execute block as
begin
    INSERT INTO "Members" ("FirstName", "LastName", "EmailName", "EmailDomain") VALUES ('test2','user2','testuser2','soroban.co.uk');
    INSERT INTO "Members" ("FirstName", "LastName", "EmailName", "EmailDomain") VALUES ('test3','user3','testuser3','soroban.co.uk');
end

Another solution is to create a temporary HSQLDB Base file and run all the commands needed. Then, drag and drop the tables from the HSQLDB Base file into your Firebird Base file.

If you are interested in writing macros, a single routine could create the table and then insert data. However, this may be overkill for entering test data.

[jcs94782]

Thank you - that is the answer!

I was hoping to include the Create SQL statement as well but that is not supported. I can live with that.