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>
102 <refentry id="insert-records">
104 <refentrytitle>INSERT-RECORDS</refentrytitle>
107 <refname>INSERT-RECORDS</refname>
108 <refpurpose>Insert tuples of data into a database table.</refpurpose>
109 <refclass>Function</refclass>
112 <title>Syntax</title>
114 <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>
117 <title>Arguments and Values</title>
120 <term><parameter>into</parameter></term>
123 A string, symbol or symbolic SQL expression representing
124 the name of a table existing in
125 <parameter>database</parameter>.
130 <term><parameter>attributes</parameter></term>
133 A list of attribute identifiers or &nil;.
138 <term><parameter>values</parameter></term>
141 A list of attribute values or &nil;.
146 <term><parameter>av-pairs</parameter></term>
149 A list of attribute identifier/value pairs or &nil;.
154 <term><parameter>query</parameter></term>
157 A query expression or &nil;.
162 <term><parameter>database</parameter></term>
165 <glossterm linkend="gloss-database-object">database
166 object</glossterm>. This will default to the value
167 of <symbol>*default-database*</symbol>.</para>
173 <title>Description</title>
175 Inserts records into the table specified by
176 <parameter>into</parameter> in <parameter>database</parameter>
177 which defaults to <symbol>*default-database*</symbol>.
180 There are five ways of specifying the values inserted into
181 each row. In the first <parameter>values</parameter> contains
182 a list of values to insert and
183 <parameter>attributes</parameter>,
184 <parameter>av-pairs</parameter> and
185 <parameter>query</parameter> are &nil;. This can be used when
186 values are supplied for all attributes in
187 <parameter>into</parameter>. In the second,
188 <parameter>attributes</parameter> is a list of column names,
189 <parameter>values</parameter> is a corresponding list of
190 values and <parameter>av-pairs</parameter> and
191 <parameter>query</parameter> are &nil;. In the third,
192 <parameter>attributes</parameter>,
193 <parameter>values</parameter> and <parameter>query</parameter>
194 are &nil; and <parameter>av-pairs</parameter> is an alist of
195 (attribute value) pairs. In the fourth,
196 <parameter>values</parameter>, <parameter>av-pairs</parameter>
197 and <parameter>attributes</parameter> are &nil; and
198 <parameter>query</parameter> is a symbolic SQL query
199 expression in which the selected columns also exist in
200 <parameter>into</parameter>. In the fifth method,
201 <parameter>values</parameter> and
202 <parameter>av-pairs</parameter> are nil and
203 <parameter>attributes</parameter> is a list of column names
204 and <parameter>query</parameter> is a symbolic SQL query
205 expression which returns values for the specified columns.
209 <title>Examples</title>
211 (select [first-name] [last-name] [email]
213 :where [= [emplid] 11]
216 (insert-records :into [employee]
217 :attributes '(emplid groupid first_name last_name email
218 ecompanyid managerid)
219 :values '(11 1 "Yuri" "Gagarin" "gagarin@soviet.org"
222 (select [first-name] [last-name] [email]
224 :where [= [emplid] 11]
226 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
230 <title>Side Effects</title>
232 Modifications are made to the underlying database.
236 <title>Affected by</title>
242 <title>Exceptional Situations</title>
244 An error of type <symbol>sql-database-data-error</symbol> is
245 signalled if <parameter>table</parameter> is not an existing
246 table in <parameter>database</parameter> or if the specified
247 attributes are not found.
251 <title>See Also</title>
253 <member><link linkend="update-records"><function>update-records</function></link></member>
254 <member><link linkend="delete-records"><function>delete-records</function></link></member>
265 <refentry id="update-records">
267 <refentrytitle>UPDATE-RECORDS</refentrytitle>
270 <refname>UPDATE-RECORDS</refname>
271 <refpurpose>Updates the values of existing records.</refpurpose>
272 <refclass>Function</refclass>
275 <title>Syntax</title>
277 <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>
280 <title>Arguments and Values</title>
283 <term><parameter>table</parameter></term>
286 A string, symbol or symbolic SQL expression representing
287 the name of a table existing in
288 <parameter>database</parameter>.
293 <term><parameter>attributes</parameter></term>
296 A list of attribute identifiers or &nil;.
301 <term><parameter>values</parameter></term>
304 A list of attribute values or &nil;.
309 <term><parameter>av-pairs</parameter></term>
312 A list of attribute identifier/value pairs or &nil;.
317 <term><parameter>where</parameter></term>
320 A symbolic SQL expression.
325 <term><parameter>database</parameter></term>
328 <glossterm linkend="gloss-database-object">database
329 object</glossterm>. This will default to the value
330 of <symbol>*default-database*</symbol>.</para>
336 <title>Description</title>
338 Updates the attribute values of existing records satsifying
339 the SQL expression <parameter>where</parameter> in the table
340 specified by <parameter>table</parameter> in
341 <parameter>database</parameter> which defaults to
342 <symbol>*default-database*</symbol>.
345 There are three ways of specifying the values to update for
346 each row. In the first, <parameter>values</parameter> contains
347 a list of values to use in the update and
348 <parameter>attributes</parameter> and
349 <parameter>av-pairs</parameter> are &nil;. This can be used
350 when values are supplied for all attributes in
351 <parameter>table</parameter>. In the second,
352 <parameter>attributes</parameter> is a list of column names,
353 <parameter>values</parameter> is a corresponding list of
354 values and <parameter>av-pairs</parameter> is &nil;. In the
355 third, <parameter>attributes</parameter> and
356 <parameter>values</parameter> are &nil; and
357 <parameter>av-pairs</parameter> is an alist of (attribute
362 <title>Examples</title>
364 (select [first-name] [last-name] [email]
366 :where [= [emplid] 1]
368 => (("Vladamir" "Lenin" "lenin@soviet.org"))
369 (update-records [employee]
370 :av-pairs'((first_name "Yuri")
371 (last_name "Gagarin")
372 (email "gagarin@soviet.org"))
373 :where [= [emplid] 1])
375 (select [first-name] [last-name] [email]
377 :where [= [emplid] 1]
379 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
383 <title>Side Effects</title>
385 Modifications are made to the underlying database.
389 <title>Affected by</title>
395 <title>Exceptional Situations</title>
397 An error of type <symbol>sql-database-data-error</symbol> is
398 signalled if <parameter>table</parameter> is not an existing
399 table in <parameter>database</parameter>, if the specified
400 attributes are not found or if the SQL statement resulting
401 from the symbolic expression <parameter>where</parameter> does
402 not return a Boolean value.
404 <para>If the execution of the SQL query leads to any errors, an
405 error of type <errortype>sql-database-error</errortype> is
409 <title>See Also</title>
412 <member><link linkend="insert-records"><function>insert-records</function></link></member>
413 <member><link linkend="delete-records"><function>delete-records</function></link></member>
425 <refentry id="delete-records">
427 <refentrytitle>DELETE-RECORDS</refentrytitle>
430 <refname>DELETE-RECORDS</refname>
431 <refpurpose>Delete records from a database table.</refpurpose>
432 <refclass>Function</refclass>
435 <title>Syntax</title>
437 <function>delete-records</function> &key <replaceable>from</replaceable> <replaceable>where</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
440 <title>Arguments and Values</title>
443 <term><parameter>from</parameter></term>
446 A string, symbol or symbolic SQL expression representing
447 the name of a table existing in
448 <parameter>database</parameter>.
453 <term><parameter>where</parameter></term>
456 A symbolic SQL expression.
461 <term><parameter>database</parameter></term>
464 <glossterm linkend="gloss-database-object">database
465 object</glossterm>. This will default to the value
466 of <symbol>*default-database*</symbol>.</para>
472 <title>Description</title>
473 <para>Deletes records satisfying the SQL expression
474 <parameter>where</parameter> from the table specified by
475 <parameter>from</parameter> in <parameter>database</parameter>
476 specifies a database which defaults to
477 <symbol>*default-database*</symbol>.
481 <title>Examples</title>
483 (select [first-name] [last-name] [email]
485 :where [= [emplid] 11]
487 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
488 (delete-records :from [employee] :where [= [emplid] 11])
490 (select [first-name] [last-name] [email]
492 :where [= [emplid] 11]
498 <title>Side Effects</title>
500 Modifications are made to the underlying database.
504 <title>Affected by</title>
510 <title>Exceptional Situations</title>
512 An error of type <symbol>sql-database-data-error</symbol> is
513 signalled if <parameter>from</parameter> is not an existing
514 table in <parameter>database</parameter> or if the SQL
515 statement resulting from the symbolic expression
516 <parameter>where</parameter> does not return a Boolean value.
520 <title>See Also</title>
523 <member><link linkend="insert-records"><function>insert-records</function></link></member>
524 <member><link linkend="update-records"><function>update-records</function></link></member>
537 <!-- executing SQL commands and queries -->
539 <refentry id="execute-command">
541 <refentrytitle>EXECUTE-COMMAND</refentrytitle>
544 <refname>EXECUTE-COMMAND</refname>
545 <refpurpose>Execute an SQL command which returns no values.</refpurpose>
546 <refclass>Generic Function</refclass>
549 <title>Syntax</title>
551 <function>execute-command</function> <replaceable>sql-expression</replaceable> &key <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
554 <title>Arguments and Values</title>
557 <term><parameter>sql-expression</parameter></term>
559 <para>An <glossterm linkend="gloss-sql-expression">sql
560 expression</glossterm> that represents an SQL
561 statement which will return no values.</para>
565 <term><parameter>database</parameter></term>
568 <glossterm linkend="gloss-database-object">database
569 object</glossterm>. This will default to the value
570 of <symbol>*default-database*</symbol>.</para>
572 </varlistentry></variablelist>
575 <title>Description</title>
576 <para>Executes the SQL command
577 <parameter>sql-expression</parameter>, which may be a symbolic
578 SQL expression or a string representing any SQL statement apart
579 from a query, on the supplied <parameter>database</parameter>
580 which defaults to <symbol>*default-database*</symbol>.
584 <title>Examples</title>
586 (execute-command "create table eventlog (time char(30),event char(70))")
589 (execute-command "create table eventlog (time char(30),event char(70))")
591 >> While accessing database #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {480B2B6D}>
592 >> with expression "create table eventlog (time char(30),event char(70))":
593 >> Error NIL: ERROR: amcreate: eventlog relation already exists
597 >> 0: [ABORT] Return to Top-Level.
599 >> Debug (type H for help)
601 >> (CLSQL-POSTGRESQL::|(PCL::FAST-METHOD DATABASE-EXECUTE-COMMAND (T POSTGRESQL-DATABASE))|
604 >> #<unavailable-arg>
605 >> #<unavailable-arg>)
606 >> Source: (ERROR 'SQL-DATABASE-ERROR :DATABASE DATABASE :EXPRESSION ...)
609 (execute-command "drop table eventlog")
614 <title>Side Effects</title>
615 <para>Whatever effects the execution of the SQL statement has
616 on the underlying database, if any.</para>
619 <title>Affected by</title>
623 <title>Exceptional Situations</title>
624 <para>If the execution of the SQL statement leads to any errors,
625 an error of type <errortype>sql-database-error</errortype> is
629 <title>See Also</title>
632 <member><link linkend="query"><function>query</function></link></member>
643 <refentry id="query">
645 <refentrytitle>QUERY</refentrytitle>
648 <refname>QUERY</refname>
649 <refpurpose>Execute an SQL query and return the tuples as a
651 <refclass>Generic Function</refclass>
654 <title>Syntax</title>
656 <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>
659 <title>Arguments and Values</title>
662 <term><parameter>query-expression</parameter></term>
664 <para>An <glossterm linkend="gloss-sql-expression">sql
665 expression</glossterm> that represents an SQL
666 query which is expected to return a (possibly empty)
671 <term><parameter>database</parameter></term>
674 <glossterm linkend="gloss-database-object">database
675 object</glossterm>. This will default to the value
676 of <symbol>*default-database*</symbol>.</para>
680 <term><parameter>flatp</parameter></term>
682 <para>A Boolean whose default value is &nil;.</para>
686 <term><parameter>result-types</parameter></term>
689 <glossterm linkend="gloss-field-types">field type
690 specifier</glossterm>. The default is &nil;.
693 The purpose of this argument is cause &clsql; to
694 import SQL numeric fields into numeric Lisp objects
695 rather than strings. This reduces the cost of
696 allocating a temporary string and the &clsql; users'
697 inconvenience of converting number strings into number
701 A value of <symbol>:auto</symbol> causes &clsql;
702 to automatically convert SQL fields into a
703 numeric format where applicable. The default value of
704 &nil; causes all fields to be returned as strings
705 regardless of the SQL type. Otherwise a list is expected
706 which has a element for each field that specifies the
707 conversion. Valid type identifiers are:
708 <simplelist type="vert">
709 <member><symbol>:int</symbol> Field is imported as a
710 signed integer, from 8-bits to 64-bits depending
713 <member><symbol>:double</symbol> Field is imported as a
716 <member><symbol>t</symbol> Field is imported as a
720 If the list is shorter than the number of fields, the a
721 value of <symbol>t</symbol> is assumed for the field.
722 If the list is longer than the number of fields, the
723 extra elements are ignored.
728 <term><parameter>field-names</parameter></term>
731 A boolean with a default value of &t;. When &t;, this
732 function returns a second value of a list of field
733 names. When &nil;, this function only returns one value -
739 <term><returnvalue>result</returnvalue></term>
741 <para>A list representing the result set obtained. For
742 each tuple in the result set, there is an element in
743 this list, which is itself a list of all the attribute
744 values in the tuple.</para>
750 <title>Description</title>
752 Executes the SQL query expression
753 <parameter>query-expression</parameter>, which may be an SQL
754 expression or a string, on the supplied
755 <parameter>database</parameter> which defaults to
756 <symbol>*default-database*</symbol>. <parameter>result-types</parameter>
757 is a list of symbols which specifies the lisp type for each
758 field returned by <parameter>query-expression</parameter>.
761 If <parameter>result-types</parameter> is &nil; all results
762 are returned as strings whereas the default value of
763 <symbol>:auto</symbol> means that the lisp types are
764 automatically computed for each field.
767 <parameter>field-names</parameter> is &t; by default which
768 means that the second value returned is a list of strings
769 representing the columns selected by
770 <parameter>query-expression</parameter>. If
771 <parameter>field-names</parameter> is &nil;, the list of column
772 names is not returned as a second value.
775 <parameter>flatp</parameter> has a default value of &nil;
776 which means that the results are returned as a list of
777 lists.If FLATP is &t; and only one result is returned for each
778 record selected by <parameter>query-expression</parameter>,
779 the results are returned as elements of a list.
783 <title>Examples</title>
785 (query "select emplid,first_name,last_name,height from employee where emplid = 1")
786 => ((1 "Vladamir" "Lenin" 1.5564661d0)),
787 ("emplid" "first_name" "last_name" "height")
789 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
791 => ((1 "Vladamir" "Lenin" 1.5564661d0))
793 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
796 => (("1" "Vladamir" "Lenin" "1.5564661"))
798 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
800 :result-types '(:int t t :double))
801 => ((1 "Vladamir" "Lenin" 1.5564661))
803 (query "select last_name from employee where emplid > 5" :flatp t)
804 => ("Andropov" "Chernenko" "Gorbachev" "Yeltsin" "Putin"),
807 (query "select last_name from employee where emplid > 10"
814 <title>Side Effects</title>
815 <para>Whatever effects the execution of the SQL query has
816 on the underlying database, if any.</para>
819 <title>Affected by</title>
823 <title>Exceptional Situations</title>
824 <para>If the execution of the SQL query leads to any errors, an
825 error of type <errortype>sql-database-error</errortype> is
829 <title>See Also</title>
831 <member><link linkend="execute-command"><function>execute-command</function></link></member>
832 <member><link linkend="print-query"><function>print-query</function></link></member>
833 <member><link linkend="do-query"><function>do-query</function></link></member>
834 <member><link linkend="map-query"><function>map-query</function></link></member>
835 <member><link linkend="loop-tuples"><function>loop</function></link></member>
836 <member><link linkend="select"><function>select</function></link></member>
841 <para>The <parameter>field-names</parameter> and
842 <parameter>result-types</parameter> keyword arguments are a
843 &clsql; extension.</para>
847 <refentry id="print-query">
849 <refentrytitle>PRINT-QUERY</refentrytitle>
852 <refname>PRINT-QUERY</refname>
853 <refpurpose>Prints a tabular report of query results.</refpurpose>
854 <refclass>Function</refclass>
857 <title>Syntax</title>
859 <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>
862 <title>Arguments and Values</title>
865 <term><parameter>query-expression</parameter></term>
867 <para>An <glossterm linkend="gloss-sql-expression">sql
868 expression</glossterm> that represents an SQL
869 query which is expected to return a (possibly empty)
874 <term><parameter>database</parameter></term>
877 <glossterm linkend="gloss-database-object">database
878 object</glossterm>. This will default to the value
879 of <symbol>*default-database*</symbol>.</para>
883 <term><parameter>titles</parameter></term>
886 A list of strings or &nil; which is the default value.
891 <term><parameter>formats</parameter></term>
894 A list of strings, &nil; or &t; which is the default value.
899 <term><parameter>sizes</parameter></term>
902 A list of numbers, &nil; or &t; which is the default value.
907 <term><parameter>stream</parameter></term>
910 An output stream or &t; which is the default value.
917 <title>Description</title>
918 <para>Prints a tabular report of the results returned by the SQL
919 query <parameter>query-expression</parameter>, which may be a
920 symbolic SQL expression or a string, in
921 <parameter>database</parameter> which defaults to
922 <symbol>*default-database*</symbol>. The report is printed onto
923 <parameter>stream</parameter> which has a default value of &t;
924 which means that <symbol>*standard-output*</symbol> is used. The
925 <parameter>title</parameter> argument, which defaults to &nil;,
926 allows the specification of a list of strings to use as column
927 titles in the tabular output. <parameter>sizes</parameter>
928 accepts a list of column sizes, one for each column selected by
929 <parameter>query-expression</parameter>, to use in formatting
930 the tabular report. The default value of &t; means that minimum
931 sizes are computed. <parameter>formats</parameter> is a list of
932 format strings to be used for printing each column selected by
933 <parameter>query-expression</parameter>. The default value of
934 <parameter>formats</parameter> is &t; meaning that
935 <symbol>~A</symbol> is used to format all columns or
936 <symbol>~VA</symbol> if column sizes are used.
940 <title>Examples</title>
942 (print-query [select [emplid] [first-name] [last-name] [email]
944 :where [< [emplid] 5]]
945 :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
946 ID FORENAME SURNAME EMAIL
947 1 Vladamir Lenin lenin@soviet.org
948 2 Josef Stalin stalin@soviet.org
949 3 Leon Trotsky trotsky@soviet.org
950 4 Nikita Kruschev kruschev@soviet.org
953 (print-query "select emplid,first_name,last_name,email from employee where emplid >= 5"
954 :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
955 ID FORENAME SURNAME EMAIL
956 5 Leonid Brezhnev brezhnev@soviet.org
957 6 Yuri Andropov andropov@soviet.org
958 7 Konstantin Chernenko chernenko@soviet.org
959 8 Mikhail Gorbachev gorbachev@soviet.org
960 9 Boris Yeltsin yeltsin@soviet.org
961 10 Vladamir Putin putin@soviet.org
966 <title>Side Effects</title>
972 <title>Affected by</title>
978 <title>Exceptional Situations</title>
980 If the execution of the SQL query leads to any errors, an
981 error of type <errortype>sql-database-error</errortype> is
986 <title>See Also</title>
988 <member><link linkend="query"><function>query</function></link></member>
989 <member><link linkend="do-query"><function>do-query</function></link></member>
990 <member><link linkend="map-query"><function>map-query</function></link></member>
991 <member><link linkend="loop-tuples"><function>loop</function></link></member>
992 <member><link linkend="select"><function>select</function></link></member>
1003 <refentry id="select">
1005 <refentrytitle>SELECT</refentrytitle>
1008 <refname>SELECT</refname>
1009 <refpurpose>Executes a query given the supplied constraints.</refpurpose>
1010 <refclass>Function</refclass>
1013 <title>Syntax</title>
1015 <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>
1018 <title>Arguments and Values</title>
1021 <term><parameter>identifiers</parameter></term>
1024 A set of <glossterm linkend="gloss-sql-expression">sql
1025 expressions</glossterm> each of which indicates a column
1031 <term><parameter>all</parameter></term>
1039 <term><parameter>distinct</parameter></term>
1047 <term><parameter>from</parameter></term>
1050 One or more SQL expression representing tables.
1055 <term><parameter>group-by</parameter></term>
1063 <term><parameter>having</parameter></term>
1071 <term><parameter>order-by</parameter></term>
1079 <term><parameter>set-operation</parameter></term>
1087 <term><parameter>where</parameter></term>
1095 <term><parameter>database</parameter></term>
1098 <glossterm linkend="gloss-database-object">database
1099 object</glossterm>. This will default to the value
1100 of <symbol>*default-database*</symbol>.</para>
1104 <term><parameter>flatp</parameter></term>
1106 <para>A Boolean whose default value is &nil;.</para>
1110 <term><parameter>result-types</parameter></term>
1113 <glossterm linkend="gloss-field-types">field type
1114 specifier</glossterm>. The default is &nil;.
1117 The purpose of this argument is cause &clsql; to
1118 import SQL numeric fields into numeric Lisp objects
1119 rather than strings. This reduces the cost of
1120 allocating a temporary string and the &clsql; users'
1121 inconvenience of converting number strings into number
1125 A value of <symbol>:auto</symbol> causes &clsql;
1126 to automatically convert SQL fields into a
1127 numeric format where applicable. The default value of
1128 &nil; causes all fields to be returned as strings
1129 regardless of the SQL type. Otherwise a list is expected
1130 which has a element for each field that specifies the
1131 conversion. Valid type identifiers are:
1132 <simplelist type="vert">
1133 <member><symbol>:int</symbol> Field is imported as a
1134 signed integer, from 8-bits to 64-bits depending
1135 upon the field type.
1137 <member><symbol>:double</symbol> Field is imported as a
1138 double-float number.
1140 <member><symbol>t</symbol> Field is imported as a
1144 If the list is shorter than the number of fields, the a
1145 value of <symbol>t</symbol> is assumed for the field.
1146 If the list is longer than the number of fields, the
1147 extra elements are ignored.
1152 <term><parameter>field-names</parameter></term>
1155 A boolean with a default value of &t;. When &t;, this
1156 function returns a second value of a list of field
1157 names. When &nil;, this function only returns one value -
1163 <term><parameter>result</parameter></term>
1166 A list representing the result set obtained. For each
1167 tuple in the result set, there is an element in this
1168 list, which is itself a list of all the attribute values
1176 <title>Description</title>
1178 Executes a query on <parameter>database</parameter>, which has
1179 a default value of <symbol>*default-database*</symbol>,
1180 specified by the SQL expressions supplied using the remaining
1181 arguments in <parameter>args</parameter>. The
1182 <function>select</function> function can be used to generate
1183 queries in both functional and object oriented contexts.
1186 In the functional case, the required arguments specify the
1187 columns selected by the query and may be symbolic SQL
1188 expressions or strings representing attribute
1189 identifiers. Type modified identifiers indicate that the
1190 values selected from the specified column are converted to the
1191 specified lisp type. The keyword arguments
1192 <parameter>all</parameter>, <parameter>distinct</parameter>,
1193 <parameter>from</parameter>, <parameter>group-by</parameter>,
1194 <parameter>having</parameter>,
1195 <parameter>order-by</parameter>,
1196 <parameter>set-operation</parameter> and
1197 <parameter>where</parameter> are used to specify, using the
1198 symbolic SQL syntax, the corresponding components of the SQL
1199 query generated by the call to
1200 <function>select</function>.
1203 <parameter>result-types</parameter> is a list of symbols which
1204 specifies the lisp type for each field returned by the
1205 query. If <parameter>result-types</parameter> is &nil; all
1206 results are returned as strings whereas the default value of
1207 <symbol>:auto</symbol> means that the lisp types are
1208 automatically computed for each
1209 field. <parameter>field-names</parameter> is &t; by default
1210 which means that the second value returned is a list of
1211 strings representing the columns selected by the query. If
1212 <parameter>field-names</parameter> is &nil;, the list of
1213 column names is not returned as a second value.
1216 In the object oriented case, the required arguments to
1217 <function>select</function> are symbols denoting View Classes
1218 which specify the database tables to query. In this case,
1219 <function>select</function> returns a list of View Class
1220 instances whose slots are set from the attribute values of the
1221 records in the specified table. <symbol>Slot-value</symbol> is
1222 a legal operator which can be employed as part of the symbolic
1223 SQL syntax used in the <parameter>where</parameter> keyword
1224 argument to <function>select</function>.
1225 <parameter>refresh</parameter> is &nil; by default which means
1226 that the View Class instances returned are retrieved from a
1227 cache if an equivalent call to <function>select</function> has
1228 previously been issued. If <parameter>refresh</parameter> is
1229 true, the View Class instances returned are updated as
1230 necessary from the database and the generic function
1231 <function>instance-refreshed</function> is called to perform
1232 any necessary operations on the updated instances.
1235 In both object oriented and functional contexts,
1236 <parameter>flatp</parameter> has a default value of &nil;
1237 which means that the results are returned as a list of
1238 lists. If <parameter>flatp</parameter> is t and only one
1239 result is returned for each record selected in the query, the
1240 results are returned as elements of a list.
1244 <title>Examples</title>
1246 (select [first-name] :from [employee] :flatp t :distinct t
1249 :order-by [first-name])
1250 => ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
1253 (select [first-name] [count [*]] :from [employee]
1255 :group-by [first-name]
1256 :order-by [first-name]
1258 => (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
1259 ("Mikhail" "1") ("Nikita" "1") ("Vladamir" "2") ("Yuri" "1"))
1261 (select [last-name] :from [employee]
1262 :where [like [email] "%org"]
1263 :order-by [last-name]
1267 => ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
1268 "Stalin" "Trotsky" "Yeltsin")
1270 (select [max [emplid]] :from [employee]
1273 :result-types :auto)
1276 (clsql:select [avg [height]] :from [employee] :flatp t :field-names nil)
1279 (select [emplid] [last-name] :from [employee] :where [= [emplid] 1])
1281 ("emplid" "last_name")
1283 (select [emplid :string] :from [employee]
1284 :where [= 1 [emplid]]
1289 (select [emplid] :from [employee] :order-by [emplid]
1290 :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]]
1295 (clsql:select [emplid] :from [employee]
1296 :where [in [emplid] '(1 2 3 4)]
1302 (select [first-name] [last-name] :from [employee]
1304 :order-by '(([first-name] :asc) ([last-name] :desc)))
1305 => (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
1306 ("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
1307 ("Nikita" "Kruschev") ("Vladamir" "Putin") ("Vladamir" "Lenin")
1308 ("Yuri" "Andropov"))
1310 (select [last-name] :from [employee]
1311 :set-operation [union [select [first-name] :from [employee]
1312 :order-by [last-name]]]
1316 => ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
1317 "Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
1318 "Trotsky" "Vladamir" "Yeltsin" "Yuri")
1322 <title>Side Effects</title>
1324 <para>Whatever effects the execution of the SQL query has on
1325 the underlying database, if any.</para>
1329 <title>Affected by</title>
1335 <title>Exceptional Situations</title>
1337 If the execution of the SQL query leads to any errors, an
1338 error of type <errortype>sql-database-error</errortype> is
1343 <title>See Also</title>
1345 <member><link linkend="query"><function>query</function></link></member>
1346 <member><link linkend="print-query"><function>print-query</function></link></member>
1347 <member><link linkend="do-query"><function>do-query</function></link></member>
1348 <member><link linkend="map-query"><function>map-query</function></link></member>
1349 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1350 <member><link linkend="instance-refreshed"><function>instance-refreshed</function></link></member>
1354 <title>Notes</title>
1356 The <function>select</function> function is actually
1357 implemented in &clsql; with a single
1358 <symbol>&rest</symbol> parameter (which is subsequently
1359 destructured) rather than the keyword parameters presented
1360 here for the purposes of exposition. This means that incorrect
1361 or missing keywords or values may not trigger errors in the
1362 way that they would if <function>select</function> had been
1363 defined using keyword arguments.
1366 The <parameter>field-names</parameter> and
1367 <parameter>result-types</parameter> keyword arguments are a
1371 <parameter>select</parameter> is common across the functional
1372 and object-oriented data manipulation languages.
1378 <!-- iteration and mapping -->
1380 <refentry id="do-query">
1382 <refentrytitle>DO-QUERY</refentrytitle>
1385 <refname>DO-QUERY</refname>
1386 <refpurpose>Iterate over all the tuples of a query.</refpurpose>
1387 <refclass>Macro</refclass>
1390 <title>Syntax</title>
1392 <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>
1395 <title>Arguments and Values</title>
1398 <term><parameter>args</parameter></term>
1400 <para>A list of variable names.</para>
1404 <term><parameter>query-expression</parameter></term>
1406 <para>An <glossterm linkend="gloss-sql-expression">sql
1407 expression</glossterm> that represents an SQL
1408 query which is expected to return a (possibly empty)
1409 result set, where each tuple has as many attributes as
1410 <parameter>function</parameter> takes arguments.</para>
1414 <term><parameter>database</parameter></term>
1417 <glossterm linkend="gloss-database-object">database
1418 object</glossterm>. This will default to
1419 <symbol>*default-database*</symbol>.</para>
1423 <term><parameter>result-types</parameter></term>
1426 A <glossterm linkend="gloss-field-types">field type
1427 specifier</glossterm>. The default is &nil;. See <link
1428 linkend="query"><function>query</function></link> for
1429 the semantics of this argument.
1434 <term><parameter>body</parameter></term>
1436 <para>A body of Lisp code, like in a
1437 <function>destructuring-bind</function> form.</para>
1441 <term><parameter>result</parameter></term>
1443 <para>The result of executing <parameter>body</parameter>.</para>
1449 <title>Description</title>
1451 Repeatedly executes <parameter>body</parameter> within a
1452 binding of <parameter>args</parameter> on the fields of each
1453 row selected by the SQL query
1454 <parameter>query-expression</parameter>, which may be a string
1455 or a symbolic SQL expression, in
1456 <parameter>database</parameter> which defaults to
1457 <symbol>*default-database*</symbol>.
1460 The body of code is executed in a block named
1461 <symbol>nil</symbol> which may be returned from prematurely
1462 via <function>return</function> or
1463 <function>return-from</function>. In this case the result of
1464 evaluating the <function>do-query</function> form will be the
1465 one supplied to <function>return</function> or
1466 <function>return-from</function>. Otherwise the result will
1467 be <symbol>nil</symbol>.
1470 The body of code appears also is if wrapped in a
1471 <function>destructuring-bind</function> form, thus allowing
1472 declarations at the start of the body, especially those
1473 pertaining to the bindings of the variables named in
1474 <parameter>args</parameter>.
1477 <parameter>result-types</parameter> is a list of symbols which
1478 specifies the lisp type for each field returned by
1479 <parameter>query-expression</parameter>. If
1480 <parameter>result-types</parameter> is &nil; all results are
1481 returned as strings whereas the default value of
1482 <symbol>:auto</symbol> means that the lisp types are
1483 automatically computed for each field.
1486 <parameter>query-expression</parameter> may be an object query
1487 (i.e., the selection arguments refer to View Classes), in
1488 which case <parameter>args</parameter> are bound to the tuples
1489 of View Class instances returned by the object oriented query.
1493 <title>Examples</title>
1495 (do-query ((salary name) "select salary,name from simple")
1496 (format t "~30A gets $~2,5$~%" name (read-from-string salary)))
1497 >> Mai, Pierre gets $10000.00
1498 >> Hacker, Random J. gets $08000.50
1501 (do-query ((salary name) "select salary,name from simple")
1502 (return (cons salary name)))
1503 => ("10000.00" . "Mai, Pierre")
1506 (do-query ((name) [select [last-name] :from [employee]
1507 :order-by [last-name]])
1510 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1511 "Chernenko" "Brezhnev" "Andropov")
1514 (do-query ((e) [select 'employee :order-by [last-name]])
1515 (push (slot-value e 'last-name) result))
1517 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1518 "Chernenko" "Brezhnev" "Andropov")
1522 <title>Side Effects</title>
1523 <para>Whatever effects the execution of the SQL query has
1524 on the underlying database, if any.</para>
1527 <title>Affected by</title>
1531 <title>Exceptional Situations</title>
1532 <para>If the execution of the SQL query leads to any
1533 errors, an error of type
1534 <errortype>sql-database-error</errortype> is signalled.</para>
1535 <para>If the number of variable names in
1536 <parameter>args</parameter> and the number of attributes in
1537 the tuples in the result set don't match up, an error is
1541 <title>See Also</title>
1543 <member><link linkend="query"><function>query</function></link></member>
1544 <member><link linkend="map-query"><function>map-query</function></link></member>
1545 <member><link linkend="print-query"><function>print-query</function></link></member>
1546 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1547 <member><link linkend="select"><function>select</function></link></member>
1551 <title>Notes</title>
1552 <para>The <parameter>result-types</parameter> keyword argument
1553 is a &clsql; extension.</para>
1555 <parameter>do-query</parameter> is common across the functional
1556 and object-oriented data manipulation languages.
1561 <refentry id="loop-tuples">
1563 <refentrytitle>LOOP</refentrytitle>
1566 <refname>LOOP</refname>
1567 <refpurpose>Extension to Common Lisp
1568 <computeroutput>Loop</computeroutput> to iterate over all the
1569 tuples of a query via a loop clause.</refpurpose>
1570 <refclass>Loop Clause</refclass>
1573 <title>Compatibility</title>
1574 <caution><para><function>loop-for-as-tuples</function> only works with &cmucl;.</para></caution>
1577 <title>Syntax</title>
1578 <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>
1581 <title>Arguments and Values</title>
1584 <term><parameter>var</parameter></term>
1586 <para>A <literal>d-var-spec</literal>, as defined in the
1587 grammar for <function>loop</function>-clauses in the ANSI
1588 Standard for Common Lisp. This allows for the usual
1589 loop-style destructuring.</para>
1593 <term><parameter>type-spec</parameter></term>
1595 <para>An optional <literal>type-spec</literal> either
1596 simple or destructured, as defined in the grammar for
1597 <function>loop</function>-clauses in the ANSI Standard for
1602 <term><parameter>query</parameter></term>
1604 <para>An <glossterm linkend="gloss-sql-expression">sql
1605 expression</glossterm> that represents an SQL
1606 query which is expected to return a (possibly empty)
1607 result set, where each tuple has as many attributes as
1608 <parameter>function</parameter> takes arguments.</para>
1612 <term><parameter>database</parameter></term>
1615 <glossterm linkend="gloss-database-object">database
1616 object</glossterm>. This will default to the value
1617 of <symbol>*default-database*</symbol>.</para>
1623 <title>Description</title>
1624 <para>This clause is an iteration driver for
1625 <function>loop</function>, that binds the given variable
1626 (possibly destructured) to the consecutive tuples (which are
1627 represented as lists of attribute values) in the result set
1628 returned by executing the SQL <parameter>query</parameter>
1629 expression on the <parameter>database</parameter>
1632 <parameter>query</parameter> may be an object query (i.e., the
1633 selection arguments refer to View Classes), in which case the
1634 supplied variable is bound to the tuples of View Class
1635 instances returned by the object oriented query.
1639 <title>Examples</title>
1641 (defvar *my-db* (connect '("dent" "newesim" "dent" "dent"))
1644 (loop with time-graph = (make-hash-table :test #'equal)
1645 with event-graph = (make-hash-table :test #'equal)
1646 for (time event) being the tuples of "select time,event from log"
1649 (incf (gethash time time-graph 0))
1650 (incf (gethash event event-graph 0))
1652 (flet ((show-graph (k v) (format t "~40A => ~5D~%" k v)))
1653 (format t "~&Time-Graph:~%===========~%")
1654 (maphash #'show-graph time-graph)
1655 (format t "~&~%Event-Graph:~%============~%")
1656 (maphash #'show-graph event-graph))
1657 (return (values time-graph event-graph)))
1666 >> CLOS Benchmark entry. => 9000
1667 >> Demo Text... => 3
1668 >> doit-text => 3000
1669 >> C Benchmark entry. => 12000
1670 >> CLOS Benchmark entry => 32000
1671 => #<EQUAL hash table, 3 entries {48350A1D}>
1672 => #<EQUAL hash table, 5 entries {48350FCD}>
1674 (loop for (forename surname)
1676 [select [first-name] [last-name] :from [employee]
1677 :order-by [last-name]]
1678 collect (concatenate 'string forename " " surname))
1679 => ("Yuri Andropov" "Leonid Brezhnev" "Konstantin Chernenko" "Mikhail Gorbachev"
1680 "Nikita Kruschev" "Vladamir Lenin" "Vladamir Putin" "Josef Stalin"
1681 "Leon Trotsky" "Boris Yeltsin")
1683 (loop for (e) being the records in
1684 [select 'employee :where [< [emplid] 4] :order-by [emplid]]
1685 collect (slot-value e 'last-name))
1686 => ("Lenin" "Stalin" "Trotsky")
1690 <title>Side Effects</title>
1691 <para>Whatever effects the execution of the SQL query has
1692 on the underlying database, if any.</para>
1695 <title>Affected by</title>
1699 <title>Exceptional Situations</title>
1700 <para>If the execution of the SQL query leads to any
1701 errors, an error of type
1702 <errortype>sql-database-error</errortype> is signalled.</para>
1703 <para>Otherwise, any of the exceptional situations of
1704 <function>loop</function> applies.</para>
1707 <title>See Also</title>
1710 <member><link linkend="query"><function>query</function></link></member>
1711 <member><link linkend="map-query"><function>map-query</function></link></member>
1712 <member><link linkend="do-query"><function>do-query</function></link></member>
1713 <member><link linkend="print-query"><function>print-query</function></link></member>
1714 <member><link linkend="select"><function>select</function></link></member>
1719 <title>Notes</title>
1720 <para>The <parameter>database</parameter> loop keyword is a
1721 &clsql; extension.</para>
1723 The extended <function>loop</function> syntax is common across
1724 the functional and object-oriented data manipulation
1730 <refentry id="map-query">
1732 <refentrytitle>MAP-QUERY</refentrytitle>
1735 <refname>MAP-QUERY</refname>
1736 <refpurpose>Map a function over all the tuples from a
1738 <refclass>Function</refclass>
1741 <title>Syntax</title>
1742 <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>
1745 <title>Arguments and Values</title>
1748 <term><parameter>output-type-spec</parameter></term>
1750 <para>A sequence type specifier or <symbol>nil</symbol>.</para>
1754 <term><parameter>function</parameter></term>
1756 <para>A function designator.
1757 <parameter>function</parameter> takes a single argument which
1758 is the atom value for a query single with a single column
1759 or is a list of values for a multi-column query.</para>
1763 <term><parameter>query-expression</parameter></term>
1765 <para>An <glossterm linkend="gloss-sql-expression">sql
1766 expression</glossterm> that represents an SQL
1767 query which is expected to return a (possibly empty)
1772 <term><parameter>database</parameter></term>
1775 <glossterm linkend="gloss-database-object">database
1776 object</glossterm>. This will default to the value
1777 of <symbol>*default-database*</symbol>.</para>
1781 <term><parameter>result-types</parameter></term>
1784 A <glossterm linkend="gloss-field-types">field type
1785 specifier</glossterm>. The default is &nil;. See <link
1786 linkend="query"><function>query</function></link> for
1787 the semantics of this argument.
1792 <term><returnvalue>result</returnvalue></term>
1794 <para>If <parameter>output-type-spec</parameter> is a
1795 type specifier other than <symbol>nil</symbol>, then a
1796 sequence of the type it denotes. Otherwise
1797 <symbol>nil</symbol> is returned.</para>
1803 <title>Description</title>
1805 Applies <parameter>function</parameter> to the successive
1806 tuples in the result set returned by executing the SQL
1807 <parameter>query-expression</parameter>. If the
1808 <parameter>output-type-spec</parameter> is
1809 <symbol>nil</symbol>, then the result of each application of
1810 <parameter>function</parameter> is discarded, and
1811 <function>map-query</function> returns <symbol>nil</symbol>.
1812 Otherwise the result of each successive application of
1813 <parameter>function</parameter> is collected in a sequence of
1814 type <parameter>output-type-spec</parameter>, where the jths
1815 element is the result of applying
1816 <parameter>function</parameter> to the attributes of the jths
1817 tuple in the result set. The collected sequence is the result
1818 of the call to <function>map-query</function>.
1821 If the <parameter>output-type-spec</parameter> is a subtype of
1822 <type>list</type>, the result will be a <type>list</type>.
1825 If the <parameter>result-type</parameter> is a subtype of
1826 <type>vector</type>, then if the implementation can determine
1827 the element type specified for the
1828 <parameter>result-type</parameter>, the element type of the
1829 resulting array is the result of
1830 <emphasis>upgrading</emphasis> that element type; or, if the
1831 implementation can determine that the element type is
1832 unspecified (or <symbol>*</symbol>), the element type of the
1833 resulting array is <type>t</type>; otherwise, an error is
1837 If <parameter>result-types</parameter> is &nil; all results
1838 are returned as strings whereas the default value of
1839 <symbol>:auto</symbol> means that the lisp types are
1840 automatically computed for each field.</para>
1842 <parameter>query-expression</parameter> may be an object query
1843 (i.e., the selection arguments refer to View Classes), in
1844 which case the supplied function is applied to the tuples of
1845 View Class instances returned by the object oriented query.
1849 <title>Examples</title>
1851 (map-query 'list #'(lambda (tuple)
1852 (multiple-value-bind (salary name) tuple
1853 (declare (ignorable name))
1854 (read-from-string salary)))
1855 "select salary,name from simple where salary > 8000")
1858 (map-query '(vector double-float)
1860 (multiple-value-bind (salary name) tuple
1861 (declare (ignorable name))
1862 (let ((*read-default-float-format* 'double-float))
1863 (coerce (read-from-string salary) 'double-float))
1864 "select salary,name from simple where salary > 8000")))
1865 => #(10000.0d0 8000.5d0)
1867 => (SIMPLE-ARRAY DOUBLE-FLOAT (2))
1870 (values (map-query nil #'(lambda (tuple)
1871 (multiple-value-bind (salary name) tuple
1872 (push (cons name (read-from-string salary)) list))
1873 "select salary,name from simple where salary > 8000"))
1876 => (("Hacker, Random J." . 8000.5) ("Mai, Pierre" . 10000.0))
1878 (map-query 'vector #'identity
1879 [select [last-name] :from [employee] :flatp t
1880 :order-by [last-name]])
1881 => #("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
1882 "Stalin" "Trotsky" "Yeltsin")
1884 (map-query 'list #'identity
1885 [select [first-name] [last-name] :from [employee]
1886 :order-by [last-name]])
1887 => (("Yuri" "Andropov") ("Leonid" "Brezhnev") ("Konstantin" "Chernenko")
1888 ("Mikhail" "Gorbachev") ("Nikita" "Kruschev") ("Vladamir" "Lenin")
1889 ("Vladamir" "Putin") ("Josef" "Stalin") ("Leon" "Trotsky")
1890 ("Boris" "Yeltsin"))
1892 (map-query 'list #'last-name [select 'employee :order-by [emplid]])
1893 => ("Lenin" "Stalin" "Trotsky" "Kruschev" "Brezhnev" "Andropov" "Chernenko"
1894 "Gorbachev" "Yeltsin" "Putin")
1898 <title>Side Effects</title>
1899 <para>Whatever effects the execution of the SQL query has
1900 on the underlying database, if any.</para>
1903 <title>Affected by</title>
1907 <title>Exceptional Situations</title>
1908 <para>If the execution of the SQL query leads to any
1909 errors, an error of type
1910 <errortype>sql-database-error</errortype> is signalled.</para>
1911 <para>An error of type <errortype>type-error</errortype> must
1912 be signaled if the <parameter>output-type-spec</parameter> is
1913 not a recognizable subtype of <type>list</type>, not a
1914 recognizable subtype of <type>vector</type>, and not
1915 <symbol>nil</symbol>.</para>
1916 <para>An error of type <errortype>type-error</errortype>
1917 should be signaled if
1918 <parameter>output-type-spec</parameter> specifies the number
1919 of elements and the size of the result set is different from
1923 <title>See Also</title>
1925 <member><link linkend="query"><function>query</function></link></member>
1926 <member><link linkend="do-query"><function>do-query</function></link></member>
1927 <member><link linkend="print-query"><function>print-query</function></link></member>
1928 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1929 <member><link linkend="select"><function>select</function></link></member>
1933 <title>Notes</title>
1934 <para>The <parameter>result-types</parameter> keyword argument
1935 is a &clsql; extension.</para>
1937 <parameter>map-query</parameter> is common across the
1938 functional and object-oriented data manipulation languages.