ToC | DocOverview | CGDoc | RelNotes | FAQ | Index | PermutedIndex
| Allegro CL version 10.1 Unrevised from 10.0 to 10.1. 10.0 version

# Allegro MySQL Direct Connect Library

This document contains the following sections:

1.0 Allegro MySQL introduction
2.0 MariaDB databases
3.0 The Basic Operation of Allegro MySQL
3.1 MySQL Errors
4.0 MySQL Examples
5.0 Conversions
6.0 MySQL and stored procedures
7.0 MySQL and multiprocessing
8.0 MySQL programming interface

## 1.0 Allegro MySQL introduction

MySQL is a powerful, efficient, production ready open-source database that runs on all popular platforms. There are language bindings for most popular languages. This document describes the bindings for the Allegro Common Lisp language.

MySQL was written by the founders of the MySQL AB company. It supports and enhances MySQL and owns the code. They release an open source version with restrictions and will license you to use MySQL for commercial purposes. See http://www.mysql.com.

There exist ODBC drivers for MySQL. ODBC offers a method of programmatically accessing a database that's somewhat portable across very different databases.

Allegro CL users can access MySQL through the Allegro ODBC library. With this being the case one might ask why would anyone want to use the library we describe here? We'll describe the pros and cons of ODBC first:

### Using ODBC:

1. ODBC offers a portable framework for accessing varying databases. Once connected to a database you direct the database via SQL commands in the database's native format. Thus if you want portability between database vendors you still have to be careful not to use SQL commands that are not portable.
2. ODBC imposes its own model of what a database is and this may not be a good match to a particular database. Thus you may lose efficiency when interacting with certain databases.
3. Since ODBC was started by Microsoft, ODBC is available for free on Windows and well supported by database vendors. On Unix, ODBC drivers are generally not database vendor written, can be expensive (although one could use ODBC with MySQL using totally free libraries.)
4. Using ODBC requires using ODBC driver manager libraries which are usually written in C. When a command is sent to a database the C library sits and waits for a response to be returned. While it is waiting Lisp is blocked from running.

### Using Allegro MySQL Direct Connect:

1. This library connects directly to the MySQL server and speaks the same protocol used by all clients in all languages that work with the MySQL server. Therefore there is no more efficient way to programmatically access a MySQL database.
2. When data is returned by the MySQL server Lisp has access to the network buffers in which the data arrives. Lisp can convert the data into the desired final form immediately without allocating space for intermediate forms of the data needed in a multi-level MySQL interface.
3. Each database has important and unique features and MySQL is no exception. MySQL has the notion of an autoincrement column that's ideal for automatically creating a unique number to represent a row in a table. Inserting a new row and retrieving the unique id assigned is a complex process using ODBC. With this library however the response to the insert command includes the unique id value, thus it's trivial to use this feature.
4. Because normal Lisp socket I/O is used to communicate with the MySQL server, only one Lisp thread will block while awaiting a response from the server.
5. The downside of using this library is that the code will only work with MySQL. However, the code looks very similar to Allegro ODBC so if there were a need to port to Allegro ODBC it wouldn't be too difficult (depending on how many unique features of MySQL (like autoincrement columns) were used.

Symbols implementing the Allegro MySQL facility are in the `dbi.mysql` package. The Allegro MySQL module is the `:mysql` module and can be loaded by evaluating `(require :mysql)`.

## 2.0 MariaDB databases

You can use this module with [MariaDB](https://mariadb.com/) databases. Just specify them wherever MySQL databases are called for.

## 3.0 The Basic Operation of Allegro MySQL

To access a MySQL database you call connect to connect to the database. Then you repeatedly call sql to send commands to the database and get results back. Finally you call disconnect to disconnect from the database.

You can call connect more than once to establish multiple simultaneous connections to the database. This is useful, for example, if you want to create a pool of ready-to-use database connections for an AllegroServe web server application.

connect has an external-format keyword argument which allows you to specify the external format of the connection.

When data is returned it is converted to the appropriate format. See Section 5.0 Conversions below for details.

A single MySQL connection can only handle one query at a time (where is a query begins when an sql command is executed and ends when the last result row is returned). It is oaky to create more than one connection to a mysql database and run a query in each connection simultaneously. In this case you must be careful to pass the connection you want to use as the value of the :db keyword argument to the mysql functions.

## 3.1 MySQL Errors

MySQL errors have condition `mysql-error` or, when appropriate, the `mysql-protocol-error`, a subclass of `mysql-error`.

## 4.0 MySQL Examples

Examples of connecting to the database:

    ; if the server is on the same machine and is setup to
    ; accept anonymous connections then this is
    ; the simplest way to connect:

    cl-user(23): (use-package :dbi.mysql)
    t
    cl-user(24): (connect)
    #<mysql connected to localhost/3306 @ #x7199102a>
    cl-user(25):

    ; to connect and set the current database:
    cl-user(25): (connect :database "test")
    #<mysql connected to localhost/3306 @ #x7199454a>
    cl-user(26):

    ; to connect to MySQL running on machine tiger as user "jkf"
    ; with password "mypass" and to set the active database to "test"
    cl-user(26): (connect :host "tiger" :user "jkf"
                          :password "mypass" :database "test")
    #<mysql connected to tiger.franz.com/3306 @ #x71997bf2>
    cl-user(27):

    ; to connect via the unix-domain socket
    cl-user(27): (connect :file "/var/lib/mysql/mysql.sock")
    #<mysql connected to "/var/lib/mysql/mysql.sock" @ #x7199e102>
    cl-user(28):

### MySQL interface examples

Here we see how to connect to the MySQL server, tell it we'll be using the database "test", and then create a table in that database and populate it. Then we retrieve all rows from the table.

Note that we count on the fact that the connect function not only returns the connection to the database but also sets the `*mysql*` variable to the value of that connection. The value of `*mysql*` is used as the default value for the db keyword argument, thus this last connection is used for subsequent sql and disconnect functions. This makes it easy to work interactively with the database

    cl-user(13): (connect :user "jkf" :password "jkf" :database "test")
    #<mysql connected to localhost/3306 @ #x71f4d082>
    cl-user(14): (sql "create table sample(i int, j int)")
    nil
    cl-user(17): (sql "insert into sample values(1,2)")
    nil
    cl-user(18): (sql "insert into sample values(3,4)")
    nil
    cl-user(19): (sql "select * from sample")
    ((1 2) (3 4))
    ("i" "j")
    cl-user(20): (disconnect)
    nil
    cl-user(21):

In this example we store the value of the connection in the variable db and use it in subsequent sql and disconnect calls. This is the preferred style when writing programs that use this interface. In a program it is best to be explicit about the database you're accessing and not depend on a global variable that some other component of your program may modify.

In this example we show how to build an sql statement at runtime using the format function. We populate the database with 100 rows using a dotimes statement.

    cl-user(21): (setq db (connect :user "jkf" :password "jkf" :database "test"))
    #<mysql connected to localhost/3306 @ #x71f50d72>
    cl-user(22): (sql "create table samp2(val int, square int)" :db db)
    nil
    cl-user(23): (dotimes (i 100) (sql (format nil "insert into samp2 values(~d,~d)" i (* i i)) :db db))
    nil
    cl-user(24): (sql "select * from samp2" :db db)
    ((0 0) (1 1) (2 4) (3 9) (4 16) (5 25) (6 36) (7 49) (8 64) (9 81) ...)
    ("val" "square")
    cl-user(25): (disconnect :db db)
    nil
    cl-user(26):

MySQL's version of the sql language supports a number of extensions which allow you to get information about the database itself. This information is returned as rows of data just like the data in the tables you create. Here is an example of the use of these sql extensions to find out the names of all the tables in the database and to find out the scheme of a particular table

    cl-user(26): (connect :user "jkf" :password "jkf" :database "test")
    #<mysql connected to localhost/3306 @ #x71f5725a>
    cl-user(27): (sql "show tables")
    (("bar") ("bblob") ("bigtab") ("foo") ("samp2") ("sample") ("snart") ("snob")
     ("testetab") ("testtab") ...)
    ("Tables_in_test")
    cl-user(28): (sql "describe samp2")
    (("val" "int(11)" "YES" "" #:null "") ("square" "int(11)" "YES" "" #:null ""))
    ("Field" "Type" "Null" "Key" "Default" "Extra")
    cl-user(29): (disconnect)
    nil
    cl-user(30):

MySQL can store large binary objects (blobs) as well as numbers and strings. Here we see how to store a five element array of type (unsigned-byte 8) in the database. This is done by converting the array into a string with mysql-escape-sequence and then storing it like any other string. Note that mysql-escape-sequence must be used to escape strings as well as arrays of (unsigned-byte 8). This is because we are generating an sql statement at runtime and it is important that the MySQL parser be able to parse that sql statement. Certain characters appearing unescaped in the string would confuse the sql parser.

    cl-user(30): (connect :user "jkf" :password "jkf" :database "test")
    #<mysql connected to localhost/3306 @ #x71f5a4da>
    cl-user(31): (setq val
                       (make-array 5
                                   :element-type '(unsigned-byte 8)
                                   :initial-contents '(10 20 30 40 50)))
    #(10 20 30 40 50)
    cl-user(32): (sql "create table btest(i int, bl blob)")
    nil
    cl-user(33): (sql (format nil "insert into btest values(1,'~a')"
                        (mysql-escape-sequence val)))
    nil

    ;; even though we declared the type of the field to be blob
    ;; the default behavior of the sql statement is to return
    ;; the value as a string, and in this case the array data
    ;; returned as a string results in gibberish:
    cl-user(34): (sql "select * from btest")
    ((1 "
    ^T^^(2"))
    ("i" "bl")

    ;; if we tell the sql function to return the field as a blob
    ;; then we see the correct array value returned:
    cl-user(35): (sql "select * from btest" :blob t)
    ((1 #(10 20 30 40 50)))
    ("i" "bl")
    cl-user(36): (disconnect)
    nil
    cl-user(37):

Often you would like for a row in one table to reference a row in another table. This is done using keys. If you want a row in table A to point to a row in table B then in table B you designate one column as a key column. No two rows in a table have the same value for a key column, thus a key column value can be used to find a distinct row. Thus in table B one column is designated a key column and in a column in table A you store a key column value from table B. The value in column A is called a 'foreign key' since it's a key value from some other table.

One problem you face then is how do you create a unique key value for each row in a table? MySQL makes this very easy. You designate a row to be auto_increment and every time you add a row MySQL will automatically give that column a value one higher that the last value it stored in that column. You can find out what value was stored in the auto_increment column by using the mysql-insert-id function right after the row was inserted. This saves you the trouble of doing a query to find out the key value for the last inserted row.

Here's an example of using an auto_increment column. We're setting up a contest between pairs of players. We want to record the players' first and last names and then record the pairings between the players.

We create a table usertab to hold the players names and we add a key column usernum to give each player a unique number. And we create a table pairs to record the matchups.

    (defun setup-tables (db)
      ;;   call this once to setup the tables
      (sql "create table usertab(usernum int auto_increment primary key,
                                   lastname varchar(30),
                                   firstname varchar(30))"
           :db db)
      (sql "create table pairs(first int, second int)"
           :db db))

    (defun add-pair (db first second)
      ;; call this for each pair of players.
      ;; first and second are player names.  A player name
      ;; is  list  ("lastname" "firstname")
      (sql (format nil "insert into pairs values(~d,~d)"
    	       (get-user-id db first)
    	       (get-user-id db second))
           :db db))

    (defun get-user-id (db name)
      ;; retrieve the user identifier for the given name
      ;; if the user is not known, add the user to the database.
      (let ((lastname (car name))
    	(firstname (cadr name)))

        ;; since we are going to check first for the user
        ;; and if not found, add him , we want to make sure
        ;; that no other process does the same thing and
        ;; he gets added twice.  Thus we lock the table
        ;; while we do this two step process.
        (sql "lock tables usertab write")
        (with-locked-tables (db)

          ;; check first to see if the user is known.  Note
          ;; we use mysql-escape-sequence to escape characters
          ;; in the names that could confuse the sql parser.
          (let ((uid (caar
    		  (sql
    		   (format nil
    			   "select usernum from usertab
                                    where lastname = '~a' and
                                          firstname = '~a'"
    			   (mysql-escape-sequence lastname)
    			   (mysql-escape-sequence firstname))
    		   :db db))))
    	(if* uid
    	   then (return-from get-user-id uid))

            ;; user is not known, add him.
    	(sql (format
    	      nil
    	      "insert into usertab(lastname, firstname) values('~a','~a')"
    	      (mysql-escape-sequence lastname)
    	      (mysql-escape-sequence firstname))

    	     :db db)

            ;; return the value of usernum, the auto_increment column
    	(mysql-insert-id db)))))

    ;;; here it is in action

    cl-user(54): (setq db (connect :user "jkf" :password "jkf" :database "test"))
    #<mysql connected to localhost/3306 @ #x71f90552>
    cl-user(55): (setup-tables db)
    nil
    cl-user(58): (add-pair db '("franklin" "joe")   '("douglas" "mike"))
    nil
    cl-user(59): (add-pair db '("namath" "joe") '("montana" "joe"))
    nil

    ;; here we refer to users we've seen before:

    cl-user(60): (add-pair db '("franklin" "joe") '("montana" "joe"))
    nil

    ;; we see that four users have been added:
    cl-user(61): (sql "select * from usertab" :db db)
    ((1 "franklin" "joe") (2 "douglas" "mike") (3 "namath" "joe")
     (4 "montana" "joe"))
    ("usernum" "lastname" "firstname")

    ;; and here are the matchups

    cl-user(62): (sql "select * from pairs" :db db)
    ((1 2) (3 4) (1 4))
    ("first" "second")
    cl-user(63): (disconnect :db db)
    nil
    cl-user(64):

The macro with-db-rows creates sql statements based on input, executes the statements, and processes the results. The description of with-db-rows has numerous examples.

## 5.0 Conversions

All data returned by the MySQL server is in the form of characters. Even numbers like 385 are returned as a sequence of the characters #\3, #\8 and #\5. The application retrieving the data may want the data returned as a Lisp string of characters or it may want a more appropriate Lisp data object returned (e.g. the Lisp integer 385 is probably more useful to the application than the string "385").

The sql and sql-start functions send an sql command to the server. If this is a command that returns values the server will be responding with a table (that is a sequence of rows with each row comprising the same columns of data). Before the data is returned descriptions of the columns are returned. This description includes the column name and the type of data found in the column. Based on column type data, the sql function computes the conversion it will do for each column. All of the column information is stored in a list of mysql-field objects stored in the MySQL object that holds the current database connection (and accessible with mysql-fields). After the conversion is computed the rows are retrieved and the directed conversion done (unless overridden by arguments to the sql function).

If you use the sql function you can only override the conversion process using the keyword arguments: convert, blob and float. If you call sql-start instead then when sql-start returns the query has been sent to the database and the column information returned. You can examine the column information and change the conversion chosen by sql-start. Then you call get-next-row to retrieve each row of data until it returns `nil` which means no more rows are available.

At the end of this section, we describe the datatypes that might be converted and what they will be converted to. Briefly, integers, floats, and text or blobs are dataypes suitable for conversion.

The application can tell the MySQL interface whether it should convert the data returned by the MySQL server. The convert argument to sql and sql-start serves this purpose. The convert argument is a list of values one for each column to be returned. If the value for a column is true then the column data is converted to the appropriate type. If the value is `nil` then the data is returned as a string.

If there are more columns then elements in the list, no conversion is done on the extra columns (as if the elements were `nil`).

convert can also be a symbol, in which case it is treated as if it were a list with as many elements as columns, each entry being the symbol. So if convert is `t`, it is treated as the list `(t t t t ... t)`, and if convert is `:blob`, it is treated as the list `(:blob :blob :blob ... :blob)`.

The typical values are `nil` (meaning convert nothing) or `t`. Another meaningful value is `:blob`. (Any other non-`nil` non cons value is equivalent to `t`.) In the remaining discussion, we assume convert is a list.

The default value for the convert argument is `t`.

The MySQL server returns the type of each column with the result set and it is this type that determines which conversions are possible on this data.

Because there are several types of columns suitable for conversion, it is somewhat complicated to make sure that each column is converted as you wish. (You may want integers converted but floats not, for example, and you probably do want blobs converted but text not, as the default string format is suitable for text but not for blobs.)

The blob and float arguments to sql and sql-start provide finer control over conversion, as described under the headings below.

Integer data in a column is converted when the corresponding element of the convert list is non-`nil`.

#### Conversion of float columns

MySQL supports single and double float values. The default conversion function for these column types will return an integer if the database returns an integer value (e.g. "385") and otherwise will return either a single-float or double-float value if the database returns a value with a fractional part (e.g. "385.44").

If the float argument is `nil`, no float columns are converted regardless of the value of convert. If float is non-`nil`, a float column is converted if the element of the convert list corresponding to the column is non-`nil`.

When the conversion is done to a float, as must be done for a number like "-123.12", the type of float (single or double) created is determined as follows:

1. If the column whose value is being converted has type `float` then the resulting value is a Lisp integer or a single-float value.
2. If the column whose value is being converted has type `double` then the resulting value is a Lisp integer or a double-float value.
3. If the column whose value is being converted has type `decimal` then the resulting value is a Lisp integer or a double-float value. A decimal value is a fixed point number (e.g. decimal(13,2) is a 13 digit number with two digits to the right of the decimal point.)

#### Conversion of text or blob columns

MySQL supports both text and blob valued columns. The only difference between text and blob in MySQL is that text values are compared case insensitively and blob values are compared case sensitively. When a text or blob valued column is retrieved the MySQL interface is told only that the column is "text or blob", but not which one it is.

If the value is a text value then the application would typically like a Lisp string returned. If the value is a blob (e.g. a jpeg picture) then the application most likely wants an array of bytes (an (unsigned-byte 8) array in Lisp terms) returned.

The blob argument combined with convert determines how "text or blob" columns are converted. If blob is true, then if the element in the convert list corresponding to a "text or blob" column is also true, the string is converted to an array of (unsigned-byte 8) values.

If blob is `nil`, then conversion of a text or blob column is still controlled by the value of the corresponding element in the convert list: if that value is `:blob`, conversion is done. If that value is `nil` or any non-`nil` value other than `:blob`, no conversion is done and contents are returned as a Lisp string.

The blob argument defaults to `nil`.

Thus convert and blob together provide extra control for columns whose data is text or blobs. There are three cases:

1. All text or blob columns contain text.
2. All text or blob columns contain blobs.
3. Some text or blob columns contain text and some contain blobs.

In the first case, no conversion should be done (because all text or blob columns are text and text is already in the correct format). Make blob `nil` make sure none of the relevant elements of the convert list has value `:blob`.

In the second, assuming you want conversion at all, all text or blob columns should be converted to (unsigned-byte 8) arrays (because all text or blob columns are blobs and blobs need conversion). Make blob `t` and control conversion using the convert argument.

In the third case, assuming you want conversion at all, some columns should be converted and some not converted. If most are blobs, make blob `t` and make the value of convert a list with `nil` for each text column. If most columns are text, make blob `nil` and make the value of convert a list with `:blob` for each blob column.

The following table summarizes what we have just said, showing whether or not conversion is done, given the value of blob and the value of the convert argument for the column in question. (Again, if convert is a symbol, it is interpreted to be a list all of whose elements are that symbol.)

Value of convert for the column | Value of blob | Conversion performed?
`nil` | `nil` | No
`nil` | `t` | No
`t` | `nil` | No
`t` | `t` | Yes
`:blob` | `nil` | Yes
`:blob` | `t` | Yes

### Datatypes and conversions

These datatypes are converted to Lisp integers:

- all integer based datatypes (tiny, short, long, etc).
- set
- enum

These datatypes are converted to Lisp integers or single-float or double-float:

- float
- double
- decimal

These datatypes are converted to arrays of (unsigned-byte 8) if the blob argument is true or convert is a list whose value corresponding to a column is `:blob`:

- all text datatypes
- all blob datatypes

All other datatypes are not converted, they are returned as Lisp strings.

## 6.0 MySQL and stored procedures

Allegro MySQL provides limited support for stored procedure calls. At present, only stored procedures that return a single set of results can be used. To turn on this support, you must establish a database connection using dbi.mysql:connect that includes `dbi.mysql:CLIENT_MULTI_RESULTS` in the set of flags passed as the client-flags keyword argument.

If you require support for multiple result set query responses, please contact support@franz.com.

## 7.0 MySQL and multiprocessing

A database connection is a socket connection which is used to transmit requests to MySQL and to retrieve responses. Sending a request is not an atomic operation and retrieving a response is not an atomic operation.

Thus two separate Lisp threads using the same connection will likely overlap in their writes and reads leading to garbage being sent to the mysql server or incomplete results being read by the Lisp threads.

So there are two choices:

1. Use a locking to ensure that one thread makes a complete request and retrieves a complete response before any other thread is allowed to use this connection. (See Process locks in multiprocessing.htm for more information on process locks in Allegro CL. See also gates in the same document for another way to effectively lock processes.
2. Allocate one a database connection per Lisp thread. MySQL then thinks it's talking to multiple separate clients and this works perfectly well.

## 8.0 MySQL programming interface

The programming interface contains the following operators, variables, and classes:

### Operators

All are functions except with-locked-tables, which is a macro.

- connect (function)
- disconnect (function)
- sql (function)
- sql-start (function)
- get-next-row (function)
- mysql-escape-sequence (function)
- mysql-external-format (function)
- mysql-affected-rows (function)
- mysql-insert-id (function)
- lock-tables (function)
- table-list (function)
- insert-db (function)
- update-db (macro)
- with-locked-tables (macro)
- with-db-rows (macro)

### Variables

- `*mysql*`
- `*null*`

### Classes

- `mysql`
- `mysql-error`
- `mysql-protocol-error`

Copyright (c) 1998-2022, Franz Inc. Lafayette, CA., USA. All rights reserved.
This page was not revised from the 10.0 page.
Created 2019.8.20.

ToC | DocOverview | CGDoc | RelNotes | FAQ | Index | PermutedIndex
| Allegro CL version 10.1 Unrevised from 10.0 to 10.1. 10.0 version