2 <!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.2//EN"
3 "http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd" [
4 <!ENTITY % myents SYSTEM "entities.inc">
8 <!-- Functional Data Manipulation Language -->
9 <reference id="ref-fdml">
10 <title>Functional Data Manipulation Language (FDML)</title>
13 The functional data manipulation interface provided by &clsql;
14 includes functions for inserting, updating and deleting records
15 in existing database tables and executing SQL queries and
16 statements with the results of queries returned as Lisp types.
17 SQL statements expressed as strings may be executed with the
18 <link linkend="query"><function>query</function></link> and
20 linkend="execute-command"><function>execute-command</function></link>
22 linkend="select"><function>select</function></link> function, on
23 the other hand, allows for the construction of queries in Lisp
24 using the symbolic SQL syntax. Finally, iterative manipulation
25 of query results is supported by <link
26 linkend="do-query"><function>do-query</function></link>, <link
27 linkend="map-query"><function>map-query</function></link> and an
28 extended clause for the <link
29 linkend="loop-tuples"><function>loop</function></link> macro.
33 <!-- Caching table queries -->
35 <refentry id="cache-table-queries-default">
37 <refentrytitle>*CACHE-TABLE-QUERIES-DEFAULT*</refentrytitle>
40 <refname>*CACHE-TABLE-QUERIES-DEFAULT*</refname>
41 <refpurpose>Specifies the default behaviour for caching of
42 attribute types.</refpurpose>
43 <refclass>Variable</refclass>
46 <title>Value Type</title>
48 A valid argument to the <parameter>action</parameter>
49 parameter of <function>cache-table-queries</function>,
51 <simplelist type="inline">
53 <member>&nil;</member>
54 <member><symbol>:flush</symbol></member>
59 <title>Initial Value</title>
60 <para><symbol>nil</symbol></para>
63 <title>Description</title>
65 Specifies the default behaivour for caching of attribute
66 types. Meaningful values are &t;, &nil; and
67 <symbol>:flush</symbol> as described for the
68 <parameter>action</parameter> argument to
69 <function>cache-table-queries</function>.
73 <title>Examples</title>
77 <title>Affected By</title>
81 <title>See Also</title>
83 <member><link linkend="cache-table-queries"><function>cache-table-queries</function></link></member>
92 <refentry id="cache-table-queries">
94 <refentrytitle>CACHE-TABLE-QUERIES</refentrytitle>
97 <refname>CACHE-TABLE-QUERIES</refname>
98 <refpurpose>Control the caching of table attribute types.</refpurpose>
99 <refclass>Function</refclass>
102 <title>Syntax</title>
104 <function>cache-table-queries</function> <replaceable>table</replaceable> &key <replaceable>action</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
107 <title>Arguments and Values</title>
110 <term><parameter>table</parameter></term>
113 A string representing a database table, &t; or
114 <symbol>:default</symbol>.
119 <term><parameter>action</parameter></term>
122 &t;, &nil; or <symbol>:flush</symbol>.
127 <term><parameter>database</parameter></term>
130 <glossterm linkend="gloss-database-object">database
131 object</glossterm>. This will default to the value
132 of <symbol>*default-database*</symbol>.</para>
138 <title>Description</title>
139 <para>Controls the caching of attribute type information on the
140 table specified by <parameter>table</parameter> in
141 <parameter>database</parameter> which defaults to
142 <symbol>*default-database*</symbol>. <parameter>action</parameter>
143 specifies the caching behaviour to adopt. If its value is &t;
144 then attribute type information is cached whereas if its value
145 is &nil; then attribute type information is not cached. If
146 <parameter>action</parameter> is <symbol>:flush</symbol> then
147 all existing type information in the cache for
148 <parameter>table</parameter> is removed, but caching is still
149 enabled. <parameter>table</parameter> may be a string
150 representing a table for which the caching action is to be taken
151 while the caching action is applied to all tables if
152 <parameter>table</parameter> is &t;. Alternatively, when
153 <parameter>table</parameter> is <symbol>:default</symbol>, the
154 default caching action specified by
155 <symbol>*cache-table-queries-default*</symbol> is applied to all
156 tables for which a caching action has not been explicitly set.
160 <title>Examples</title>
162 (setf *cache-table-queries-default* t)
168 ([comments] varchar)))
170 (cache-table-queries "foo")
172 (list-attribute-types "foo")
173 => (("id" :INT4 4 NIL 1) ("height" :FLOAT8 8 NIL 1) ("name" :BPCHAR 24 NIL 1)
174 ("comments" :VARCHAR 255 NIL 1))
181 ([comments] (string 100))))
183 (cache-table-queries "foo" :action :flush)
185 (list-attribute-types "foo")
186 => (("id" :INT4 4 NIL 1) ("height" :FLOAT8 8 NIL 1) ("name" :BPCHAR 36 NIL 1)
187 ("comments" :BPCHAR 100 NIL 1))
191 <title>Side Effects</title>
193 The internal attribute cache for
194 <parameter>database</parameter> is modified.
198 <title>Affected by</title>
200 <member><link linkend="cache-table-queries-default"><symbol>*cache-table-queries-default*</symbol></link></member>
204 <title>Exceptional Situations</title>
210 <title>See Also</title>
212 <member><link linkend="cache-table-queries-default"><symbol>*cache-table-queries-default*</symbol></link></member>
223 <refentry id="insert-records">
225 <refentrytitle>INSERT-RECORDS</refentrytitle>
228 <refname>INSERT-RECORDS</refname>
229 <refpurpose>Insert tuples of data into a database table.</refpurpose>
230 <refclass>Function</refclass>
233 <title>Syntax</title>
235 <function>insert-records</function> &key <replaceable>into</replaceable> <replaceable>attributes</replaceable> <replaceable>values</replaceable> <replaceable>av-pairs</replaceable> <replaceable>query</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
238 <title>Arguments and Values</title>
241 <term><parameter>into</parameter></term>
244 A string, symbol or symbolic SQL expression representing
245 the name of a table existing in
246 <parameter>database</parameter>.
251 <term><parameter>attributes</parameter></term>
254 A list of attribute identifiers or &nil;.
259 <term><parameter>values</parameter></term>
262 A list of attribute values or &nil;.
267 <term><parameter>av-pairs</parameter></term>
270 A list of attribute identifier/value pairs or &nil;.
275 <term><parameter>query</parameter></term>
278 A query expression or &nil;.
283 <term><parameter>database</parameter></term>
286 <glossterm linkend="gloss-database-object">database
287 object</glossterm>. This will default to the value
288 of <symbol>*default-database*</symbol>.</para>
294 <title>Description</title>
296 Inserts records into the table specified by
297 <parameter>into</parameter> in <parameter>database</parameter>
298 which defaults to <symbol>*default-database*</symbol>.
301 There are five ways of specifying the values inserted into
302 each row. In the first <parameter>values</parameter> contains
303 a list of values to insert and
304 <parameter>attributes</parameter>,
305 <parameter>av-pairs</parameter> and
306 <parameter>query</parameter> are &nil;. This can be used when
307 values are supplied for all attributes in
308 <parameter>into</parameter>. In the second,
309 <parameter>attributes</parameter> is a list of column names,
310 <parameter>values</parameter> is a corresponding list of
311 values and <parameter>av-pairs</parameter> and
312 <parameter>query</parameter> are &nil;. In the third,
313 <parameter>attributes</parameter>,
314 <parameter>values</parameter> and <parameter>query</parameter>
315 are &nil; and <parameter>av-pairs</parameter> is an alist of
316 (attribute value) pairs. In the fourth,
317 <parameter>values</parameter>, <parameter>av-pairs</parameter>
318 and <parameter>attributes</parameter> are &nil; and
319 <parameter>query</parameter> is a symbolic SQL query
320 expression in which the selected columns also exist in
321 <parameter>into</parameter>. In the fifth method,
322 <parameter>values</parameter> and
323 <parameter>av-pairs</parameter> are nil and
324 <parameter>attributes</parameter> is a list of column names
325 and <parameter>query</parameter> is a symbolic SQL query
326 expression which returns values for the specified columns.
330 <title>Examples</title>
332 (select [first-name] [last-name] [email]
334 :where [= [emplid] 11]
337 (insert-records :into [employee]
338 :attributes '(emplid groupid first_name last_name email
339 ecompanyid managerid)
340 :values '(11 1 "Yuri" "Gagarin" "gagarin@soviet.org"
343 (select [first-name] [last-name] [email]
345 :where [= [emplid] 11]
347 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
351 <title>Side Effects</title>
353 Modifications are made to the underlying database.
357 <title>Affected by</title>
363 <title>Exceptional Situations</title>
365 An error of type <symbol>sql-database-data-error</symbol> is
366 signalled if <parameter>table</parameter> is not an existing
367 table in <parameter>database</parameter> or if the specified
368 attributes are not found.
372 <title>See Also</title>
374 <member><link linkend="update-records"><function>update-records</function></link></member>
375 <member><link linkend="delete-records"><function>delete-records</function></link></member>
386 <refentry id="update-records">
388 <refentrytitle>UPDATE-RECORDS</refentrytitle>
391 <refname>UPDATE-RECORDS</refname>
392 <refpurpose>Updates the values of existing records.</refpurpose>
393 <refclass>Function</refclass>
396 <title>Syntax</title>
398 <function>update-records</function> <replaceable>table</replaceable> &key <replaceable>attributes</replaceable> <replaceable>values</replaceable> <replaceable>av-pairs</replaceable> <replaceable>where</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
401 <title>Arguments and Values</title>
404 <term><parameter>table</parameter></term>
407 A string, symbol or symbolic SQL expression representing
408 the name of a table existing in
409 <parameter>database</parameter>.
414 <term><parameter>attributes</parameter></term>
417 A list of attribute identifiers or &nil;.
422 <term><parameter>values</parameter></term>
425 A list of attribute values or &nil;.
430 <term><parameter>av-pairs</parameter></term>
433 A list of attribute identifier/value pairs or &nil;.
438 <term><parameter>where</parameter></term>
441 A symbolic SQL expression.
446 <term><parameter>database</parameter></term>
449 <glossterm linkend="gloss-database-object">database
450 object</glossterm>. This will default to the value
451 of <symbol>*default-database*</symbol>.</para>
457 <title>Description</title>
459 Updates the attribute values of existing records satsifying
460 the SQL expression <parameter>where</parameter> in the table
461 specified by <parameter>table</parameter> in
462 <parameter>database</parameter> which defaults to
463 <symbol>*default-database*</symbol>.
466 There are three ways of specifying the values to update for
467 each row. In the first, <parameter>values</parameter> contains
468 a list of values to use in the update and
469 <parameter>attributes</parameter> and
470 <parameter>av-pairs</parameter> are &nil;. This can be used
471 when values are supplied for all attributes in
472 <parameter>table</parameter>. In the second,
473 <parameter>attributes</parameter> is a list of column names,
474 <parameter>values</parameter> is a corresponding list of
475 values and <parameter>av-pairs</parameter> is &nil;. In the
476 third, <parameter>attributes</parameter> and
477 <parameter>values</parameter> are &nil; and
478 <parameter>av-pairs</parameter> is an alist of (attribute
483 <title>Examples</title>
485 (select [first-name] [last-name] [email]
487 :where [= [emplid] 1]
489 => (("Vladimir" "Lenin" "lenin@soviet.org"))
490 (update-records [employee]
491 :av-pairs'((first_name "Yuri")
492 (last_name "Gagarin")
493 (email "gagarin@soviet.org"))
494 :where [= [emplid] 1])
496 (select [first-name] [last-name] [email]
498 :where [= [emplid] 1]
500 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
504 <title>Side Effects</title>
506 Modifications are made to the underlying database.
510 <title>Affected by</title>
516 <title>Exceptional Situations</title>
518 An error of type <symbol>sql-database-data-error</symbol> is
519 signalled if <parameter>table</parameter> is not an existing
520 table in <parameter>database</parameter>, if the specified
521 attributes are not found or if the SQL statement resulting
522 from the symbolic expression <parameter>where</parameter> does
523 not return a Boolean value.
525 <para>If the execution of the SQL query leads to any errors, an
526 error of type <errortype>sql-database-error</errortype> is
530 <title>See Also</title>
533 <member><link linkend="insert-records"><function>insert-records</function></link></member>
534 <member><link linkend="delete-records"><function>delete-records</function></link></member>
546 <refentry id="delete-records">
548 <refentrytitle>DELETE-RECORDS</refentrytitle>
551 <refname>DELETE-RECORDS</refname>
552 <refpurpose>Delete records from a database table.</refpurpose>
553 <refclass>Function</refclass>
556 <title>Syntax</title>
558 <function>delete-records</function> &key <replaceable>from</replaceable> <replaceable>where</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
561 <title>Arguments and Values</title>
564 <term><parameter>from</parameter></term>
567 A string, symbol or symbolic SQL expression representing
568 the name of a table existing in
569 <parameter>database</parameter>.
574 <term><parameter>where</parameter></term>
577 A symbolic SQL expression.
582 <term><parameter>database</parameter></term>
585 <glossterm linkend="gloss-database-object">database
586 object</glossterm>. This will default to the value
587 of <symbol>*default-database*</symbol>.</para>
593 <title>Description</title>
594 <para>Deletes records satisfying the SQL expression
595 <parameter>where</parameter> from the table specified by
596 <parameter>from</parameter> in <parameter>database</parameter>
597 specifies a database which defaults to
598 <symbol>*default-database*</symbol>.
602 <title>Examples</title>
604 (select [first-name] [last-name] [email]
606 :where [= [emplid] 11]
608 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
609 (delete-records :from [employee] :where [= [emplid] 11])
611 (select [first-name] [last-name] [email]
613 :where [= [emplid] 11]
619 <title>Side Effects</title>
621 Modifications are made to the underlying database.
625 <title>Affected by</title>
631 <title>Exceptional Situations</title>
633 An error of type <symbol>sql-database-data-error</symbol> is
634 signalled if <parameter>from</parameter> is not an existing
635 table in <parameter>database</parameter> or if the SQL
636 statement resulting from the symbolic expression
637 <parameter>where</parameter> does not return a Boolean value.
641 <title>See Also</title>
644 <member><link linkend="insert-records"><function>insert-records</function></link></member>
645 <member><link linkend="update-records"><function>update-records</function></link></member>
658 <!-- executing SQL commands and queries -->
660 <refentry id="execute-command">
662 <refentrytitle>EXECUTE-COMMAND</refentrytitle>
665 <refname>EXECUTE-COMMAND</refname>
666 <refpurpose>Execute an SQL command which returns no values.</refpurpose>
667 <refclass>Generic Function</refclass>
670 <title>Syntax</title>
672 <function>execute-command</function> <replaceable>sql-expression</replaceable> &key <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
675 <title>Arguments and Values</title>
678 <term><parameter>sql-expression</parameter></term>
680 <para>An <glossterm linkend="gloss-sql-expression">sql
681 expression</glossterm> that represents an SQL
682 statement which will return no values.</para>
686 <term><parameter>database</parameter></term>
689 <glossterm linkend="gloss-database-object">database
690 object</glossterm>. This will default to the value
691 of <symbol>*default-database*</symbol>.</para>
693 </varlistentry></variablelist>
696 <title>Description</title>
697 <para>Executes the SQL command
698 <parameter>sql-expression</parameter>, which may be a symbolic
699 SQL expression or a string representing any SQL statement apart
700 from a query, on the supplied <parameter>database</parameter>
701 which defaults to <symbol>*default-database*</symbol>.
705 <title>Examples</title>
707 (execute-command "create table eventlog (time char(30),event char(70))")
710 (execute-command "create table eventlog (time char(30),event char(70))")
712 >> While accessing database #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {480B2B6D}>
713 >> with expression "create table eventlog (time char(30),event char(70))":
714 >> Error NIL: ERROR: amcreate: eventlog relation already exists
718 >> 0: [ABORT] Return to Top-Level.
720 >> Debug (type H for help)
722 >> (CLSQL-POSTGRESQL::|(PCL::FAST-METHOD DATABASE-EXECUTE-COMMAND (T POSTGRESQL-DATABASE))|
725 >> #<unavailable-arg>
726 >> #<unavailable-arg>)
727 >> Source: (ERROR 'SQL-DATABASE-ERROR :DATABASE DATABASE :EXPRESSION ...)
730 (execute-command "drop table eventlog")
735 <title>Side Effects</title>
736 <para>Whatever effects the execution of the SQL statement has
737 on the underlying database, if any.</para>
740 <title>Affected by</title>
744 <title>Exceptional Situations</title>
745 <para>If the execution of the SQL statement leads to any errors,
746 an error of type <errortype>sql-database-error</errortype> is
750 <title>See Also</title>
753 <member><link linkend="query"><function>query</function></link></member>
764 <refentry id="query">
766 <refentrytitle>QUERY</refentrytitle>
769 <refname>QUERY</refname>
770 <refpurpose>Execute an SQL query and return the tuples as a
772 <refclass>Generic Function</refclass>
775 <title>Syntax</title>
777 <function>query</function> <replaceable>query-expression</replaceable> &key <replaceable>database</replaceable> <replaceable>result-types</replaceable> <replaceable>flatp</replaceable> <replaceable>field-names</replaceable> => <returnvalue>result</returnvalue></synopsis>
780 <title>Arguments and Values</title>
783 <term><parameter>query-expression</parameter></term>
785 <para>An <glossterm linkend="gloss-sql-expression">sql
786 expression</glossterm> that represents an SQL
787 query which is expected to return a (possibly empty)
792 <term><parameter>database</parameter></term>
795 <glossterm linkend="gloss-database-object">database
796 object</glossterm>. This will default to the value
797 of <symbol>*default-database*</symbol>.</para>
801 <term><parameter>flatp</parameter></term>
803 <para>A Boolean whose default value is &nil;.</para>
807 <term><parameter>result-types</parameter></term>
810 <glossterm linkend="gloss-field-types">field type
811 specifier</glossterm>. The default is <symbol>:auto</symbol>;.
814 The purpose of this argument is cause &clsql; to
815 import SQL numeric fields into numeric Lisp objects
816 rather than strings. This reduces the cost of
817 allocating a temporary string and the &clsql; users'
818 inconvenience of converting number strings into number
822 A value of <symbol>:auto</symbol> causes &clsql;
823 to automatically convert SQL fields into a
824 numeric format where applicable. The default value of
825 &nil; causes all fields to be returned as strings
826 regardless of the SQL type. Otherwise a list is expected
827 which has a element for each field that specifies the
828 conversion. Valid type identifiers are:
829 <simplelist type="vert">
830 <member><symbol>:int</symbol> Field is imported as a
831 signed integer, from 8-bits to 64-bits depending
834 <member><symbol>:double</symbol> Field is imported as a
837 <member><symbol>t</symbol> Field is imported as a
841 If the list is shorter than the number of fields, the a
842 value of <symbol>t</symbol> is assumed for the field.
843 If the list is longer than the number of fields, the
844 extra elements are ignored.
849 <term><parameter>field-names</parameter></term>
852 A boolean with a default value of &t;. When &t;, this
853 function returns a second value of a list of field
854 names. When &nil;, this function only returns one value -
860 <term><returnvalue>result</returnvalue></term>
862 <para>A list representing the result set obtained. For
863 each tuple in the result set, there is an element in
864 this list, which is itself a list of all the attribute
865 values in the tuple.</para>
871 <title>Description</title>
873 Executes the SQL query expression
874 <parameter>query-expression</parameter>, which may be an SQL
875 expression or a string, on the supplied
876 <parameter>database</parameter> which defaults to
877 <symbol>*default-database*</symbol>. <parameter>result-types</parameter>
878 is a list of symbols which specifies the lisp type for each
879 field returned by <parameter>query-expression</parameter>.
882 If <parameter>result-types</parameter> is &nil; all results
883 are returned as strings whereas the default value of
884 <symbol>:auto</symbol> means that the lisp types are
885 automatically computed for each field.
888 <parameter>field-names</parameter> is &t; by default which
889 means that the second value returned is a list of strings
890 representing the columns selected by
891 <parameter>query-expression</parameter>. If
892 <parameter>field-names</parameter> is &nil;, the list of column
893 names is not returned as a second value.
896 <parameter>flatp</parameter> has a default value of &nil;
897 which means that the results are returned as a list of
898 lists.If FLATP is &t; and only one result is returned for each
899 record selected by <parameter>query-expression</parameter>,
900 the results are returned as elements of a list.
904 <title>Examples</title>
906 (query "select emplid,first_name,last_name,height from employee where emplid = 1")
907 => ((1 "Vladimir" "Lenin" 1.5564661d0)),
908 ("emplid" "first_name" "last_name" "height")
910 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
912 => ((1 "Vladimir" "Lenin" 1.5564661d0))
914 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
917 => (("1" "Vladimir" "Lenin" "1.5564661"))
919 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
921 :result-types '(:int t t :double))
922 => ((1 "Vladimir" "Lenin" 1.5564661))
924 (query "select last_name from employee where emplid > 5" :flatp t)
925 => ("Andropov" "Chernenko" "Gorbachev" "Yeltsin" "Putin"),
928 (query "select last_name from employee where emplid > 10"
935 <title>Side Effects</title>
936 <para>Whatever effects the execution of the SQL query has
937 on the underlying database, if any.</para>
940 <title>Affected by</title>
944 <title>Exceptional Situations</title>
945 <para>If the execution of the SQL query leads to any errors, an
946 error of type <errortype>sql-database-error</errortype> is
950 <title>See Also</title>
952 <member><link linkend="execute-command"><function>execute-command</function></link></member>
953 <member><link linkend="print-query"><function>print-query</function></link></member>
954 <member><link linkend="do-query"><function>do-query</function></link></member>
955 <member><link linkend="map-query"><function>map-query</function></link></member>
956 <member><link linkend="loop-tuples"><function>loop</function></link></member>
957 <member><link linkend="select"><function>select</function></link></member>
962 <para>The <parameter>field-names</parameter> and
963 <parameter>result-types</parameter> keyword arguments are a
964 &clsql; extension.</para>
968 <refentry id="print-query">
970 <refentrytitle>PRINT-QUERY</refentrytitle>
973 <refname>PRINT-QUERY</refname>
974 <refpurpose>Prints a tabular report of query results.</refpurpose>
975 <refclass>Function</refclass>
978 <title>Syntax</title>
980 <function>print-query</function> <replaceable>query-expression</replaceable> &key <replaceable>titles</replaceable> <replaceable>formats</replaceable> <replaceable>sizes</replaceable> <replaceable>stream</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
983 <title>Arguments and Values</title>
986 <term><parameter>query-expression</parameter></term>
988 <para>An <glossterm linkend="gloss-sql-expression">sql
989 expression</glossterm> that represents an SQL
990 query which is expected to return a (possibly empty)
995 <term><parameter>database</parameter></term>
998 <glossterm linkend="gloss-database-object">database
999 object</glossterm>. This will default to the value
1000 of <symbol>*default-database*</symbol>.</para>
1004 <term><parameter>titles</parameter></term>
1007 A list of strings or &nil; which is the default value.
1012 <term><parameter>formats</parameter></term>
1015 A list of strings, &nil; or &t; which is the default value.
1020 <term><parameter>sizes</parameter></term>
1023 A list of numbers, &nil; or &t; which is the default value.
1028 <term><parameter>stream</parameter></term>
1031 An output stream or &t; which is the default value.
1038 <title>Description</title>
1039 <para>Prints a tabular report of the results returned by the SQL
1040 query <parameter>query-expression</parameter>, which may be a
1041 symbolic SQL expression or a string, in
1042 <parameter>database</parameter> which defaults to
1043 <symbol>*default-database*</symbol>. The report is printed onto
1044 <parameter>stream</parameter> which has a default value of &t;
1045 which means that <symbol>*standard-output*</symbol> is used. The
1046 <parameter>title</parameter> argument, which defaults to &nil;,
1047 allows the specification of a list of strings to use as column
1048 titles in the tabular output. <parameter>sizes</parameter>
1049 accepts a list of column sizes, one for each column selected by
1050 <parameter>query-expression</parameter>, to use in formatting
1051 the tabular report. The default value of &t; means that minimum
1052 sizes are computed. <parameter>formats</parameter> is a list of
1053 format strings to be used for printing each column selected by
1054 <parameter>query-expression</parameter>. The default value of
1055 <parameter>formats</parameter> is &t; meaning that
1056 <symbol>~A</symbol> is used to format all columns or
1057 <symbol>~VA</symbol> if column sizes are used.
1061 <title>Examples</title>
1063 (print-query [select [emplid] [first-name] [last-name] [email]
1065 :where [< [emplid] 5]]
1066 :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
1067 ID FORENAME SURNAME EMAIL
1068 1 Vladimir Lenin lenin@soviet.org
1069 2 Josef Stalin stalin@soviet.org
1070 3 Leon Trotsky trotsky@soviet.org
1071 4 Nikita Kruschev kruschev@soviet.org
1074 (print-query "select emplid,first_name,last_name,email from employee where emplid >= 5"
1075 :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
1076 ID FORENAME SURNAME EMAIL
1077 5 Leonid Brezhnev brezhnev@soviet.org
1078 6 Yuri Andropov andropov@soviet.org
1079 7 Konstantin Chernenko chernenko@soviet.org
1080 8 Mikhail Gorbachev gorbachev@soviet.org
1081 9 Boris Yeltsin yeltsin@soviet.org
1082 10 Vladimir Putin putin@soviet.org
1087 <title>Side Effects</title>
1093 <title>Affected by</title>
1099 <title>Exceptional Situations</title>
1101 If the execution of the SQL query leads to any errors, an
1102 error of type <errortype>sql-database-error</errortype> is
1107 <title>See Also</title>
1109 <member><link linkend="query"><function>query</function></link></member>
1110 <member><link linkend="do-query"><function>do-query</function></link></member>
1111 <member><link linkend="map-query"><function>map-query</function></link></member>
1112 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1113 <member><link linkend="select"><function>select</function></link></member>
1117 <title>Notes</title>
1124 <refentry id="select">
1126 <refentrytitle>SELECT</refentrytitle>
1129 <refname>SELECT</refname>
1130 <refpurpose>Executes a query given the supplied constraints.</refpurpose>
1131 <refclass>Function</refclass>
1134 <title>Syntax</title>
1136 <function>select</function> &rest <replaceable>identifiers</replaceable> &key <replaceable>all</replaceable> <replaceable>distinct</replaceable> <replaceable>from</replaceable> <replaceable>group-by</replaceable> <replaceable>having</replaceable> <replaceable>limit</replaceable> <replaceable>offset</replaceable> <replaceable>order-by</replaceable> <replaceable>set-operation</replaceable> <replaceable>where</replaceable> <replaceable>result-types</replaceable> <replaceable>field-names</replaceable> <replaceable>flatp</replaceable> <replaceable>refresh</replaceable> <replaceable>caching</replaceable> <replaceable>database</replaceable> => <returnvalue>result</returnvalue></synopsis>
1139 <title>Arguments and Values</title>
1142 <term><parameter>identifiers</parameter></term>
1145 A set of <glossterm linkend="gloss-sql-expression">sql
1146 expressions</glossterm> each of which indicates a column
1152 <term><parameter>all</parameter></term>
1160 <term><parameter>distinct</parameter></term>
1168 <term><parameter>from</parameter></term>
1171 One or more SQL expression representing tables.
1176 <term><parameter>group-by</parameter></term>
1184 <term><parameter>having</parameter></term>
1192 <term><parameter>limit</parameter></term>
1195 A non-negative integer.
1200 <term><parameter>offset</parameter></term>
1203 A non-negative integer.
1208 <term><parameter>order-by</parameter></term>
1216 <term><parameter>set-operation</parameter></term>
1224 <term><parameter>where</parameter></term>
1232 <term><parameter>database</parameter></term>
1235 <glossterm linkend="gloss-database-object">database
1236 object</glossterm>. This will default to the value
1237 of <symbol>*default-database*</symbol>.</para>
1241 <term><parameter>flatp</parameter></term>
1243 <para>A Boolean whose default value is &nil;.</para>
1247 <term><parameter>result-types</parameter></term>
1250 <glossterm linkend="gloss-field-types">field type
1251 specifier</glossterm>. The default is <symbol>:auto</symbol>.
1254 The purpose of this argument is cause &clsql; to
1255 import SQL numeric fields into numeric Lisp objects
1256 rather than strings. This reduces the cost of
1257 allocating a temporary string and the &clsql; users'
1258 inconvenience of converting number strings into number
1262 A value of <symbol>:auto</symbol> causes &clsql;
1263 to automatically convert SQL fields into a
1264 numeric format where applicable. The default value of
1265 &nil; causes all fields to be returned as strings
1266 regardless of the SQL type. Otherwise a list is expected
1267 which has a element for each field that specifies the
1268 conversion. Valid type identifiers are:
1269 <simplelist type="vert">
1270 <member><symbol>:int</symbol> Field is imported as a
1271 signed integer, from 8-bits to 64-bits depending
1272 upon the field type.
1274 <member><symbol>:double</symbol> Field is imported as a
1275 double-float number.
1277 <member><symbol>t</symbol> Field is imported as a
1281 If the list is shorter than the number of fields, the a
1282 value of <symbol>t</symbol> is assumed for the field.
1283 If the list is longer than the number of fields, the
1284 extra elements are ignored.
1289 <term><parameter>field-names</parameter></term>
1292 A boolean with a default value of &t;. When &t;, this
1293 function returns a second value of a list of field
1294 names. When &nil;, this function only returns one value -
1300 <term><parameter>refresh</parameter></term>
1303 This value is only considered when CLOS objects are being
1304 selected. A boolean with a default value of &nil;. When
1305 the value of the <varname>caching</varname> keyword is
1306 &t;, a second equivalent <function>select</function> call
1307 will return the same view class instance objects. When
1308 <varname>refresh</varname> is &t;, then slots of the
1309 existing instances are updated as necessary. In such
1310 cases, you may wish to override the hook
1311 <function>instance-refresh</function>.
1316 <term><parameter>caching</parameter></term>
1319 This value is only considered when CLOS objects are being
1320 selected. A boolean with a default value of
1321 <varname>*default-caching*</varname>. &clsql; caches
1322 objects in accordance with the &commonsql; interface: a
1323 second equivalent <function>select</function> call will
1324 return the same view class instance objects.
1329 <term><parameter>result</parameter></term>
1332 A list representing the result set obtained. For each
1333 tuple in the result set, there is an element in this
1334 list, which is itself a list of all the attribute values
1342 <title>Description</title>
1344 Executes a query on <parameter>database</parameter>, which has
1345 a default value of <symbol>*default-database*</symbol>,
1346 specified by the SQL expressions supplied using the remaining
1347 arguments in <parameter>args</parameter>. The
1348 <function>select</function> function can be used to generate
1349 queries in both functional and object oriented contexts.
1352 In the functional case, the required arguments specify the
1353 columns selected by the query and may be symbolic SQL
1354 expressions or strings representing attribute
1355 identifiers. Type modified identifiers indicate that the
1356 values selected from the specified column are converted to the
1357 specified lisp type. The keyword arguments
1358 <parameter>all</parameter>, <parameter>distinct</parameter>,
1359 <parameter>from</parameter>, <parameter>group-by</parameter>,
1360 <parameter>having</parameter>, <parameter>limit</parameter>,
1361 <parameter>offset</parameter>, <parameter>order-by</parameter>,
1362 <parameter>set-operation</parameter> and
1363 <parameter>where</parameter> are used to specify, using the
1364 symbolic SQL syntax, the corresponding components of the SQL
1365 query generated by the call to
1366 <function>select</function>.
1369 <parameter>result-types</parameter> is a list of symbols which
1370 specifies the lisp type for each field returned by the
1371 query. If <parameter>result-types</parameter> is &nil; all
1372 results are returned as strings whereas the default value of
1373 <symbol>:auto</symbol> means that the lisp types are
1374 automatically computed for each
1375 field. <parameter>field-names</parameter> is &t; by default
1376 which means that the second value returned is a list of
1377 strings representing the columns selected by the query. If
1378 <parameter>field-names</parameter> is &nil;, the list of
1379 column names is not returned as a second value.
1382 In the object oriented case, the required arguments to
1383 <function>select</function> are symbols denoting View Classes
1384 which specify the database tables to query. In this case,
1385 <function>select</function> returns a list of View Class
1386 instances whose slots are set from the attribute values of the
1387 records in the specified table. <symbol>Slot-value</symbol> is
1388 a legal operator which can be employed as part of the symbolic
1389 SQL syntax used in the <parameter>where</parameter> keyword
1390 argument to <function>select</function>.
1391 <parameter>refresh</parameter> is &nil; by default which means
1392 that the View Class instances returned are retrieved from a
1393 cache if an equivalent call to <function>select</function> has
1394 previously been issued. If <parameter>refresh</parameter> is
1395 true, the View Class instances returned are updated as
1396 necessary from the database and the generic function
1397 <function>instance-refreshed</function> is called to perform
1398 any necessary operations on the updated instances.
1401 In both object oriented and functional contexts,
1402 <parameter>flatp</parameter> has a default value of &nil;
1403 which means that the results are returned as a list of
1404 lists. If <parameter>flatp</parameter> is t and only one
1405 result is returned for each record selected in the query, the
1406 results are returned as elements of a list.
1410 <title>Examples</title>
1412 (select [first-name] :from [employee] :flatp t :distinct t
1415 :order-by [first-name])
1416 => ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladimir"
1419 (select [first-name] [count [*]] :from [employee]
1421 :group-by [first-name]
1422 :order-by [first-name]
1424 => (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
1425 ("Mikhail" "1") ("Nikita" "1") ("Vladimir" "2") ("Yuri" "1"))
1427 (select [last-name] :from [employee]
1428 :where [like [email] "%org"]
1429 :order-by [last-name]
1433 => ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
1434 "Stalin" "Trotsky" "Yeltsin")
1436 (select [max [emplid]] :from [employee]
1439 :result-types :auto)
1442 (select [avg [height]] :from [employee] :flatp t :field-names nil)
1445 (select [emplid] [last-name] :from [employee] :where [= [emplid] 1])
1447 ("emplid" "last_name")
1449 (select [emplid :string] :from [employee]
1450 :where [= 1 [emplid]]
1455 (select [emplid] :from [employee] :order-by [emplid]
1456 :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]]
1461 (select [emplid] :from [employee]
1462 :where [in [emplid] '(1 2 3 4)]
1468 (select [emplid] :from [employee]
1476 (select [first-name] [last-name] :from [employee]
1478 :order-by '(([first-name] :asc) ([last-name] :desc)))
1479 => (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
1480 ("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
1481 ("Nikita" "Kruschev") ("Vladimir" "Putin") ("Vladimir" "Lenin")
1482 ("Yuri" "Andropov"))
1484 (select [last-name] :from [employee]
1485 :set-operation [union [select [first-name] :from [employee]
1486 :order-by [last-name]]]
1490 => ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
1491 "Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
1492 "Trotsky" "Vladimir" "Yeltsin" "Yuri")
1496 <title>Side Effects</title>
1497 <para>Whatever effects the execution of the SQL query has on
1498 the underlying database, if any.</para>
1501 <title>Affected by</title>
1507 <title>Exceptional Situations</title>
1509 If the execution of the SQL query leads to any errors, an
1510 error of type <errortype>sql-database-error</errortype> is
1515 <title>See Also</title>
1517 <member><link linkend="query"><function>query</function></link></member>
1518 <member><link linkend="print-query"><function>print-query</function></link></member>
1519 <member><link linkend="do-query"><function>do-query</function></link></member>
1520 <member><link linkend="map-query"><function>map-query</function></link></member>
1521 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1522 <member><link linkend="instance-refreshed"><function>instance-refreshed</function></link></member>
1526 <title>Notes</title>
1528 The <function>select</function> function is actually
1529 implemented in &clsql; with a single
1530 <symbol>&rest</symbol> parameter (which is subsequently
1531 destructured) rather than the keyword parameters presented
1532 here for the purposes of exposition. This means that incorrect
1533 or missing keywords or values may not trigger errors in the
1534 way that they would if <function>select</function> had been
1535 defined using keyword arguments.
1538 The <parameter>field-names</parameter> and
1539 <parameter>result-types</parameter> keyword arguments are a
1543 <parameter>select</parameter> is common across the functional
1544 and object-oriented data manipulation languages.
1550 <!-- iteration and mapping -->
1552 <refentry id="do-query">
1554 <refentrytitle>DO-QUERY</refentrytitle>
1557 <refname>DO-QUERY</refname>
1558 <refpurpose>Iterate over all the tuples of a query.</refpurpose>
1559 <refclass>Macro</refclass>
1562 <title>Syntax</title>
1564 <function>do-query</function> ((&rest <replaceable>args</replaceable>) <replaceable>query-expression</replaceable> &key <replaceable>database</replaceable> <replaceable>result-types</replaceable> &body <replaceable>body</replaceable> => <returnvalue>result</returnvalue></synopsis>
1567 <title>Arguments and Values</title>
1570 <term><parameter>args</parameter></term>
1572 <para>A list of variable names.</para>
1576 <term><parameter>query-expression</parameter></term>
1578 <para>An <glossterm linkend="gloss-sql-expression">sql
1579 expression</glossterm> that represents an SQL
1580 query which is expected to return a (possibly empty)
1581 result set, where each tuple has as many attributes as
1582 <parameter>function</parameter> takes arguments.</para>
1586 <term><parameter>database</parameter></term>
1589 <glossterm linkend="gloss-database-object">database
1590 object</glossterm>. This will default to
1591 <symbol>*default-database*</symbol>.</para>
1595 <term><parameter>result-types</parameter></term>
1598 A <glossterm linkend="gloss-field-types">field type
1599 specifier</glossterm>. The default is &nil;. See <link
1600 linkend="query"><function>query</function></link> for
1601 the semantics of this argument.
1606 <term><parameter>body</parameter></term>
1608 <para>A body of Lisp code, like in a
1609 <function>destructuring-bind</function> form.</para>
1613 <term><parameter>result</parameter></term>
1615 <para>The result of executing <parameter>body</parameter>.</para>
1621 <title>Description</title>
1623 Repeatedly executes <parameter>body</parameter> within a
1624 binding of <parameter>args</parameter> on the fields of each
1625 row selected by the SQL query
1626 <parameter>query-expression</parameter>, which may be a string
1627 or a symbolic SQL expression, in
1628 <parameter>database</parameter> which defaults to
1629 <symbol>*default-database*</symbol>.
1632 The body of code is executed in a block named
1633 <symbol>nil</symbol> which may be returned from prematurely
1634 via <function>return</function> or
1635 <function>return-from</function>. In this case the result of
1636 evaluating the <function>do-query</function> form will be the
1637 one supplied to <function>return</function> or
1638 <function>return-from</function>. Otherwise the result will
1639 be <symbol>nil</symbol>.
1642 The body of code appears also is if wrapped in a
1643 <function>destructuring-bind</function> form, thus allowing
1644 declarations at the start of the body, especially those
1645 pertaining to the bindings of the variables named in
1646 <parameter>args</parameter>.
1649 <parameter>result-types</parameter> is a list of symbols which
1650 specifies the lisp type for each field returned by
1651 <parameter>query-expression</parameter>. If
1652 <parameter>result-types</parameter> is &nil; all results are
1653 returned as strings whereas the default value of
1654 <symbol>:auto</symbol> means that the lisp types are
1655 automatically computed for each field.
1658 <parameter>query-expression</parameter> may be an object query
1659 (i.e., the selection arguments refer to View Classes), in
1660 which case <parameter>args</parameter> are bound to the tuples
1661 of View Class instances returned by the object oriented query.
1665 <title>Examples</title>
1667 (do-query ((salary name) "select salary,name from simple")
1668 (format t "~30A gets $~2,5$~%" name (read-from-string salary)))
1669 >> Mai, Pierre gets $10000.00
1670 >> Hacker, Random J. gets $08000.50
1673 (do-query ((salary name) "select salary,name from simple")
1674 (return (cons salary name)))
1675 => ("10000.00" . "Mai, Pierre")
1678 (do-query ((name) [select [last-name] :from [employee]
1679 :order-by [last-name]])
1682 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1683 "Chernenko" "Brezhnev" "Andropov")
1686 (do-query ((e) [select 'employee :order-by [last-name]])
1687 (push (slot-value e 'last-name) result))
1689 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1690 "Chernenko" "Brezhnev" "Andropov")
1694 <title>Side Effects</title>
1695 <para>Whatever effects the execution of the SQL query has
1696 on the underlying database, if any.</para>
1699 <title>Affected by</title>
1703 <title>Exceptional Situations</title>
1704 <para>If the execution of the SQL query leads to any
1705 errors, an error of type
1706 <errortype>sql-database-error</errortype> is signalled.</para>
1707 <para>If the number of variable names in
1708 <parameter>args</parameter> and the number of attributes in
1709 the tuples in the result set don't match up, an error is
1713 <title>See Also</title>
1715 <member><link linkend="query"><function>query</function></link></member>
1716 <member><link linkend="map-query"><function>map-query</function></link></member>
1717 <member><link linkend="print-query"><function>print-query</function></link></member>
1718 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1719 <member><link linkend="select"><function>select</function></link></member>
1723 <title>Notes</title>
1724 <para>The <parameter>result-types</parameter> keyword argument
1725 is a &clsql; extension.</para>
1727 <parameter>do-query</parameter> is common across the functional
1728 and object-oriented data manipulation languages.
1733 <refentry id="loop-tuples">
1735 <refentrytitle>LOOP</refentrytitle>
1738 <refname>LOOP</refname>
1739 <refpurpose>Extension to Common Lisp
1740 <computeroutput>Loop</computeroutput> to iterate over all the
1741 tuples of a query via a loop clause.</refpurpose>
1742 <refclass>Loop Clause</refclass>
1745 <title>Compatibility</title>
1746 <caution><para><function>loop-for-as-tuples</function> only works with &cmucl;.</para></caution>
1749 <title>Syntax</title>
1750 <synopsis>{as | for} <replaceable>var</replaceable> [<replaceable>type-spec</replaceable>] being {each | the} {record | records | tuple | tuples} {in | of} <replaceable>query</replaceable> [from <replaceable>database</replaceable>]</synopsis>
1753 <title>Arguments and Values</title>
1756 <term><parameter>var</parameter></term>
1758 <para>A <literal>d-var-spec</literal>, as defined in the
1759 grammar for <function>loop</function>-clauses in the ANSI
1760 Standard for Common Lisp. This allows for the usual
1761 loop-style destructuring.</para>
1765 <term><parameter>type-spec</parameter></term>
1767 <para>An optional <literal>type-spec</literal> either
1768 simple or destructured, as defined in the grammar for
1769 <function>loop</function>-clauses in the ANSI Standard for
1774 <term><parameter>query</parameter></term>
1776 <para>An <glossterm linkend="gloss-sql-expression">sql
1777 expression</glossterm> that represents an SQL
1778 query which is expected to return a (possibly empty)
1779 result set, where each tuple has as many attributes as
1780 <parameter>function</parameter> takes arguments.</para>
1784 <term><parameter>database</parameter></term>
1787 <glossterm linkend="gloss-database-object">database
1788 object</glossterm>. This will default to the value
1789 of <symbol>*default-database*</symbol>.</para>
1795 <title>Description</title>
1796 <para>This clause is an iteration driver for
1797 <function>loop</function>, that binds the given variable
1798 (possibly destructured) to the consecutive tuples (which are
1799 represented as lists of attribute values) in the result set
1800 returned by executing the SQL <parameter>query</parameter>
1801 expression on the <parameter>database</parameter>
1804 <parameter>query</parameter> may be an object query (i.e., the
1805 selection arguments refer to View Classes), in which case the
1806 supplied variable is bound to the tuples of View Class
1807 instances returned by the object oriented query.
1811 <title>Examples</title>
1813 (defvar *my-db* (connect '("dent" "newesim" "dent" "dent"))
1816 (loop with time-graph = (make-hash-table :test #'equal)
1817 with event-graph = (make-hash-table :test #'equal)
1818 for (time event) being the tuples of "select time,event from log"
1821 (incf (gethash time time-graph 0))
1822 (incf (gethash event event-graph 0))
1824 (flet ((show-graph (k v) (format t "~40A => ~5D~%" k v)))
1825 (format t "~&Time-Graph:~%===========~%")
1826 (maphash #'show-graph time-graph)
1827 (format t "~&~%Event-Graph:~%============~%")
1828 (maphash #'show-graph event-graph))
1829 (return (values time-graph event-graph)))
1838 >> CLOS Benchmark entry. => 9000
1839 >> Demo Text... => 3
1840 >> doit-text => 3000
1841 >> C Benchmark entry. => 12000
1842 >> CLOS Benchmark entry => 32000
1843 => #<EQUAL hash table, 3 entries {48350A1D}>
1844 => #<EQUAL hash table, 5 entries {48350FCD}>
1846 (loop for (forename surname)
1848 [select [first-name] [last-name] :from [employee]
1849 :order-by [last-name]]
1850 collect (concatenate 'string forename " " surname))
1851 => ("Yuri Andropov" "Leonid Brezhnev" "Konstantin Chernenko" "Mikhail Gorbachev"
1852 "Nikita Kruschev" "Vladimir Lenin" "Vladimir Putin" "Josef Stalin"
1853 "Leon Trotsky" "Boris Yeltsin")
1855 (loop for (e) being the records in
1856 [select 'employee :where [< [emplid] 4] :order-by [emplid]]
1857 collect (slot-value e 'last-name))
1858 => ("Lenin" "Stalin" "Trotsky")
1862 <title>Side Effects</title>
1863 <para>Whatever effects the execution of the SQL query has
1864 on the underlying database, if any.</para>
1867 <title>Affected by</title>
1871 <title>Exceptional Situations</title>
1872 <para>If the execution of the SQL query leads to any
1873 errors, an error of type
1874 <errortype>sql-database-error</errortype> is signalled.</para>
1875 <para>Otherwise, any of the exceptional situations of
1876 <function>loop</function> applies.</para>
1879 <title>See Also</title>
1882 <member><link linkend="query"><function>query</function></link></member>
1883 <member><link linkend="map-query"><function>map-query</function></link></member>
1884 <member><link linkend="do-query"><function>do-query</function></link></member>
1885 <member><link linkend="print-query"><function>print-query</function></link></member>
1886 <member><link linkend="select"><function>select</function></link></member>
1891 <title>Notes</title>
1892 <para>The <parameter>database</parameter> loop keyword is a
1893 &clsql; extension.</para>
1895 The extended <function>loop</function> syntax is common across
1896 the functional and object-oriented data manipulation
1902 <refentry id="map-query">
1904 <refentrytitle>MAP-QUERY</refentrytitle>
1907 <refname>MAP-QUERY</refname>
1908 <refpurpose>Map a function over all the tuples from a
1910 <refclass>Function</refclass>
1913 <title>Syntax</title>
1914 <synopsis><function>map-query</function> <replaceable>output-type-spec</replaceable> <replaceable>function</replaceable> <replaceable>query-expression</replaceable> &key <replaceable>database</replaceable> <replaceable>result-types</replaceable> => <returnvalue>result</returnvalue></synopsis>
1917 <title>Arguments and Values</title>
1920 <term><parameter>output-type-spec</parameter></term>
1922 <para>A sequence type specifier or <symbol>nil</symbol>.</para>
1926 <term><parameter>function</parameter></term>
1928 <para>A function designator.
1929 <parameter>function</parameter> takes a single argument which
1930 is the atom value for a query single with a single column
1931 or is a list of values for a multi-column query.</para>
1935 <term><parameter>query-expression</parameter></term>
1937 <para>An <glossterm linkend="gloss-sql-expression">sql
1938 expression</glossterm> that represents an SQL
1939 query which is expected to return a (possibly empty)
1944 <term><parameter>database</parameter></term>
1947 <glossterm linkend="gloss-database-object">database
1948 object</glossterm>. This will default to the value
1949 of <symbol>*default-database*</symbol>.</para>
1953 <term><parameter>result-types</parameter></term>
1956 A <glossterm linkend="gloss-field-types">field type
1957 specifier</glossterm>. The default is &nil;. See <link
1958 linkend="query"><function>query</function></link> for
1959 the semantics of this argument.
1964 <term><returnvalue>result</returnvalue></term>
1966 <para>If <parameter>output-type-spec</parameter> is a
1967 type specifier other than <symbol>nil</symbol>, then a
1968 sequence of the type it denotes. Otherwise
1969 <symbol>nil</symbol> is returned.</para>
1975 <title>Description</title>
1977 Applies <parameter>function</parameter> to the successive
1978 tuples in the result set returned by executing the SQL
1979 <parameter>query-expression</parameter>. If the
1980 <parameter>output-type-spec</parameter> is
1981 <symbol>nil</symbol>, then the result of each application of
1982 <parameter>function</parameter> is discarded, and
1983 <function>map-query</function> returns <symbol>nil</symbol>.
1984 Otherwise the result of each successive application of
1985 <parameter>function</parameter> is collected in a sequence of
1986 type <parameter>output-type-spec</parameter>, where the jths
1987 element is the result of applying
1988 <parameter>function</parameter> to the attributes of the jths
1989 tuple in the result set. The collected sequence is the result
1990 of the call to <function>map-query</function>.
1993 If the <parameter>output-type-spec</parameter> is a subtype of
1994 <type>list</type>, the result will be a <type>list</type>.
1997 If the <parameter>result-type</parameter> is a subtype of
1998 <type>vector</type>, then if the implementation can determine
1999 the element type specified for the
2000 <parameter>result-type</parameter>, the element type of the
2001 resulting array is the result of
2002 <emphasis>upgrading</emphasis> that element type; or, if the
2003 implementation can determine that the element type is
2004 unspecified (or <symbol>*</symbol>), the element type of the
2005 resulting array is <type>t</type>; otherwise, an error is
2009 If <parameter>result-types</parameter> is &nil; all results
2010 are returned as strings whereas the default value of
2011 <symbol>:auto</symbol> means that the lisp types are
2012 automatically computed for each field.</para>
2014 <parameter>query-expression</parameter> may be an object query
2015 (i.e., the selection arguments refer to View Classes), in
2016 which case the supplied function is applied to the tuples of
2017 View Class instances returned by the object oriented query.
2021 <title>Examples</title>
2023 (map-query 'list #'(lambda (tuple)
2024 (multiple-value-bind (salary name) tuple
2025 (declare (ignorable name))
2026 (read-from-string salary)))
2027 "select salary,name from simple where salary > 8000")
2030 (map-query '(vector double-float)
2032 (multiple-value-bind (salary name) tuple
2033 (declare (ignorable name))
2034 (let ((*read-default-float-format* 'double-float))
2035 (coerce (read-from-string salary) 'double-float))
2036 "select salary,name from simple where salary > 8000")))
2037 => #(10000.0d0 8000.5d0)
2039 => (SIMPLE-ARRAY DOUBLE-FLOAT (2))
2042 (values (map-query nil #'(lambda (tuple)
2043 (multiple-value-bind (salary name) tuple
2044 (push (cons name (read-from-string salary)) list))
2045 "select salary,name from simple where salary > 8000"))
2048 => (("Hacker, Random J." . 8000.5) ("Mai, Pierre" . 10000.0))
2050 (map-query 'vector #'identity
2051 [select [last-name] :from [employee] :flatp t
2052 :order-by [last-name]])
2053 => #("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
2054 "Stalin" "Trotsky" "Yeltsin")
2056 (map-query 'list #'identity
2057 [select [first-name] [last-name] :from [employee]
2058 :order-by [last-name]])
2059 => (("Yuri" "Andropov") ("Leonid" "Brezhnev") ("Konstantin" "Chernenko")
2060 ("Mikhail" "Gorbachev") ("Nikita" "Kruschev") ("Vladimir" "Lenin")
2061 ("Vladimir" "Putin") ("Josef" "Stalin") ("Leon" "Trotsky")
2062 ("Boris" "Yeltsin"))
2064 (map-query 'list #'last-name [select 'employee :order-by [emplid]])
2065 => ("Lenin" "Stalin" "Trotsky" "Kruschev" "Brezhnev" "Andropov" "Chernenko"
2066 "Gorbachev" "Yeltsin" "Putin")
2070 <title>Side Effects</title>
2071 <para>Whatever effects the execution of the SQL query has
2072 on the underlying database, if any.</para>
2075 <title>Affected by</title>
2079 <title>Exceptional Situations</title>
2080 <para>If the execution of the SQL query leads to any
2081 errors, an error of type
2082 <errortype>sql-database-error</errortype> is signalled.</para>
2083 <para>An error of type <errortype>type-error</errortype> must
2084 be signaled if the <parameter>output-type-spec</parameter> is
2085 not a recognizable subtype of <type>list</type>, not a
2086 recognizable subtype of <type>vector</type>, and not
2087 <symbol>nil</symbol>.</para>
2088 <para>An error of type <errortype>type-error</errortype>
2089 should be signaled if
2090 <parameter>output-type-spec</parameter> specifies the number
2091 of elements and the size of the result set is different from
2095 <title>See Also</title>
2097 <member><link linkend="query"><function>query</function></link></member>
2098 <member><link linkend="do-query"><function>do-query</function></link></member>
2099 <member><link linkend="print-query"><function>print-query</function></link></member>
2100 <member><link linkend="loop-tuples"><function>loop</function></link></member>
2101 <member><link linkend="select"><function>select</function></link></member>
2105 <title>Notes</title>
2106 <para>The <parameter>result-types</parameter> keyword argument
2107 is a &clsql; extension.</para>
2109 <parameter>map-query</parameter> is common across the
2110 functional and object-oriented data manipulation languages.