Path: utzoo!attcan!utgpu!jarvis.csri.toronto.edu!mailrus!tut.cis.ohio-state.edu!gem.mps.ohio-state.edu!ginosko!uunet!philmtl!philabs!linus!mbunix!bds
From: bds@mbunix.mitre.org (Barry D Smith)
Newsgroups: comp.lang.lisp
Subject: Lucid to Oracle connection
Message-ID: <64385@linus.UUCP>
Date: 17 Aug 89 18:28:05 GMT
Sender: news@linus.UUCP
Reply-To: bds@mbunix (Smith)
Organization: The MITRE Corporation, Bedford, Mass.
Lines: 58
References:


If anyone has developed a Lucid to Oracle interface, I wonder if you
would be willing to share some of your discoveries with us.  We've
been working on this for a while now, and although we can make it
work, the solution is very kludgy.  Neither Lucid nor Oracle are much
help in this.

When we bought Oracle v5.3x we were running SunOS 3.5, but by the
time it arrived, we had updated to SunOS 4.0, including an upgrade
to Lucid 3.0 for SunOS 4.0.  It turns out that Oracle v.5 has some
fundamental incompatiblities with SunOS 4.0, including the fact that
SQLNET doesn't work.  Unfortunately, we needed to run SunOS 4.0.

The plan was to write C code to query Oracle using Pro-C, then to
compile the code to object code, and load it and all necessary
libraries into Lucid using def-foreign-function and
load-foreign-files.  Lucid would then do all the linking.  We would
then run a Lisp function to connect to Oracle, leave the connection
open, and run our query and update functions in Lisp as needed.  The
data could then be loaded into LISP variables using Lucid's
def-foreign-callable.

This worked using Oracle v.5.3, as long as we didn't have to go
over the net.  We finally received an Oracle update, v.6.0x, which
fixed the networking problem.  However, Oracle v.6 requires SunOS
v.4.0.3, which we got and installed.  Oracle now does everything
properly, but Lucid will no longer link the Oracle libraries properly.
It appears that the linker under SunOS 4.0 is not compatible with the
linker under SunOS 4.0.3.  So we no longer could stick by the plan.

An alternative plan was developed, in which we would compile and link
the c code in unix, producing executable files, and then run them
with Lucid's run-program.  The c code would print the data, and
Lucid would redirect the output to a LISP stream, which we would then
read into LISP variables.  This works but requires that we open a new
connection to Oracle everytime we need to do anything, since the 
c program exits.  It takes about 3 seconds to make each connection,
which causes unnecessary slowdowns at runtime.

So then we decided to open a 2-way buffer between Lucid and a c
program by using :input :stream :output :stream in run-program.  We
took the code directly from the manual, but it did not work.
Apparently we could not open a 2-way stream (it seems we could write
to it, but trying to read from it caused it to hang).  It is
unfortunate, since if it did work, we could make the c program open a
connection to Oracle and then just sit in a constant wait state.  Then
we would be back at the original plan, except that Lucid would not be
doing the linking.  But it doesn't work.  It does appear that we could
have the c program write its results to a file, and then have Lucid
read that file.  We are currently investigating this.  The point is
that this seems very kludgy.  We should be able to load c variables
directly into LISP variables (no pointers, just terminal data).

If anyone has worked this problem and would like to discuss it, I
would welcome it.  Thanks.

Barry Smith
bds@mbunix.mitre.org