We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
1

Base - Execute SQL command

asked 2020-05-04 13:49:27 +0200

jcs94782 gravatar image

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?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2020-05-04 22:43:20 +0200

Jim K gravatar image

updated 2020-05-04 22:46:09 +0200

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 https://stackoverflow.com/questions/3....

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.

edit flag offensive delete link more

Comments

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.

jcs94782 gravatar imagejcs94782 ( 2020-05-05 00:00:39 +0200 )edit
0

answered 2020-05-04 19:41:44 +0200

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

image description

image description

edit flag offensive delete link more

Comments

@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

Ratslinger gravatar imageRatslinger ( 2020-05-04 20:39:53 +0200 )edit

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

mauricio gravatar imagemauricio ( 2020-05-04 20:48:48 +0200 )edit

Bug reported - 132693

jcs94782 gravatar imagejcs94782 ( 2020-05-04 21:43:36 +0200 )edit

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.

Jim K gravatar imageJim K ( 2020-05-04 23:05:57 +0200 )edit

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

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

Ratslinger gravatar imageRatslinger ( 2020-05-04 23:57:53 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-05-04 13:49:27 +0200

Seen: 648 times

Last updated: May 04 '20