sql

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.

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, the second column, and so on. If there are too few type specifiers given then the t specifier is assumed for those unlabeled. 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.

Condition types

Errors are signaled with the dbi:odbc-error condition or its subclass dbi:odbc-error-width-too-small. See Conditions in aodbc.htm for more information on these conditions.

See aodbc.htm for more information on Allegro ODBC.

Copyright (c) 1998-2019, Franz Inc. Oakland, CA., USA. All rights reserved.
This page was not revised from the 9.0 page.
Created 2015.5.21.