From time to time someone posts about ODBC access from Lisp. Lisp
implementations that support Microsoft's COM specification have an
alternative: Microsoft's OLEDB data access specification and the ActiveX
Data Objects (ADO) layer built on top of it.
There are several reasons to look at these:
1. OLEDB supports a wider range of data sources than ODBC, including RDBMS
systems, ODBMS systems, and multidimensional OLAP databases.
2. OLEDB sometimes provides faster access than ODBC. For example, OLEDB can
read and write from Access .mdb files using Microsoft's Jet data engine
rather than the slower Access ODBC interface. There are also custom OLEDB
providers for MS SQL Server and Oracle 8.
3. The ADO API is much simpler than the ODBC API, but no less powerful and
flexible.
For more on ADO see Microsoft's documentation at:
http://msdn.microsoft.com/library/psdk/dasdk/mdov8ddj.htm
I've written a small library to illustrate using ADO from ACLWin 5. Here is
a usage example:
(open-connection "DSN=anODBCdatasourceName")
(let ((rs (execute "select * from user")))
(if (not (eof rs))
(print (field-names rs)))
(while (not (eof rs))
(print (field-values rs))
(move-next rs))
(close-recordset rs))
(close-and-end)
The library code:
(defparameter *connection* nil)
(defun interface-to-autotool (ifc)
(make-instance 'ole:remote-autotool :dispatch ifc))
(defun ugetf (autotool prop &rest parms)
"undispatching-getf; like auto-getf except that if the return is an
interface it converts it to an autotool"
(let ((retval (apply #'ole:auto-getf (append (list autotool prop)
parms))))
(if (eq (type-of retval) 'ole::idispatch-client)
(interface-to-autotool retval)
retval)))
(defun umethod (autotool method &rest parms)
"undispatching-method; like auto-method except that if the return is an
interface it converts it to an autotool"
(let ((retval (apply #'ole:auto-method (append (list autotool method)
parms))))
(if (eq (type-of retval) 'ole::idispatch-client)
(interface-to-autotool retval)
retval)))
(defun open-connection (connect-string)
(ole:start-ole)
(setq *connection* (ole:ask-for-autotool
"ADODB.Connection"
ole:CLSCTX_INPROC_SERVER))
(ole::auto-method *connection* :open connect-string))
(defun execute (sql-string)
"returns an ADO recordset if the sql-string is a SELECT"
(umethod *connection* :execute sql-string))
(defun field-names (recordset)
(let* ((fields (ugetf recordset :fields))
(field-count (ugetf fields :count))
(names nil))
(dotimes (n (1- field-count))
(push (ugetf (ugetf fields :item (1+ n)) :name)
names))
(nreverse names)))
(defun field-values (recordset)
(let* ((fields (ugetf recordset :fields))
(field-count (ugetf fields :count))
(values nil))
(dotimes (n (1- field-count))
(push (ugetf (ugetf fields :item (1+ n)) :value)
values))
(nreverse values)))
(defun eof (recordset)
(ugetf recordset :EOF))
(defun move-next (recordset)
(umethod recordset :MoveNext))
(defun move-first (recordset)
(umethod recordset :MoveFirst))
(defun move-last (recordset)
(umethod recordset :MoveLast))
(defun move-previous (recordset)
(umethod recordset :MovePrevious))
(defun close-recordset (rs)
(umethod rs :close))
(defun close-connection ()
(umethod *connection* :close))
(defun ado-end ()
(ole:release *connection*)
(ole:stop-ole))
(defun close-and-end ()
(close-connection)
(ado-end))