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>
18 <!-- Caching table queries -->
20 <refentry id="cache-table-queries-default">
22 <refentrytitle>*CACHE-TABLE-QUERIES-DEFAULT*</refentrytitle>
25 <refname><emphasis>Variable</emphasis> <emphasis role="bold">*CACHE-TABLE-QUERIES-DEFAULT*</emphasis></refname>
26 <refpurpose>Specifies the default behaviour for caching of
27 attribute types.</refpurpose>
28 <refclass>Variable</refclass>
31 <title>Value Type</title>
33 A valid argument to the <parameter>action</parameter>
34 parameter of <function>cache-table-queries</function>,
36 <simplelist type="inline">
38 <member>&nil;</member>
39 <member><symbol>:flush</symbol></member>
44 <title>Initial Value</title>
45 <para><symbol>nil</symbol></para>
48 <title>Description</title>
50 Specifies the default behaivour for caching of attribute
51 types. Meaningful values are &t;, &nil; and
52 <symbol>:flush</symbol> as described for the
53 <parameter>action</parameter> argument to
54 <function>cache-table-queries</function>.
58 <title>Examples</title>
62 <title>Affected By</title>
66 <title>See Also</title>
68 <member><link linkend="cache-table-queries"><function>cache-table-queries</function></link></member>
77 <refentry id="cache-table-queries">
79 <refentrytitle>CACHE-TABLE-QUERIES</refentrytitle>
82 <refname><emphasis>Function</emphasis> <emphasis role="bold">CACHE-TABLE-QUERIES</emphasis></refname>
83 <refpurpose>Controls the caching of attribute type information for a database table.</refpurpose>
84 <refclass>Function</refclass>
89 <function>cache-table-queries</function> <replaceable>table</replaceable> &key <replaceable>action</replaceable> <replaceable>database</replaceable>) => <returnvalue></returnvalue></synopsis>
92 <title>Arguments and Values</title>
95 <term><parameter>table</parameter></term>
98 A string representing a database table, &t; or
99 <symbol>:default</symbol>.
104 <term><parameter>action</parameter></term>
107 &t;, &nil; or <symbol>:flush</symbol>.
112 <term><parameter>database</parameter></term>
115 <glossterm linkend="gloss-database-object">database
116 object</glossterm>. This will default to the value
117 of <symbol>*default-database*</symbol>.</para>
123 <title>Description</title>
124 <para>Controls the caching of attribute type information on the
125 table specified by <parameter>table</parameter> in
126 <parameter>database</parameter> which defaults to
127 <symbol>*default-database*</symbol>. <parameter>action</parameter>
128 specifies the caching behaviour to adopt. If its value is &t;
129 then attribute type information is cached whereas if its value
130 is &nil; then attribute type information is not cached. If
131 <parameter>action</parameter> is <symbol>:flush</symbol> then
132 all existing type information in the cache for
133 <parameter>table</parameter> is removed, but caching is still
134 enabled. <parameter>table</parameter> may be a string
135 representing a table for which the caching action is to be taken
136 while the caching action is applied to all tables if
137 <parameter>table</parameter> is &t;. Alternatively, when
138 <parameter>table</parameter> is <symbol>:default</symbol>, the
139 default caching action specified by
140 <symbol>*cache-table-queries-default*</symbol> is applied to all
141 tables for which a caching action has not been explicitly set.
145 <title>Examples</title>
147 (setf *cache-table-queries-default* t)
153 ([comments] varchar)))
155 (cache-table-queries "foo")
157 (list-attribute-types "foo")
158 => (("id" :INT4 4 NIL 1) ("height" :FLOAT8 8 NIL 1) ("name" :BPCHAR 24 NIL 1)
159 ("comments" :VARCHAR 255 NIL 1))
166 ([comments] (string 100))))
168 (cache-table-queries "foo" :action :flush)
170 (list-attribute-types "foo")
171 => (("id" :INT4 4 NIL 1) ("height" :FLOAT8 8 NIL 1) ("name" :BPCHAR 36 NIL 1)
172 ("comments" :BPCHAR 100 NIL 1))
176 <title>Side Effects</title>
178 The internal attribute cache for
179 <parameter>database</parameter> is modified.
183 <title>Affected by</title>
185 <member><link linkend="cache-table-queries-default"><symbol>*cache-table-queries-default*</symbol></link></member>
189 <title>Exceptional Situations</title>
195 <title>See Also</title>
197 <member><link linkend="cache-table-queries-default"><symbol>*cache-table-queries-default*</symbol></link></member>
209 <!-- insert/update/delete records -->
211 <refentry id="insert-records">
213 <refentrytitle>INSERT-RECORDS</refentrytitle>
216 <refname><emphasis>Function</emphasis> <emphasis role="bold">INSERT-RECORDS</emphasis></refname>
217 <refpurpose>Insert tuples of data into a database table.</refpurpose>
218 <refclass>Function</refclass>
221 <title>Syntax</title>
223 <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>
226 <title>Arguments and Values</title>
229 <term><parameter>into</parameter></term>
232 A string, symbol or symbolic SQL expression representing
233 the name of a table existing in
234 <parameter>database</parameter>.
239 <term><parameter>attributes</parameter></term>
242 A list of attribute identifiers or &nil;.
247 <term><parameter>values</parameter></term>
250 A list of attribute values or &nil;.
255 <term><parameter>av-pairs</parameter></term>
258 A list of attribute identifier/value pairs or &nil;.
263 <term><parameter>query</parameter></term>
266 A query expression or &nil;.
271 <term><parameter>database</parameter></term>
274 <glossterm linkend="gloss-database-object">database
275 object</glossterm>. This will default to the value
276 of <symbol>*default-database*</symbol>.</para>
282 <title>Description</title>
284 Inserts records into the table specified by
285 <parameter>into</parameter> in <parameter>database</parameter>
286 which defaults to <symbol>*default-database*</symbol>.
289 There are five ways of specifying the values inserted into
290 each row. In the first <parameter>values</parameter> contains
291 a list of values to insert and
292 <parameter>attributes</parameter>,
293 <parameter>av-pairs</parameter> and
294 <parameter>query</parameter> are &nil;. This can be used when
295 values are supplied for all attributes in
296 <parameter>into</parameter>. In the second,
297 <parameter>attributes</parameter> is a list of column names,
298 <parameter>values</parameter> is a corresponding list of
299 values and <parameter>av-pairs</parameter> and
300 <parameter>query</parameter> are &nil;. In the third,
301 <parameter>attributes</parameter>,
302 <parameter>values</parameter> and <parameter>query</parameter>
303 are &nil; and <parameter>av-pairs</parameter> is an alist of
304 (attribute value) pairs. In the fourth,
305 <parameter>values</parameter>, <parameter>av-pairs</parameter>
306 and <parameter>attributes</parameter> are &nil; and
307 <parameter>query</parameter> is a symbolic SQL query
308 expression in which the selected columns also exist in
309 <parameter>into</parameter>. In the fifth method,
310 <parameter>values</parameter> and
311 <parameter>av-pairs</parameter> are nil and
312 <parameter>attributes</parameter> is a list of column names
313 and <parameter>query</parameter> is a symbolic SQL query
314 expression which returns values for the specified columns.
318 <title>Examples</title>
320 (select [first-name] [last-name] [email]
322 :where [= [emplid] 11]
325 (insert-records :into [employee]
326 :attributes '(emplid groupid first_name last_name email
327 ecompanyid managerid)
328 :values '(11 1 "Yuri" "Gagarin" "gagarin@soviet.org"
331 (select [first-name] [last-name] [email]
333 :where [= [emplid] 11]
335 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
339 <title>Side Effects</title>
341 Modifications are made to the underlying database.
345 <title>Affected by</title>
351 <title>Exceptional Situations</title>
353 An error of type <symbol>sql-database-data-error</symbol> is
354 signalled if <parameter>table</parameter> is not an existing
355 table in <parameter>database</parameter> or if the specified
356 attributes are not found.
360 <title>See Also</title>
362 <member><link linkend="update-records"><function>update-records</function></link></member>
363 <member><link linkend="delete-records"><function>delete-records</function></link></member>
374 <refentry id="update-records">
376 <refentrytitle>UPDATE-RECORDS</refentrytitle>
379 <refname><emphasis>Function</emphasis> <emphasis role="bold">UPDATE-RECORDS</emphasis></refname>
380 <refpurpose>Updates the values of existing records.</refpurpose>
381 <refclass>Function</refclass>
384 <title>Syntax</title>
386 <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>
389 <title>Arguments and Values</title>
392 <term><parameter>table</parameter></term>
395 A string, symbol or symbolic SQL expression representing
396 the name of a table existing in
397 <parameter>database</parameter>.
402 <term><parameter>attributes</parameter></term>
405 A list of attribute identifiers or &nil;.
410 <term><parameter>values</parameter></term>
413 A list of attribute values or &nil;.
418 <term><parameter>av-pairs</parameter></term>
421 A list of attribute identifier/value pairs or &nil;.
426 <term><parameter>where</parameter></term>
429 A symbolic SQL expression.
434 <term><parameter>database</parameter></term>
437 <glossterm linkend="gloss-database-object">database
438 object</glossterm>. This will default to the value
439 of <symbol>*default-database*</symbol>.</para>
445 <title>Description</title>
447 Updates the attribute values of existing records satsifying
448 the SQL expression <parameter>where</parameter> in the table
449 specified by <parameter>table</parameter> in
450 <parameter>database</parameter> which defaults to
451 <symbol>*default-database*</symbol>.
454 There are three ways of specifying the values to update for
455 each row. In the first, <parameter>values</parameter> contains
456 a list of values to use in the update and
457 <parameter>attributes</parameter> and
458 <parameter>av-pairs</parameter> are &nil;. This can be used
459 when values are supplied for all attributes in
460 <parameter>table</parameter>. In the second,
461 <parameter>attributes</parameter> is a list of column names,
462 <parameter>values</parameter> is a corresponding list of
463 values and <parameter>av-pairs</parameter> is &nil;. In the
464 third, <parameter>attributes</parameter> and
465 <parameter>values</parameter> are &nil; and
466 <parameter>av-pairs</parameter> is an alist of (attribute
471 <title>Examples</title>
473 (select [first-name] [last-name] [email]
475 :where [= [emplid] 1]
477 => (("Vladamir" "Lenin" "lenin@soviet.org"))
478 (update-records [employee]
479 :av-pairs'((first_name "Yuri")
480 (last_name "Gagarin")
481 (email "gagarin@soviet.org"))
482 :where [= [emplid] 1])
484 (select [first-name] [last-name] [email]
486 :where [= [emplid] 1]
488 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
492 <title>Side Effects</title>
494 Modifications are made to the underlying database.
498 <title>Affected by</title>
504 <title>Exceptional Situations</title>
506 An error of type <symbol>sql-database-data-error</symbol> is
507 signalled if <parameter>table</parameter> is not an existing
508 table in <parameter>database</parameter>, if the specified
509 attributes are not found or if the SQL statement resulting
510 from the symbolic expression <parameter>where</parameter> does
511 not return a Boolean value.
513 <para>If the execution of the SQL query leads to any errors, an
514 error of type <errortype>sql-database-error</errortype> is
518 <title>See Also</title>
521 <member><link linkend="insert-records"><function>insert-records</function></link></member>
522 <member><link linkend="delete-records"><function>delete-records</function></link></member>
534 <refentry id="delete-records">
536 <refentrytitle>DELETE-RECORDS</refentrytitle>
539 <refname><emphasis>Function</emphasis> <emphasis role="bold">DELETE-RECORDS</emphasis></refname>
540 <refpurpose>Delete records from a database table.</refpurpose>
541 <refclass>Function</refclass>
544 <title>Syntax</title>
546 <function>delete-records</function> &key <replaceable>from</replaceable> <replaceable>where</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
549 <title>Arguments and Values</title>
552 <term><parameter>from</parameter></term>
555 A string, symbol or symbolic SQL expression representing
556 the name of a table existing in
557 <parameter>database</parameter>.
562 <term><parameter>where</parameter></term>
565 A symbolic SQL expression.
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>
581 <title>Description</title>
582 <para>Deletes records satisfying the SQL expression
583 <parameter>where</parameter> from the table specified by
584 <parameter>from</parameter> in <parameter>database</parameter>
585 specifies a database which defaults to
586 <symbol>*default-database*</symbol>.
590 <title>Examples</title>
592 (select [first-name] [last-name] [email]
594 :where [= [emplid] 11]
596 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
597 (delete-records :from [employee] :where [= [emplid] 11])
599 (select [first-name] [last-name] [email]
601 :where [= [emplid] 11]
607 <title>Side Effects</title>
609 Modifications are made to the underlying database.
613 <title>Affected by</title>
619 <title>Exceptional Situations</title>
621 An error of type <symbol>sql-database-data-error</symbol> is
622 signalled if <parameter>from</parameter> is not an existing
623 table in <parameter>database</parameter> or if the SQL
624 statement resulting from the symbolic expression
625 <parameter>where</parameter> does not return a Boolean value.
629 <title>See Also</title>
632 <member><link linkend="insert-records"><function>insert-records</function></link></member>
633 <member><link linkend="update-records"><function>update-records</function></link></member>
646 <!-- executing SQL commands and queries -->
648 <refentry id="execute-command">
650 <refentrytitle>EXECUTE-COMMAND</refentrytitle>
653 <refname><emphasis>Generic Function</emphasis> <emphasis role="bold">EXECUTE-COMMAND</emphasis></refname>
654 <refpurpose>Execute an SQL command which returns no values.</refpurpose>
655 <refclass>Generic Function</refclass>
658 <title>Syntax</title>
660 <function>execute-command</function> <replaceable>sql-expression</replaceable> &key <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
663 <title>Arguments and Values</title>
666 <term><parameter>sql-expression</parameter></term>
668 <para>An <glossterm linkend="gloss-sql-expression">sql
669 expression</glossterm> that represents an SQL
670 statement which will return no values.</para>
674 <term><parameter>database</parameter></term>
677 <glossterm linkend="gloss-database-object">database
678 object</glossterm>. This will default to the value
679 of <symbol>*default-database*</symbol>.</para>
681 </varlistentry></variablelist>
684 <title>Description</title>
685 <para>Executes the SQL command
686 <parameter>sql-expression</parameter>, which may be a symbolic
687 SQL expression or a string representing any SQL statement apart
688 from a query, on the supplied <parameter>database</parameter>
689 which defaults to <symbol>*default-database*</symbol>.
693 <title>Examples</title>
695 (execute-command "create table eventlog (time char(30),event char(70))")
698 (execute-command "create table eventlog (time char(30),event char(70))")
700 >> While accessing database #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {480B2B6D}>
701 >> with expression "create table eventlog (time char(30),event char(70))":
702 >> Error NIL: ERROR: amcreate: eventlog relation already exists
706 >> 0: [ABORT] Return to Top-Level.
708 >> Debug (type H for help)
710 >> (CLSQL-POSTGRESQL::|(PCL::FAST-METHOD DATABASE-EXECUTE-COMMAND (T POSTGRESQL-DATABASE))|
713 >> #<unavailable-arg>
714 >> #<unavailable-arg>)
715 >> Source: (ERROR 'SQL-DATABASE-ERROR :DATABASE DATABASE :EXPRESSION ...)
718 (execute-command "drop table eventlog")
723 <title>Side Effects</title>
724 <para>Whatever effects the execution of the SQL statement has
725 on the underlying database, if any.</para>
728 <title>Affected by</title>
732 <title>Exceptional Situations</title>
733 <para>If the execution of the SQL statement leads to any errors,
734 an error of type <errortype>sql-database-error</errortype> is
738 <title>See Also</title>
741 <member><link linkend="query"><function>query</function></link></member>
752 <refentry id="query">
754 <refentrytitle>QUERY</refentrytitle>
757 <refname><emphasis>Generic Function</emphasis> <emphasis role="bold">QUERY</emphasis></refname>
758 <refpurpose>Execute an SQL query and return the tuples as a
760 <refclass>Generic Function</refclass>
763 <title>Syntax</title>
765 <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>
768 <title>Arguments and Values</title>
771 <term><parameter>query-expression</parameter></term>
773 <para>An <glossterm linkend="gloss-sql-expression">sql
774 expression</glossterm> that represents an SQL
775 query which is expected to return a (possibly empty)
780 <term><parameter>database</parameter></term>
783 <glossterm linkend="gloss-database-object">database
784 object</glossterm>. This will default to the value
785 of <symbol>*default-database*</symbol>.</para>
789 <term><parameter>flatp</parameter></term>
791 <para>A Boolean whose default value is &nil;.</para>
795 <term><parameter>result-types</parameter></term>
798 <glossterm linkend="gloss-field-types">field type
799 specifier</glossterm>. The default is &nil;.
802 The purpose of this argument is cause &clsql; to
803 import SQL numeric fields into numeric Lisp objects
804 rather than strings. This reduces the cost of
805 allocating a temporary string and the &clsql; users'
806 inconvenience of converting number strings into number
810 A value of <symbol>:auto</symbol> causes &clsql;
811 to automatically convert SQL fields into a
812 numeric format where applicable. The default value of
813 &nil; causes all fields to be returned as strings
814 regardless of the SQL type. Otherwise a list is expected
815 which has a element for each field that specifies the
816 conversion. Valid type identifiers are:
817 <simplelist type="vert">
818 <member><symbol>:int</symbol> Field is imported as a
819 signed integer, from 8-bits to 64-bits depending
822 <member><symbol>:double</symbol> Field is imported as a
825 <member><symbol>t</symbol> Field is imported as a
829 If the list is shorter than the number of fields, the a
830 value of <symbol>t</symbol> is assumed for the field.
831 If the list is longer than the number of fields, the
832 extra elements are ignored.
837 <term><parameter>field-names</parameter></term>
840 A boolean with a default value of &t;. When &t;, this
841 function returns a second value of a list of field
842 names. When &nil;, this function only returns one value -
848 <term><returnvalue>result</returnvalue></term>
850 <para>A list representing the result set obtained. For
851 each tuple in the result set, there is an element in
852 this list, which is itself a list of all the attribute
853 values in the tuple.</para>
859 <title>Description</title>
861 Executes the SQL query expression
862 <parameter>query-expression</parameter>, which may be an SQL
863 expression or a string, on the supplied
864 <parameter>database</parameter> which defaults to
865 <symbol>*default-database*</symbol>. <parameter>result-types</parameter>
866 is a list of symbols which specifies the lisp type for each
867 field returned by <parameter>query-expression</parameter>.
870 If <parameter>result-types</parameter> is &nil; all results
871 are returned as strings whereas the default value of
872 <symbol>:auto</symbol> means that the lisp types are
873 automatically computed for each field.
876 <parameter>field-names</parameter> is &t; by default which
877 means that the second value returned is a list of strings
878 representing the columns selected by
879 <parameter>query-expression</parameter>. If
880 <parameter>field-names</parameter> is &nil;, the list of column
881 names is not returned as a second value.
884 <parameter>flatp</parameter> has a default value of &nil;
885 which means that the results are returned as a list of
886 lists.If FLATP is &t; and only one result is returned for each
887 record selected by <parameter>query-expression</parameter>,
888 the results are returned as elements of a list.
892 <title>Examples</title>
894 (query "select emplid,first_name,last_name,height from employee where emplid = 1")
895 => ((1 "Vladamir" "Lenin" 1.5564661d0)),
896 ("emplid" "first_name" "last_name" "height")
898 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
900 => ((1 "Vladamir" "Lenin" 1.5564661d0))
902 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
905 => (("1" "Vladamir" "Lenin" "1.5564661"))
907 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
909 :result-types '(:int t t :double))
910 => ((1 "Vladamir" "Lenin" 1.5564661))
912 (query "select last_name from employee where emplid > 5" :flatp t)
913 => ("Andropov" "Chernenko" "Gorbachev" "Yeltsin" "Putin"),
916 (query "select last_name from employee where emplid > 10"
923 <title>Side Effects</title>
924 <para>Whatever effects the execution of the SQL query has
925 on the underlying database, if any.</para>
928 <title>Affected by</title>
932 <title>Exceptional Situations</title>
933 <para>If the execution of the SQL query leads to any errors, an
934 error of type <errortype>sql-database-error</errortype> is
938 <title>See Also</title>
940 <member><link linkend="execute-command"><function>execute-command</function></link></member>
941 <member><link linkend="print-query"><function>print-query</function></link></member>
942 <member><link linkend="do-query"><function>do-query</function></link></member>
943 <member><link linkend="map-query"><function>map-query</function></link></member>
944 <member><link linkend="loop-tuples"><function>loop</function></link></member>
945 <member><link linkend="select"><function>select</function></link></member>
950 <para>The <parameter>field-names</parameter> and
951 <parameter>result-types</parameter> keyword arguments are a
952 &clsql; extension.</para>
956 <refentry id="print-query">
958 <refentrytitle>PRINT-QUERY</refentrytitle>
961 <refname><emphasis>Function</emphasis> <emphasis role="bold">PRINT-QUERY</emphasis></refname>
962 <refpurpose>Prints a tabular report of query results.</refpurpose>
963 <refclass>Function</refclass>
966 <title>Syntax</title>
968 <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>
971 <title>Arguments and Values</title>
974 <term><parameter>query-expression</parameter></term>
976 <para>An <glossterm linkend="gloss-sql-expression">sql
977 expression</glossterm> that represents an SQL
978 query which is expected to return a (possibly empty)
983 <term><parameter>database</parameter></term>
986 <glossterm linkend="gloss-database-object">database
987 object</glossterm>. This will default to the value
988 of <symbol>*default-database*</symbol>.</para>
992 <term><parameter>titles</parameter></term>
995 A list of strings or &nil; which is the default value.
1000 <term><parameter>formats</parameter></term>
1003 A list of strings, &nil; or &t; which is the default value.
1008 <term><parameter>sizes</parameter></term>
1011 A list of numbers, &nil; or &t; which is the default value.
1016 <term><parameter>stream</parameter></term>
1019 An output stream or &t; which is the default value.
1026 <title>Description</title>
1027 <para>Prints a tabular report of the results returned by the SQL
1028 query <parameter>query-expression</parameter>, which may be a
1029 symbolic SQL expression or a string, in
1030 <parameter>database</parameter> which defaults to
1031 <symbol>*default-database*</symbol>. The report is printed onto
1032 <parameter>stream</parameter> which has a default value of &t;
1033 which means that <symbol>*standard-output*</symbol> is used. The
1034 <parameter>title</parameter> argument, which defaults to &nil;,
1035 allows the specification of a list of strings to use as column
1036 titles in the tabular output. <parameter>sizes</parameter>
1037 accepts a list of column sizes, one for each column selected by
1038 <parameter>query-expression</parameter>, to use in formatting
1039 the tabular report. The default value of &t; means that minimum
1040 sizes are computed. <parameter>formats</parameter> is a list of
1041 format strings to be used for printing each column selected by
1042 <parameter>query-expression</parameter>. The default value of
1043 <parameter>formats</parameter> is &t; meaning that
1044 <symbol>~A</symbol> is used to format all columns or
1045 <symbol>~VA</symbol> if column sizes are used.
1049 <title>Examples</title>
1051 (print-query [select [emplid] [first-name] [last-name] [email]
1053 :where [< [emplid] 5]]
1054 :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
1055 ID FORENAME SURNAME EMAIL
1056 1 Vladamir Lenin lenin@soviet.org
1057 2 Josef Stalin stalin@soviet.org
1058 3 Leon Trotsky trotsky@soviet.org
1059 4 Nikita Kruschev kruschev@soviet.org
1062 (print-query "select emplid,first_name,last_name,email from employee where emplid >= 5"
1063 :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
1064 ID FORENAME SURNAME EMAIL
1065 5 Leonid Brezhnev brezhnev@soviet.org
1066 6 Yuri Andropov andropov@soviet.org
1067 7 Konstantin Chernenko chernenko@soviet.org
1068 8 Mikhail Gorbachev gorbachev@soviet.org
1069 9 Boris Yeltsin yeltsin@soviet.org
1070 10 Vladamir Putin putin@soviet.org
1075 <title>Side Effects</title>
1081 <title>Affected by</title>
1087 <title>Exceptional Situations</title>
1089 If the execution of the SQL query leads to any errors, an
1090 error of type <errortype>sql-database-error</errortype> is
1095 <title>See Also</title>
1097 <member><link linkend="query"><function>query</function></link></member>
1098 <member><link linkend="do-query"><function>do-query</function></link></member>
1099 <member><link linkend="map-query"><function>map-query</function></link></member>
1100 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1101 <member><link linkend="select"><function>select</function></link></member>
1105 <title>Notes</title>
1112 <refentry id="select">
1114 <refentrytitle>SELECT</refentrytitle>
1117 <refname><emphasis>Function</emphasis> <emphasis role="bold">SELECT</emphasis></refname>
1118 <refpurpose>Executes a query given the supplied constraints.</refpurpose>
1119 <refclass>Function</refclass>
1122 <title>Syntax</title>
1124 <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>
1127 <title>Arguments and Values</title>
1130 <term><parameter>identifiers</parameter></term>
1133 A set of <glossterm linkend="gloss-sql-expression">sql
1134 expressions</glossterm> each of which indicates a column
1140 <term><parameter>all</parameter></term>
1148 <term><parameter>distinct</parameter></term>
1156 <term><parameter>from</parameter></term>
1159 One or more SQL expression representing tables.
1164 <term><parameter>group-by</parameter></term>
1172 <term><parameter>having</parameter></term>
1180 <term><parameter>order-by</parameter></term>
1188 <term><parameter>set-operation</parameter></term>
1196 <term><parameter>where</parameter></term>
1204 <term><parameter>database</parameter></term>
1207 <glossterm linkend="gloss-database-object">database
1208 object</glossterm>. This will default to the value
1209 of <symbol>*default-database*</symbol>.</para>
1213 <term><parameter>flatp</parameter></term>
1215 <para>A Boolean whose default value is &nil;.</para>
1219 <term><parameter>result-types</parameter></term>
1222 <glossterm linkend="gloss-field-types">field type
1223 specifier</glossterm>. The default is &nil;.
1226 The purpose of this argument is cause &clsql; to
1227 import SQL numeric fields into numeric Lisp objects
1228 rather than strings. This reduces the cost of
1229 allocating a temporary string and the &clsql; users'
1230 inconvenience of converting number strings into number
1234 A value of <symbol>:auto</symbol> causes &clsql;
1235 to automatically convert SQL fields into a
1236 numeric format where applicable. The default value of
1237 &nil; causes all fields to be returned as strings
1238 regardless of the SQL type. Otherwise a list is expected
1239 which has a element for each field that specifies the
1240 conversion. Valid type identifiers are:
1241 <simplelist type="vert">
1242 <member><symbol>:int</symbol> Field is imported as a
1243 signed integer, from 8-bits to 64-bits depending
1244 upon the field type.
1246 <member><symbol>:double</symbol> Field is imported as a
1247 double-float number.
1249 <member><symbol>t</symbol> Field is imported as a
1253 If the list is shorter than the number of fields, the a
1254 value of <symbol>t</symbol> is assumed for the field.
1255 If the list is longer than the number of fields, the
1256 extra elements are ignored.
1261 <term><parameter>field-names</parameter></term>
1264 A boolean with a default value of &t;. When &t;, this
1265 function returns a second value of a list of field
1266 names. When &nil;, this function only returns one value -
1272 <term><parameter>result</parameter></term>
1275 A list representing the result set obtained. For each
1276 tuple in the result set, there is an element in this
1277 list, which is itself a list of all the attribute values
1285 <title>Description</title>
1287 Executes a query on <parameter>database</parameter>, which has
1288 a default value of <symbol>*default-database*</symbol>,
1289 specified by the SQL expressions supplied using the remaining
1290 arguments in <parameter>args</parameter>. The
1291 <function>select</function> function can be used to generate
1292 queries in both functional and object oriented contexts.
1295 In the functional case, the required arguments specify the
1296 columns selected by the query and may be symbolic SQL
1297 expressions or strings representing attribute
1298 identifiers. Type modified identifiers indicate that the
1299 values selected from the specified column are converted to the
1300 specified lisp type. The keyword arguments
1301 <parameter>all</parameter>, <parameter>distinct</parameter>,
1302 <parameter>from</parameter>, <parameter>group-by</parameter>,
1303 <parameter>having</parameter>,
1304 <parameter>order-by</parameter>,
1305 <parameter>set-operation</parameter> and
1306 <parameter>where</parameter> are used to specify, using the
1307 symbolic SQL syntax, the corresponding components of the SQL
1308 query generated by the call to
1309 <function>select</function>.
1312 <parameter>result-types</parameter> is a list of symbols which
1313 specifies the lisp type for each field returned by the
1314 query. If <parameter>result-types</parameter> is &nil; all
1315 results are returned as strings whereas the default value of
1316 <symbol>:auto</symbol> means that the lisp types are
1317 automatically computed for each
1318 field. <parameter>field-names</parameter> is &t; by default
1319 which means that the second value returned is a list of
1320 strings representing the columns selected by the query. If
1321 <parameter>field-names</parameter> is &nil;, the list of
1322 column names is not returned as a second value.
1325 In the object oriented case, the required arguments to
1326 <function>select</function> are symbols denoting View Classes
1327 which specify the database tables to query. In this case,
1328 <function>select</function> returns a list of View Class
1329 instances whose slots are set from the attribute values of the
1330 records in the specified table. <symbol>Slot-value</symbol> is
1331 a legal operator which can be employed as part of the symbolic
1332 SQL syntax used in the <parameter>where</parameter> keyword
1333 argument to <function>select</function>.
1334 <parameter>refresh</parameter> is &nil; by default which means
1335 that the View Class instances returned are retrieved from a
1336 cache if an equivalent call to <function>select</function> has
1337 previously been issued. If <parameter>refresh</parameter> is
1338 true, the View Class instances returned are updated as
1339 necessary from the database and the generic function
1340 <function>instance-refreshed</function> is called to perform
1341 any necessary operations on the updated instances.
1344 In both object oriented and functional contexts,
1345 <parameter>flatp</parameter> has a default value of &nil;
1346 which means that the results are returned as a list of
1347 lists. If <parameter>flatp</parameter> is t and only one
1348 result is returned for each record selected in the query, the
1349 results are returned as elements of a list.
1353 <title>Examples</title>
1355 (select [first-name] :from [employee] :flatp t :distinct t
1358 :order-by [first-name])
1359 => ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
1362 (select [first-name] [count [*]] :from [employee]
1364 :group-by [first-name]
1365 :order-by [first-name]
1367 => (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
1368 ("Mikhail" "1") ("Nikita" "1") ("Vladamir" "2") ("Yuri" "1"))
1370 (select [last-name] :from [employee]
1371 :where [like [email] "%org"]
1372 :order-by [last-name]
1376 => ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
1377 "Stalin" "Trotsky" "Yeltsin")
1379 (select [max [emplid]] :from [employee]
1382 :result-types :auto)
1385 (clsql:select [avg [height]] :from [employee] :flatp t :field-names nil)
1388 (select [emplid] [last-name] :from [employee] :where [= [emplid] 1])
1390 ("emplid" "last_name")
1392 (select [emplid :string] :from [employee]
1393 :where [= 1 [emplid]]
1398 (select [emplid] :from [employee] :order-by [emplid]
1399 :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]]
1404 (clsql:select [emplid] :from [employee]
1405 :where [in [emplid] '(1 2 3 4)]
1411 (select [first-name] [last-name] :from [employee]
1413 :order-by '(([first-name] :asc) ([last-name] :desc)))
1414 => (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
1415 ("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
1416 ("Nikita" "Kruschev") ("Vladamir" "Putin") ("Vladamir" "Lenin")
1417 ("Yuri" "Andropov"))
1419 (select [last-name] :from [employee]
1420 :set-operation [union [select [first-name] :from [employee]
1421 :order-by [last-name]]]
1425 => ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
1426 "Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
1427 "Trotsky" "Vladamir" "Yeltsin" "Yuri")
1431 <title>Side Effects</title>
1433 <para>Whatever effects the execution of the SQL query has on
1434 the underlying database, if any.</para>
1438 <title>Affected by</title>
1444 <title>Exceptional Situations</title>
1446 If the execution of the SQL query leads to any errors, an
1447 error of type <errortype>sql-database-error</errortype> is
1452 <title>See Also</title>
1454 <member><link linkend="query"><function>query</function></link></member>
1455 <member><link linkend="print-query"><function>print-query</function></link></member>
1456 <member><link linkend="do-query"><function>do-query</function></link></member>
1457 <member><link linkend="map-query"><function>map-query</function></link></member>
1458 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1459 <member><link linkend="instance-refreshed"><function>instance-refreshed</function></link></member>
1463 <title>Notes</title>
1465 The <parameter>field-names</parameter> and
1466 <parameter>result-types</parameter> keyword arguments are a
1470 <parameter>select</parameter> is common across the functional
1471 and object-oriented data manipulation languages.
1477 <!-- iteration and mapping -->
1479 <refentry id="do-query">
1481 <refentrytitle>DO-QUERY</refentrytitle>
1484 <refname><emphasis>Macro</emphasis> <emphasis role="bold">DO-QUERY</emphasis></refname>
1485 <refpurpose>Iterate over all the tuples of a query.</refpurpose>
1486 <refclass>Macro</refclass>
1489 <title>Syntax</title>
1491 <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>
1494 <title>Arguments and Values</title>
1497 <term><parameter>args</parameter></term>
1499 <para>A list of variable names.</para>
1503 <term><parameter>query-expression</parameter></term>
1505 <para>An <glossterm linkend="gloss-sql-expression">sql
1506 expression</glossterm> that represents an SQL
1507 query which is expected to return a (possibly empty)
1508 result set, where each tuple has as many attributes as
1509 <parameter>function</parameter> takes arguments.</para>
1513 <term><parameter>database</parameter></term>
1516 <glossterm linkend="gloss-database-object">database
1517 object</glossterm>. This will default to
1518 <symbol>*default-database*</symbol>.</para>
1522 <term><parameter>result-types</parameter></term>
1525 A <glossterm linkend="gloss-field-types">field type
1526 specifier</glossterm>. The default is &nil;. See <link
1527 linkend="query"><function>query</function></link> for
1528 the semantics of this argument.
1533 <term><parameter>body</parameter></term>
1535 <para>A body of Lisp code, like in a
1536 <function>destructuring-bind</function> form.</para>
1540 <term><parameter>result</parameter></term>
1542 <para>The result of executing <parameter>body</parameter>.</para>
1548 <title>Description</title>
1550 Repeatedly executes <parameter>body</parameter> within a
1551 binding of <parameter>args</parameter> on the fields of each
1552 row selected by the SQL query
1553 <parameter>query-expression</parameter>, which may be a string
1554 or a symbolic SQL expression, in
1555 <parameter>database</parameter> which defaults to
1556 <symbol>*default-database*</symbol>.
1559 The body of code is executed in a block named
1560 <symbol>nil</symbol> which may be returned from prematurely
1561 via <function>return</function> or
1562 <function>return-from</function>. In this case the result of
1563 evaluating the <function>do-query</function> form will be the
1564 one supplied to <function>return</function> or
1565 <function>return-from</function>. Otherwise the result will
1566 be <symbol>nil</symbol>.
1569 The body of code appears also is if wrapped in a
1570 <function>destructuring-bind</function> form, thus allowing
1571 declarations at the start of the body, especially those
1572 pertaining to the bindings of the variables named in
1573 <parameter>args</parameter>.
1576 <parameter>result-types</parameter> is a list of symbols which
1577 specifies the lisp type for each field returned by
1578 <parameter>query-expression</parameter>. If
1579 <parameter>result-types</parameter> is &nil; all results are
1580 returned as strings whereas the default value of
1581 <symbol>:auto</symbol> means that the lisp types are
1582 automatically computed for each field.
1585 <parameter>query-expression</parameter> may be an object query
1586 (i.e., the selection arguments refer to View Classes), in
1587 which case <parameter>args</parameter> are bound to the tuples
1588 of View Class instances returned by the object oriented query.
1592 <title>Examples</title>
1594 (do-query ((salary name) "select salary,name from simple")
1595 (format t "~30A gets $~2,5$~%" name (read-from-string salary)))
1596 >> Mai, Pierre gets $10000.00
1597 >> Hacker, Random J. gets $08000.50
1600 (do-query ((salary name) "select salary,name from simple")
1601 (return (cons salary name)))
1602 => ("10000.00" . "Mai, Pierre")
1605 (do-query ((name) [select [last-name] :from [employee]
1606 :order-by [last-name]])
1609 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1610 "Chernenko" "Brezhnev" "Andropov")
1613 (do-query ((e) [select 'employee :order-by [last-name]])
1614 (push (slot-value e 'last-name) result))
1616 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1617 "Chernenko" "Brezhnev" "Andropov")
1621 <title>Side Effects</title>
1622 <para>Whatever effects the execution of the SQL query has
1623 on the underlying database, if any.</para>
1626 <title>Affected by</title>
1630 <title>Exceptional Situations</title>
1631 <para>If the execution of the SQL query leads to any
1632 errors, an error of type
1633 <errortype>sql-database-error</errortype> is signalled.</para>
1634 <para>If the number of variable names in
1635 <parameter>args</parameter> and the number of attributes in
1636 the tuples in the result set don't match up, an error is
1640 <title>See Also</title>
1642 <member><link linkend="query"><function>query</function></link></member>
1643 <member><link linkend="map-query"><function>map-query</function></link></member>
1644 <member><link linkend="print-query"><function>print-query</function></link></member>
1645 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1646 <member><link linkend="select"><function>select</function></link></member>
1650 <title>Notes</title>
1651 <para>The <parameter>result-types</parameter> keyword argument
1652 is a &clsql; extension.</para>
1654 <parameter>do-query</parameter> is common across the functional
1655 and object-oriented data manipulation languages.
1660 <refentry id="for-each-row">
1662 <refentrytitle>FOR-EACH-ROW</refentrytitle>
1665 <refname><emphasis>Function</emphasis> <emphasis role="bold">FOR-EACH-ROW</emphasis></refname>
1666 <refpurpose><!-- purpose --></refpurpose>
1667 <refclass>Function</refclass>
1670 <title>Syntax</title>
1672 <function>for-each-row</function> &KEY FROM ORDER-BY WHERE DISTINCT LIMIT &REST FIELDS &body <replaceable>body</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
1675 <title>Arguments and Values</title>
1677 <!-- arguments and values -->
1681 <title>Description</title>
1683 <!-- description -->
1687 <title>Examples</title>
1693 <title>Side Effects</title>
1695 <!-- side effects -->
1699 <title>Affected by</title>
1702 <!-- affected by -->
1707 <title>Exceptional Situations</title>
1709 <!-- execeptional situations -->
1713 <title>See Also</title>
1715 <member><link linkend="query"><function>query</function></link></member>
1716 <member><link linkend="print-query"><function>print-query</function></link></member>
1717 <member><link linkend="do-query"><function>do-query</function></link></member>
1718 <member><link linkend="map-query"><function>map-query</function></link></member>
1719 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1720 <member><link linkend="select"><function>select</function></link></member>
1724 <title>Notes</title>
1731 <refentry id="loop-tuples">
1733 <refentrytitle>LOOP</refentrytitle>
1736 <refname><emphasis>Additional clause</emphasis> for <emphasis role="bold">LOOP</emphasis></refname>
1737 <refpurpose>Iterate over all the tuples of a
1738 query via a loop clause.</refpurpose>
1739 <refclass>Loop Clause</refclass>
1742 <title>Compatibility</title>
1743 <caution><para><function>loop-for-as-tuples</function> only works with &cmucl;.</para></caution>
1746 <title>Syntax</title>
1747 <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>
1750 <title>Arguments and Values</title>
1753 <term><parameter>var</parameter></term>
1755 <para>A <literal>d-var-spec</literal>, as defined in the
1756 grammar for <function>loop</function>-clauses in the ANSI
1757 Standard for Common Lisp. This allows for the usual
1758 loop-style destructuring.</para>
1762 <term><parameter>type-spec</parameter></term>
1764 <para>An optional <literal>type-spec</literal> either
1765 simple or destructured, as defined in the grammar for
1766 <function>loop</function>-clauses in the ANSI Standard for
1771 <term><parameter>query</parameter></term>
1773 <para>An <glossterm linkend="gloss-sql-expression">sql
1774 expression</glossterm> that represents an SQL
1775 query which is expected to return a (possibly empty)
1776 result set, where each tuple has as many attributes as
1777 <parameter>function</parameter> takes arguments.</para>
1781 <term><parameter>database</parameter></term>
1784 <glossterm linkend="gloss-database-object">database
1785 object</glossterm>. This will default to the value
1786 of <symbol>*default-database*</symbol>.</para>
1792 <title>Description</title>
1793 <para>This clause is an iteration driver for
1794 <function>loop</function>, that binds the given variable
1795 (possibly destructured) to the consecutive tuples (which are
1796 represented as lists of attribute values) in the result set
1797 returned by executing the SQL <parameter>query</parameter>
1798 expression on the <parameter>database</parameter>
1801 <parameter>query</parameter> may be an object query (i.e., the
1802 selection arguments refer to View Classes), in which case the
1803 supplied variable is bound to the tuples of View Class
1804 instances returned by the object oriented query.
1808 <title>Examples</title>
1810 (defvar *my-db* (connect '("dent" "newesim" "dent" "dent"))
1813 (loop with time-graph = (make-hash-table :test #'equal)
1814 with event-graph = (make-hash-table :test #'equal)
1815 for (time event) being the tuples of "select time,event from log"
1818 (incf (gethash time time-graph 0))
1819 (incf (gethash event event-graph 0))
1821 (flet ((show-graph (k v) (format t "~40A => ~5D~%" k v)))
1822 (format t "~&Time-Graph:~%===========~%")
1823 (maphash #'show-graph time-graph)
1824 (format t "~&~%Event-Graph:~%============~%")
1825 (maphash #'show-graph event-graph))
1826 (return (values time-graph event-graph)))
1835 >> CLOS Benchmark entry. => 9000
1836 >> Demo Text... => 3
1837 >> doit-text => 3000
1838 >> C Benchmark entry. => 12000
1839 >> CLOS Benchmark entry => 32000
1840 => #<EQUAL hash table, 3 entries {48350A1D}>
1841 => #<EQUAL hash table, 5 entries {48350FCD}>
1843 (loop for (forename surname)
1845 [select [first-name] [last-name] :from [employee]
1846 :order-by [last-name]]
1847 collect (concatenate 'string forename " " surname))
1848 => ("Yuri Andropov" "Leonid Brezhnev" "Konstantin Chernenko" "Mikhail Gorbachev"
1849 "Nikita Kruschev" "Vladamir Lenin" "Vladamir Putin" "Josef Stalin"
1850 "Leon Trotsky" "Boris Yeltsin")
1852 (loop for (e) being the records in
1853 [select 'employee :where [< [emplid] 4] :order-by [emplid]]
1854 collect (slot-value e 'last-name))
1855 => ("Lenin" "Stalin" "Trotsky")
1859 <title>Side Effects</title>
1860 <para>Whatever effects the execution of the SQL query has
1861 on the underlying database, if any.</para>
1864 <title>Affected by</title>
1868 <title>Exceptional Situations</title>
1869 <para>If the execution of the SQL query leads to any
1870 errors, an error of type
1871 <errortype>sql-database-error</errortype> is signalled.</para>
1872 <para>Otherwise, any of the exceptional situations of
1873 <function>loop</function> applies.</para>
1876 <title>See Also</title>
1879 <member><link linkend="query"><function>query</function></link></member>
1880 <member><link linkend="map-query"><function>map-query</function></link></member>
1881 <member><link linkend="do-query"><function>do-query</function></link></member>
1882 <member><link linkend="print-query"><function>print-query</function></link></member>
1883 <member><link linkend="select"><function>select</function></link></member>
1888 <title>Notes</title>
1889 <para>The <parameter>database</parameter> loop keyword is a
1890 &clsql; extension.</para>
1892 The extended <function>loop</function> syntax is common across
1893 the functional and object-oriented data manipulation
1899 <refentry id="map-query">
1901 <refentrytitle>MAP-QUERY</refentrytitle>
1904 <refname><emphasis>Function</emphasis> <emphasis role="bold">MAP-QUERY</emphasis></refname>
1905 <refpurpose>Map a function over all the tuples from a
1907 <refclass>Function</refclass>
1910 <title>Syntax</title>
1911 <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>
1914 <title>Arguments and Values</title>
1917 <term><parameter>output-type-spec</parameter></term>
1919 <para>A sequence type specifier or <symbol>nil</symbol>.</para>
1923 <term><parameter>function</parameter></term>
1925 <para>A function designator.
1926 <parameter>function</parameter> takes a single argument which
1927 is the atom value for a query single with a single column
1928 or is a list of values for a multi-column query.</para>
1932 <term><parameter>query-expression</parameter></term>
1934 <para>An <glossterm linkend="gloss-sql-expression">sql
1935 expression</glossterm> that represents an SQL
1936 query which is expected to return a (possibly empty)
1941 <term><parameter>database</parameter></term>
1944 <glossterm linkend="gloss-database-object">database
1945 object</glossterm>. This will default to the value
1946 of <symbol>*default-database*</symbol>.</para>
1950 <term><parameter>result-types</parameter></term>
1953 A <glossterm linkend="gloss-field-types">field type
1954 specifier</glossterm>. The default is &nil;. See <link
1955 linkend="query"><function>query</function></link> for
1956 the semantics of this argument.
1961 <term><returnvalue>result</returnvalue></term>
1963 <para>If <parameter>output-type-spec</parameter> is a
1964 type specifier other than <symbol>nil</symbol>, then a
1965 sequence of the type it denotes. Otherwise
1966 <symbol>nil</symbol> is returned.</para>
1972 <title>Description</title>
1974 Applies <parameter>function</parameter> to the successive
1975 tuples in the result set returned by executing the SQL
1976 <parameter>query-expression</parameter>. If the
1977 <parameter>output-type-spec</parameter> is
1978 <symbol>nil</symbol>, then the result of each application of
1979 <parameter>function</parameter> is discarded, and
1980 <function>map-query</function> returns <symbol>nil</symbol>.
1981 Otherwise the result of each successive application of
1982 <parameter>function</parameter> is collected in a sequence of
1983 type <parameter>output-type-spec</parameter>, where the jths
1984 element is the result of applying
1985 <parameter>function</parameter> to the attributes of the jths
1986 tuple in the result set. The collected sequence is the result
1987 of the call to <function>map-query</function>.
1990 If the <parameter>output-type-spec</parameter> is a subtype of
1991 <type>list</type>, the result will be a <type>list</type>.
1994 If the <parameter>result-type</parameter> is a subtype of
1995 <type>vector</type>, then if the implementation can determine
1996 the element type specified for the
1997 <parameter>result-type</parameter>, the element type of the
1998 resulting array is the result of
1999 <emphasis>upgrading</emphasis> that element type; or, if the
2000 implementation can determine that the element type is
2001 unspecified (or <symbol>*</symbol>), the element type of the
2002 resulting array is <type>t</type>; otherwise, an error is
2006 If <parameter>result-types</parameter> is &nil; all results
2007 are returned as strings whereas the default value of
2008 <symbol>:auto</symbol> means that the lisp types are
2009 automatically computed for each field.</para>
2011 <parameter>query-expression</parameter> may be an object query
2012 (i.e., the selection arguments refer to View Classes), in
2013 which case the supplied function is applied to the tuples of
2014 View Class instances returned by the object oriented query.
2018 <title>Examples</title>
2020 (map-query 'list #'(lambda (tuple)
2021 (multiple-value-bind (salary name) tuple
2022 (declare (ignorable name))
2023 (read-from-string salary)))
2024 "select salary,name from simple where salary > 8000")
2027 (map-query '(vector double-float)
2029 (multiple-value-bind (salary name) tuple
2030 (declare (ignorable name))
2031 (let ((*read-default-float-format* 'double-float))
2032 (coerce (read-from-string salary) 'double-float))
2033 "select salary,name from simple where salary > 8000")))
2034 => #(10000.0d0 8000.5d0)
2036 => (SIMPLE-ARRAY DOUBLE-FLOAT (2))
2039 (values (map-query nil #'(lambda (tuple)
2040 (multiple-value-bind (salary name) tuple
2041 (push (cons name (read-from-string salary)) list))
2042 "select salary,name from simple where salary > 8000"))
2045 => (("Hacker, Random J." . 8000.5) ("Mai, Pierre" . 10000.0))
2047 (map-query 'vector #'identity
2048 [select [last-name] :from [employee] :flatp t
2049 :order-by [last-name]])
2050 => #("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
2051 "Stalin" "Trotsky" "Yeltsin")
2053 (map-query 'list #'identity
2054 [select [first-name] [last-name] :from [employee]
2055 :order-by [last-name]])
2056 => (("Yuri" "Andropov") ("Leonid" "Brezhnev") ("Konstantin" "Chernenko")
2057 ("Mikhail" "Gorbachev") ("Nikita" "Kruschev") ("Vladamir" "Lenin")
2058 ("Vladamir" "Putin") ("Josef" "Stalin") ("Leon" "Trotsky")
2059 ("Boris" "Yeltsin"))
2061 (map-query 'list #'last-name [select 'employee :order-by [emplid]])
2062 => ("Lenin" "Stalin" "Trotsky" "Kruschev" "Brezhnev" "Andropov" "Chernenko"
2063 "Gorbachev" "Yeltsin" "Putin")
2067 <title>Side Effects</title>
2068 <para>Whatever effects the execution of the SQL query has
2069 on the underlying database, if any.</para>
2072 <title>Affected by</title>
2076 <title>Exceptional Situations</title>
2077 <para>If the execution of the SQL query leads to any
2078 errors, an error of type
2079 <errortype>sql-database-error</errortype> is signalled.</para>
2080 <para>An error of type <errortype>type-error</errortype> must
2081 be signaled if the <parameter>output-type-spec</parameter> is
2082 not a recognizable subtype of <type>list</type>, not a
2083 recognizable subtype of <type>vector</type>, and not
2084 <symbol>nil</symbol>.</para>
2085 <para>An error of type <errortype>type-error</errortype>
2086 should be signaled if
2087 <parameter>output-type-spec</parameter> specifies the number
2088 of elements and the size of the result set is different from
2092 <title>See Also</title>
2094 <member><link linkend="query"><function>query</function></link></member>
2095 <member><link linkend="do-query"><function>do-query</function></link></member>
2096 <member><link linkend="print-query"><function>print-query</function></link></member>
2097 <member><link linkend="loop-tuples"><function>loop</function></link></member>
2098 <member><link linkend="select"><function>select</function></link></member>
2102 <title>Notes</title>
2103 <para>The <parameter>result-types</parameter> keyword argument
2104 is a &clsql; extension.</para>
2106 <parameter>map-query</parameter> is common across the
2107 functional and object-oriented data manipulation languages.
2113 <!-- prepared statements -->
2115 <refentry id="prepare-sql">
2117 <refentrytitle>PREPARE-SQL</refentrytitle>
2120 <refname><emphasis>Function</emphasis> <emphasis role="bold">PREPARE-SQL</emphasis></refname>
2121 <refpurpose>Create a prepared statement.</refpurpose>
2122 <refclass>Function</refclass>
2125 <title>Syntax</title>
2127 <function>prepare-sql</function> <replaceable>sql-stmt types</replaceable> &key <replaceable>database</replaceable> <replaceable>result-types</replaceable> <replaceable>field-names</replaceable> => <returnvalue>result</returnvalue></synopsis>
2130 <title>Arguments and Values</title>
2132 <!-- arguments and values -->
2136 <title>Description</title>
2137 <para>Prepares a SQL statement <parameter>sql-stmt</parameter>
2138 for execution. <parameter>types</parameter> contains a list of
2139 types corresponding to the input parameters. Returns a
2140 prepared-statement object.
2151 <title>Examples</title>
2157 <title>Side Effects</title>
2159 <!-- side effects -->
2163 <title>Affected by</title>
2166 <!-- affected by -->
2171 <title>Exceptional Situations</title>
2173 <!-- execeptional situations -->
2177 <title>See Also</title>
2185 <title>Notes</title>
2192 <refentry id="run-prepared-sql">
2194 <refentrytitle>RUN-PREPARED-SQL</refentrytitle>
2197 <refname><emphasis>Function</emphasis> <emphasis role="bold">RUN-PREPARED-SQL</emphasis></refname>
2198 <refpurpose>Execute a prepared statement.</refpurpose>
2199 <refclass>Function</refclass>
2202 <title>Syntax</title>
2204 <function>run-prepared-sql</function> <replaceable>prepared-stmt</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
2207 <title>Arguments and Values</title>
2209 <!-- arguments and values -->
2213 <title>Description</title>
2214 <para>Execute the prepared sql statment. All input
2215 parameters must be bound.
2219 <title>Examples</title>
2225 <title>Side Effects</title>
2227 <!-- side effects -->
2231 <title>Affected by</title>
2234 <!-- affected by -->
2239 <title>Exceptional Situations</title>
2241 <!-- execeptional situations -->
2245 <title>See Also</title>
2253 <title>Notes</title>
2260 <refentry id="free-prepared-sql">
2262 <refentrytitle>FREE-PREPARED-SQL</refentrytitle>
2265 <refname><emphasis>Function</emphasis> <emphasis role="bold">FREE-PREPARED-SQL</emphasis></refname>
2266 <refpurpose>Delete a prepared statement object.</refpurpose>
2267 <refclass>Function</refclass>
2270 <title>Syntax</title>
2272 <function>free-prepared-sql</function> <replaceable>prepared-stmt</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
2275 <title>Arguments and Values</title>
2277 <!-- arguments and values -->
2281 <title>Description</title>
2282 <para>Delete the objects associated with a prepared
2287 <title>Examples</title>
2293 <title>Side Effects</title>
2295 <!-- side effects -->
2299 <title>Affected by</title>
2302 <!-- affected by -->
2307 <title>Exceptional Situations</title>
2309 <!-- execeptional situations -->
2313 <title>See Also</title>
2321 <title>Notes</title>
2328 <refentry id="bind-parameter">
2330 <refentrytitle>BIND-PARAMETER</refentrytitle>
2333 <refname><emphasis>Function</emphasis> <emphasis role="bold">BIND-PARAMETER</emphasis></refname>
2334 <refpurpose>Bind a parameter in a prepared statement.</refpurpose>
2335 <refclass>Function</refclass>
2338 <title>Syntax</title>
2340 <function>bind-parameter</function> <replaceable>prepared-stmt</replaceable> <replaceable>position</replaceable> <replaceable>value</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
2343 <title>Arguments and Values</title>
2345 <!-- arguments and values -->
2349 <title>Description</title>
2350 <para>Sets the value of a parameter in a prepared statement.
2354 <title>Examples</title>
2360 <title>Side Effects</title>
2362 <!-- side effects -->
2366 <title>Affected by</title>
2369 <!-- affected by -->
2374 <title>Exceptional Situations</title>
2376 <!-- execeptional situations -->
2380 <title>See Also</title>
2388 <title>Notes</title>