[Solved] Base won't execute valid SQL command

Discuss the database features
Post Reply
tkwatcher
Posts: 3
Joined: Thu Sep 21, 2017 4:15 pm

[Solved] Base won't execute valid SQL command

Post 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
Last edited by tkwatcher on Fri Sep 22, 2017 6:53 am, edited 1 time in total.
TKWatcher
Windows 7
Open Office 4.1.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base won't execute valid SQL command

Post 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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
tkwatcher
Posts: 3
Joined: Thu Sep 21, 2017 4:15 pm

Re: Base won't execute valid SQL command

Post by tkwatcher »

Okay, so why does it not run in my OpenOffice?
TKWatcher
Windows 7
Open Office 4.1.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base won't execute valid SQL command

Post by Villeroy »

Is your database document connected to HSQL or something different?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
tkwatcher
Posts: 3
Joined: Thu Sep 21, 2017 4:15 pm

Re: Base won't execute valid SQL command

Post 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.
TKWatcher
Windows 7
Open Office 4.1.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base won't execute valid SQL command

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply