From: Tom Fawcett
Subject: Communicating between Lisp and a database
Date:
Message-ID: <8j1zfzvi45.fsf@basit.com>
I'm interested in developing an agenda based AI system in Lisp, but I need
the program to be able to access a database. Currently I use Allegro
Common Lisp and MySQL. I'd like to be able to issue SQL commands from lisp
and retrieve the results back into lisp. (I realize data type conversions
may be problematic but I'll deal with that later.)
I'm using ACL Personal Edition, whose documentation has nothing on this
topic. I searched the comp.lang.lisp FAQ, dejanews and various archives
and I've found very little on this topic. It appears there is a general
interface to ODBC, and I've found a vague reference to 'Common SQL" but no
details. Before I give up on Lisp entirely, does anyone know of any
resources I've missed?
I'm willing to switch CL and DB systems, but unfortunately I'm constrained
to freeware.
Thanks and regards,
-Tom
Tom Fawcett <·······@basit.com> wrote in message
···················@basit.com...
>
> I'm interested in developing an agenda based AI system in Lisp, but I need
> the program to be able to access a database. Currently I use Allegro
> Common Lisp and MySQL. I'd like to be able to issue SQL commands from
lisp
> and retrieve the results back into lisp. (I realize data type conversions
> may be problematic but I'll deal with that later.)
>
> I'm using ACL Personal Edition, whose documentation has nothing on this
> topic. I searched the comp.lang.lisp FAQ, dejanews and various archives
> and I've found very little on this topic. It appears there is a general
> interface to ODBC, and I've found a vague reference to 'Common SQL" but no
> details. Before I give up on Lisp entirely, does anyone know of any
> resources I've missed?
Have you checked Paul Meurer's ODBC bindings for LispWorks and ACL ?
Try his ftp account: amirani.hit.uib.no, with anonymous login
Marc Battyani
From: Alan Gunderson
Subject: Re: Communicating between Lisp and a database
Date:
Message-ID: <3750714B.E3BB7D83@acm.org>
Tom Fawcett wrote:
> I'd like to be able to issue SQL commands from lisp
> and retrieve the results back into lisp. (I realize data type conversions
> may be problematic but I'll deal with that later.)
Tom:
I wrote a paper on this topic in 1991. See:
"An SQL Interface for Common Lisp" by A. Gunderson, ACM Lisp Pointers
Journal, Volume IV,
Number 2, pp. 32-43, April-June 1991.
This used the FFI in Lucid Common Lisp running on and Ultrix workstation
to call a C program. The C program accesses the database and then
returned the data to Lisp. The API contained functions to turn the
result table from the database into a list of structures in Lisp. Was
then just a simple matter to walk down the list and use accessor
functions to obtain the attributes you needed. The SQL queries are
right in the Lisp program and are passed as strings to C through the
FFI. Thus, you could use all the string manipulation routines in Lisp
to generate SQL queries, which are then passed through the FFI to C to
use dynamic SQL to query the database
We used this to access both Ingres and Rdb databases. It worked well
for the prottoype work we were doing. It is not approprate for queries
that return million row result tables, but for low volumn access to
persistent data, it was fine.
I probably have the software on tape or diskettes somewhere and could
send you a copy to use for your application. It was a fairly small
piece of code-8 to 10 pages, as I recall.
--
Alan Gunderson
Cygnus, Inc.
Eagle River, Alaska
e-mail: ··········@acm.org
Franz has ODBC bindings for ACL, just call a sales rep to get more information.
1-888-CLOS-NOW. No, unfortunately, I don't get a click-through kick-back ;-)
Tom Fawcett <·······@basit.com> wrote:
>
>I'm interested in developing an agenda based AI system in Lisp, but I need
>the program to be able to access a database. Currently I use Allegro
>Common Lisp and MySQL. I'd like to be able to issue SQL commands from lisp
>and retrieve the results back into lisp. (I realize data type conversions
>may be problematic but I'll deal with that later.)
>
>I'm using ACL Personal Edition, whose documentation has nothing on this
>topic. I searched the comp.lang.lisp FAQ, dejanews and various archives
>and I've found very little on this topic. It appears there is a general
>interface to ODBC, and I've found a vague reference to 'Common SQL" but no
>details. Before I give up on Lisp entirely, does anyone know of any
>resources I've missed?
>
>I'm willing to switch CL and DB systems, but unfortunately I'm constrained
>to freeware.
>
>Thanks and regards,
>-Tom
D. Tenny
············@mediaone.net - no spam please
From: ·······@hhmi.org
Subject: Re: Communicating between Lisp and a database
Date:
Message-ID: <7k2udb$o7q$1@nnrp1.deja.com>
In article <··············@basit.com>,
Tom Fawcett <·······@basit.com> wrote:
>
> I'm interested in developing an agenda based AI system in Lisp, but I
need
> the program to be able to access a database. Currently I use Allegro
> Common Lisp and MySQL. I'd like to be able to issue SQL commands from
lisp
> and retrieve the results back into lisp.
Try (all free) ILU. Putting the SQL in your Lisp will make you blind.
Instead, how about a Perl (or Java) agent type thing running on or
close to, the database tier(Oracle gives away 8i(corba) for NT) which
encodes/decodes the SQL and spews out Lisp forms to your AI system?
Embedded SQL & network database drivers is old tech...
--
Richard J Kucera
N-Tier Developer in the AI lab(well really just a "web apps developer")
Howard Hughes Medical Institute
Thou marshalest me the way that I was going -shak
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
·······@hhmi.org writes:
> Try (all free) ILU. Putting the SQL in your Lisp will make you blind.
Why would that be the case? Apart from not knowing of anything in
computer technology that will make you blind (well, with the possible
exception of certain very old monitors, and maybe mis-callibrated iris
scans ;), I'd rather think that doing SQL from Lisp is the least
"blinding" approach to SQL possible, given that of course SQL itself
is hated by many.
There exist a large number of possibilities for doing SQL from Lisp
that are quite nice, like e.g. Common SQL from Harlequin, or SQL-ODBC
(which has the same interface) which works on Harlequin's Lispworks,
Franz's Allegro Common Lisp, and Macintosh Common Lisp, with a port to
CMU CL possibly being worked on...
I seem to remember that Franz have an SQL/ODBC interface themselves,
and no doubt there are other solutions. And just for the heck of it,
I did a "low-level" SQL interface for CMU CL and MySQL/PostgreSQL for
proof of concept, which I'm currently polishing up a bit, and will
release shortly (this library is patterned a bit after Harlequin's
CommonSQL, though it isn't all that compatible, nor does it aim to be,
and no code from Harlequin's CommonSQL was used, so they don't carry
any responsibility/blame for this ;).
Especially the commercial solutions and SQL-ODBC have very nice
high-level features, like symbolic SQL syntax, functional and
object-oriented interfaces, and e.g. specialized streams for direct
BLOB access, which make working with SQL much more pain-free than it
usually is. Take a look at Harlequin's site, there should be online
versions of their Lispworks Userguide, which has a section on Common
SQL.
> Instead, how about a Perl (or Java) agent type thing running on or
> close to, the database tier(Oracle gives away 8i(corba) for NT) which
> encodes/decodes the SQL and spews out Lisp forms to your AI system?
Hmmm, I don't think this will do much for maintainability, nor do I
think that this will provide any kind of performance level that I'd
consider acceptable. Not to mention all the hassle of keeping your
database consistent in the face of failures, which just get
exponentially bigger by the number of layers involved. Perl's
"advanced condition handling" will not make things easier, either.
BTW: Even if you don't use one of the shiny SQL interfaces, making a
direct interface to your favourite C databse client library via FFI is
quite trivial for most Lisps, given that the number of functions and
data-structures involved is quite small.
> Embedded SQL & network database drivers is old tech...
That's true, and Common Lisp is old tech, too, so we surely don't
need Perl or Java (older tech in newer clothes) to do it.
Regs, Pierre.
--
Pierre Mai <····@acm.org> PGP and GPG keys at your nearest Keyserver
"One smaller motivation which, in part, stems from altruism is Microsoft-
bashing." [Microsoft memo, see http://www.opensource.org/halloween1.html]
BTW: Even if you don't use one of the shiny SQL interfaces,
making a
direct interface to your favourite C databse client library via
FFI is
quite trivial for most Lisps, given that the number of functions
and
data-structures involved is quite small.
On a smaller scale yes, client libraries a fine idea, but client
libraries
eventually won't cut it. If you want Lisp as an enterprise tool, seems
like you have to get the object bus going. And then there's TP monitors
to handle consistency and performance scaling. For exploring, still,
you
could design an explorer agent to execute SELECT statements for you and
not need the client library or sql*net...not that I've tried any of this
:)
Perhaps I will tomorrow...I'll have to fake the 8i corba with a Java or
Perl tier using client drivers(I've got 8i up at home, but no PC at
work
big enough for it). Heck, 8i probably has some canned select objects
already in there for you to get at through corba. have to try
it...lemme
talk to my dba.
> Embedded SQL & network database drivers is old tech...
That's true, and Common Lisp is old tech, too, so we surely
don't
need Perl or Java (older tech in newer clothes) to do it.
Yeah yeah Lisp is as old as Fortran, but it's fresh compared to all
the procedural lingos always coming out with all their squiggly syntax
rules...perl is useful for processing results, and for, as I mentioned,
the client library tier if database built-in corba not there...
The only reason I'd suggest Java is that there's industry support
behind it(free drivers and other products etc etc) if Perl tier doesn't
work out.
Hello,
> In article <··············@basit.com>,
> Tom Fawcett <·······@basit.com> wrote:
> >
> > I'm interested in developing an agenda based AI system in Lisp, but I
> need
> > the program to be able to access a database. Currently I use Allegro
> > Common Lisp and MySQL. I'd like to be able to issue SQL commands from
> lisp
> > and retrieve the results back into lisp.
There's a portable (and stable) ODBC/SQL - Package by Paul Meurer
(paul.meurer at hit.uib.no). It has been posted to Digitool's FTP
Server: Last version I know of is "sql-odbc-082.zip".
Johannes Beck
--
Johannes Beck ····@informatik.uni-wuerzburg.de
http://www-info6.informatik.uni-wuerzburg.de/~beck/
Tel.: +49 931 312198
Fax.: +49 931 7056120
PGP Public Key available by ·············@informatik.uni-wuerzburg.de