[Solved] Base won't execute valid SQL command

[tkwatcher]

I have just started studying SQL, and an excersize requires me to use Base to execute the following SQL command (executed through Tools-->SQL...):

Code: Select all   Expand viewCollapse view

CREATE TABLE "tbl_customers" ("customer_id" INTEGER IDENTITY, "first_name" VARCHAR(50), "last_name" VARCHAR(50), "phone" VARCHAR(25), "email" VARCHAR(30), PRIMARY KEY ("customer_id"));


This code has been verified by three different sources as a valid and workable command and has been tested in Open Office 4.1.3, which is the same version I have installed on my Windows 7 computer.

Whatever I do, the following error message is shown:

Code: Select all   Expand viewCollapse view

1: syntax error, unexpected NAME, expecting ')' or ','


Why is this command not working (and only on my computer apparently). At this point I doubt that the command is wrong, I believe there is something else. Maybe my installation, maybe something I don't know. I have uninstalled and reinstalled Open Office too, but it didn't change anything.

20170921154801_screenshot.png

[Villeroy]

I copied your SQL and tested it successfully with HSQL1.8 (embedded HSQL) and with HSQL2.4 both engines connected to both LibreOffice 5.4 and OpenOffice 4.1

[tkwatcher]

Okay, so why does it not run in my OpenOffice?

[Villeroy]

Is your database document connected to HSQL or something different?

[tkwatcher]

I have just started to learn about SQL and databases. I don't think it is connected to HSQL unless that's the default setting for Open Office.
Anyways, I posted this problem on stackoverflow too, and the suggestion that helped was for me to install LibreOffice. That ran the command fine. So I am going to use that.

[Villeroy]

Base is not a database. A Base document is always connected to some kind of actual database. The default database for both OpenOffice and LibreOffice is HSQL in the same old version 1.8. It is documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html. Therefore all CREATE statements are exactly the same in both office suites. Whatever you execute from Tools>SQL is executed by the underlying database engine. For any database connection the status bar of the Base window indicates the type and location of the connected database. For databases that are not embedded you can manipulate the connection via menu:Edit>Database>Connection...

Unlike other types of databases this type is integrated (embedded) in the Base file itself. This is a good solution for learning SQL and for exchanging demo databases on the internet. However, it is a bad solution because HSQL1.8 is somewhat limited in features and because embedded databases put all your data at risk when something bad happens with any component of the office suite while the database is extracted/rewrapped from/to the Base file.
The community of this forum provides tools to convert embedded HSQLDB into a stand-alone HSQLDB which can be used with a more recent database driver and which is more safe. See https://forum.openoffice.org/en/forum/v ... m.php?f=83

Well, I would like to know why your database engine could not run that CREATE statement with OpenOffice. I have no explanation for that.