sql

$Revision: 1.1.2.3 $

Function

Package: DBI

Arguments: (sql-statement &key db hstmt types column-names row-count width query)

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.

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 is used for the conversation with its associated database. Otherwise if db is non-nil then it is the database on which the sql statement is run. If db is also nil then the database used is the one that is the value of *default-database*.

Normally the column values in each row are returned a strings (or nil for the null value). You can specify in types the kinds of values you want returned for each column. types is a list of type specifiers from this table

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)
:auto	return the best Lisp type for the given database type. In 5.0.1, only integers and floating-point numbers are converted. All other types are returned as strings, as with the t specifier.

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.

This symbol is associated with the Allegro CL ODBC (AODBC) interface, version 2. Version 2 is new in Allegro CL 5.0.1. It is incompatible with the earlier version 1. Not all customers will have Allegro ODBC version 2. Contact your Franz Inc. sales representative for more information.

Allegro ODBC version 2 is described in aodbc_v2.htm.

The general documentation description is in introduction.htm. The index in index.htm.