Allegro ODBC

This document contains the following sections:

1.0 Aodbc introduction

This document describes the Allegro ODBC interface. This is the second version of the interface, originally introduced with release 5.0. The earlier version (version 1 in earlier releases) is no longer supported.

Allegro ODBC requires ODBC drivers that support either the version 2.0 or the version 3.0 ODBC definition.

An Allegro CL application accessing a database via this interface consists of these layers

1. The application written in Lisp
2. Allegro ODBC
3. An ODBC driver manager. This is not part of the Allegro ODBC interface. There is a driver manager that comes with certain Windows programs (and is controllable via the ODBC object in the Control Panel). For Unix users, companies such as Intersolv sell ODBC driver managers.
4. An ODBC driver for the specific database being accessed. This driver may be supplied by the same company that supplied the driver manager or it may come from the database vendor.
5. The database server [optional] - for simple databases the driver itself can do database operations and a server isn't needed. For high performance client-server databases, the driver will communicate with the database server.

The application program accesses and modifies the database via sql statements. While there is a standard for sql, most database vendors have extended sql to support special features of their database engines. ODBC doesn't impose a restriction on the sql statements you can use, it just passes the sql on to the database-specific driver. Thus if the goal is to make an application that can work with any of a number of different databases, it is the responsibility of the programmer to create only sql statements that are portable between the databases.

Symbols naming functionality in AODBC version 2 are in the dbi (nicknames include database-interface) package.

1.1 Platform-specific information for aodbc

Allegro ODBC version 2 should run similarly on all platforms. Differences arise not from the Allegro CL interface itself but from differences in the ODBC drivers and databases and database servers (if used). This section deals with platform-specific details, mostly having to do with machine configuration. At this writing, there is only one comment about the IBM/AIX platform.

IBM/AIX specific information:

Allegro ODBC now works with DataDirect Technologies DataDirect drivers 3.11 or 3.7. (DataDirect Technologies was formerly Merant and before that Intersolve.) To use them, you must be sure to install the nothread version, as described in the /opt/odbc/READ.ME file (installed on your computer when the driver is installed).

2.0 Implementation notes for aodbc

• The ODBC driver manager that Allegro ODBC communicates with must conform to ODBC version 2.0 or 3.0.
• There are no intermediate level lisp-sql functions such as select-columns and update-columns because such functions need to generate sql statements that would be acceptable to all database servers, and that is not possible.
• Parameterized sql is supported. This allows an sql statement to be parsed once and repeatedly executed, with a new parameter value supplied each time. This reduces network traffic and server overhead.
• The application can control the creation of Lisp objects to represent results. For commands that return a sequence of rows the application can control when specific columns of the row data are converted to a Lisp form.
• Errors are signaled when exceptional conditions arise.

2.1 Representing characters in databases in aodbc

Allegro CL supports various representations of characters. Along with the standard 8-bit ASCII representation, 16-bit character formats are supported. See iacl.htm for a discussion of this issue. External formats are described in the External formats in that document.

Allegro ODBC supports using external formats to specify the way characters are represented in databases. It is possible to specify on a per-database basis how the characters in Lisp should be encoded into a sequence of octets to be stored in a string object in the database. Each database connection has an external-format slot which you can set in the call to connect and can read with db-external-format and change with (setf db-external-format).

The default external format that aodbc uses is the one returned by the form (crlf-base-ef :latin1) (see crlf-base-ef). This external format encodes all 8 bit characters as the character's char-code. Characters outside the 8 bit range are encoded as the question mark character code. This external format is ideal if you're running on an 8 bit character lisp (using alisp8 or mlisp8 -- see Allegro CL Executables in startup.htm) or if you restrict yourself to 8 bit characters on a 16 bit lisp. If you plan to use 16 bit characters in a 16 bit lisp then you may want to use the :utf-8 external format instead. If you plan for the database to be used by other applications then you'll want to use an external format in Lisp that those other applications understand.

If you store information in the database using the sql function then the external format associated with the connection object of the database will ensure that the correct encoding is done.

If you use parameters to store data then you are reponsible for ensuring that the correct encoding is done when you copy a lisp string into a parameter object.

If you simply do:

(setf (ff:fslot-value param :data) "joe smith")

you will end up using whatever external format is in the current locale object (stored in *locale*). If you want to be sure to use utf-8 then you would want to bind *locale* to a locale with a utf-8 external format:

(let ((*locale* (find-locale "x.utf-8")))
   (setf (ff:fslot-value param :data) "joe smith"))

Another method that retrieves the external format from the db object is to do

(with-native-string (s "joe smith" :external-format 
                     (db-external-format db))
   (setf (ff:fslot-value param :data) s))

In this case the string s is dynamic extent. This is fine for (setf ff:fslot-value) since it copies the contents of s into the param object.

If you retrieve data with loop-over-results and rr-sql then you should note that this macro and function now take db keyword argument. You will want to be sure to pass the db being accessed as the value of the keyword argument to ensure that the external format for that database is used for extracting string data.

3.0 Using ODBC

You must load the aodbc-v2 module into Lisp. Do this with

(require :aodbc-v2)

Symbols in the aodbc-v2 module are in the :dbi package.

ODBC refers to databases by data source names. A data source name refers to an entry in a table that lists which driver is to be used, which database is to be opened, and certain driver-specific parameters that are to be used when the database is opened. On Windows data source names are defined using the ODBC item in the control panel. On Unix data source names are defined in the .odbc.ini file (although this may be done differently for different driver manager vendors).

Once the connection is made to a database a conversation ensues in which the application directs the database to do something and the database responds with the results of that operation (or signals an error). An application can have more than one conversation going with a database and the conversations are kept distinct by using distinct statement handles. When Allegro ODBC connects to a database it allocates a statement handle which it calls the default statement handle. This handle is usually all that's needed for most applications.

A conversation consists of the application asking or telling the database something and the database responding with zero or more rows of information. Each row returned has the same number of columns. The database will also return the names for columns that are named.

An application speaks to a database using the language sql. The sql understood by different databases varies quite a bit. There are two areas of differences: the data types supported and the sql extensions present. The common subset of sql supported by all ODBC accessible databases is too weak to be useful. Therefore you must study the sql manuals for the databases you plan to access.

4.0 ODBC examples

4.1 A simple interaction

In this example run on Windows we've previously created a data source name test-db using the ODBC control in the Control Panel. We start by establishing a connection to the database with connect. We are using the Microsoft Access database which doesn't require us to supply a user name or password to connect.

user(3): (setf testdb (dbi:connect :data-source-name "test-db"))
#<dbi::odbc-db
     "DSN=test-db;DBQ=C:\\temp\\testdb.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=512;
     PageTimeout=5;" @ #x2089808a>

Next we create a table in the database. Note that we have to pass the database connection object, testdb, to the sql function (as the value of the db keyword argument). If we had set *default-database* to testdb then we wouldn't have to pass in testdb.

user(4): (dbi:sql "create table mytab (i integer)" :db testdb)
nil

Had the sql statement failed, an error would have been signaled.

Next we fill the table with ten rows. This isn't the most efficient way to fill the table, both from the Lisp and ODBC perspective. Later we'll see examples of parameterized sql statements that work faster.

user(5): (dotimes (i 10) 
           (dbi:sql (format nil "insert into mytab values(~d)" i) 
                    :db testdb))
nil

Next we query the database and retrieve some of the rows:

user(6): (dbi:sql "select * from mytab where i < 5" :db testdb)
(("0") ("1") ("2") ("3") ("4"))
("i")

There are two defaults at work in this call to sql. One is that a second value is returned from the call and that value is a list of the column names describing the data returned as the first value. If you aren't going to use the column names then adding :column-names nil to the call to sql is a good idea. The second default is that the values returned by the database are treated as strings. This is the most general way to view the database data. If you know ahead of time the types of the data returned you can have the data returned as other types. Here we specify that the rows contain integers:

user(6):  (dbi:sql "select * from mytab where i < 5" :types '(:int) :db testdb )
((0) (1) (2) (3) (4))
("i")

Finally, it's good practice to disconnect from a database when the interaction is over, using disconnect:

user(7): (dbi:disconnect testdb)
#<dbi::odbc-db
    "DSN=test-db;DBQ=C:\\temp\\testdb.mdb;DriverId=25;FIL=MS Access;
     MaxBufferSize=512;PageTimeout=5;" @ #x207137ca>
user(8): 

4.2 Binding input parameters

Executing an sql statement is done in three steps: first the sql statement is parsed by the driver or database server. Next a plan is created for executing the statement in the database. Finally the plan is executed and the results returned. The cost of the first two steps can be eliminated in certain situations through the use of parameters. A parameter is denoted by a question mark in an sql statement and this denotes a value that will be supplied later. A parameterized sql statement combined with the ability to pre-parse an sql statement and then repeatedly execute it allows one to effectively execute different sql statements with no parsing overhead.

As an example we'll show two functions that create a table and fill it with 20,000 rows, each holding a number. The straightforward way using only sql is:

(defun param-store-simple (db)
  ;;
  (ignore-errors (dbi:sql "drop table foo12" :db db))
  (dbi:sql "create table foo12 (i int)" :db db)

  (dotimes (i 20000)
     (dbi:sql (format nil "insert into foo12 values (~d)" i) :db db)))

The first action this function takes is to create a fresh table. It does that by first removing the old table if it exists. If the old table doesn't exist the drop table will signal an error which the ignore-errors will cause to be ignored. Next it creates the new table and fills the table by building an sql statement for each row entered. This method is expensive since it allocates a lot of lisp strings and it causes the database server to have to repeatedly parse very similar sql statements.

Now here's a version using parameters, prepare-sql and run-prepared-sql along with sql:

(defun param-store-fast (db)
  ;;
  (ignore-errors (dbi:sql "drop table foo12" :db db))
  (dbi:sql "create table foo12 (i int)" :db db)

  (dbi:prepare-sql "insert into foo12 values (?)" :db db)
  (let ((par (dbi:bind-parameter 1 nil :int nil :db db)))
     (dotimes (i 20000)
          (setf (ff:fslot-value par :data) i)
          (dbi:run-prepared-sql :db db))))

After creating the fresh table we prepare to execute a parameterized sql statement for repeated execution. Note the question mark in the sql statement where the value would be. Before we can execute this sql statement we must create a block of memory to hold the parameter's value. This is what bind-parameter does. It creates a block of memory big enough to hold an integer and then binds it to the one and only parameter of the sql statement. Each time around the loop we set the value in the parameter memory block and then we call run-prepared-sql.

Running these two functions against an Informix database, we found that param-store-fast was three times faster than param-store-simple and allocated one quarter of the lisp heap space.

Caution must be exercised when using bind-parameter. The user program must make sure that the object bound to a parameter is not garbage collected before the parameter is needed by a call to sql or run-prepared-sql. An object stays bound to a parameter until that parameter is rebound to another object. The parameter binding is associated with a particular statement handle, which is usually the default statement handle for the database accessed.

bind-parameter allocates different objects for different types of values. For integers it allocates an object defined as

(def-foreign-type (odbc-int-param (:accessor odbc-int-param))
    (:struct (data :int)
             (strlen :int)))

The data slot is where the value should be stored (as shown in the example above). The strlen slot is for use by the ODBC interface code and should not be modified by user code.

For floating point parameters bind-parameter allocates an object defined as

(def-foreign-type (odbc-double-param (:accessor odbc-double-param))
    (:struct (data :double)
             (strlen :int)))

Note that the data field is a double precision floating point number. The user code must be sure that what it is storing in this field is a lisp double-float, for example:

(setf (ff:fslot-value param :data) (float (compute-the-value) 1.0d0)

For string parameters bind-parameter allocates one of the set of foreign data types designed to hold strings. Again the way to store values into the parameter is an expression like this

(setf (ff:fslot-value param :data) "joe smith")

4.3 Scanning through rows

There are times when it's beneficial to run Lisp code during the retrieval of rows that result from a query to the database. The rr-sql function and loop-over-results macros support this. The rr-sql function issues the sql query but does not retrieve the values. The loop-over-results macro causes its body argument to be invoked once per row returned by the database. Within the body values the column values for that particular row can be retrieved.

One important use for this retrieval method is to run a Lisp-written predicate over the database. The predicate can examine just the columns it needs to determine if a row should be included in the result set. By not examining columns that aren't needed for the predicate, a large amount of consing of lisp values can be eliminated.

The following is a complete example of the idea mentioned above. A new table is defined and populated. Then we retrieve all the rows from the table and return a set that satisfies a lisp predicate. This predicate cannot be written in sql.

(defun loop-over-example (db)
  
  ;; create a new table 
  (ignore-errors (dbi:sql "drop table loetab" :db db))
  (dbi:sql "create table loetab (a int, b int, c int, d int, e int)" :db db)
  
  ;; fill the table
  (dotimes (i 50)
    (dbi:sql (format nil "insert into loetab values(~d,~d,~d,~d,~d)"
		     i
		     (* 2 i)
		     (* 3 i)
		     (* 4 i)
		     (* 5 i))
	     :db db))
  
  (let ((hstmt (dbi:db-hstmt db)))
    (dbi:rr-sql hstmt (format nil "select * from loetab") :db db)
    
    (let (res)
      (dbi:loop-over-results
       (:db db :hstmt hstmt  :types '(:int :int :int :int :int) :return res)
       
       (let ((a (dbi:column-value 1))
	     (b (dbi:column-value 2)))
          (if* (eql #x40 (logand #x40 (logxor a b))) ;; sample predicate	 
            then ; satisfied, save this row
		 (push (list a b (dbi:column-value 3) (dbi:column-value 4)
			     (dbi:column-value 5))
		       res)))
       ))))

5.0 Character input buffers

When an sql statement is executed and data is to be returned, Allegro ODBC assigns buffers to each column and then fetches each row. Some databases support a character datatype that can be very long. Unfortunately the ODBC driver doesn't inform Allegro ODBC just how big the buffers must be in order to read the row data for a given query. Thus Allegro ODBC just picks an arbitrary sized buffer (1024 characters, by default). If data is returned that is too big for the buffer Allegro ODBC is notified and tries to retrieve it using a different method. This usually succeeds but it depends on features not found in all ODBC drivers. If Allegro ODBC can't retrieve the data then it signals the dbi:odbc-error-width-too-small condition. In that case you'll have to use the :width argument found in many of the functions and macros described below in order to increase the size of the buffer before a query is done.

The only database we've encountered so far for which Allegro ODBC has to signal the dbi:odbc-error-width-too-small condition is Oracle version 7.

6.0 Queries

Allegro ODBC offers three ways to retrieve data after a select sql statement is executed. The simplest is to just retrieve all of the returned rows at once and this is the default behavior of the sql function. A more complex way of retrieving results is with rr-sql and loop-over-results. This allows you to abort the retrieve of all rows at any time. A third way to retrieve row data is via a query object. A query object is returned by sql when the query keyword is given a true value. A query object can be asked at any time, by the function fetch-row, for the next row of data from the query. There can be multiple query objects active and they can be accessed in any order.

Query objects use resources and should be closed with close-query when no longer needed.

7.0 Transactions

Normally when a sql statement is executed the results are commited to the database and other users of the database can view them.

You may wish to execute two or more sql statements before making the results visible. You may also wish to undo the effects of a sequence of sql statements. Grouping a sequence of sql statements into a transaction allows you to treat the effect of all the statements as one atomic change to the database which you either cause to occur by commiting the change or cancel by rolling back the change.

In order to use transactions in allegro odbc you must first turn off the autocommit behavior (connections all start autocommitting), controlled by the set-autocommit function:

(dbi:sql-autocommit nil :db db)

Now you can issue a sequence of sql statements. You'll see the effect of those statements reflected in the contents of the database but no other user connected to the database will see them. In order to make these changes visible to everyone you commit the changes with a call to end-transaction:

(dbi:end-transaction :commit :db db)

This ends that transaction and starts a new one.

If you decided that you didn't want the changes you've made then you can roll them back, again with a call to end-transaction:

(dbi:end-transaction :rollback :db db)

This form also ends a transaction and starts another.

Notes:

• Some database drivers don't support transactions. Be sure to make some tests with your database configuration before designing an application to depend on transactions.
• In order to implement transactions some databases will lock out access to rows you've changed but haven't yet committed. Thus transactions that take a long time can harm the multi-user performance of a database application.
• Do not use the sql commit and rollback operators in place of end-transaction. In certain cirumstances this can confuse the odbc drivers.

8.0 Reference

The symbols naming functionality in Allegro ODBC version 2 are in the dbi package. Each has its own description page. In this section, we provide brief descriptions (and argument lists for operators) along with a link to the documentation page.

Operators

Functions are labeled (f), generic functions (gf), and macros (m).

Variables

9.0 Conditions

Errors in ODBC are signaled with the dbi:odbc-error condition. It has these fields:

There is one condition, dbi:odbc-error-width-too-small (a subclass of dbi:odbc-error) which is signaled when data cannot be extracted from the database because the buffer was too small and the database does have the ability to send the data in small chunks.

Copyright (c) 1998-2016, Franz Inc. Oakland, CA., USA. All rights reserved.
This page was not revised from the 8.1 page.
Created 2010.1.21.