Page 1 of 1

[Solved] Base won't execute valid SQL command

Posted: Thu Sep 21, 2017 4:27 pm
by 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

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

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

Re: Base won't execute valid SQL command

Posted: Thu Sep 21, 2017 6:07 pm
by 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

Re: Base won't execute valid SQL command

Posted: Thu Sep 21, 2017 6:10 pm
by tkwatcher
Okay, so why does it not run in my OpenOffice?

Re: Base won't execute valid SQL command

Posted: Thu Sep 21, 2017 11:54 pm
by Villeroy
Is your database document connected to HSQL or something different?

Re: Base won't execute valid SQL command

Posted: Fri Sep 22, 2017 6:50 am
by 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.

Re: Base won't execute valid SQL command

Posted: Fri Sep 22, 2017 11:38 am
by 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 viewforum.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.