sql

Function

Package: dbi

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 true then it is used for the conversation with its associated database. Otherwise if db is true 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 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 in 6.0, 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.

The documentation is described in introduction.htm and the index is in index.htm.

Created 2000.10.5.