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><emphasis>Variable</emphasis> <emphasis role="bold">*CACHE-TABLE-QUERIES-DEFAULT*</emphasis></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><emphasis>Function</emphasis> <emphasis role="bold">CACHE-TABLE-QUERIES</emphasis></refname>
98 <refpurpose>Controls the caching of attribute type information for a database table.</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>
224 <!-- insert/update/delete records -->
226 <refentry id="insert-records">
228 <refentrytitle>INSERT-RECORDS</refentrytitle>
231 <refname><emphasis>Function</emphasis> <emphasis role="bold">INSERT-RECORDS</emphasis></refname>
232 <refpurpose>Insert tuples of data into a database table.</refpurpose>
233 <refclass>Function</refclass>
236 <title>Syntax</title>
238 <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>
241 <title>Arguments and Values</title>
244 <term><parameter>into</parameter></term>
247 A string, symbol or symbolic SQL expression representing
248 the name of a table existing in
249 <parameter>database</parameter>.
254 <term><parameter>attributes</parameter></term>
257 A list of attribute identifiers or &nil;.
262 <term><parameter>values</parameter></term>
265 A list of attribute values or &nil;.
270 <term><parameter>av-pairs</parameter></term>
273 A list of attribute identifier/value pairs or &nil;.
278 <term><parameter>query</parameter></term>
281 A query expression or &nil;.
286 <term><parameter>database</parameter></term>
289 <glossterm linkend="gloss-database-object">database
290 object</glossterm>. This will default to the value
291 of <symbol>*default-database*</symbol>.</para>
297 <title>Description</title>
299 Inserts records into the table specified by
300 <parameter>into</parameter> in <parameter>database</parameter>
301 which defaults to <symbol>*default-database*</symbol>.
304 There are five ways of specifying the values inserted into
305 each row. In the first <parameter>values</parameter> contains
306 a list of values to insert and
307 <parameter>attributes</parameter>,
308 <parameter>av-pairs</parameter> and
309 <parameter>query</parameter> are &nil;. This can be used when
310 values are supplied for all attributes in
311 <parameter>into</parameter>. In the second,
312 <parameter>attributes</parameter> is a list of column names,
313 <parameter>values</parameter> is a corresponding list of
314 values and <parameter>av-pairs</parameter> and
315 <parameter>query</parameter> are &nil;. In the third,
316 <parameter>attributes</parameter>,
317 <parameter>values</parameter> and <parameter>query</parameter>
318 are &nil; and <parameter>av-pairs</parameter> is an alist of
319 (attribute value) pairs. In the fourth,
320 <parameter>values</parameter>, <parameter>av-pairs</parameter>
321 and <parameter>attributes</parameter> are &nil; and
322 <parameter>query</parameter> is a symbolic SQL query
323 expression in which the selected columns also exist in
324 <parameter>into</parameter>. In the fifth method,
325 <parameter>values</parameter> and
326 <parameter>av-pairs</parameter> are nil and
327 <parameter>attributes</parameter> is a list of column names
328 and <parameter>query</parameter> is a symbolic SQL query
329 expression which returns values for the specified columns.
333 <title>Examples</title>
335 (select [first-name] [last-name] [email]
337 :where [= [emplid] 11]
340 (insert-records :into [employee]
341 :attributes '(emplid groupid first_name last_name email
342 ecompanyid managerid)
343 :values '(11 1 "Yuri" "Gagarin" "gagarin@soviet.org"
346 (select [first-name] [last-name] [email]
348 :where [= [emplid] 11]
350 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
354 <title>Side Effects</title>
356 Modifications are made to the underlying database.
360 <title>Affected by</title>
366 <title>Exceptional Situations</title>
368 An error of type <symbol>sql-database-data-error</symbol> is
369 signalled if <parameter>table</parameter> is not an existing
370 table in <parameter>database</parameter> or if the specified
371 attributes are not found.
375 <title>See Also</title>
377 <member><link linkend="update-records"><function>update-records</function></link></member>
378 <member><link linkend="delete-records"><function>delete-records</function></link></member>
389 <refentry id="update-records">
391 <refentrytitle>UPDATE-RECORDS</refentrytitle>
394 <refname><emphasis>Function</emphasis> <emphasis role="bold">UPDATE-RECORDS</emphasis></refname>
395 <refpurpose>Updates the values of existing records.</refpurpose>
396 <refclass>Function</refclass>
399 <title>Syntax</title>
401 <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>
404 <title>Arguments and Values</title>
407 <term><parameter>table</parameter></term>
410 A string, symbol or symbolic SQL expression representing
411 the name of a table existing in
412 <parameter>database</parameter>.
417 <term><parameter>attributes</parameter></term>
420 A list of attribute identifiers or &nil;.
425 <term><parameter>values</parameter></term>
428 A list of attribute values or &nil;.
433 <term><parameter>av-pairs</parameter></term>
436 A list of attribute identifier/value pairs or &nil;.
441 <term><parameter>where</parameter></term>
444 A symbolic SQL expression.
449 <term><parameter>database</parameter></term>
452 <glossterm linkend="gloss-database-object">database
453 object</glossterm>. This will default to the value
454 of <symbol>*default-database*</symbol>.</para>
460 <title>Description</title>
462 Updates the attribute values of existing records satsifying
463 the SQL expression <parameter>where</parameter> in the table
464 specified by <parameter>table</parameter> in
465 <parameter>database</parameter> which defaults to
466 <symbol>*default-database*</symbol>.
469 There are three ways of specifying the values to update for
470 each row. In the first, <parameter>values</parameter> contains
471 a list of values to use in the update and
472 <parameter>attributes</parameter> and
473 <parameter>av-pairs</parameter> are &nil;. This can be used
474 when values are supplied for all attributes in
475 <parameter>table</parameter>. In the second,
476 <parameter>attributes</parameter> is a list of column names,
477 <parameter>values</parameter> is a corresponding list of
478 values and <parameter>av-pairs</parameter> is &nil;. In the
479 third, <parameter>attributes</parameter> and
480 <parameter>values</parameter> are &nil; and
481 <parameter>av-pairs</parameter> is an alist of (attribute
486 <title>Examples</title>
488 (select [first-name] [last-name] [email]
490 :where [= [emplid] 1]
492 => (("Vladamir" "Lenin" "lenin@soviet.org"))
493 (update-records [employee]
494 :av-pairs'((first_name "Yuri")
495 (last_name "Gagarin")
496 (email "gagarin@soviet.org"))
497 :where [= [emplid] 1])
499 (select [first-name] [last-name] [email]
501 :where [= [emplid] 1]
503 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
507 <title>Side Effects</title>
509 Modifications are made to the underlying database.
513 <title>Affected by</title>
519 <title>Exceptional Situations</title>
521 An error of type <symbol>sql-database-data-error</symbol> is
522 signalled if <parameter>table</parameter> is not an existing
523 table in <parameter>database</parameter>, if the specified
524 attributes are not found or if the SQL statement resulting
525 from the symbolic expression <parameter>where</parameter> does
526 not return a Boolean value.
528 <para>If the execution of the SQL query leads to any errors, an
529 error of type <errortype>sql-database-error</errortype> is
533 <title>See Also</title>
536 <member><link linkend="insert-records"><function>insert-records</function></link></member>
537 <member><link linkend="delete-records"><function>delete-records</function></link></member>
549 <refentry id="delete-records">
551 <refentrytitle>DELETE-RECORDS</refentrytitle>
554 <refname><emphasis>Function</emphasis> <emphasis role="bold">DELETE-RECORDS</emphasis></refname>
555 <refpurpose>Delete records from a database table.</refpurpose>
556 <refclass>Function</refclass>
559 <title>Syntax</title>
561 <function>delete-records</function> &key <replaceable>from</replaceable> <replaceable>where</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
564 <title>Arguments and Values</title>
567 <term><parameter>from</parameter></term>
570 A string, symbol or symbolic SQL expression representing
571 the name of a table existing in
572 <parameter>database</parameter>.
577 <term><parameter>where</parameter></term>
580 A symbolic SQL expression.
585 <term><parameter>database</parameter></term>
588 <glossterm linkend="gloss-database-object">database
589 object</glossterm>. This will default to the value
590 of <symbol>*default-database*</symbol>.</para>
596 <title>Description</title>
597 <para>Deletes records satisfying the SQL expression
598 <parameter>where</parameter> from the table specified by
599 <parameter>from</parameter> in <parameter>database</parameter>
600 specifies a database which defaults to
601 <symbol>*default-database*</symbol>.
605 <title>Examples</title>
607 (select [first-name] [last-name] [email]
609 :where [= [emplid] 11]
611 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
612 (delete-records :from [employee] :where [= [emplid] 11])
614 (select [first-name] [last-name] [email]
616 :where [= [emplid] 11]
622 <title>Side Effects</title>
624 Modifications are made to the underlying database.
628 <title>Affected by</title>
634 <title>Exceptional Situations</title>
636 An error of type <symbol>sql-database-data-error</symbol> is
637 signalled if <parameter>from</parameter> is not an existing
638 table in <parameter>database</parameter> or if the SQL
639 statement resulting from the symbolic expression
640 <parameter>where</parameter> does not return a Boolean value.
644 <title>See Also</title>
647 <member><link linkend="insert-records"><function>insert-records</function></link></member>
648 <member><link linkend="update-records"><function>update-records</function></link></member>
661 <!-- executing SQL commands and queries -->
663 <refentry id="execute-command">
665 <refentrytitle>EXECUTE-COMMAND</refentrytitle>
668 <refname><emphasis>Generic Function</emphasis> <emphasis role="bold">EXECUTE-COMMAND</emphasis></refname>
669 <refpurpose>Execute an SQL command which returns no values.</refpurpose>
670 <refclass>Generic Function</refclass>
673 <title>Syntax</title>
675 <function>execute-command</function> <replaceable>sql-expression</replaceable> &key <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
678 <title>Arguments and Values</title>
681 <term><parameter>sql-expression</parameter></term>
683 <para>An <glossterm linkend="gloss-sql-expression">sql
684 expression</glossterm> that represents an SQL
685 statement which will return no values.</para>
689 <term><parameter>database</parameter></term>
692 <glossterm linkend="gloss-database-object">database
693 object</glossterm>. This will default to the value
694 of <symbol>*default-database*</symbol>.</para>
696 </varlistentry></variablelist>
699 <title>Description</title>
700 <para>Executes the SQL command
701 <parameter>sql-expression</parameter>, which may be a symbolic
702 SQL expression or a string representing any SQL statement apart
703 from a query, on the supplied <parameter>database</parameter>
704 which defaults to <symbol>*default-database*</symbol>.
708 <title>Examples</title>
710 (execute-command "create table eventlog (time char(30),event char(70))")
713 (execute-command "create table eventlog (time char(30),event char(70))")
715 >> While accessing database #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {480B2B6D}>
716 >> with expression "create table eventlog (time char(30),event char(70))":
717 >> Error NIL: ERROR: amcreate: eventlog relation already exists
721 >> 0: [ABORT] Return to Top-Level.
723 >> Debug (type H for help)
725 >> (CLSQL-POSTGRESQL::|(PCL::FAST-METHOD DATABASE-EXECUTE-COMMAND (T POSTGRESQL-DATABASE))|
728 >> #<unavailable-arg>
729 >> #<unavailable-arg>)
730 >> Source: (ERROR 'SQL-DATABASE-ERROR :DATABASE DATABASE :EXPRESSION ...)
733 (execute-command "drop table eventlog")
738 <title>Side Effects</title>
739 <para>Whatever effects the execution of the SQL statement has
740 on the underlying database, if any.</para>
743 <title>Affected by</title>
747 <title>Exceptional Situations</title>
748 <para>If the execution of the SQL statement leads to any errors,
749 an error of type <errortype>sql-database-error</errortype> is
753 <title>See Also</title>
756 <member><link linkend="query"><function>query</function></link></member>
767 <refentry id="query">
769 <refentrytitle>QUERY</refentrytitle>
772 <refname><emphasis>Generic Function</emphasis> <emphasis role="bold">QUERY</emphasis></refname>
773 <refpurpose>Execute an SQL query and return the tuples as a
775 <refclass>Generic Function</refclass>
778 <title>Syntax</title>
780 <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>
783 <title>Arguments and Values</title>
786 <term><parameter>query-expression</parameter></term>
788 <para>An <glossterm linkend="gloss-sql-expression">sql
789 expression</glossterm> that represents an SQL
790 query which is expected to return a (possibly empty)
795 <term><parameter>database</parameter></term>
798 <glossterm linkend="gloss-database-object">database
799 object</glossterm>. This will default to the value
800 of <symbol>*default-database*</symbol>.</para>
804 <term><parameter>flatp</parameter></term>
806 <para>A Boolean whose default value is &nil;.</para>
810 <term><parameter>result-types</parameter></term>
813 <glossterm linkend="gloss-field-types">field type
814 specifier</glossterm>. The default is &nil;.
817 The purpose of this argument is cause &clsql; to
818 import SQL numeric fields into numeric Lisp objects
819 rather than strings. This reduces the cost of
820 allocating a temporary string and the &clsql; users'
821 inconvenience of converting number strings into number
825 A value of <symbol>:auto</symbol> causes &clsql;
826 to automatically convert SQL fields into a
827 numeric format where applicable. The default value of
828 &nil; causes all fields to be returned as strings
829 regardless of the SQL type. Otherwise a list is expected
830 which has a element for each field that specifies the
831 conversion. Valid type identifiers are:
832 <simplelist type="vert">
833 <member><symbol>:int</symbol> Field is imported as a
834 signed integer, from 8-bits to 64-bits depending
837 <member><symbol>:double</symbol> Field is imported as a
840 <member><symbol>t</symbol> Field is imported as a
844 If the list is shorter than the number of fields, the a
845 value of <symbol>t</symbol> is assumed for the field.
846 If the list is longer than the number of fields, the
847 extra elements are ignored.
852 <term><parameter>field-names</parameter></term>
855 A boolean with a default value of &t;. When &t;, this
856 function returns a second value of a list of field
857 names. When &nil;, this function only returns one value -
863 <term><returnvalue>result</returnvalue></term>
865 <para>A list representing the result set obtained. For
866 each tuple in the result set, there is an element in
867 this list, which is itself a list of all the attribute
868 values in the tuple.</para>
874 <title>Description</title>
876 Executes the SQL query expression
877 <parameter>query-expression</parameter>, which may be an SQL
878 expression or a string, on the supplied
879 <parameter>database</parameter> which defaults to
880 <symbol>*default-database*</symbol>. <parameter>result-types</parameter>
881 is a list of symbols which specifies the lisp type for each
882 field returned by <parameter>query-expression</parameter>.
885 If <parameter>result-types</parameter> is &nil; all results
886 are returned as strings whereas the default value of
887 <symbol>:auto</symbol> means that the lisp types are
888 automatically computed for each field.
891 <parameter>field-names</parameter> is &t; by default which
892 means that the second value returned is a list of strings
893 representing the columns selected by
894 <parameter>query-expression</parameter>. If
895 <parameter>field-names</parameter> is &nil;, the list of column
896 names is not returned as a second value.
899 <parameter>flatp</parameter> has a default value of &nil;
900 which means that the results are returned as a list of
901 lists.If FLATP is &t; and only one result is returned for each
902 record selected by <parameter>query-expression</parameter>,
903 the results are returned as elements of a list.
907 <title>Examples</title>
909 (query "select emplid,first_name,last_name,height from employee where emplid = 1")
910 => ((1 "Vladamir" "Lenin" 1.5564661d0)),
911 ("emplid" "first_name" "last_name" "height")
913 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
915 => ((1 "Vladamir" "Lenin" 1.5564661d0))
917 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
920 => (("1" "Vladamir" "Lenin" "1.5564661"))
922 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
924 :result-types '(:int t t :double))
925 => ((1 "Vladamir" "Lenin" 1.5564661))
927 (query "select last_name from employee where emplid > 5" :flatp t)
928 => ("Andropov" "Chernenko" "Gorbachev" "Yeltsin" "Putin"),
931 (query "select last_name from employee where emplid > 10"
938 <title>Side Effects</title>
939 <para>Whatever effects the execution of the SQL query has
940 on the underlying database, if any.</para>
943 <title>Affected by</title>
947 <title>Exceptional Situations</title>
948 <para>If the execution of the SQL query leads to any errors, an
949 error of type <errortype>sql-database-error</errortype> is
953 <title>See Also</title>
955 <member><link linkend="execute-command"><function>execute-command</function></link></member>
956 <member><link linkend="print-query"><function>print-query</function></link></member>
957 <member><link linkend="do-query"><function>do-query</function></link></member>
958 <member><link linkend="map-query"><function>map-query</function></link></member>
959 <member><link linkend="loop-tuples"><function>loop</function></link></member>
960 <member><link linkend="select"><function>select</function></link></member>
965 <para>The <parameter>field-names</parameter> and
966 <parameter>result-types</parameter> keyword arguments are a
967 &clsql; extension.</para>
971 <refentry id="print-query">
973 <refentrytitle>PRINT-QUERY</refentrytitle>
976 <refname><emphasis>Function</emphasis> <emphasis role="bold">PRINT-QUERY</emphasis></refname>
977 <refpurpose>Prints a tabular report of query results.</refpurpose>
978 <refclass>Function</refclass>
981 <title>Syntax</title>
983 <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>
986 <title>Arguments and Values</title>
989 <term><parameter>query-expression</parameter></term>
991 <para>An <glossterm linkend="gloss-sql-expression">sql
992 expression</glossterm> that represents an SQL
993 query which is expected to return a (possibly empty)
998 <term><parameter>database</parameter></term>
1001 <glossterm linkend="gloss-database-object">database
1002 object</glossterm>. This will default to the value
1003 of <symbol>*default-database*</symbol>.</para>
1007 <term><parameter>titles</parameter></term>
1010 A list of strings or &nil; which is the default value.
1015 <term><parameter>formats</parameter></term>
1018 A list of strings, &nil; or &t; which is the default value.
1023 <term><parameter>sizes</parameter></term>
1026 A list of numbers, &nil; or &t; which is the default value.
1031 <term><parameter>stream</parameter></term>
1034 An output stream or &t; which is the default value.
1041 <title>Description</title>
1042 <para>Prints a tabular report of the results returned by the SQL
1043 query <parameter>query-expression</parameter>, which may be a
1044 symbolic SQL expression or a string, in
1045 <parameter>database</parameter> which defaults to
1046 <symbol>*default-database*</symbol>. The report is printed onto
1047 <parameter>stream</parameter> which has a default value of &t;
1048 which means that <symbol>*standard-output*</symbol> is used. The
1049 <parameter>title</parameter> argument, which defaults to &nil;,
1050 allows the specification of a list of strings to use as column
1051 titles in the tabular output. <parameter>sizes</parameter>
1052 accepts a list of column sizes, one for each column selected by
1053 <parameter>query-expression</parameter>, to use in formatting
1054 the tabular report. The default value of &t; means that minimum
1055 sizes are computed. <parameter>formats</parameter> is a list of
1056 format strings to be used for printing each column selected by
1057 <parameter>query-expression</parameter>. The default value of
1058 <parameter>formats</parameter> is &t; meaning that
1059 <symbol>~A</symbol> is used to format all columns or
1060 <symbol>~VA</symbol> if column sizes are used.
1064 <title>Examples</title>
1066 (print-query [select [emplid] [first-name] [last-name] [email]
1068 :where [< [emplid] 5]]
1069 :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
1070 ID FORENAME SURNAME EMAIL
1071 1 Vladamir Lenin lenin@soviet.org
1072 2 Josef Stalin stalin@soviet.org
1073 3 Leon Trotsky trotsky@soviet.org
1074 4 Nikita Kruschev kruschev@soviet.org
1077 (print-query "select emplid,first_name,last_name,email from employee where emplid >= 5"
1078 :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
1079 ID FORENAME SURNAME EMAIL
1080 5 Leonid Brezhnev brezhnev@soviet.org
1081 6 Yuri Andropov andropov@soviet.org
1082 7 Konstantin Chernenko chernenko@soviet.org
1083 8 Mikhail Gorbachev gorbachev@soviet.org
1084 9 Boris Yeltsin yeltsin@soviet.org
1085 10 Vladamir Putin putin@soviet.org
1090 <title>Side Effects</title>
1096 <title>Affected by</title>
1102 <title>Exceptional Situations</title>
1104 If the execution of the SQL query leads to any errors, an
1105 error of type <errortype>sql-database-error</errortype> is
1110 <title>See Also</title>
1112 <member><link linkend="query"><function>query</function></link></member>
1113 <member><link linkend="do-query"><function>do-query</function></link></member>
1114 <member><link linkend="map-query"><function>map-query</function></link></member>
1115 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1116 <member><link linkend="select"><function>select</function></link></member>
1120 <title>Notes</title>
1127 <refentry id="select">
1129 <refentrytitle>SELECT</refentrytitle>
1132 <refname><emphasis>Function</emphasis> <emphasis role="bold">SELECT</emphasis></refname>
1133 <refpurpose>Executes a query given the supplied constraints.</refpurpose>
1134 <refclass>Function</refclass>
1137 <title>Syntax</title>
1139 <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>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>database</replaceable> => <returnvalue>result</returnvalue></synopsis>
1142 <title>Arguments and Values</title>
1145 <term><parameter>identifiers</parameter></term>
1148 A set of <glossterm linkend="gloss-sql-expression">sql
1149 expressions</glossterm> each of which indicates a column
1155 <term><parameter>all</parameter></term>
1163 <term><parameter>distinct</parameter></term>
1171 <term><parameter>from</parameter></term>
1174 One or more SQL expression representing tables.
1179 <term><parameter>group-by</parameter></term>
1187 <term><parameter>having</parameter></term>
1195 <term><parameter>order-by</parameter></term>
1203 <term><parameter>set-operation</parameter></term>
1211 <term><parameter>where</parameter></term>
1219 <term><parameter>database</parameter></term>
1222 <glossterm linkend="gloss-database-object">database
1223 object</glossterm>. This will default to the value
1224 of <symbol>*default-database*</symbol>.</para>
1228 <term><parameter>flatp</parameter></term>
1230 <para>A Boolean whose default value is &nil;.</para>
1234 <term><parameter>result-types</parameter></term>
1237 <glossterm linkend="gloss-field-types">field type
1238 specifier</glossterm>. The default is &nil;.
1241 The purpose of this argument is cause &clsql; to
1242 import SQL numeric fields into numeric Lisp objects
1243 rather than strings. This reduces the cost of
1244 allocating a temporary string and the &clsql; users'
1245 inconvenience of converting number strings into number
1249 A value of <symbol>:auto</symbol> causes &clsql;
1250 to automatically convert SQL fields into a
1251 numeric format where applicable. The default value of
1252 &nil; causes all fields to be returned as strings
1253 regardless of the SQL type. Otherwise a list is expected
1254 which has a element for each field that specifies the
1255 conversion. Valid type identifiers are:
1256 <simplelist type="vert">
1257 <member><symbol>:int</symbol> Field is imported as a
1258 signed integer, from 8-bits to 64-bits depending
1259 upon the field type.
1261 <member><symbol>:double</symbol> Field is imported as a
1262 double-float number.
1264 <member><symbol>t</symbol> Field is imported as a
1268 If the list is shorter than the number of fields, the a
1269 value of <symbol>t</symbol> is assumed for the field.
1270 If the list is longer than the number of fields, the
1271 extra elements are ignored.
1276 <term><parameter>field-names</parameter></term>
1279 A boolean with a default value of &t;. When &t;, this
1280 function returns a second value of a list of field
1281 names. When &nil;, this function only returns one value -
1287 <term><parameter>result</parameter></term>
1290 A list representing the result set obtained. For each
1291 tuple in the result set, there is an element in this
1292 list, which is itself a list of all the attribute values
1300 <title>Description</title>
1302 Executes a query on <parameter>database</parameter>, which has
1303 a default value of <symbol>*default-database*</symbol>,
1304 specified by the SQL expressions supplied using the remaining
1305 arguments in <parameter>args</parameter>. The
1306 <function>select</function> function can be used to generate
1307 queries in both functional and object oriented contexts.
1310 In the functional case, the required arguments specify the
1311 columns selected by the query and may be symbolic SQL
1312 expressions or strings representing attribute
1313 identifiers. Type modified identifiers indicate that the
1314 values selected from the specified column are converted to the
1315 specified lisp type. The keyword arguments
1316 <parameter>all</parameter>, <parameter>distinct</parameter>,
1317 <parameter>from</parameter>, <parameter>group-by</parameter>,
1318 <parameter>having</parameter>,
1319 <parameter>order-by</parameter>,
1320 <parameter>set-operation</parameter> and
1321 <parameter>where</parameter> are used to specify, using the
1322 symbolic SQL syntax, the corresponding components of the SQL
1323 query generated by the call to
1324 <function>select</function>.
1327 <parameter>result-types</parameter> is a list of symbols which
1328 specifies the lisp type for each field returned by the
1329 query. If <parameter>result-types</parameter> is &nil; all
1330 results are returned as strings whereas the default value of
1331 <symbol>:auto</symbol> means that the lisp types are
1332 automatically computed for each
1333 field. <parameter>field-names</parameter> is &t; by default
1334 which means that the second value returned is a list of
1335 strings representing the columns selected by the query. If
1336 <parameter>field-names</parameter> is &nil;, the list of
1337 column names is not returned as a second value.
1340 In the object oriented case, the required arguments to
1341 <function>select</function> are symbols denoting View Classes
1342 which specify the database tables to query. In this case,
1343 <function>select</function> returns a list of View Class
1344 instances whose slots are set from the attribute values of the
1345 records in the specified table. <symbol>Slot-value</symbol> is
1346 a legal operator which can be employed as part of the symbolic
1347 SQL syntax used in the <parameter>where</parameter> keyword
1348 argument to <function>select</function>.
1349 <parameter>refresh</parameter> is &nil; by default which means
1350 that the View Class instances returned are retrieved from a
1351 cache if an equivalent call to <function>select</function> has
1352 previously been issued. If <parameter>refresh</parameter> is
1353 true, the View Class instances returned are updated as
1354 necessary from the database and the generic function
1355 <function>instance-refreshed</function> is called to perform
1356 any necessary operations on the updated instances.
1359 In both object oriented and functional contexts,
1360 <parameter>flatp</parameter> has a default value of &nil;
1361 which means that the results are returned as a list of
1362 lists. If <parameter>flatp</parameter> is t and only one
1363 result is returned for each record selected in the query, the
1364 results are returned as elements of a list.
1368 <title>Examples</title>
1370 (select [first-name] :from [employee] :flatp t :distinct t
1373 :order-by [first-name])
1374 => ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
1377 (select [first-name] [count [*]] :from [employee]
1379 :group-by [first-name]
1380 :order-by [first-name]
1382 => (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
1383 ("Mikhail" "1") ("Nikita" "1") ("Vladamir" "2") ("Yuri" "1"))
1385 (select [last-name] :from [employee]
1386 :where [like [email] "%org"]
1387 :order-by [last-name]
1391 => ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
1392 "Stalin" "Trotsky" "Yeltsin")
1394 (select [max [emplid]] :from [employee]
1397 :result-types :auto)
1400 (clsql:select [avg [height]] :from [employee] :flatp t :field-names nil)
1403 (select [emplid] [last-name] :from [employee] :where [= [emplid] 1])
1405 ("emplid" "last_name")
1407 (select [emplid :string] :from [employee]
1408 :where [= 1 [emplid]]
1413 (select [emplid] :from [employee] :order-by [emplid]
1414 :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]]
1419 (clsql:select [emplid] :from [employee]
1420 :where [in [emplid] '(1 2 3 4)]
1426 (select [first-name] [last-name] :from [employee]
1428 :order-by '(([first-name] :asc) ([last-name] :desc)))
1429 => (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
1430 ("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
1431 ("Nikita" "Kruschev") ("Vladamir" "Putin") ("Vladamir" "Lenin")
1432 ("Yuri" "Andropov"))
1434 (select [last-name] :from [employee]
1435 :set-operation [union [select [first-name] :from [employee]
1436 :order-by [last-name]]]
1440 => ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
1441 "Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
1442 "Trotsky" "Vladamir" "Yeltsin" "Yuri")
1446 <title>Side Effects</title>
1448 <para>Whatever effects the execution of the SQL query has on
1449 the underlying database, if any.</para>
1453 <title>Affected by</title>
1459 <title>Exceptional Situations</title>
1461 If the execution of the SQL query leads to any errors, an
1462 error of type <errortype>sql-database-error</errortype> is
1467 <title>See Also</title>
1469 <member><link linkend="query"><function>query</function></link></member>
1470 <member><link linkend="print-query"><function>print-query</function></link></member>
1471 <member><link linkend="do-query"><function>do-query</function></link></member>
1472 <member><link linkend="map-query"><function>map-query</function></link></member>
1473 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1474 <member><link linkend="instance-refreshed"><function>instance-refreshed</function></link></member>
1478 <title>Notes</title>
1480 The <function>select</function> function is actually
1481 implemented in &clsql; with a single
1482 <symbol>&rest</symbol> parameter (which is subsequently
1483 destructured) rather than the keyword parameters presented
1484 here for the purposes of exposition. This means that incorrect
1485 or missing keywords or values may not trigger errors in the
1486 way that they would if <function>select</function> had been
1487 defined using keyword arguments.
1490 The <parameter>field-names</parameter> and
1491 <parameter>result-types</parameter> keyword arguments are a
1495 <parameter>select</parameter> is common across the functional
1496 and object-oriented data manipulation languages.
1502 <!-- iteration and mapping -->
1504 <refentry id="do-query">
1506 <refentrytitle>DO-QUERY</refentrytitle>
1509 <refname><emphasis>Macro</emphasis> <emphasis role="bold">DO-QUERY</emphasis></refname>
1510 <refpurpose>Iterate over all the tuples of a query.</refpurpose>
1511 <refclass>Macro</refclass>
1514 <title>Syntax</title>
1516 <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>
1519 <title>Arguments and Values</title>
1522 <term><parameter>args</parameter></term>
1524 <para>A list of variable names.</para>
1528 <term><parameter>query-expression</parameter></term>
1530 <para>An <glossterm linkend="gloss-sql-expression">sql
1531 expression</glossterm> that represents an SQL
1532 query which is expected to return a (possibly empty)
1533 result set, where each tuple has as many attributes as
1534 <parameter>function</parameter> takes arguments.</para>
1538 <term><parameter>database</parameter></term>
1541 <glossterm linkend="gloss-database-object">database
1542 object</glossterm>. This will default to
1543 <symbol>*default-database*</symbol>.</para>
1547 <term><parameter>result-types</parameter></term>
1550 A <glossterm linkend="gloss-field-types">field type
1551 specifier</glossterm>. The default is &nil;. See <link
1552 linkend="query"><function>query</function></link> for
1553 the semantics of this argument.
1558 <term><parameter>body</parameter></term>
1560 <para>A body of Lisp code, like in a
1561 <function>destructuring-bind</function> form.</para>
1565 <term><parameter>result</parameter></term>
1567 <para>The result of executing <parameter>body</parameter>.</para>
1573 <title>Description</title>
1575 Repeatedly executes <parameter>body</parameter> within a
1576 binding of <parameter>args</parameter> on the fields of each
1577 row selected by the SQL query
1578 <parameter>query-expression</parameter>, which may be a string
1579 or a symbolic SQL expression, in
1580 <parameter>database</parameter> which defaults to
1581 <symbol>*default-database*</symbol>.
1584 The body of code is executed in a block named
1585 <symbol>nil</symbol> which may be returned from prematurely
1586 via <function>return</function> or
1587 <function>return-from</function>. In this case the result of
1588 evaluating the <function>do-query</function> form will be the
1589 one supplied to <function>return</function> or
1590 <function>return-from</function>. Otherwise the result will
1591 be <symbol>nil</symbol>.
1594 The body of code appears also is if wrapped in a
1595 <function>destructuring-bind</function> form, thus allowing
1596 declarations at the start of the body, especially those
1597 pertaining to the bindings of the variables named in
1598 <parameter>args</parameter>.
1601 <parameter>result-types</parameter> is a list of symbols which
1602 specifies the lisp type for each field returned by
1603 <parameter>query-expression</parameter>. If
1604 <parameter>result-types</parameter> is &nil; all results are
1605 returned as strings whereas the default value of
1606 <symbol>:auto</symbol> means that the lisp types are
1607 automatically computed for each field.
1610 <parameter>query-expression</parameter> may be an object query
1611 (i.e., the selection arguments refer to View Classes), in
1612 which case <parameter>args</parameter> are bound to the tuples
1613 of View Class instances returned by the object oriented query.
1617 <title>Examples</title>
1619 (do-query ((salary name) "select salary,name from simple")
1620 (format t "~30A gets $~2,5$~%" name (read-from-string salary)))
1621 >> Mai, Pierre gets $10000.00
1622 >> Hacker, Random J. gets $08000.50
1625 (do-query ((salary name) "select salary,name from simple")
1626 (return (cons salary name)))
1627 => ("10000.00" . "Mai, Pierre")
1630 (do-query ((name) [select [last-name] :from [employee]
1631 :order-by [last-name]])
1634 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1635 "Chernenko" "Brezhnev" "Andropov")
1638 (do-query ((e) [select 'employee :order-by [last-name]])
1639 (push (slot-value e 'last-name) result))
1641 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1642 "Chernenko" "Brezhnev" "Andropov")
1646 <title>Side Effects</title>
1647 <para>Whatever effects the execution of the SQL query has
1648 on the underlying database, if any.</para>
1651 <title>Affected by</title>
1655 <title>Exceptional Situations</title>
1656 <para>If the execution of the SQL query leads to any
1657 errors, an error of type
1658 <errortype>sql-database-error</errortype> is signalled.</para>
1659 <para>If the number of variable names in
1660 <parameter>args</parameter> and the number of attributes in
1661 the tuples in the result set don't match up, an error is
1665 <title>See Also</title>
1667 <member><link linkend="query"><function>query</function></link></member>
1668 <member><link linkend="map-query"><function>map-query</function></link></member>
1669 <member><link linkend="print-query"><function>print-query</function></link></member>
1670 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1671 <member><link linkend="select"><function>select</function></link></member>
1675 <title>Notes</title>
1676 <para>The <parameter>result-types</parameter> keyword argument
1677 is a &clsql; extension.</para>
1679 <parameter>do-query</parameter> is common across the functional
1680 and object-oriented data manipulation languages.
1685 <refentry id="loop-tuples">
1687 <refentrytitle>LOOP</refentrytitle>
1690 <refname><emphasis>Additional clause</emphasis> for <emphasis role="bold">LOOP</emphasis></refname>
1691 <refpurpose>Iterate over all the tuples of a
1692 query via a loop clause.</refpurpose>
1693 <refclass>Loop Clause</refclass>
1696 <title>Compatibility</title>
1697 <caution><para><function>loop-for-as-tuples</function> only works with &cmucl;.</para></caution>
1700 <title>Syntax</title>
1701 <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>
1704 <title>Arguments and Values</title>
1707 <term><parameter>var</parameter></term>
1709 <para>A <literal>d-var-spec</literal>, as defined in the
1710 grammar for <function>loop</function>-clauses in the ANSI
1711 Standard for Common Lisp. This allows for the usual
1712 loop-style destructuring.</para>
1716 <term><parameter>type-spec</parameter></term>
1718 <para>An optional <literal>type-spec</literal> either
1719 simple or destructured, as defined in the grammar for
1720 <function>loop</function>-clauses in the ANSI Standard for
1725 <term><parameter>query</parameter></term>
1727 <para>An <glossterm linkend="gloss-sql-expression">sql
1728 expression</glossterm> that represents an SQL
1729 query which is expected to return a (possibly empty)
1730 result set, where each tuple has as many attributes as
1731 <parameter>function</parameter> takes arguments.</para>
1735 <term><parameter>database</parameter></term>
1738 <glossterm linkend="gloss-database-object">database
1739 object</glossterm>. This will default to the value
1740 of <symbol>*default-database*</symbol>.</para>
1746 <title>Description</title>
1747 <para>This clause is an iteration driver for
1748 <function>loop</function>, that binds the given variable
1749 (possibly destructured) to the consecutive tuples (which are
1750 represented as lists of attribute values) in the result set
1751 returned by executing the SQL <parameter>query</parameter>
1752 expression on the <parameter>database</parameter>
1755 <parameter>query</parameter> may be an object query (i.e., the
1756 selection arguments refer to View Classes), in which case the
1757 supplied variable is bound to the tuples of View Class
1758 instances returned by the object oriented query.
1762 <title>Examples</title>
1764 (defvar *my-db* (connect '("dent" "newesim" "dent" "dent"))
1767 (loop with time-graph = (make-hash-table :test #'equal)
1768 with event-graph = (make-hash-table :test #'equal)
1769 for (time event) being the tuples of "select time,event from log"
1772 (incf (gethash time time-graph 0))
1773 (incf (gethash event event-graph 0))
1775 (flet ((show-graph (k v) (format t "~40A => ~5D~%" k v)))
1776 (format t "~&Time-Graph:~%===========~%")
1777 (maphash #'show-graph time-graph)
1778 (format t "~&~%Event-Graph:~%============~%")
1779 (maphash #'show-graph event-graph))
1780 (return (values time-graph event-graph)))
1789 >> CLOS Benchmark entry. => 9000
1790 >> Demo Text... => 3
1791 >> doit-text => 3000
1792 >> C Benchmark entry. => 12000
1793 >> CLOS Benchmark entry => 32000
1794 => #<EQUAL hash table, 3 entries {48350A1D}>
1795 => #<EQUAL hash table, 5 entries {48350FCD}>
1797 (loop for (forename surname)
1799 [select [first-name] [last-name] :from [employee]
1800 :order-by [last-name]]
1801 collect (concatenate 'string forename " " surname))
1802 => ("Yuri Andropov" "Leonid Brezhnev" "Konstantin Chernenko" "Mikhail Gorbachev"
1803 "Nikita Kruschev" "Vladamir Lenin" "Vladamir Putin" "Josef Stalin"
1804 "Leon Trotsky" "Boris Yeltsin")
1806 (loop for (e) being the records in
1807 [select 'employee :where [< [emplid] 4] :order-by [emplid]]
1808 collect (slot-value e 'last-name))
1809 => ("Lenin" "Stalin" "Trotsky")
1813 <title>Side Effects</title>
1814 <para>Whatever effects the execution of the SQL query has
1815 on the underlying database, if any.</para>
1818 <title>Affected by</title>
1822 <title>Exceptional Situations</title>
1823 <para>If the execution of the SQL query leads to any
1824 errors, an error of type
1825 <errortype>sql-database-error</errortype> is signalled.</para>
1826 <para>Otherwise, any of the exceptional situations of
1827 <function>loop</function> applies.</para>
1830 <title>See Also</title>
1833 <member><link linkend="query"><function>query</function></link></member>
1834 <member><link linkend="map-query"><function>map-query</function></link></member>
1835 <member><link linkend="do-query"><function>do-query</function></link></member>
1836 <member><link linkend="print-query"><function>print-query</function></link></member>
1837 <member><link linkend="select"><function>select</function></link></member>
1842 <title>Notes</title>
1843 <para>The <parameter>database</parameter> loop keyword is a
1844 &clsql; extension.</para>
1846 The extended <function>loop</function> syntax is common across
1847 the functional and object-oriented data manipulation
1853 <refentry id="map-query">
1855 <refentrytitle>MAP-QUERY</refentrytitle>
1858 <refname><emphasis>Function</emphasis> <emphasis role="bold">MAP-QUERY</emphasis></refname>
1859 <refpurpose>Map a function over all the tuples from a
1861 <refclass>Function</refclass>
1864 <title>Syntax</title>
1865 <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>
1868 <title>Arguments and Values</title>
1871 <term><parameter>output-type-spec</parameter></term>
1873 <para>A sequence type specifier or <symbol>nil</symbol>.</para>
1877 <term><parameter>function</parameter></term>
1879 <para>A function designator.
1880 <parameter>function</parameter> takes a single argument which
1881 is the atom value for a query single with a single column
1882 or is a list of values for a multi-column query.</para>
1886 <term><parameter>query-expression</parameter></term>
1888 <para>An <glossterm linkend="gloss-sql-expression">sql
1889 expression</glossterm> that represents an SQL
1890 query which is expected to return a (possibly empty)
1895 <term><parameter>database</parameter></term>
1898 <glossterm linkend="gloss-database-object">database
1899 object</glossterm>. This will default to the value
1900 of <symbol>*default-database*</symbol>.</para>
1904 <term><parameter>result-types</parameter></term>
1907 A <glossterm linkend="gloss-field-types">field type
1908 specifier</glossterm>. The default is &nil;. See <link
1909 linkend="query"><function>query</function></link> for
1910 the semantics of this argument.
1915 <term><returnvalue>result</returnvalue></term>
1917 <para>If <parameter>output-type-spec</parameter> is a
1918 type specifier other than <symbol>nil</symbol>, then a
1919 sequence of the type it denotes. Otherwise
1920 <symbol>nil</symbol> is returned.</para>
1926 <title>Description</title>
1928 Applies <parameter>function</parameter> to the successive
1929 tuples in the result set returned by executing the SQL
1930 <parameter>query-expression</parameter>. If the
1931 <parameter>output-type-spec</parameter> is
1932 <symbol>nil</symbol>, then the result of each application of
1933 <parameter>function</parameter> is discarded, and
1934 <function>map-query</function> returns <symbol>nil</symbol>.
1935 Otherwise the result of each successive application of
1936 <parameter>function</parameter> is collected in a sequence of
1937 type <parameter>output-type-spec</parameter>, where the jths
1938 element is the result of applying
1939 <parameter>function</parameter> to the attributes of the jths
1940 tuple in the result set. The collected sequence is the result
1941 of the call to <function>map-query</function>.
1944 If the <parameter>output-type-spec</parameter> is a subtype of
1945 <type>list</type>, the result will be a <type>list</type>.
1948 If the <parameter>result-type</parameter> is a subtype of
1949 <type>vector</type>, then if the implementation can determine
1950 the element type specified for the
1951 <parameter>result-type</parameter>, the element type of the
1952 resulting array is the result of
1953 <emphasis>upgrading</emphasis> that element type; or, if the
1954 implementation can determine that the element type is
1955 unspecified (or <symbol>*</symbol>), the element type of the
1956 resulting array is <type>t</type>; otherwise, an error is
1960 If <parameter>result-types</parameter> is &nil; all results
1961 are returned as strings whereas the default value of
1962 <symbol>:auto</symbol> means that the lisp types are
1963 automatically computed for each field.</para>
1965 <parameter>query-expression</parameter> may be an object query
1966 (i.e., the selection arguments refer to View Classes), in
1967 which case the supplied function is applied to the tuples of
1968 View Class instances returned by the object oriented query.
1972 <title>Examples</title>
1974 (map-query 'list #'(lambda (tuple)
1975 (multiple-value-bind (salary name) tuple
1976 (declare (ignorable name))
1977 (read-from-string salary)))
1978 "select salary,name from simple where salary > 8000")
1981 (map-query '(vector double-float)
1983 (multiple-value-bind (salary name) tuple
1984 (declare (ignorable name))
1985 (let ((*read-default-float-format* 'double-float))
1986 (coerce (read-from-string salary) 'double-float))
1987 "select salary,name from simple where salary > 8000")))
1988 => #(10000.0d0 8000.5d0)
1990 => (SIMPLE-ARRAY DOUBLE-FLOAT (2))
1993 (values (map-query nil #'(lambda (tuple)
1994 (multiple-value-bind (salary name) tuple
1995 (push (cons name (read-from-string salary)) list))
1996 "select salary,name from simple where salary > 8000"))
1999 => (("Hacker, Random J." . 8000.5) ("Mai, Pierre" . 10000.0))
2001 (map-query 'vector #'identity
2002 [select [last-name] :from [employee] :flatp t
2003 :order-by [last-name]])
2004 => #("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
2005 "Stalin" "Trotsky" "Yeltsin")
2007 (map-query 'list #'identity
2008 [select [first-name] [last-name] :from [employee]
2009 :order-by [last-name]])
2010 => (("Yuri" "Andropov") ("Leonid" "Brezhnev") ("Konstantin" "Chernenko")
2011 ("Mikhail" "Gorbachev") ("Nikita" "Kruschev") ("Vladamir" "Lenin")
2012 ("Vladamir" "Putin") ("Josef" "Stalin") ("Leon" "Trotsky")
2013 ("Boris" "Yeltsin"))
2015 (map-query 'list #'last-name [select 'employee :order-by [emplid]])
2016 => ("Lenin" "Stalin" "Trotsky" "Kruschev" "Brezhnev" "Andropov" "Chernenko"
2017 "Gorbachev" "Yeltsin" "Putin")
2021 <title>Side Effects</title>
2022 <para>Whatever effects the execution of the SQL query has
2023 on the underlying database, if any.</para>
2026 <title>Affected by</title>
2030 <title>Exceptional Situations</title>
2031 <para>If the execution of the SQL query leads to any
2032 errors, an error of type
2033 <errortype>sql-database-error</errortype> is signalled.</para>
2034 <para>An error of type <errortype>type-error</errortype> must
2035 be signaled if the <parameter>output-type-spec</parameter> is
2036 not a recognizable subtype of <type>list</type>, not a
2037 recognizable subtype of <type>vector</type>, and not
2038 <symbol>nil</symbol>.</para>
2039 <para>An error of type <errortype>type-error</errortype>
2040 should be signaled if
2041 <parameter>output-type-spec</parameter> specifies the number
2042 of elements and the size of the result set is different from
2046 <title>See Also</title>
2048 <member><link linkend="query"><function>query</function></link></member>
2049 <member><link linkend="do-query"><function>do-query</function></link></member>
2050 <member><link linkend="print-query"><function>print-query</function></link></member>
2051 <member><link linkend="loop-tuples"><function>loop</function></link></member>
2052 <member><link linkend="select"><function>select</function></link></member>
2056 <title>Notes</title>
2057 <para>The <parameter>result-types</parameter> keyword argument
2058 is a &clsql; extension.</para>
2060 <parameter>map-query</parameter> is common across the
2061 functional and object-oriented data manipulation languages.