[Solved] Inconsistent Behaviour in SQL Syntax

[peterwt]

Create a Query in design view which has the SQL -

Code: Select all

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

SELECT * FROM TestTable1 WHERE  MY_ID  = 0

without any quotes which should not.

However an update using this SQL -

Code: Select all

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?

[Villeroy]

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.

[MTP]

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.