Accessing MySql From SBCL

This weekend I spent some time to test clsql on sbcl. I tried to do this before on Windows, but I couldn’t get it to work. So I used my trusted Amazon ec2 linux machine to do the job.

Initially, I had to use yum to install sbcl. It is as simple as

yum install sbcl

and watch the packages being downloaded. I also needed to install gcc and mysql-devel (this depends on the distribution).

yum install gcc
yum install mysql-devel

Then, I used asdf to install the required packages, including clsql. These are some of the commands I used:

(require :asdf)
(require :asdf-install)
(asdf-install:install :uffi)
(asdf-install:install :clsql)

There was a lot of complaining from asdf-install, especially related to a missing gpg library. Also, most backends other than mysql failed to compile, which is ok, and I just ignored it selecting one of the options given by the debugger.

Once installed, you can use clsql to make calls to mysql. I created a simple database called carlosdb with the commands (typed on mysql prompt):

create database carlosdb;
use carlosdb;
create table users (
     `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     `name` VARCHAR(100) NOT NULL);

Then, we can use clsql in the following way:

(require :asdf)
(asdf:operate 'asdf:load-op 'clsql)
(use-package :clsql-user)
(connect `("" "carlosdb" "" "") :database-type :mysql)
(execute-command "use carlosdb");
(query "select * from users")

The two results of the last operation are lists like the following:

((1 "john nash") (2 "james jim") (3 "jon williams"))
("id" "name")

where the first list is composed of sublists, each have as elements the contents of a record. The second list just stores the names of the fields (id and name, in this case).