A new interface to Oracle databases in Allegro CL: Allegro Oracle Direct Connect (added 7/27/04)

Allegro Oracle Direct Connect is a new Oracle database (version 8 and higher) interface available in Allegro CL. The API is similar to the Allegro ODBC interface (see aodbc.htm) and the Allegro MySQL Direct interface (see mysql.htm) but it is specialized for Oracle. To use the interface, you must have the Oracle C Interface libraries. An ODBC library is not necessary.

The interface works by establishing a connection from Lisp to a database. A connection is a single channel through which sql commands can be sent and values returned. It is possible to establish multiple connections using multiple threads if you want to issue more than one command at a time to the database (but in that case, you must guard against deadlocks).

Here are a couple of simple examples. In the first, we connect to the (Oracle) default database and insert and then retrieve some data.

;; we connect to the default database with user name "jkf" and
;; password "jkf".  This assigns the variable *db* to
;; the database object that's returned.
cl-user(3): (connect :user "jkf" :password "jkf")
; Foreign loading libclntsh.so.
#<db @ #x71aafb12>

;; we drop the table "samp" if it already exists so that we
;; can redefine it (the ignored error indicates it did not exist).
cl-user(4): (ignore-errors (sql "drop table samp"))
#<simple-error @ #x71dbf262>

;; we create the table
cl-user(5): (sql "create table samp(i int, j int)")

;; we add some rows to the table
cl-user(6): (sql "insert into samp values(1,10)")
cl-user(7): (sql "insert into samp values(2,20)")
cl-user(8): (sql "insert into samp values(3,30)")

;; we test to see if those rows are in the table
cl-user(9): (sql "select * from samp")
(("1" "10") ("2" "20") ("3" "30"))
("I" "J")

;; we commit our changes.  In this case the commit wasn't
;; necessary since the disconnect we do next also does a commit.
cl-user(10): (commit)

;; we disconnect.
cl-user(11): (disconnect)

In the second example, we show fetching one row at a time, a useful technique when you have a huge result set and do not want to create a big list of all the rows.

cl-user(2): (connect :user "jkf" :password "jkf")
; Foreign loading libclntsh.so.
#<db @ #x71db7b8a>

;; specify that we don't want the sql function to retrieve the rows
cl-user(3): (sql "select * from samp" :retrieve nil)

;; now call fetch-next-row until it returns nil
cl-user(4): (fetch-next-row)

;; and after each successful fetch call get-row-data to get the row
cl-user(5): (get-row-data)
("1" "10")
cl-user(6): (fetch-next-row)
cl-user(7): (get-row-data)
("300" "20")
cl-user(8): (fetch-next-row)
cl-user(9): (get-row-data)
("3" "30")

;; this time fetch-next-row returns nil - no more rows to read
cl-user(10): (fetch-next-row)
cl-user(11): (disconnect)

There are additional examples in the documentation, linked to below.

The new oracle interface is included with the Allegro 7.0 distribution and is available by patch by patch for Allegro CL 6.2. The patch will be downloaded by sys:update-allegro. Allegro CL 6.2 customers need a special evaluation license. Allegro CL 6.2 customers should contact their Franz Inc. account manager for information on obtaining the license.

The documentation for the new Oracle interface is here. This documentation is preliminary.

Copyright © 2023 Franz Inc., All Rights Reserved | Privacy Statement Twitter