select columns with escaped characters

[Robert Robertino]

Hello. I had a question but I found out the answer in the meantime.
"SELECT \"CUSTOMER ID\" FROM Orders"
+ " ORDER BY " + "1 " + " limit ? offset ? "

This query simply returns a list of strings "CUSTOMER ID" instead of the column named "CUSTOMER ID".
[{C1=CUSTOMER ID}, {C1=CUSTOMER ID},...]

The correct way to query in such a way is:
"SELECT `CUSTOMER ID` FROM Orders"
Where the escaping character is `, the reverse quote.

Also, if anyone is wondering about column aliases:
Column aliases simply change the .getColumnLabel() result, but not the .getColumnName() like in most other databases.

[Marco Amadei]

You should use, as documented in the UCanAccess web site, the square brackets around the column/table name to escape, as well as in the MS Access SQL.
So your query should be:
select [CUSTOMER ID] from ...
You should use square bracket also for aliases when the escaping is needed.
UCanAccess converts the Access SQL into hsqldb SQL.
In the MS Access SQL you can use both ' and " as characters string delimeters and that explains your original results:

SELECT "CUSTOMER ID" FROM Orders
is equivalent to
SELECT 'CUSTOMER ID' FROM Orders
in both MS Access and UCanAccess, so you're selecting the constant string CUSTOMER ID.

Also, as you said, you can use the backtick character (`) which is supported by MS Access too. I implemented it mainly for compatibilty with tools like Libre Office and Open Office (which build dynamically some queries using DatabaseMetaData, getIdentifierQuoteString method).

[Robert Robertino]

I see. However even with ` and with [] I still cannot get the driver to return lowercase aliases. Even with ignorecase=false in the url.

"jdbc:ucanaccess://d:/accessDB.accdb;ignorecase=false", "Admin", ""

"SELECT id [ceVa] from Customers"

[Marco Amadei]

Ignorecase property is for the case sensitivity of text comparison, so in the comparison between data and it's unrelated to metadata (remove it!).

See my answer here, http://stackoverflow.com/questions/22962667/reading-mdb-files-with-ucanaccess-returns-columnames-in-all-uppercase:

UCanAccess is based on hsqldb, which returns table and column names in upper-case when you call DatadaseMetaData or ResultSetMetaData methods. That's because these names are internally registered, in hsqldb, as all uppercase. Instead, with an SQL statement , you can refer to a table or a column using a case insensitive identifier. Also in ResultSet.getXXX(<columnLabel>) methods, the parameter columnLabel is evaluated as case insensitive.

The same thing is true for aliases(there is a way to use the exact case in hsqldb but the column/table/aliases naming in your sql would become case sensitive).
I will implement a naming remap in the 3.0.0 :case insesitivity in SQL and get methods of ResultSet, right case in metadata provided by DatabaseMetaData and ResultSetMetaData.
The 3.0.0 won't be the next release, likely I release it in October.