[Solved] Base won't execute valid SQL command

Discuss the database features

[Solved] Base won't execute valid SQL command

Postby tkwatcher » Thu Sep 21, 2017 4:27 pm

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
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
tkwatcher
 
Posts: 3
Joined: Thu Sep 21, 2017 4:15 pm

Re: Base won't execute valid SQL command

Postby Villeroy » Thu Sep 21, 2017 6:07 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24592
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base won't execute valid SQL command

Postby tkwatcher » Thu Sep 21, 2017 6:10 pm

Okay, so why does it not run in my OpenOffice?
TKWatcher
Windows 7
Open Office 4.1.3
tkwatcher
 
Posts: 3
Joined: Thu Sep 21, 2017 4:15 pm

Re: Base won't execute valid SQL command

Postby Villeroy » Thu Sep 21, 2017 11:54 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24592
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base won't execute valid SQL command

Postby tkwatcher » Fri Sep 22, 2017 6:50 am

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
tkwatcher
 
Posts: 3
Joined: Thu Sep 21, 2017 4:15 pm

Re: Base won't execute valid SQL command

Postby Villeroy » Fri Sep 22, 2017 11:38 am

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24592
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 5 guests