UCanAccess supports backticks (as well as MS Access) mainly for compatibility with metadata based tools (e.g. Libre Office, Open Office, your SQL Workbench/J and so on), but it isn't the suggested way to escape special characters in column and table names.
In fact the suggested way is the use of square bracket:
select [1° strange column name] from [column name].
Our aim has always been to support Access SQL, in order to make possible the reuse of both old code and code generated by Access(e.g. embedded queries in your accdb and mdb). For example we support the pivot queries, the calculated fields and other advanced Access features that aren't obviously supported by hsqldb.
Double quotes are totally equivalent to sigle quotes in Access syntax, so their meaning is different.
In other words both UCanAccess and Access support:
select * from company where name='my company'
but also
select * from company where name="my company"
and
select * from company where owner='D''antonio'
but also (that's totally equivalent)
select * from company where owner="D'antonio"
So double quotes can't work as escaping character for special column/table names.
Last edit: Marco Amadei 2015-09-19
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
SQL Workbench uses whatever the driver returns from DatabaseMetaData.getIdentifierQuoteString() - that's where I noticed that UCanAccess returns the backtick for the identifier quote.
But if Access compatibility is the priority (and not compatibility with the SQL standard) then this makes sense I guess.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
An identifier with special characters is quoted using double quotes in SQL.
Why is UCanAccess using a "backtick" - especially when the underlying HSQL complies with the standard and does use double quotes as well.
Is there a way to allow standard compliant SQL using double quotes?
UCanAccess supports backticks (as well as MS Access) mainly for compatibility with metadata based tools (e.g. Libre Office, Open Office, your SQL Workbench/J and so on), but it isn't the suggested way to escape special characters in column and table names.
In fact the suggested way is the use of square bracket:
select [1° strange column name] from [column name].
Our aim has always been to support Access SQL, in order to make possible the reuse of both old code and code generated by Access(e.g. embedded queries in your accdb and mdb). For example we support the pivot queries, the calculated fields and other advanced Access features that aren't obviously supported by hsqldb.
Double quotes are totally equivalent to sigle quotes in Access syntax, so their meaning is different.
In other words both UCanAccess and Access support:
select * from company where name='my company'
but also
select * from company where name="my company"
and
select * from company where owner='D''antonio'
but also (that's totally equivalent)
select * from company where owner="D'antonio"
So double quotes can't work as escaping character for special column/table names.
Last edit: Marco Amadei 2015-09-19
SQL Workbench uses whatever the driver returns from DatabaseMetaData.getIdentifierQuoteString() - that's where I noticed that UCanAccess returns the backtick for the identifier quote.
But if Access compatibility is the priority (and not compatibility with the SQL standard) then this makes sense I guess.