| Allegro CL version 8.2 Unrevised from 8.1 to 8.2. 8.1 version |
Arguments: sql-statement &key db hstmt types column-names row-count width query
This is the primary function for doing database queries and modification. The sql-statement is executed on the database and the results are returned.
If row-count is nil
, the data is
returned as one or two values. The first value is a list of rows. Each
row is a list of column values (or
nil
for null
values). If column-names is true (which is the default) then
a second value is returned which is the list of column names for each
row value. Some column names may be empty strings (especially if this
column is a computed column).
If row-count is true then a single integer is returned which is the number of rows affected by the operation. It only makes sense to ask for the number of rows after an sql update, insert, or delete statement.
The query keyword argument is a boolean which if true causes a query object to be returned as the first value (instead of a list of the results of the sql statement). The query object then can be passed to the fetch-row function to retrieve the next row of data. Query objects are closed with close-query.
If statement handle hstmt is non-nil
then it must be a valid statement handle value
for the database to be accessed. Otherwise, if
hstmt is nil
then the
value of db is checked. If it is non-nil
then it must be a database object and the sql
statement is run on that database. If db is nil
then the database object that is the value of
*default-database*
is
used.
Normally the column values in each row are returned as strings (or
nil
for the null value). You can specify in
types the kinds of values you want returned for each
column. types can either be the keyword
:auto
or a list of type specifiers from the table
below. The value :auto
means use the best Lisp type
for the given database type. In practice, only integers and
floating-point numbers are converted; all other types are returned as
strings, as with the t specifier.
Specifier | Meaning |
:int | return as a 4 byte signed value |
:long | return as a 4 byte signed value |
:double | return as an 8 byte floating point value |
t | return as a string (the default) |
Note: an earlier version of this document stated that
:auto
was permissible as a value in the list of type
specifiers when types is a list. That was
incorrect. types can be :auto
or a list
consisting of the specifiers in the table above, which does not
contain :auto
.
The first type specifier in the types list describes the type of the first column, and so on. If there are too few type specifiers given then the t specifier is assumed. If there are too many type specifiers given then the extras are ignored.
width is the size for character buffers used in retrieving character-valued fields. If not given, then the width for the database, db-width, is used. Data wider than width will still be retrieved correctly.
The sql-statement must be a valid sql statement for the given database connection. Each database vendor has a different version of sql although they all support a very minimal subset of expressions and data types.
The sql-statement may contain parameters. A parameter is denoted by a ? where a value would normally be found. Before calling sql on a parameterized sql-statement it is necessary to set values for each of the parameters in the statement. This is done with bind-parameter.
If the same sql statement is going to be repeatedly executed you may want to use prepare-sql and run-prepared-sql.
See aodbc.htm for more information on Allegro ODBC.
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.
| Allegro CL version 8.2 Unrevised from 8.1 to 8.2. 8.1 version |