HOW TO: Allegro CL, cl-sql, MySQL and utf-8

29 Dec

Recently I tried to use Allegro Common Lisp (Express Edition 8.1) with cl-sql and MySQL in order to retrieve some utf-8 encoded Turkish strings. As expected (unfortunately) I had problems getting what I want and as expected (fortunately) programmer friendly community of comp.lang.lisp heard my cry for help.

So a possible solution for my case is to create a :db-reader function which will be invoked when cl-sql retrieves utf-8 encoded character data from MySQL (thanks to Pascal J. Bourguignon and Alex Mizrahi):

(defun utf-8-bytes  (data)
  (let ((bytes
         (string-to-octets data :external-format :iso-8859-1)))
    (octets-to-string bytes :external-format :utf-8)))

(clsql:def-view-class gecici ()
    :db-kind :base
    :type (varchar 75)
    :db-reader utf-8-bytes
    :accessor text
    :initarg :text))
  (:base-table "gecici"))

(let ((new-text (car
                  (clsql:select 'gecici
                                :flatp t))))
  (format t "~A" (text new-text)))

Of course one should not forget to tell MySQL to send its data in utf-8:

(clsql:execute-command "SET NAMES 'utf8'")
Leave a comment

Posted by on December 29, 2008 in General, Lisp, Programlama


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: