Embedded HSQLDB SQL vs Firbird - syntax document?

[FKlusmann]

INSERT into table fails in Firbird. Where may I see the correct syntax documentation?
LibreOffice 7.1.0.3(x64) on Windows 10.0 Build 18363
This works in HSQLDB, fails in Firebird.

CREATE TABLE "T_Honors"("Honorific_PK" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Title" VARCHAR(10),"Addressing" VARCHAR(20),"Note" VARCHAR(75))

INSERT INTO "T_Honors" VALUES(0,NULL,NULL,'unknown or not defined')

INSERT INTO "T_Honors" VALUES(1,'Master',NULL,'boys, young men, professional title (master of a college, master of a ship')

INSERT INTO "T_Honors" VALUES(2,'Mr.',NULL,'men without a profession')

[Edited formatting for readability robleyd]

[FKlusmann]

The Status given is:
1: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 181
*INSERT
caused by
‘isc_dsql_prepare’

NOTE: Each “INSERT …” is on its own line, not run together as seen here.

[Ratslinger]

Hello,

You can run multiple Insert's but you need different syntax. This post has the example and links to the Firebird documentation → BASE firebird - Error inserting multiple rows into a column

Edit:

Should also note that you can run each of your Insert statements individually without error.

Edit 2021-02-14:

Using the information provided in the above link, this SQL:

INSERT INTO "T_Honors" ("Honorific_PK", "Title", "Addressing", "Note")
SELECT 0, NULL, NULL, 'unknown or not defined' FROM RDB$DATABASE UNION ALL
SELECT 1, 'Master', NULL, 'boys, young men, professional title master of a college, master of a ship' FROM RDB$DATABASE UNION ALL
SELECT 2, 'Mr.', NULL, 'men without a profession' FROM RDB$DATABASE;

Run from Base menu Tools->SQL creates the required records in the Firebird table:

This statement can also be run from the query section with Run SQL directly turned on. An error is generated but the statements seem to execute properly. Could save some time.

[FKlusmann]

Thank you, @Ratslinger!
Your answer points to good information that does work.
I note that the answer to the prior question expects a copy FROM RDB$DATABASE, not a direct insert.
One would expect that what is found in LoBase documents to match, or at least, show the difference between Firebird and HSQLDB.
(1) Output from HSQLDB’s script ‘filespect’ cannot be used with Firebird without major modification.
(2) script ‘filespect’ does not work for me in Firebird.
(3) The statement “The INSERT INTO SELECT statement copies data from one table and inserts it into another table.” - from w3schools - leads me to believe that Firebird is not following the ISO.

[FKlusmann]

How does one keep the format of the text without everything being run together?

[Ratslinger]

I see no need for a workaround. You can create and insert using SQL in Firebird. This was demonstrated in the link.

LibreOffice is not the author of HSQLDB or Firebird or any other database to my knowledge.

There should be no need to have this information in the LO documentation. The database you choose has its’ own documentation.

There is no HSQLDB's <tools><sql>. HSQLDB is a database. tools->sql is a menu item in Base as a pass through to whatever database you are using. Works with Firebird - embedded and server.

Do not know the basis for your statement of:

“The INSERT INTO SELECT statement copies data from one table and inserts it into another table.” - from w3schools - leads me to believe that Firebird is not following the ISO.

See → INSERT

Edit:

For:

How does one keep the format of the text without everything being run together?

That is an open enhancement → tdf#86315

[FKlusmann]

Thank you, Ratslinger. From the viewpoint of one who is still learning, and trying to follow the suggestions provided here it looks the same. I have yet to find a clear breakdown of where LibreOffice stops and Firebird or HSQLDB begins. I guess that I don’t know enough yet to intelligently search for answers. As for the quote, see w3schools (DOT) com/SQL/sql_insert_into_select.asp and their "to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner. (their …sql/sql_intro.asp).

[Ratslinger]

It is not the quote but rather what makes you state that Firebird does not do those things? I see no evidence that it is not accommodated for. Please provide example of what you mean.

As for where Base stops and ‘databases’ (this is not just HSQLDB and Firebird embedded) begin, Base is a front end to databases. If a database is reported to have a capability and it cannot be done through Base, an enhancement or bug report needs to be submitted.

Although there is some information in Base documentation on multiple databases, it should not be relied upon as to all the possibilities of that particular database nor should LO be required to show all possibilities or differences.

[FKlusmann]

Thank you again.
[Entered as a new line] My question about formatting relates to posting here. Everything runs together.
[Another new line] Thank you.

[Ratslinger]

Add blank line between

newlines as I did here.

Also there is the toolbar (upper left) to add further editing capabilities.

[FKlusmann]

Thank

you

again!

[FKlusmann]

Workarounds:

(1) Create the new DB tables in HSQLDB, close and open again, then accept the Firebird conversion offer.

(2) Copy the tables from an existing HSQLDB and paste them into the Firebird DB.

[Ratslinger]

Do not see any need for this workaround. New tables and entering data for Firebird embedded database can be done through main Base menu Tools->SQL.

Ran your SQL in the question one line at a time and all worked - table creation and added data.

[FKlusmann]

True, Ratslinger. This is not fun when doing it for a table like States (72 records as it has US + Canada + territories) or Birds ( more than 1500 records). Would you like to do these one line at a time?

[Ratslinger]

Method given in answer for multiple - one at a time is not necessary with Firebird. Only stating that using the SQL in question does not need a workaround.

And why would you use SQL to write entry for that many records when direct entry would be quicker? And if it coming from elsewhere there must be other provisions. Statement is not logical.

[FKlusmann]

For me, it is logical to use HSQLDB’s SCRIPT to a file, fix any spelling errors, add or delete a record, and then copy and paste into Base menu Tools → SQL to create and populate a table in a new embedded database.