QUERY — Execute an SQL query and return the tuples as a list.Generic Function
query-expressionAn sql expression that represents an SQL query which is expected to return a (possibly empty) result set.
databaseA database object. This will default to the value of *default-database*.
flatpA Boolean whose default value is NIL.
result-typesA field type specifier. The default is :auto;.
The purpose of this argument is cause CLSQL to import SQL numeric fields into numeric Lisp objects rather than strings. This reduces the cost of allocating a temporary string and the CLSQL users' inconvenience of converting number strings into number objects.
A value of :auto causes CLSQL
to automatically convert SQL fields into a
numeric format where applicable. The default value of
NIL causes all fields to be returned as strings
regardless of the SQL type. Otherwise a list is expected
which has a element for each field that specifies the
conversion. Valid type identifiers are:
| :int Field is imported as a signed integer, from 8-bits to 64-bits depending upon the field type. |
| :double Field is imported as a double-float number. |
| t Field is imported as a string. |
If the list is shorter than the number of fields, the a value of t is assumed for the field. If the list is longer than the number of fields, the extra elements are ignored.
field-names
A boolean with a default value of T. When T, this
function returns a second value of a list of field
names. When NIL, this function only returns one value -
the list of rows.
A list representing the result set obtained. For each tuple in the result set, there is an element in this list, which is itself a list of all the attribute values in the tuple.
Executes the SQL query expression
query-expression, which may be an SQL
expression or a string, on the supplied
database which defaults to
*default-database*. result-types
is a list of symbols which specifies the lisp type for each
field returned by query-expression.
If result-types is NIL all results
are returned as strings whereas the default value of
:auto means that the lisp types are
automatically computed for each field.
field-names is T by default which
means that the second value returned is a list of strings
representing the columns selected by
query-expression. If
field-names is NIL, the list of column
names is not returned as a second value.
flatp has a default value of NIL
which means that the results are returned as a list of
lists.If FLATP is T and only one result is returned for each
record selected by query-expression,
the results are returned as elements of a list.
(query "select emplid,first_name,last_name,height from employee where emplid = 1")
=> ((1 "Vladimir" "Lenin" 1.5564661d0)),
("emplid" "first_name" "last_name" "height")
(query "select emplid,first_name,last_name,height from employee where emplid = 1"
:field-names nil)
=> ((1 "Vladimir" "Lenin" 1.5564661d0))
(query "select emplid,first_name,last_name,height from employee where emplid = 1"
:field-names nil
:result-types nil)
=> (("1" "Vladimir" "Lenin" "1.5564661"))
(query "select emplid,first_name,last_name,height from employee where emplid = 1"
:field-names nil
:result-types '(:int t t :double))
=> ((1 "Vladimir" "Lenin" 1.5564661))
(query "select last_name from employee where emplid > 5" :flatp t)
=> ("Andropov" "Chernenko" "Gorbachev" "Yeltsin" "Putin"),
("last_name")
(query "select last_name from employee where emplid > 10"
:flatp t
:field-names nil)
=> NIL