[Solved] Inconsistent Behaviour in SQL Syntax

Creating tables and queries

[Solved] Inconsistent Behaviour in SQL Syntax

Postby peterwt » Sat Apr 16, 2016 1:01 pm

Create a Query in design view which has the SQL -
Code: Select all   Expand viewCollapse view
SELECT * FROM "TestTable1" WHERE "MY_ID" = 0

This works in Tools >SQL and in a macro where this is the query statement. I was under the impression that Table and Field names need to be quoted (“TestTable1”) in the version of SQL used in Base (HSQLDB). I have found that this also works -
Code: Select all   Expand viewCollapse view
SELECT * FROM TestTable1 WHERE  MY_ID  = 0

without any quotes which should not.

However an update using this SQL -
Code: Select all   Expand viewCollapse view
UPDATE "TestTable1" SET "Cust" = '0' WHERE "MY_ID" = 0

only works if the quotes are used. If they are left out the error is TestTable1 not found.

Looking at the information on the HSQLDB website on SQL it states the Table and Field names need to be quoted unless they are in block capitals. If the table used in the above update query is replaced by a table named TEST then the quotes can be left out.

So why does a Select work without quotes?
Last edited by peterwt on Mon May 16, 2016 12:48 pm, edited 1 time in total.
Peter
LibreOffice 4.4.1.2 on Windows 8
peterwt
 
Posts: 35
Joined: Mon Apr 19, 2010 4:04 pm
Location: South Wales

Re: Inconsistent Behaviour in SQL Syntax

Postby Villeroy » Sat Apr 16, 2016 1:21 pm

So why does a Select work without quotes?

Because TestTable is named TESTTABLE ? With an upper case name you can leave out the quotes and use upper/lower/mixed case. Exception: When your name conflicts with an SQL keyword you need to quote anyway.

SET X = '0' puts the character '0' in char field "X".
SET Y = 0 puts the number 0 in numeric field "Y".
 Edit: you can put a number into a char field and numeric strings into numeric fields as long as the values are convertible without ambiguity. Decimal char is always the point. Literal dates and times can be written as ISO strings. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28536
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Inconsistent Behaviour in SQL Syntax

Postby MTP » Tue Apr 19, 2016 10:05 pm

The Base parser will sometimes detect when quotes are needed and add them silently before sending the SQL statement to HSQLDB. If the command is going through the parser, then, often the quotes can be omitted. If the command is being issued in a way that bypasses the parser then the quotes are required.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests