QUERY
Prev | Functional Data Manipulation Language (FDML) | Next

## Name

QUERY — Execute an SQL query and return the tuples as a list.Generic Function

## Syntax

          query query-expression &key database result-types flatp field-names => result

## Arguments and Values

`query-expression`

  An sql expression that represents an SQL query which is expected to return a (possibly empty) result set.

`database`

  A database object. This will default to the value of *default-database*.

`flatp`

  A Boolean whose default value is `NIL`.

`result-types`

  A 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.

result

  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.

## Description

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.

## Examples

    (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

## Side Effects

Whatever effects the execution of the SQL query has on the underlying database, if any.

## Affected by

None.

## Exceptional Situations

If the execution of the SQL query leads to any errors, an error of type sql-database-error is signalled.

## See Also

`execute-command`
`print-query`
`do-query`
`map-query`
`loop`
`select`

## Notes

The `field-names` and `result-types` keyword arguments are a CLSQL extension.

Prev | Up | Next
EXECUTE-COMMAND | Home | PRINT-QUERY