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>
101 <title>Syntax</title>
107 <refentry id="insert-records">
109 <refentrytitle>INSERT-RECORDS</refentrytitle>
112 <refname>INSERT-RECORDS</refname>
113 <refpurpose>Insert tuples of data into a database table.</refpurpose>
114 <refclass>Function</refclass>
117 <title>Syntax</title>
119 <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>
122 <title>Arguments and Values</title>
125 <term><parameter>into</parameter></term>
128 A string, symbol or symbolic SQL expression representing
129 the name of a table existing in
130 <parameter>database</parameter>.
135 <term><parameter>attributes</parameter></term>
138 A list of attribute identifiers or &nil;.
143 <term><parameter>values</parameter></term>
146 A list of attribute values or &nil;.
151 <term><parameter>av-pairs</parameter></term>
154 A list of attribute identifier/value pairs or &nil;.
159 <term><parameter>query</parameter></term>
162 A query expression or &nil;.
167 <term><parameter>database</parameter></term>
170 <glossterm linkend="gloss-database-object">database
171 object</glossterm>. This will default to the value
172 of <symbol>*default-database*</symbol>.</para>
178 <title>Description</title>
180 Inserts records into the table specified by
181 <parameter>into</parameter> in <parameter>database</parameter>
182 which defaults to <symbol>*default-database*</symbol>.
185 There are five ways of specifying the values inserted into
186 each row. In the first <parameter>values</parameter> contains
187 a list of values to insert and
188 <parameter>attributes</parameter>,
189 <parameter>av-pairs</parameter> and
190 <parameter>query</parameter> are &nil;. This can be used when
191 values are supplied for all attributes in
192 <parameter>into</parameter>. In the second,
193 <parameter>attributes</parameter> is a list of column names,
194 <parameter>values</parameter> is a corresponding list of
195 values and <parameter>av-pairs</parameter> and
196 <parameter>query</parameter> are &nil;. In the third,
197 <parameter>attributes</parameter>,
198 <parameter>values</parameter> and <parameter>query</parameter>
199 are &nil; and <parameter>av-pairs</parameter> is an alist of
200 (attribute value) pairs. In the fourth,
201 <parameter>values</parameter>, <parameter>av-pairs</parameter>
202 and <parameter>attributes</parameter> are &nil; and
203 <parameter>query</parameter> is a symbolic SQL query
204 expression in which the selected columns also exist in
205 <parameter>into</parameter>. In the fifth method,
206 <parameter>values</parameter> and
207 <parameter>av-pairs</parameter> are nil and
208 <parameter>attributes</parameter> is a list of column names
209 and <parameter>query</parameter> is a symbolic SQL query
210 expression which returns values for the specified columns.
214 <title>Examples</title>
216 (select [first-name] [last-name] [email]
218 :where [= [emplid] 11]
221 (insert-records :into [employee]
222 :attributes '(emplid groupid first_name last_name email
223 ecompanyid managerid)
224 :values '(11 1 "Yuri" "Gagarin" "gagarin@soviet.org"
227 (select [first-name] [last-name] [email]
229 :where [= [emplid] 11]
231 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
235 <title>Side Effects</title>
237 Modifications are made to the underlying database.
241 <title>Affected by</title>
247 <title>Exceptional Situations</title>
249 An error of type <symbol>sql-database-data-error</symbol> is
250 signalled if <parameter>table</parameter> is not an existing
251 table in <parameter>database</parameter> or if the specified
252 attributes are not found.
256 <title>See Also</title>
258 <member><link linkend="update-records"><function>update-records</function></link></member>
259 <member><link linkend="delete-records"><function>delete-records</function></link></member>
270 <refentry id="update-records">
272 <refentrytitle>UPDATE-RECORDS</refentrytitle>
275 <refname>UPDATE-RECORDS</refname>
276 <refpurpose>Updates the values of existing records.</refpurpose>
277 <refclass>Function</refclass>
280 <title>Syntax</title>
282 <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>
285 <title>Arguments and Values</title>
288 <term><parameter>table</parameter></term>
291 A string, symbol or symbolic SQL expression representing
292 the name of a table existing in
293 <parameter>database</parameter>.
298 <term><parameter>attributes</parameter></term>
301 A list of attribute identifiers or &nil;.
306 <term><parameter>values</parameter></term>
309 A list of attribute values or &nil;.
314 <term><parameter>av-pairs</parameter></term>
317 A list of attribute identifier/value pairs or &nil;.
322 <term><parameter>where</parameter></term>
325 A symbolic SQL expression.
330 <term><parameter>database</parameter></term>
333 <glossterm linkend="gloss-database-object">database
334 object</glossterm>. This will default to the value
335 of <symbol>*default-database*</symbol>.</para>
341 <title>Description</title>
343 Updates the attribute values of existing records satsifying
344 the SQL expression <parameter>where</parameter> in the table
345 specified by <parameter>table</parameter> in
346 <parameter>database</parameter> which defaults to
347 <symbol>*default-database*</symbol>.
350 There are three ways of specifying the values to update for
351 each row. In the first, <parameter>values</parameter> contains
352 a list of values to use in the update and
353 <parameter>attributes</parameter> and
354 <parameter>av-pairs</parameter> are &nil;. This can be used
355 when values are supplied for all attributes in
356 <parameter>table</parameter>. In the second,
357 <parameter>attributes</parameter> is a list of column names,
358 <parameter>values</parameter> is a corresponding list of
359 values and <parameter>av-pairs</parameter> is &nil;. In the
360 third, <parameter>attributes</parameter> and
361 <parameter>values</parameter> are &nil; and
362 <parameter>av-pairs</parameter> is an alist of (attribute
367 <title>Examples</title>
369 (select [first-name] [last-name] [email]
371 :where [= [emplid] 1]
373 => (("Vladamir" "Lenin" "lenin@soviet.org"))
374 (update-records [employee]
375 :av-pairs'((first_name "Yuri")
376 (last_name "Gagarin")
377 (email "gagarin@soviet.org"))
378 :where [= [emplid] 1])
380 (select [first-name] [last-name] [email]
382 :where [= [emplid] 1]
384 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
388 <title>Side Effects</title>
390 Modifications are made to the underlying database.
394 <title>Affected by</title>
400 <title>Exceptional Situations</title>
402 An error of type <symbol>sql-database-data-error</symbol> is
403 signalled if <parameter>table</parameter> is not an existing
404 table in <parameter>database</parameter>, if the specified
405 attributes are not found or if the SQL statement resulting
406 from the symbolic expression <parameter>where</parameter> does
407 not return a Boolean value.
409 <para>If the execution of the SQL query leads to any errors, an
410 error of type <errortype>sql-database-error</errortype> is
414 <title>See Also</title>
417 <member><link linkend="insert-records"><function>insert-records</function></link></member>
418 <member><link linkend="delete-records"><function>delete-records</function></link></member>
430 <refentry id="delete-records">
432 <refentrytitle>DELETE-RECORDS</refentrytitle>
435 <refname>DELETE-RECORDS</refname>
436 <refpurpose>Delete records from a database table.</refpurpose>
437 <refclass>Function</refclass>
440 <title>Syntax</title>
442 <function>delete-records</function> &key <replaceable>from</replaceable> <replaceable>where</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
445 <title>Arguments and Values</title>
448 <term><parameter>from</parameter></term>
451 A string, symbol or symbolic SQL expression representing
452 the name of a table existing in
453 <parameter>database</parameter>.
458 <term><parameter>where</parameter></term>
461 A symbolic SQL expression.
466 <term><parameter>database</parameter></term>
469 <glossterm linkend="gloss-database-object">database
470 object</glossterm>. This will default to the value
471 of <symbol>*default-database*</symbol>.</para>
477 <title>Description</title>
478 <para>Deletes records satisfying the SQL expression
479 <parameter>where</parameter> from the table specified by
480 <parameter>from</parameter> in <parameter>database</parameter>
481 specifies a database which defaults to
482 <symbol>*default-database*</symbol>.
486 <title>Examples</title>
488 (select [first-name] [last-name] [email]
490 :where [= [emplid] 11]
492 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
493 (delete-records :from [employee] :where [= [emplid] 11])
495 (select [first-name] [last-name] [email]
497 :where [= [emplid] 11]
503 <title>Side Effects</title>
505 Modifications are made to the underlying database.
509 <title>Affected by</title>
515 <title>Exceptional Situations</title>
517 An error of type <symbol>sql-database-data-error</symbol> is
518 signalled if <parameter>from</parameter> is not an existing
519 table in <parameter>database</parameter> or if the SQL
520 statement resulting from the symbolic expression
521 <parameter>where</parameter> does not return a Boolean value.
525 <title>See Also</title>
528 <member><link linkend="insert-records"><function>insert-records</function></link></member>
529 <member><link linkend="update-records"><function>update-records</function></link></member>
542 <!-- executing SQL commands and queries -->
544 <refentry id="execute-command">
546 <refentrytitle>EXECUTE-COMMAND</refentrytitle>
549 <refname>EXECUTE-COMMAND</refname>
550 <refpurpose>Execute an SQL command which returns no values.</refpurpose>
551 <refclass>Generic Function</refclass>
554 <title>Syntax</title>
556 <function>execute-command</function> <replaceable>sql-expression</replaceable> &key <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
559 <title>Arguments and Values</title>
562 <term><parameter>sql-expression</parameter></term>
564 <para>An <glossterm linkend="gloss-sql-expression">sql
565 expression</glossterm> that represents an SQL
566 statement which will return no values.</para>
570 <term><parameter>database</parameter></term>
573 <glossterm linkend="gloss-database-object">database
574 object</glossterm>. This will default to the value
575 of <symbol>*default-database*</symbol>.</para>
577 </varlistentry></variablelist>
580 <title>Description</title>
581 <para>Executes the SQL command
582 <parameter>sql-expression</parameter>, which may be a symbolic
583 SQL expression or a string representing any SQL statement apart
584 from a query, on the supplied <parameter>database</parameter>
585 which defaults to <symbol>*default-database*</symbol>.
589 <title>Examples</title>
591 (execute-command "create table eventlog (time char(30),event char(70))")
594 (execute-command "create table eventlog (time char(30),event char(70))")
596 >> While accessing database #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {480B2B6D}>
597 >> with expression "create table eventlog (time char(30),event char(70))":
598 >> Error NIL: ERROR: amcreate: eventlog relation already exists
602 >> 0: [ABORT] Return to Top-Level.
604 >> Debug (type H for help)
606 >> (CLSQL-POSTGRESQL::|(PCL::FAST-METHOD DATABASE-EXECUTE-COMMAND (T POSTGRESQL-DATABASE))|
609 >> #<unavailable-arg>
610 >> #<unavailable-arg>)
611 >> Source: (ERROR 'SQL-DATABASE-ERROR :DATABASE DATABASE :EXPRESSION ...)
614 (execute-command "drop table eventlog")
619 <title>Side Effects</title>
620 <para>Whatever effects the execution of the SQL statement has
621 on the underlying database, if any.</para>
624 <title>Affected by</title>
628 <title>Exceptional Situations</title>
629 <para>If the execution of the SQL statement leads to any errors,
630 an error of type <errortype>sql-database-error</errortype> is
634 <title>See Also</title>
637 <member><link linkend="query"><function>query</function></link></member>
648 <refentry id="query">
650 <refentrytitle>QUERY</refentrytitle>
653 <refname>QUERY</refname>
654 <refpurpose>Execute an SQL query and return the tuples as a
656 <refclass>Generic Function</refclass>
659 <title>Syntax</title>
661 <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>
664 <title>Arguments and Values</title>
667 <term><parameter>query-expression</parameter></term>
669 <para>An <glossterm linkend="gloss-sql-expression">sql
670 expression</glossterm> that represents an SQL
671 query which is expected to return a (possibly empty)
676 <term><parameter>database</parameter></term>
679 <glossterm linkend="gloss-database-object">database
680 object</glossterm>. This will default to the value
681 of <symbol>*default-database*</symbol>.</para>
685 <term><parameter>flatp</parameter></term>
687 <para>A Boolean whose default value is &nil;.</para>
691 <term><parameter>result-types</parameter></term>
694 <glossterm linkend="gloss-field-types">field type
695 specifier</glossterm>. The default is &nil;.
698 The purpose of this argument is cause &clsql; to
699 import SQL numeric fields into numeric Lisp objects
700 rather than strings. This reduces the cost of
701 allocating a temporary string and the &clsql; users'
702 inconvenience of converting number strings into number
706 A value of <symbol>:auto</symbol> causes &clsql;
707 to automatically convert SQL fields into a
708 numeric format where applicable. The default value of
709 &nil; causes all fields to be returned as strings
710 regardless of the SQL type. Otherwise a list is expected
711 which has a element for each field that specifies the
712 conversion. Valid type identifiers are:
713 <simplelist type="vert">
714 <member><symbol>:int</symbol> Field is imported as a
715 signed integer, from 8-bits to 64-bits depending
718 <member><symbol>:double</symbol> Field is imported as a
721 <member><symbol>t</symbol> Field is imported as a
725 If the list is shorter than the number of fields, the a
726 value of <symbol>t</symbol> is assumed for the field.
727 If the list is longer than the number of fields, the
728 extra elements are ignored.
733 <term><parameter>field-names</parameter></term>
736 A boolean with a default value of &t;. When &t;, this
737 function returns a second value of a list of field
738 names. When &nil;, this function only returns one value -
744 <term><returnvalue>result</returnvalue></term>
746 <para>A list representing the result set obtained. For
747 each tuple in the result set, there is an element in
748 this list, which is itself a list of all the attribute
749 values in the tuple.</para>
755 <title>Description</title>
757 Executes the SQL query expression
758 <parameter>query-expression</parameter>, which may be an SQL
759 expression or a string, on the supplied
760 <parameter>database</parameter> which defaults to
761 <symbol>*default-database*</symbol>. <parameter>result-types</parameter>
762 is a list of symbols which specifies the lisp type for each
763 field returned by <parameter>query-expression</parameter>.
766 If <parameter>result-types</parameter> is &nil; all results
767 are returned as strings whereas the default value of
768 <symbol>:auto</symbol> means that the lisp types are
769 automatically computed for each field.
772 <parameter>field-names</parameter> is &t; by default which
773 means that the second value returned is a list of strings
774 representing the columns selected by
775 <parameter>query-expression</parameter>. If
776 <parameter>field-names</parameter> is &nil;, the list of column
777 names is not returned as a second value.
780 <parameter>flatp</parameter> has a default value of &nil;
781 which means that the results are returned as a list of
782 lists.If FLATP is &t; and only one result is returned for each
783 record selected by <parameter>query-expression</parameter>,
784 the results are returned as elements of a list.
788 <title>Examples</title>
790 (query "select emplid,first_name,last_name,height from employee where emplid = 1")
791 => ((1 "Vladamir" "Lenin" 1.5564661d0)),
792 ("emplid" "first_name" "last_name" "height")
794 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
796 => ((1 "Vladamir" "Lenin" 1.5564661d0))
798 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
801 => (("1" "Vladamir" "Lenin" "1.5564661"))
803 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
805 :result-types '(:int t t :double))
806 => ((1 "Vladamir" "Lenin" 1.5564661))
808 (query "select last_name from employee where emplid > 5" :flatp t)
809 => ("Andropov" "Chernenko" "Gorbachev" "Yeltsin" "Putin"),
812 (query "select last_name from employee where emplid > 10"
819 <title>Side Effects</title>
820 <para>Whatever effects the execution of the SQL query has
821 on the underlying database, if any.</para>
824 <title>Affected by</title>
828 <title>Exceptional Situations</title>
829 <para>If the execution of the SQL query leads to any errors, an
830 error of type <errortype>sql-database-error</errortype> is
834 <title>See Also</title>
836 <member><link linkend="execute-command"><function>execute-command</function></link></member>
837 <member><link linkend="print-query"><function>print-query</function></link></member>
838 <member><link linkend="do-query"><function>do-query</function></link></member>
839 <member><link linkend="map-query"><function>map-query</function></link></member>
840 <member><link linkend="loop-tuples"><function>loop</function></link></member>
841 <member><link linkend="select"><function>select</function></link></member>
846 <para>The <parameter>field-names</parameter> and
847 <parameter>result-types</parameter> keyword arguments are a
848 &clsql; extension.</para>
852 <refentry id="print-query">
854 <refentrytitle>PRINT-QUERY</refentrytitle>
857 <refname>PRINT-QUERY</refname>
858 <refpurpose>Prints a tabular report of query results.</refpurpose>
859 <refclass>Function</refclass>
862 <title>Syntax</title>
864 <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>
867 <title>Arguments and Values</title>
870 <term><parameter>query-expression</parameter></term>
872 <para>An <glossterm linkend="gloss-sql-expression">sql
873 expression</glossterm> that represents an SQL
874 query which is expected to return a (possibly empty)
879 <term><parameter>database</parameter></term>
882 <glossterm linkend="gloss-database-object">database
883 object</glossterm>. This will default to the value
884 of <symbol>*default-database*</symbol>.</para>
888 <term><parameter>titles</parameter></term>
891 A list of strings or &nil; which is the default value.
896 <term><parameter>formats</parameter></term>
899 A list of strings, &nil; or &t; which is the default value.
904 <term><parameter>sizes</parameter></term>
907 A list of numbers, &nil; or &t; which is the default value.
912 <term><parameter>stream</parameter></term>
915 An output stream or &t; which is the default value.
922 <title>Description</title>
923 <para>Prints a tabular report of the results returned by the SQL
924 query <parameter>query-expression</parameter>, which may be a
925 symbolic SQL expression or a string, in
926 <parameter>database</parameter> which defaults to
927 <symbol>*default-database*</symbol>. The report is printed onto
928 <parameter>stream</parameter> which has a default value of &t;
929 which means that <symbol>*standard-output*</symbol> is used. The
930 <parameter>title</parameter> argument, which defaults to &nil;,
931 allows the specification of a list of strings to use as column
932 titles in the tabular output. <parameter>sizes</parameter>
933 accepts a list of column sizes, one for each column selected by
934 <parameter>query-expression</parameter>, to use in formatting
935 the tabular report. The default value of &t; means that minimum
936 sizes are computed. <parameter>formats</parameter> is a list of
937 format strings to be used for printing each column selected by
938 <parameter>query-expression</parameter>. The default value of
939 <parameter>formats</parameter> is &t; meaning that
940 <symbol>~A</symbol> is used to format all columns or
941 <symbol>~VA</symbol> if column sizes are used.
945 <title>Examples</title>
947 (print-query [select [emplid] [first-name] [last-name] [email]
949 :where [< [emplid] 5]]
950 :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
951 ID FORENAME SURNAME EMAIL
952 1 Vladamir Lenin lenin@soviet.org
953 2 Josef Stalin stalin@soviet.org
954 3 Leon Trotsky trotsky@soviet.org
955 4 Nikita Kruschev kruschev@soviet.org
958 (print-query "select emplid,first_name,last_name,email from employee where emplid >= 5"
959 :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
960 ID FORENAME SURNAME EMAIL
961 5 Leonid Brezhnev brezhnev@soviet.org
962 6 Yuri Andropov andropov@soviet.org
963 7 Konstantin Chernenko chernenko@soviet.org
964 8 Mikhail Gorbachev gorbachev@soviet.org
965 9 Boris Yeltsin yeltsin@soviet.org
966 10 Vladamir Putin putin@soviet.org
971 <title>Side Effects</title>
977 <title>Affected by</title>
983 <title>Exceptional Situations</title>
985 If the execution of the SQL query leads to any errors, an
986 error of type <errortype>sql-database-error</errortype> is
991 <title>See Also</title>
993 <member><link linkend="query"><function>query</function></link></member>
994 <member><link linkend="do-query"><function>do-query</function></link></member>
995 <member><link linkend="map-query"><function>map-query</function></link></member>
996 <member><link linkend="loop-tuples"><function>loop</function></link></member>
997 <member><link linkend="select"><function>select</function></link></member>
1001 <title>Notes</title>
1008 <refentry id="select">
1010 <refentrytitle>SELECT</refentrytitle>
1013 <refname>SELECT</refname>
1014 <refpurpose>Executes a query given the supplied constraints.</refpurpose>
1015 <refclass>Function</refclass>
1018 <title>Syntax</title>
1020 <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>
1023 <title>Arguments and Values</title>
1026 <term><parameter>identifiers</parameter></term>
1029 A set of <glossterm linkend="gloss-sql-expression">sql
1030 expressions</glossterm> each of which indicates a column
1036 <term><parameter>all</parameter></term>
1044 <term><parameter>distinct</parameter></term>
1052 <term><parameter>from</parameter></term>
1055 One or more SQL expression representing tables.
1060 <term><parameter>group-by</parameter></term>
1068 <term><parameter>having</parameter></term>
1076 <term><parameter>order-by</parameter></term>
1084 <term><parameter>set-operation</parameter></term>
1092 <term><parameter>where</parameter></term>
1100 <term><parameter>database</parameter></term>
1103 <glossterm linkend="gloss-database-object">database
1104 object</glossterm>. This will default to the value
1105 of <symbol>*default-database*</symbol>.</para>
1109 <term><parameter>flatp</parameter></term>
1111 <para>A Boolean whose default value is &nil;.</para>
1115 <term><parameter>result-types</parameter></term>
1118 <glossterm linkend="gloss-field-types">field type
1119 specifier</glossterm>. The default is &nil;.
1122 The purpose of this argument is cause &clsql; to
1123 import SQL numeric fields into numeric Lisp objects
1124 rather than strings. This reduces the cost of
1125 allocating a temporary string and the &clsql; users'
1126 inconvenience of converting number strings into number
1130 A value of <symbol>:auto</symbol> causes &clsql;
1131 to automatically convert SQL fields into a
1132 numeric format where applicable. The default value of
1133 &nil; causes all fields to be returned as strings
1134 regardless of the SQL type. Otherwise a list is expected
1135 which has a element for each field that specifies the
1136 conversion. Valid type identifiers are:
1137 <simplelist type="vert">
1138 <member><symbol>:int</symbol> Field is imported as a
1139 signed integer, from 8-bits to 64-bits depending
1140 upon the field type.
1142 <member><symbol>:double</symbol> Field is imported as a
1143 double-float number.
1145 <member><symbol>t</symbol> Field is imported as a
1149 If the list is shorter than the number of fields, the a
1150 value of <symbol>t</symbol> is assumed for the field.
1151 If the list is longer than the number of fields, the
1152 extra elements are ignored.
1157 <term><parameter>field-names</parameter></term>
1160 A boolean with a default value of &t;. When &t;, this
1161 function returns a second value of a list of field
1162 names. When &nil;, this function only returns one value -
1168 <term><parameter>result</parameter></term>
1171 A list representing the result set obtained. For each
1172 tuple in the result set, there is an element in this
1173 list, which is itself a list of all the attribute values
1181 <title>Description</title>
1183 Executes a query on <parameter>database</parameter>, which has
1184 a default value of <symbol>*default-database*</symbol>,
1185 specified by the SQL expressions supplied using the remaining
1186 arguments in <parameter>args</parameter>. The
1187 <function>select</function> function can be used to generate
1188 queries in both functional and object oriented contexts.
1191 In the functional case, the required arguments specify the
1192 columns selected by the query and may be symbolic SQL
1193 expressions or strings representing attribute
1194 identifiers. Type modified identifiers indicate that the
1195 values selected from the specified column are converted to the
1196 specified lisp type. The keyword arguments
1197 <parameter>all</parameter>, <parameter>distinct</parameter>,
1198 <parameter>from</parameter>, <parameter>group-by</parameter>,
1199 <parameter>having</parameter>,
1200 <parameter>order-by</parameter>,
1201 <parameter>set-operation</parameter> and
1202 <parameter>where</parameter> are used to specify, using the
1203 symbolic SQL syntax, the corresponding components of the SQL
1204 query generated by the call to
1205 <function>select</function>.
1208 <parameter>result-types</parameter> is a list of symbols which
1209 specifies the lisp type for each field returned by the
1210 query. If <parameter>result-types</parameter> is &nil; all
1211 results are returned as strings whereas the default value of
1212 <symbol>:auto</symbol> means that the lisp types are
1213 automatically computed for each
1214 field. <parameter>field-names</parameter> is &t; by default
1215 which means that the second value returned is a list of
1216 strings representing the columns selected by the query. If
1217 <parameter>field-names</parameter> is &nil;, the list of
1218 column names is not returned as a second value.
1221 In the object oriented case, the required arguments to
1222 <function>select</function> are symbols denoting View Classes
1223 which specify the database tables to query. In this case,
1224 <function>select</function> returns a list of View Class
1225 instances whose slots are set from the attribute values of the
1226 records in the specified table. <symbol>Slot-value</symbol> is
1227 a legal operator which can be employed as part of the symbolic
1228 SQL syntax used in the <parameter>where</parameter> keyword
1229 argument to <function>select</function>.
1230 <parameter>refresh</parameter> is &nil; by default which means
1231 that the View Class instances returned are retrieved from a
1232 cache if an equivalent call to <function>select</function> has
1233 previously been issued. If <parameter>refresh</parameter> is
1234 true, the View Class instances returned are updated as
1235 necessary from the database and the generic function
1236 <function>instance-refreshed</function> is called to perform
1237 any necessary operations on the updated instances.
1240 In both object oriented and functional contexts,
1241 <parameter>flatp</parameter> has a default value of &nil;
1242 which means that the results are returned as a list of
1243 lists. If <parameter>flatp</parameter> is t and only one
1244 result is returned for each record selected in the query, the
1245 results are returned as elements of a list.
1249 <title>Examples</title>
1251 (select [first-name] :from [employee] :flatp t :distinct t
1254 :order-by [first-name])
1255 => ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
1258 (select [first-name] [count [*]] :from [employee]
1260 :group-by [first-name]
1261 :order-by [first-name]
1263 => (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
1264 ("Mikhail" "1") ("Nikita" "1") ("Vladamir" "2") ("Yuri" "1"))
1266 (select [last-name] :from [employee]
1267 :where [like [email] "%org"]
1268 :order-by [last-name]
1272 => ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
1273 "Stalin" "Trotsky" "Yeltsin")
1275 (select [max [emplid]] :from [employee]
1278 :result-types :auto)
1281 (clsql:select [avg [height]] :from [employee] :flatp t :field-names nil)
1284 (select [emplid] [last-name] :from [employee] :where [= [emplid] 1])
1286 ("emplid" "last_name")
1288 (select [emplid :string] :from [employee]
1289 :where [= 1 [emplid]]
1294 (select [emplid] :from [employee] :order-by [emplid]
1295 :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]]
1300 (clsql:select [emplid] :from [employee]
1301 :where [in [emplid] '(1 2 3 4)]
1307 (select [first-name] [last-name] :from [employee]
1309 :order-by '(([first-name] :asc) ([last-name] :desc)))
1310 => (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
1311 ("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
1312 ("Nikita" "Kruschev") ("Vladamir" "Putin") ("Vladamir" "Lenin")
1313 ("Yuri" "Andropov"))
1315 (select [last-name] :from [employee]
1316 :set-operation [union [select [first-name] :from [employee]
1317 :order-by [last-name]]]
1321 => ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
1322 "Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
1323 "Trotsky" "Vladamir" "Yeltsin" "Yuri")
1327 <title>Side Effects</title>
1328 <para>Whatever effects the execution of the SQL query has on
1329 the underlying database, if any.</para>
1332 <title>Affected by</title>
1338 <title>Exceptional Situations</title>
1340 If the execution of the SQL query leads to any errors, an
1341 error of type <errortype>sql-database-error</errortype> is
1346 <title>See Also</title>
1348 <member><link linkend="query"><function>query</function></link></member>
1349 <member><link linkend="print-query"><function>print-query</function></link></member>
1350 <member><link linkend="do-query"><function>do-query</function></link></member>
1351 <member><link linkend="map-query"><function>map-query</function></link></member>
1352 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1353 <member><link linkend="instance-refreshed"><function>instance-refreshed</function></link></member>
1357 <title>Notes</title>
1359 The <function>select</function> function is actually
1360 implemented in &clsql; with a single
1361 <symbol>&rest</symbol> parameter (which is subsequently
1362 destructured) rather than the keyword parameters presented
1363 here for the purposes of exposition. This means that incorrect
1364 or missing keywords or values may not trigger errors in the
1365 way that they would if <function>select</function> had been
1366 defined using keyword arguments.
1369 The <parameter>field-names</parameter> and
1370 <parameter>result-types</parameter> keyword arguments are a
1374 <parameter>select</parameter> is common across the functional
1375 and object-oriented data manipulation languages.
1381 <!-- iteration and mapping -->
1383 <refentry id="do-query">
1385 <refentrytitle>DO-QUERY</refentrytitle>
1388 <refname>DO-QUERY</refname>
1389 <refpurpose>Iterate over all the tuples of a query.</refpurpose>
1390 <refclass>Macro</refclass>
1393 <title>Syntax</title>
1395 <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>
1398 <title>Arguments and Values</title>
1401 <term><parameter>args</parameter></term>
1403 <para>A list of variable names.</para>
1407 <term><parameter>query-expression</parameter></term>
1409 <para>An <glossterm linkend="gloss-sql-expression">sql
1410 expression</glossterm> that represents an SQL
1411 query which is expected to return a (possibly empty)
1412 result set, where each tuple has as many attributes as
1413 <parameter>function</parameter> takes arguments.</para>
1417 <term><parameter>database</parameter></term>
1420 <glossterm linkend="gloss-database-object">database
1421 object</glossterm>. This will default to
1422 <symbol>*default-database*</symbol>.</para>
1426 <term><parameter>result-types</parameter></term>
1429 A <glossterm linkend="gloss-field-types">field type
1430 specifier</glossterm>. The default is &nil;. See <link
1431 linkend="query"><function>query</function></link> for
1432 the semantics of this argument.
1437 <term><parameter>body</parameter></term>
1439 <para>A body of Lisp code, like in a
1440 <function>destructuring-bind</function> form.</para>
1444 <term><parameter>result</parameter></term>
1446 <para>The result of executing <parameter>body</parameter>.</para>
1452 <title>Description</title>
1454 Repeatedly executes <parameter>body</parameter> within a
1455 binding of <parameter>args</parameter> on the fields of each
1456 row selected by the SQL query
1457 <parameter>query-expression</parameter>, which may be a string
1458 or a symbolic SQL expression, in
1459 <parameter>database</parameter> which defaults to
1460 <symbol>*default-database*</symbol>.
1463 The body of code is executed in a block named
1464 <symbol>nil</symbol> which may be returned from prematurely
1465 via <function>return</function> or
1466 <function>return-from</function>. In this case the result of
1467 evaluating the <function>do-query</function> form will be the
1468 one supplied to <function>return</function> or
1469 <function>return-from</function>. Otherwise the result will
1470 be <symbol>nil</symbol>.
1473 The body of code appears also is if wrapped in a
1474 <function>destructuring-bind</function> form, thus allowing
1475 declarations at the start of the body, especially those
1476 pertaining to the bindings of the variables named in
1477 <parameter>args</parameter>.
1480 <parameter>result-types</parameter> is a list of symbols which
1481 specifies the lisp type for each field returned by
1482 <parameter>query-expression</parameter>. If
1483 <parameter>result-types</parameter> is &nil; all results are
1484 returned as strings whereas the default value of
1485 <symbol>:auto</symbol> means that the lisp types are
1486 automatically computed for each field.
1489 <parameter>query-expression</parameter> may be an object query
1490 (i.e., the selection arguments refer to View Classes), in
1491 which case <parameter>args</parameter> are bound to the tuples
1492 of View Class instances returned by the object oriented query.
1496 <title>Examples</title>
1498 (do-query ((salary name) "select salary,name from simple")
1499 (format t "~30A gets $~2,5$~%" name (read-from-string salary)))
1500 >> Mai, Pierre gets $10000.00
1501 >> Hacker, Random J. gets $08000.50
1504 (do-query ((salary name) "select salary,name from simple")
1505 (return (cons salary name)))
1506 => ("10000.00" . "Mai, Pierre")
1509 (do-query ((name) [select [last-name] :from [employee]
1510 :order-by [last-name]])
1513 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1514 "Chernenko" "Brezhnev" "Andropov")
1517 (do-query ((e) [select 'employee :order-by [last-name]])
1518 (push (slot-value e 'last-name) result))
1520 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1521 "Chernenko" "Brezhnev" "Andropov")
1525 <title>Side Effects</title>
1526 <para>Whatever effects the execution of the SQL query has
1527 on the underlying database, if any.</para>
1530 <title>Affected by</title>
1534 <title>Exceptional Situations</title>
1535 <para>If the execution of the SQL query leads to any
1536 errors, an error of type
1537 <errortype>sql-database-error</errortype> is signalled.</para>
1538 <para>If the number of variable names in
1539 <parameter>args</parameter> and the number of attributes in
1540 the tuples in the result set don't match up, an error is
1544 <title>See Also</title>
1546 <member><link linkend="query"><function>query</function></link></member>
1547 <member><link linkend="map-query"><function>map-query</function></link></member>
1548 <member><link linkend="print-query"><function>print-query</function></link></member>
1549 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1550 <member><link linkend="select"><function>select</function></link></member>
1554 <title>Notes</title>
1555 <para>The <parameter>result-types</parameter> keyword argument
1556 is a &clsql; extension.</para>
1558 <parameter>do-query</parameter> is common across the functional
1559 and object-oriented data manipulation languages.
1564 <refentry id="loop-tuples">
1566 <refentrytitle>LOOP</refentrytitle>
1569 <refname>LOOP</refname>
1570 <refpurpose>Extension to Common Lisp
1571 <computeroutput>Loop</computeroutput> to iterate over all the
1572 tuples of a query via a loop clause.</refpurpose>
1573 <refclass>Loop Clause</refclass>
1576 <title>Compatibility</title>
1577 <caution><para><function>loop-for-as-tuples</function> only works with &cmucl;.</para></caution>
1580 <title>Syntax</title>
1581 <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>
1584 <title>Arguments and Values</title>
1587 <term><parameter>var</parameter></term>
1589 <para>A <literal>d-var-spec</literal>, as defined in the
1590 grammar for <function>loop</function>-clauses in the ANSI
1591 Standard for Common Lisp. This allows for the usual
1592 loop-style destructuring.</para>
1596 <term><parameter>type-spec</parameter></term>
1598 <para>An optional <literal>type-spec</literal> either
1599 simple or destructured, as defined in the grammar for
1600 <function>loop</function>-clauses in the ANSI Standard for
1605 <term><parameter>query</parameter></term>
1607 <para>An <glossterm linkend="gloss-sql-expression">sql
1608 expression</glossterm> that represents an SQL
1609 query which is expected to return a (possibly empty)
1610 result set, where each tuple has as many attributes as
1611 <parameter>function</parameter> takes arguments.</para>
1615 <term><parameter>database</parameter></term>
1618 <glossterm linkend="gloss-database-object">database
1619 object</glossterm>. This will default to the value
1620 of <symbol>*default-database*</symbol>.</para>
1626 <title>Description</title>
1627 <para>This clause is an iteration driver for
1628 <function>loop</function>, that binds the given variable
1629 (possibly destructured) to the consecutive tuples (which are
1630 represented as lists of attribute values) in the result set
1631 returned by executing the SQL <parameter>query</parameter>
1632 expression on the <parameter>database</parameter>
1635 <parameter>query</parameter> may be an object query (i.e., the
1636 selection arguments refer to View Classes), in which case the
1637 supplied variable is bound to the tuples of View Class
1638 instances returned by the object oriented query.
1642 <title>Examples</title>
1644 (defvar *my-db* (connect '("dent" "newesim" "dent" "dent"))
1647 (loop with time-graph = (make-hash-table :test #'equal)
1648 with event-graph = (make-hash-table :test #'equal)
1649 for (time event) being the tuples of "select time,event from log"
1652 (incf (gethash time time-graph 0))
1653 (incf (gethash event event-graph 0))
1655 (flet ((show-graph (k v) (format t "~40A => ~5D~%" k v)))
1656 (format t "~&Time-Graph:~%===========~%")
1657 (maphash #'show-graph time-graph)
1658 (format t "~&~%Event-Graph:~%============~%")
1659 (maphash #'show-graph event-graph))
1660 (return (values time-graph event-graph)))
1669 >> CLOS Benchmark entry. => 9000
1670 >> Demo Text... => 3
1671 >> doit-text => 3000
1672 >> C Benchmark entry. => 12000
1673 >> CLOS Benchmark entry => 32000
1674 => #<EQUAL hash table, 3 entries {48350A1D}>
1675 => #<EQUAL hash table, 5 entries {48350FCD}>
1677 (loop for (forename surname)
1679 [select [first-name] [last-name] :from [employee]
1680 :order-by [last-name]]
1681 collect (concatenate 'string forename " " surname))
1682 => ("Yuri Andropov" "Leonid Brezhnev" "Konstantin Chernenko" "Mikhail Gorbachev"
1683 "Nikita Kruschev" "Vladamir Lenin" "Vladamir Putin" "Josef Stalin"
1684 "Leon Trotsky" "Boris Yeltsin")
1686 (loop for (e) being the records in
1687 [select 'employee :where [< [emplid] 4] :order-by [emplid]]
1688 collect (slot-value e 'last-name))
1689 => ("Lenin" "Stalin" "Trotsky")
1693 <title>Side Effects</title>
1694 <para>Whatever effects the execution of the SQL query has
1695 on the underlying database, if any.</para>
1698 <title>Affected by</title>
1702 <title>Exceptional Situations</title>
1703 <para>If the execution of the SQL query leads to any
1704 errors, an error of type
1705 <errortype>sql-database-error</errortype> is signalled.</para>
1706 <para>Otherwise, any of the exceptional situations of
1707 <function>loop</function> applies.</para>
1710 <title>See Also</title>
1713 <member><link linkend="query"><function>query</function></link></member>
1714 <member><link linkend="map-query"><function>map-query</function></link></member>
1715 <member><link linkend="do-query"><function>do-query</function></link></member>
1716 <member><link linkend="print-query"><function>print-query</function></link></member>
1717 <member><link linkend="select"><function>select</function></link></member>
1722 <title>Notes</title>
1723 <para>The <parameter>database</parameter> loop keyword is a
1724 &clsql; extension.</para>
1726 The extended <function>loop</function> syntax is common across
1727 the functional and object-oriented data manipulation
1733 <refentry id="map-query">
1735 <refentrytitle>MAP-QUERY</refentrytitle>
1738 <refname>MAP-QUERY</refname>
1739 <refpurpose>Map a function over all the tuples from a
1741 <refclass>Function</refclass>
1744 <title>Syntax</title>
1745 <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>
1748 <title>Arguments and Values</title>
1751 <term><parameter>output-type-spec</parameter></term>
1753 <para>A sequence type specifier or <symbol>nil</symbol>.</para>
1757 <term><parameter>function</parameter></term>
1759 <para>A function designator.
1760 <parameter>function</parameter> takes a single argument which
1761 is the atom value for a query single with a single column
1762 or is a list of values for a multi-column query.</para>
1766 <term><parameter>query-expression</parameter></term>
1768 <para>An <glossterm linkend="gloss-sql-expression">sql
1769 expression</glossterm> that represents an SQL
1770 query which is expected to return a (possibly empty)
1775 <term><parameter>database</parameter></term>
1778 <glossterm linkend="gloss-database-object">database
1779 object</glossterm>. This will default to the value
1780 of <symbol>*default-database*</symbol>.</para>
1784 <term><parameter>result-types</parameter></term>
1787 A <glossterm linkend="gloss-field-types">field type
1788 specifier</glossterm>. The default is &nil;. See <link
1789 linkend="query"><function>query</function></link> for
1790 the semantics of this argument.
1795 <term><returnvalue>result</returnvalue></term>
1797 <para>If <parameter>output-type-spec</parameter> is a
1798 type specifier other than <symbol>nil</symbol>, then a
1799 sequence of the type it denotes. Otherwise
1800 <symbol>nil</symbol> is returned.</para>
1806 <title>Description</title>
1808 Applies <parameter>function</parameter> to the successive
1809 tuples in the result set returned by executing the SQL
1810 <parameter>query-expression</parameter>. If the
1811 <parameter>output-type-spec</parameter> is
1812 <symbol>nil</symbol>, then the result of each application of
1813 <parameter>function</parameter> is discarded, and
1814 <function>map-query</function> returns <symbol>nil</symbol>.
1815 Otherwise the result of each successive application of
1816 <parameter>function</parameter> is collected in a sequence of
1817 type <parameter>output-type-spec</parameter>, where the jths
1818 element is the result of applying
1819 <parameter>function</parameter> to the attributes of the jths
1820 tuple in the result set. The collected sequence is the result
1821 of the call to <function>map-query</function>.
1824 If the <parameter>output-type-spec</parameter> is a subtype of
1825 <type>list</type>, the result will be a <type>list</type>.
1828 If the <parameter>result-type</parameter> is a subtype of
1829 <type>vector</type>, then if the implementation can determine
1830 the element type specified for the
1831 <parameter>result-type</parameter>, the element type of the
1832 resulting array is the result of
1833 <emphasis>upgrading</emphasis> that element type; or, if the
1834 implementation can determine that the element type is
1835 unspecified (or <symbol>*</symbol>), the element type of the
1836 resulting array is <type>t</type>; otherwise, an error is
1840 If <parameter>result-types</parameter> is &nil; all results
1841 are returned as strings whereas the default value of
1842 <symbol>:auto</symbol> means that the lisp types are
1843 automatically computed for each field.</para>
1845 <parameter>query-expression</parameter> may be an object query
1846 (i.e., the selection arguments refer to View Classes), in
1847 which case the supplied function is applied to the tuples of
1848 View Class instances returned by the object oriented query.
1852 <title>Examples</title>
1854 (map-query 'list #'(lambda (tuple)
1855 (multiple-value-bind (salary name) tuple
1856 (declare (ignorable name))
1857 (read-from-string salary)))
1858 "select salary,name from simple where salary > 8000")
1861 (map-query '(vector double-float)
1863 (multiple-value-bind (salary name) tuple
1864 (declare (ignorable name))
1865 (let ((*read-default-float-format* 'double-float))
1866 (coerce (read-from-string salary) 'double-float))
1867 "select salary,name from simple where salary > 8000")))
1868 => #(10000.0d0 8000.5d0)
1870 => (SIMPLE-ARRAY DOUBLE-FLOAT (2))
1873 (values (map-query nil #'(lambda (tuple)
1874 (multiple-value-bind (salary name) tuple
1875 (push (cons name (read-from-string salary)) list))
1876 "select salary,name from simple where salary > 8000"))
1879 => (("Hacker, Random J." . 8000.5) ("Mai, Pierre" . 10000.0))
1881 (map-query 'vector #'identity
1882 [select [last-name] :from [employee] :flatp t
1883 :order-by [last-name]])
1884 => #("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
1885 "Stalin" "Trotsky" "Yeltsin")
1887 (map-query 'list #'identity
1888 [select [first-name] [last-name] :from [employee]
1889 :order-by [last-name]])
1890 => (("Yuri" "Andropov") ("Leonid" "Brezhnev") ("Konstantin" "Chernenko")
1891 ("Mikhail" "Gorbachev") ("Nikita" "Kruschev") ("Vladamir" "Lenin")
1892 ("Vladamir" "Putin") ("Josef" "Stalin") ("Leon" "Trotsky")
1893 ("Boris" "Yeltsin"))
1895 (map-query 'list #'last-name [select 'employee :order-by [emplid]])
1896 => ("Lenin" "Stalin" "Trotsky" "Kruschev" "Brezhnev" "Andropov" "Chernenko"
1897 "Gorbachev" "Yeltsin" "Putin")
1901 <title>Side Effects</title>
1902 <para>Whatever effects the execution of the SQL query has
1903 on the underlying database, if any.</para>
1906 <title>Affected by</title>
1910 <title>Exceptional Situations</title>
1911 <para>If the execution of the SQL query leads to any
1912 errors, an error of type
1913 <errortype>sql-database-error</errortype> is signalled.</para>
1914 <para>An error of type <errortype>type-error</errortype> must
1915 be signaled if the <parameter>output-type-spec</parameter> is
1916 not a recognizable subtype of <type>list</type>, not a
1917 recognizable subtype of <type>vector</type>, and not
1918 <symbol>nil</symbol>.</para>
1919 <para>An error of type <errortype>type-error</errortype>
1920 should be signaled if
1921 <parameter>output-type-spec</parameter> specifies the number
1922 of elements and the size of the result set is different from
1926 <title>See Also</title>
1928 <member><link linkend="query"><function>query</function></link></member>
1929 <member><link linkend="do-query"><function>do-query</function></link></member>
1930 <member><link linkend="print-query"><function>print-query</function></link></member>
1931 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1932 <member><link linkend="select"><function>select</function></link></member>
1936 <title>Notes</title>
1937 <para>The <parameter>result-types</parameter> keyword argument
1938 is a &clsql; extension.</para>
1940 <parameter>map-query</parameter> is common across the
1941 functional and object-oriented data manipulation languages.