| Allegro CL version 9.0 Minimally revised from 8.2. 8.2 version | ||||||||
This document contains the following sections:
1.0 Aodbc introductionThis document describes the Allegro ODBC interface. Note that the AODBC interface is not available in Allegro CL Express.
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
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.
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).
The Allegro ODBC module is module is thread safe in SMP or non-SMP Lisps so long as each connection instance (typically bound to *db*) is used by only one thread. A good way to enforce this restriction is for the application to bind *db* in each thread that uses it.
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.
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.
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):
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")
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)))
))))
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.
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.
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.
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.
Functions are labeled (f), generic functions (gf), and macros (m).
| Name | Argument List | Brief description | See examples |
| bind-parameter (f) | param-num obj type width &key db hstmt | creates a binding for a parameter for a parameterized sql statement. | 2 |
| close-query (f) | query | Closes the argument query object (sql returns a query object when the query keyword argument is true). | |
| connect (f) | &key data-source-name user password prompt hwnd width external-format connect-string non-blocking | establishes a connection to the database denoted by data-source-name (a string) and returns a database object. | 1 |
| db-external-format (f) | db | return the external format used with this database. setf can change it. | |
| db-hstmt (gf) | db | returns the default statement handle for this database connection. | 3 |
| db-non-blocking (f) | db | returns the value of the non-blocking flag of the database. It can be set with (setf db-non-blocking). | |
| db-width (gf) | db | returns the default size for character buffers used to retrieve the results of sql statements. It can be set with (setf db-width). | |
| disconnect (f) | db | Close down the given database connection. | 1 |
| end-transaction (f) | action &key; db | Commit or rollback the current transaction when
set-autocommit
is nil. |
|
| fetch-row (f) | query &optional (eof-errorp t) eof-value | Fetches the next row of query (query objects are returned by sql when the query keyword argument is true). The behavior when there are no more rows is determined by eof-errorp (if true, an error is signaled) and eof-value (if eof-errorp is nil, this value is returned instead of an error being signaled). | |
| list-all-database-tables (f) | &key db hstmt | Returns (as two values) a list of all the tables in the database and a list of the column headers that describe the data. | |
| list-all-table-columns (f) | table &key db hstmt | Returns (as two values) a list of all the columns in the table and a list of the column headers that describe the data. | |
| loop-over-results (m) | (&key types column-names do-column-names return hstmt width db) &rest body | After executing a sql statement with rr-sql, this form loops once for each result row, evaluating the body. | 3 |
| prepare-sql (f) | sql-statement &key db hstmt | Check the sql-statement for errors and prepare for its execution, but don't execute the statement. | 2 |
| rr-sql (f) | hstmt sql-statement &key db | This function runs the sql-statement on the database for which hstmt is a statement handle. The keyword argument specifies the database whose external format should be used. | 3 |
| run-prepared-sql (f) | &key db hstmt row-count width | Execute the sql statement that was last prepared via prepare-sql. | 2 |
| set-autocommit (f) | value &key; db | Set the value of the autocommit
option. If true, sql statements commit immediately.
If nil, sql statements commit only when
end-transaction is called (and can
instead be rolled back). |
|
| sql (f) | sql-statement &key db hstmt types query column-names row-count width | This is the primary function for doing database queries and modification. The sql-statement is executed on the database and the results are returned. | 1, 2, 3 |
*auto-trim-strings*
|
If true, trailing blanks in a returned string are trimmed. Default is t. |
*default-database*
|
The value must be nil or a database returned by connect. If a database, it provides the default for some functions needing a database argument, such as sql. |
*default-odbc-external-format* |
the default external format to be used for database
connections.
Initially the value is the external format
returned by evaluating the form (crlf-base-ef
:latin1) (see crlf-base-ef). |
*null-value*
|
This variable is used to represent null values in a row of values returned by a database access (such rows can contain null values). The initial value for *null-value* is nil. |
Errors in ODBC are signaled with the dbi:odbc-error condition. It has these fields:
| Field | Reader | Meaning |
| state | dbi::odbc-diag-state | official ODBC error indicator, it is a five character string where the first two characters denote the class |
| native-error | dbi::odbc-diag-native-error | integer holding the database specific error code |
| message | dbi::odbc-diag-message | official ODBC string describing the error |
| action | dbi::odbc-diag-action | string describing the context in which the error occurred |
There is another 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-2019, Franz Inc. Oakland, CA., USA. All rights reserved.
This page has had minimal revisions compared to the 8.2 page.
Created 2019.8.20.
| Allegro CL version 9.0 Minimally revised from 8.2. 8.2 version | ||||||||