19.9.2 Case of table names and and database names

MySQL is case sensitive on table names and database names when the server is on a Unix machine. MySQL does not automatically change raw names to uppercase as specified by the SQL standard. However, Common SQL is geared towards uppercasing all names, so this may cause some mismatches. In general, Common SQL uppercases strings, and uses symbol names, which are normally uppercase, as-is.

One solution, possible only if you control the naming of tables and databases, is to make them all have the same case. If this is uppercase, that suffices. If it is lowercase, you need to set the variable lower_case_table_names in the configuration of the server.

If you cannot make all the names the same case, you have to get the case right. This can be achieved in several ways:

1. Specify tables names using strings, for example:

(sql:select [*] :from ["TableNAMEwithVARIABLEcase"])

Note that this does not work in LispWorks 4.4 and previous versions.

2. Pass the Lisp string directly:

(sql:select [*] :from "TableNAMEwithVARIABLEcase")

Note that in this case the table name is passed to the database inside double quotes. That works only when the mode of the Common SQL connection contains ANSI_QUOTES (which is the default, see SQL mode for details).

3. Specify table names as escaped symbols:

(sql:select [*] :from [|TableNAMEwithVARIABLEcase|])

4. Construct the whole query string and pass it to query rather than using select:

(sql:query "select * from TableNAMEwithVARIABLEcase")

LispWorks User Guide and Reference Manual - 21 Dec 2011