This section describes particular issues in Common SQL with MySQL databases.
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.
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).
queryrather than using select:
See the description of the
:sql-mode argument to connect for details.
In the Common SQL MySQL interface, the value of the select keyword argument
:owner is interpreted to select a database name.
The function map-query and the macros do-query, simple-do-query and loop with
each record use internally
mysql-use-query, which means that the underlying MySQL code brings the data from the server one record at a time. With a small number of records, it may be preferable to bring all the data immediately instead. This can be done by passing the argument get-all, as follows:
In the MySQL interface there is no way to abort a query when part way through it. When any of the iterations above stops before reaching its end, the underlying code retrieves all the records to the end of the query (though without converting them to Lisp objects). If the query found many records, that may be an expensive (that is, time consuming) operation.
It is possible to avoid this inefficiency by passing the argument not-inside-transaction. If not-inside-transaction is true then when a query is aborted, then LispWorks closes the database connection and reopens it, rather than retrieving all the remaining records.
By default, create-table creates tables of the default type. This behavior can be overridden by the connect keyword arguments
:default-table-extra-options, and the
:extra-options keyword arguments to create-table.
If type is passed to create-table or default-table-type was passed to
connect, it is used as the argument to the "keyword"
TYPE in the SQL statement:
create table MyTable (column-specs) TYPE = type-value
connect with default-table-type and create-table with type also accept the keyword argument
:support-transactions. When support-transactions is true, these functions will attempt to make tables that support transactions. It does this by using the type
The default value of the connect keyword argument
:signal-rollback-errors is determined by the value of the
:default-table-type argument. If default-table-type is
"bdb", then the default value for
t, otherwise the default value is
By default, Common SQL converts these strings to the appropriate Lisp type corresponding to the column type (or more accurately, the type of the field in the query) according to MySQL type mapping
However, if you specify the result type as
:string, this eliminates the conversion and the return value is simply the string retrieved by MySQL. For information about specifying the result type for a column (or multiple columns) in a query, see Querying.
This result type means a Universal time. This is the default except for Year.
LispWorks User Guide and Reference Manual - 20 Sep 2017