Clementson's Blog

Using MySql with Allegro CL

Wednesday, June 2, 2004

I've been playing around with the MySQL Direct Connect support in Allegro CL. MySql is the "world's most popular open source database" and can be used as either an open source product or as a commercial offering (which license you choose depends on how you intend to use the product), making it a flexible (and high performing) alternative to commercial database products. Although Franz has offered ODBC access to a range of databases in the past, the MySQL Direct Connect (first introduced as an update to ACL 6.2) connects directly to the MySQL server. This offers the following advantages:

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

CL-USER> (use-package :dbi.mysql)
; Autoloading for package "DBI.MYSQL":
; Fast loading c:binacl-7.0codeMYSQL.fasl
T
CL-USER> (connect :database "test")
; Fast loading from bundle codeacldns.fasl.
#
CL-USER> (sql "create table customers(CustomerID varchar(5), 
                                      CompanyName varchar(40), 
                                      ContactName varchar(30), 
                                      ContactTitle varchar(30), 
                                      Address varchar(60), 
                                      City varchar(15), 
                                      Region varchar(15), 
                                      PostalCode varchar(10), 
                                      Country varchar(15), 
                                      Phone varchar(24), 
                                      Fax varchar(24))")
NIL
CL-USER> (sql "load data infile 'c:/Customers.tab' into table customers")
NIL
CL-USER> (sql "select CustomerID, CompanyName, ContactName from customers limit 5")
(("ALFKI" "Alfreds Futterkiste" "Maria Anders")
 ("ANATR" "Ana Trujillo Emparedados y helados" "Ana Trujillo")
 ("ANTON" "Antonio Moreno Taquería" "Antonio Moreno")
 ("AROUT" "Around the Horn" "Thomas Hardy")
 ("BERGS" "Berglunds snabbköp" "Christina Berglund"))
("CustomerID" "CompanyName" "ContactName")
CL-USER> (sql-start "select CustomerID, CompanyName, ContactName from customers")
("CustomerID" "CompanyName" "ContactName")
CL-USER> (get-next-row)
("ALFKI" "Alfreds Futterkiste" "Maria Anders")
CL-USER> (get-next-row)
("ANATR" "Ana Trujillo Emparedados y helados" "Ana Trujillo")
CL-USER> (get-next-row)
("ANTON" "Antonio Moreno Taquería" "Antonio Moreno")
CL-USER> (with-db-rows ((CustomerID CompanyName) :table "customers") 
                (format t "Company ~s: ~s~%" CustomerID CompanyName))
Company "ALFKI": "Alfreds Futterkiste"
Company "ANATR": "Ana Trujillo Emparedados y helados"
Company "ANTON": "Antonio Moreno Taquería"
etc.
CL-USER> (disconnect)
NIL
CL-USER>