MySQL Native Driver for Common Lisp (Lispworks)

Native driver for the MySQL database in Common Lisp. The word native here means that the code connects to the database directly through a socket and no C bindings or other foreign language bridges are used. The code requires no additional libraries, everything it needs is included in one file. Works with MySQL 5.0 or higher and any Lispworks 8.0 or higher.

Download

mysql.lisp  (8JUL2023, 34KB)

Examples of usage

Example 1
---------

(load "mysql.lisp")
(setq db (mysql:connect :host "127.0.0.1" :user "user" :password "pass"))
(mysql:query db "select 1+1")
(mysql:pquery db "select now() as datetime")
(mysql:disconnect db)


Example 2
---------

(load "mysql.lisp")

(defvar *db* nil)

(defconstant +credentials+
  '(:host "127.0.0.1" :user "user" :password "pass" :database "test"))

(defconstant +industrialists+
  '(("Bruce Wayne" 188)
    ("Anthony Stark" 185)))

(defun main ()
  (mysql:with-connection (*db* +credentials+)
    (create-table)
    (populate-table)
    (view-table)))

(defun create-table ()
  (mysql:query *db*
     "CREATE TABLE industrialist ("
     "  id BIGINT NOT NULL AUTO_INCREMENT,"
     "  name VARCHAR(128) NOT NULL,"
     "  height INT NOT NULL,"
     "  PRIMARY KEY (id))"))

(defun populate-table ()
  (mysql:with-transaction (*db*)
    (dolist (item +industrialists+)
      (mysql:query *db*
         "INSERT INTO industrialist (name, height)"
         "VALUES (" (mysql:quote-string (car item)) "," (cadr item) ")"))))

(defun view-table ()
  (dolist (item (query-table))
    (format t "~a: ~a~%" (getf item :name) (getf item :height))))

(defun query-table ()
  (mysql:pquery *db*
     "SELECT name, height FROM industrialist"))

(main)

Limitations

There are no checks in the code for SQL query statements -- therefore, know what you are querying and what you expect to retrieve! If your query returns a set of one million results, the code will generate a list with one million elements, which might slow or crash your computer. Again, be judicious in what you query.

The code sets the utf-8 encoding for opened connections to communicate with the mysql server. Do not use the 'set names' statements in your queries or the communication protocol will be broken.

The code doesn't support the BLOB data type in the databases.

The maximum size of packet is 1Mb. If you have data in any given field of the database bigger than this size, modify the code to change the value of the maximum packet size.

If during execution of the code any error occurs (exept a mysql-error) then it is advisable to "drop" the connection and open a new connection to the database.

Programming interface

MYSQL.lisp defines MYSQL package and exports some symbols as a public interface to work with mysql databases. The programming interface consists of functions for establishing connection with a database, querying it and some helper utils and macros.

Symbols available in the MYSQL package:
connect, disconnect, query, pquery, last-insert-id, escape-string, quote-string, with-connection, with-transaction, mysql-error.

[function]
connect &key host port user password database => handler

Connects to the mysql database.

host  a string with the host address running mysql server
port  a number with a port number; defaults to 3306
user  a string with a user name
password  a string with a password
database  a string with an initial database name; optional

handler  a strucure that holds the information about an opened connection


[function]
disconnect handler

Disconnects from the mysql database.

handler  a structure returned by the connection function


[function]
query handler &rest args => result

Query and retrieve information from the database as lists.

handler  a structure returned by the connection function
args  any number of query arguments of any type that would be combined together and send to the mysql server as an sql statement. All args are first converted to strings and the resultant strings are combined together appending a space chracter between the strings. For example for args (1 "2" :keyword) the resultant sql statemnt string would be " 1 2 :KEYWORD".

result  the list of result rows as lists. Each element of a result row list is a string.


[function]
pquery handler &rest args => result

Query and retrieve information from the database as property lists.

handler  a structure returned by the connection function
args  any number of query arguments of any type that would be combined together and send to the mysql server as an sql statement. See the query function for more information.

result  the list of result rows as property lists. In a property list the key is the name of a column as a keyword and the property is a string.


[function]
last-insert-id handler => result

Returns the id generated for an auto_increment column by the previous query (usually insert).

handler  a structure returned by the connection function

result  a number


[function]
escape-string string => result

Escapes special characters ', " and \ in a string.

string  an unescaped string

result  an escaped string


[function]
quote-string string => result

Escapes special characters ', " and \ in a string and adds the ' character at the beginning and the end of the escaped string.

string  an unescaped string

result  an escaped and quoted string


[macro]
with-connection (var credentials) &body body => result

Connects to the mysql database using credentials and binds information about the opened connection to var. Executes body using this binding. At the end closes the connection to the database.

var  a symbol
credentials  a list of arguments which are passed to the connection function.
body  the body of code

result  the result of evaluating body


[macro]
with-transaction (var) &body body => result

Executes body within a database transaction for the given database connection. The transaction will be committed to the database if the body exits normally or aborted if the body failed.

var  a symbol that points to a structure returned by the connection function for a given connection within which the transaction will be executed.
body  the body of code

result  the result of evaluating body


[condition type]
mysql-error

Signalled if the mysql server returns an error.

License

Copyright (c) 2009-2023, Art Obrezan
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

  1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
  2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
  3. Use in source and binary forms are not permitted in projects under GNU General Public Licenses and its derivatives.

THIS SOFTWARE IS PROVIDED BY ART OBREZAN ''AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL ART OBREZAN BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.