mORMot Open Source

alpinistbg

Member

Registered: 2014-11-12

Posts: 124

SQL reserved words as column names

"01 - In Memory ORM" example fails when modified to work with ZEOS+Firebird2.5.

The exception is:

Project Project01.exe raised exception class EZSQLException with message 'SQL Error:  Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 59 Time. Error Code: -104. Invalid token The SQL: CREATE TABLE SampleRecord (ID BIGINT NOT NULL PRIMARY KEY,Time BIGINT,Name BLOB SUB_TYPE 1 SEGMENT SIZE 2000 CHARACTER SET UTF8,Question VARCHAR(200) CHARACTER SET UTF8); '. Process stopped. Use Step or Run to continue.

AFAIK, the tokens TIME,DATE are reserved words in Firebird along with the DATETIME. Perhaps adding a double quotes around the column names will resolve the problem.

Regards,

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: SQL reserved words as column names

Use MapAutoKeywordsFields option.

alpinistbg

Member

Registered: 2014-11-12

Posts: 124

Re: SQL reserved words as column names

Use MapAutoKeywordsFields option.

The functionality is incorrect, because underscore can't be a start of a SQL-92 identifier. See: http://www.contrib.andrew.cmu.edu/~shad … ql1992.txt, section 5.4, and this applies more to the general case than to the example 01.

MapField is more appropriate for the case, but IMHO the delimited identifiers are the standard way.

See also:
https://docs.oracle.com/database/121/SQ … SQLRF00223, section 6
http://www.postgresql.org/docs/9.3/stat … xical.html, section 4.1.1
http://technet.microsoft.com/en-us/libr … l.80).aspx

Last edited by alpinistbg (2014-11-19 12:11:09)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: SQL reserved words as column names

Are you assuming the easiest standard fix could be to double quote all identifiers when we compute the SQL?
Will it work with all DBs?
I guess we should use "" for Sqlite3, Oracle and PostgreSQL, [] for MSSQL/Access, `` for MySQL.

But for FireBird, I guess the "" are very misleading, since they force the case sensitivity...

Before the SQL-92 standard, it was not legal to have object names (identifiers) in a database that duplicated keywords in the language, were case-sensitive or contained spaces. SQL-92 introduced a single new standard to make any of them legal, provided that the identifiers were defined within pairs of double-quote symbols (ASCII 34) and were always referred to using double-quote delimiters.

The purpose of this “gift” was to make it easier to migrate metadata from non-standard RDBMSes to standards-compliant ones. The down-side is that, if you choose to define an identifier in double quotes, its case-sensitivity and the enforced double-quoting will remain mandatory.

Firebird does permit a slight relaxation under a very limited set of conditions. If the identifier which was defined in double-quotes:

was defined as all upper-case,
is not a keyword, and
does not contain any spaces,
...then it can be used in SQL unquoted and case-insensitively. (But as soon as you put double-quotes around it, you must match the case again!)

Warning
Don't get too smart with this! For instance, if you have tables "TESTTABLE" and "TestTable", both defined within double-quotes, and you issue the command:

SQL>select * from TestTable;
...you will get the records from "TESTTABLE", not "TestTable"!

Unless you have a compelling reason to define quoted identifiers, it is usually recommended that you avoid them. Firebird happily accepts a mix of quoted and unquoted identifiers – so there is no problem including that keyword which you inherited from a legacy database, if you need to.

Warning
Some database admin tools enforce double-quoting of all identifiers by default. Try to choose a tool which makes double-quoting optional.

See http://www.firebirdsql.org/file/documen … bases.html

So perhaps putting the underscore at the end of the name would be preferred?

alpinistbg

Member

Registered: 2014-11-12

Posts: 124

Re: SQL reserved words as column names

Will it work with all DBs?
I guess we should use "" for Sqlite3, Oracle and PostgreSQL, [] for MSSQL/Access, `` for MySQL.

All of them support double quotes. Not sure only for MS Access, but honestly, I don't wanna know

What's wrong with the case sensitivity?

So perhaps putting the underscore at the end of the name would be preferred?

Sounds reasonable.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: SQL reserved words as column names

OK.
AutoMapKeywordFields parameter will now rename the conflictual identifier names as ####_ instead of _####
See http://synopse.info/fossil/info/8a60d551e7

Thanks for the feedback and links!