$Revision: 1.1.2.10 $
The document introduction.htm provides an overview of the Allegro CL documentation with links to all major documents. The document index.htm is an index with pointers to every documented object (operators, variables, etc.) The revision number of this document is below the title. These documents may be revised from time to time between releases.
1.0 Introduction
1.1 Platform-specific information
2.0 What has changed since version 1.0
3.0 Using ODBC
4.0 ODBC examples
4.1 A simple interaction
4.2 binding input parameters
4.3 scanning through rows
5.0 Character input buffers
6.0 Queries
7.0 Reference
8.0 Conditions
This document describes the second version of the Allegro ODBC interface. This new version is designed for higher performance. It requires ODBC drivers that support either the version 2.0 or the version 3.0 ODBC definition. Note that this 2.0 version of Allegro ODBC is incompatible with version 1 of Allegro ODBC (documented in a printed manual). You must use one version or the other. You should not use both.
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 Merant (formerly Intersolve) DirectData drivers 3.11. 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).
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 dbi: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 dbi:sql function (as the value of the db keyword argument). If we had set dbi:*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 dbi:sql statement failed, an error would have been signaled. (Unlike with version 1 of Allegro ODBC we don't have to check the return values from the dbi functions in order to tell if an error occurred. Another important difference is that in version 2 we create the table using sql, instead of calling a Lisp create-table function that would then build the sql statement and send it to the database.)
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 dbi: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 dbi: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 dbi: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 dbi: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, dbi:prepare-sql and dbi:run-prepared-sql along with dbi: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 much 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 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-sql 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"))
(let (res)
(dbi:loop-over-results
(: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. It 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.
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. | |
| 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 | establishes a connection to the database denoted by data-source-name (a string) and return a database object. | |
| db-hstmt (gf) | db | returns the default statement handle for this database connection. | |
| 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. | |
| 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, and 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) &rest body | After executing a sql statement with rr-sql, this form loops once for each result row, evaluating the body. | |
| 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 | This function runs the sql-statement on the database for which hstmt is a statement handle. | 3 |
| run-prepared-sql (f) | &key db hstmt row-count width | Execute the sql statement that was last prepared via prepare-sql. | 2 |
| sql (f) | sql-statement &key db hstmt types query column-names row-count width | This is the primary function for database doing database queries and modification. The sql-statement is executed on the database and the results are returned. | 1, 2, 3 |
*auto-trim-string* |
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. |
*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 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-1999, Franz Inc., Berkeley, CA. All Rights Reserved.