23.9.1 Connection specification

See Connecting to MySQL for information about MySQL specific extensions for the connection-spec passed to connect.

23.9.2 Case of table names 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")

23.9.3 Encoding (character sets in MySQL).

You can specify the encoding to be used by passing the :encoding argument to connect. Common SQL supports various encodings for MySQL as documented in connect.

The default is to use the default for the particular MySQL installation.

23.9.4 SQL mode

Because Common SQL is geared towards ANSI SQL, by default it connects in ANSI mode. If another mode is required, it can be set at connection time.

For example, to make MySQL treat quotes as in ANSI without setting other ANSI features, do:

(sql:connect "me/mypassword/mydb"

             :sql-mode "ANSI_QUOTES")

See the description of the :sql-mode argument to connect for details.

23.9.5 Meaning of the :owner argument to select

In the Common SQL MySQL interface, the value of the select keyword argument :owner is interpreted to select a database name.

23.9.6 Special considerations for iteration functions and macros

This section describes particular issues when fetching multiple records using Common SQL with MySQL databases.

(sql:map-query nil 'print

               "select forname,surname from people"

               :get-all t)

(sql:do-query

 ((forname surname) "select forname,surname from people"

  :get-all t) 

 body
)

(sql:simple-do-query 

 (list "select forname,surname from people"

       :get-all t)

 body
)

(loop for (forname surname) being each record 

      "select forname,surname from people"

      get-all t

      body
)

(sql:map-query nil 'print 

               "select forname,surname from people"

               :get-all t

               :not-inside-transaction t)

23.9.7 Table types

By default, create-table creates tables of the default type. This behavior can be overridden by the connect keyword arguments :default-table-type and :default-table-extra-options, and the :type and :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:

If extra-options is passed to create-table or default-table-extra-options was passed to connect, it is appended in the end of the SQL statement above.

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 innodb.

23.9.8 Rollback errors

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 :support-transactions or "innodb" or "bdb", then the default value for :signal-rollback-errors is t, otherwise the default value is nil.

23.9.9 Types of values returned from queries

Common SQL uses the MySQL mechanism that returns values as strings.

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.

Each of the five date-like types (that is, Date, Datetime, Timestamp, Time and Year) can have result type :date, :date-string or :datetime-string with the following effects:

:date

This result type means a Universal time. This is the default except for Year.

:date-string

A string with the format that MySQL uses for Date columns.

:datetime-string

A string with the format that MySQL uses for Datetime columns.

All the numeric types can have result type :int, :single-float or :double-float, causing the appropriate conversion. No check is made on whether the result is actually useful.

String types can have result type :binary, which returns an array.

23.9.10 Autocommit

Common SQL sets autocommit to 0 when it opens a MySQL connection.