Name

DO-QUERY — Iterate over all the tuples of a query.Macro

Syntax

      do-query ((&rest args) query-expression &key database result-types &body body => result

Arguments and Values

args

A list of variable names.

query-expression

An sql expression that represents an SQL query which is expected to return a (possibly empty) result set, where each tuple has as many attributes as function takes arguments.

database

A database object. This will default to *default-database*.

result-types

A field type specifier. The default is NIL. See query for the semantics of this argument.

body

A body of Lisp code, like in a destructuring-bind form.

result

The result of executing body.

Description

Repeatedly executes body within a binding of args on the fields of each row selected by the SQL query query-expression, which may be a string or a symbolic SQL expression, in database which defaults to *default-database*.

The body of code is executed in a block named nil which may be returned from prematurely via return or return-from. In this case the result of evaluating the do-query form will be the one supplied to return or return-from. Otherwise the result will be nil.

The body of code appears also is if wrapped in a destructuring-bind form, thus allowing declarations at the start of the body, especially those pertaining to the bindings of the variables named in args.

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.

query-expression may be an object query (i.e., the selection arguments refer to View Classes), in which case args are bound to the tuples of View Class instances returned by the object oriented query.

Examples

(do-query ((salary name) "select salary,name from simple")
  (format t "~30A gets $~2,5$~%" name (read-from-string salary)))
>> Mai, Pierre                    gets $10000.00
>> Hacker, Random J.              gets $08000.50
=> NIL

(do-query ((salary name) "select salary,name from simple")
  (return (cons salary name)))
=> ("10000.00" . "Mai, Pierre")

(let ((result '()))
  (do-query ((name) [select [last-name] :from [employee]
                            :order-by [last-name]])
    (push name result))
  result)
=> ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
    "Chernenko" "Brezhnev" "Andropov")

(let ((result '()))
  (do-query ((e) [select 'employee :order-by [last-name]])
    (push (slot-value e 'last-name) result))
  result)
=> ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
    "Chernenko" "Brezhnev" "Andropov")
      

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.

If the number of variable names in args and the number of attributes in the tuples in the result set don't match up, an error is signalled.

See Also

query
map-query
print-query
loop
select

Notes

The result-types keyword argument is a CLSQL extension.

do-query is common across the functional and object-oriented data manipulation languages.