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>
363 <member><link linkend="update-records"><function>update-records</function></link></member>
364 <member><link linkend="delete-records"><function>delete-records</function></link></member>
376 <refentry id="update-records">
378 <refentrytitle>UPDATE-RECORDS</refentrytitle>
381 <refname><emphasis>Function</emphasis> <emphasis role="bold">UPDATE-RECORDS</emphasis></refname>
382 <refpurpose>Updates the values of existing records.</refpurpose>
383 <refclass>Function</refclass>
386 <title>Syntax</title>
388 <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>
391 <title>Arguments and Values</title>
394 <term><parameter>table</parameter></term>
397 A string, symbol or symbolic SQL expression representing
398 the name of a table existing in
399 <parameter>database</parameter>.
404 <term><parameter>attributes</parameter></term>
407 A list of attribute identifiers or &nil;.
412 <term><parameter>values</parameter></term>
415 A list of attribute values or &nil;.
420 <term><parameter>av-pairs</parameter></term>
423 A list of attribute identifier/value pairs or &nil;.
428 <term><parameter>where</parameter></term>
431 A symbolic SQL expression.
436 <term><parameter>database</parameter></term>
439 <glossterm linkend="gloss-database-object">database
440 object</glossterm>. This will default to the value
441 of <symbol>*default-database*</symbol>.</para>
447 <title>Description</title>
449 Updates the attribute values of existing records satsifying
450 the SQL expression <parameter>where</parameter> in the table
451 specified by <parameter>table</parameter> in
452 <parameter>database</parameter> which defaults to
453 <symbol>*default-database*</symbol>.
456 There are three ways of specifying the values to update for
457 each row. In the first, <parameter>values</parameter> contains
458 a list of values to use in the update and
459 <parameter>attributes</parameter> and
460 <parameter>av-pairs</parameter> are &nil;. This can be used
461 when values are supplied for all attributes in
462 <parameter>table</parameter>. In the second,
463 <parameter>attributes</parameter> is a list of column names,
464 <parameter>values</parameter> is a corresponding list of
465 values and <parameter>av-pairs</parameter> is &nil;. In the
466 third, <parameter>attributes</parameter> and
467 <parameter>values</parameter> are &nil; and
468 <parameter>av-pairs</parameter> is an alist of (attribute
473 <title>Examples</title>
475 (select [first-name] [last-name] [email]
477 :where [= [emplid] 1]
479 => (("Vladamir" "Lenin" "lenin@soviet.org"))
480 (update-records [employee]
481 :av-pairs'((first_name "Yuri")
482 (last_name "Gagarin")
483 (email "gagarin@soviet.org"))
484 :where [= [emplid] 1])
486 (select [first-name] [last-name] [email]
488 :where [= [emplid] 1]
490 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
494 <title>Side Effects</title>
496 Modifications are made to the underlying database.
500 <title>Affected by</title>
506 <title>Exceptional Situations</title>
508 An error of type <symbol>sql-database-data-error</symbol> is
509 signalled if <parameter>table</parameter> is not an existing
510 table in <parameter>database</parameter>, if the specified
511 attributes are not found or if the SQL statement resulting
512 from the symbolic expression <parameter>where</parameter> does
513 not return a Boolean value.
515 <para>If the execution of the SQL query leads to any errors, an
516 error of type <errortype>sql-database-error</errortype> is
520 <title>See Also</title>
523 <member><link linkend="insert-records"><function>insert-records</function></link></member>
524 <member><link linkend="delete-records"><function>delete-records</function></link></member>
536 <refentry id="delete-records">
538 <refentrytitle>DELETE-RECORDS</refentrytitle>
541 <refname><emphasis>Function</emphasis> <emphasis role="bold">DELETE-RECORDS</emphasis></refname>
542 <refpurpose>Delete records from a database table.</refpurpose>
543 <refclass>Function</refclass>
546 <title>Syntax</title>
548 <function>delete-records</function> &key <replaceable>from</replaceable> <replaceable>where</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
551 <title>Arguments and Values</title>
554 <term><parameter>from</parameter></term>
557 A string, symbol or symbolic SQL expression representing
558 the name of a table existing in
559 <parameter>database</parameter>.
564 <term><parameter>where</parameter></term>
567 A symbolic SQL expression.
572 <term><parameter>database</parameter></term>
575 <glossterm linkend="gloss-database-object">database
576 object</glossterm>. This will default to the value
577 of <symbol>*default-database*</symbol>.</para>
583 <title>Description</title>
584 <para>Deletes records satisfying the SQL expression
585 <parameter>where</parameter> from the table specified by
586 <parameter>from</parameter> in <parameter>database</parameter>
587 specifies a database which defaults to
588 <symbol>*default-database*</symbol>.
592 <title>Examples</title>
594 (select [first-name] [last-name] [email]
596 :where [= [emplid] 11]
598 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
599 (delete-records :from [employee] :where [= [emplid] 11])
601 (select [first-name] [last-name] [email]
603 :where [= [emplid] 11]
609 <title>Side Effects</title>
611 Modifications are made to the underlying database.
615 <title>Affected by</title>
621 <title>Exceptional Situations</title>
623 An error of type <symbol>sql-database-data-error</symbol> is
624 signalled if <parameter>from</parameter> is not an existing
625 table in <parameter>database</parameter> or if the SQL
626 statement resulting from the symbolic expression
627 <parameter>where</parameter> does not return a Boolean value.
631 <title>See Also</title>
634 <member><link linkend="insert-records"><function>insert-records</function></link></member>
635 <member><link linkend="update-records"><function>update-records</function></link></member>
648 <!-- executing SQL commands and queries -->
650 <refentry id="execute-command">
652 <refentrytitle>EXECUTE-COMMAND</refentrytitle>
655 <refname><emphasis>Generic Function</emphasis> <emphasis role="bold">EXECUTE-COMMAND</emphasis></refname>
656 <refpurpose>Execute an SQL command which returns no values.</refpurpose>
657 <refclass>Generic Function</refclass>
660 <title>Syntax</title>
662 <function>execute-command</function> <replaceable>sql-expression</replaceable> &key <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
665 <title>Arguments and Values</title>
668 <term><parameter>sql-expression</parameter></term>
670 <para>An <glossterm linkend="gloss-sql-expression">sql
671 expression</glossterm> that represents an SQL
672 statement which will return no values.</para>
676 <term><parameter>database</parameter></term>
679 <glossterm linkend="gloss-database-object">database
680 object</glossterm>. This will default to the value
681 of <symbol>*default-database*</symbol>.</para>
683 </varlistentry></variablelist>
686 <title>Description</title>
687 <para>Executes the SQL command
688 <parameter>sql-expression</parameter>, which may be a symbolic
689 SQL expression or a string representing any SQL statement apart
690 from a query, on the supplied <parameter>database</parameter>
691 which defaults to <symbol>*default-database*</symbol>.
695 <title>Examples</title>
697 (execute-command "create table eventlog (time char(30),event char(70))")
700 (execute-command "create table eventlog (time char(30),event char(70))")
702 >> While accessing database #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {480B2B6D}>
703 >> with expression "create table eventlog (time char(30),event char(70))":
704 >> Error NIL: ERROR: amcreate: eventlog relation already exists
708 >> 0: [ABORT] Return to Top-Level.
710 >> Debug (type H for help)
712 >> (CLSQL-POSTGRESQL::|(PCL::FAST-METHOD DATABASE-EXECUTE-COMMAND (T POSTGRESQL-DATABASE))|
715 >> #<unavailable-arg>
716 >> #<unavailable-arg>)
717 >> Source: (ERROR 'SQL-DATABASE-ERROR :DATABASE DATABASE :EXPRESSION ...)
720 (execute-command "drop table eventlog")
725 <title>Side Effects</title>
726 <para>Whatever effects the execution of the SQL statement has
727 on the underlying database, if any.</para>
730 <title>Affected by</title>
734 <title>Exceptional Situations</title>
735 <para>If the execution of the SQL statement leads to any errors,
736 an error of type <errortype>sql-database-error</errortype> is
740 <title>See Also</title>
743 <member><link linkend="query"><function>query</function></link></member>
754 <refentry id="query">
756 <refentrytitle>QUERY</refentrytitle>
759 <refname><emphasis>Generic Function</emphasis> <emphasis role="bold">QUERY</emphasis></refname>
760 <refpurpose>Execute an SQL query and return the tuples as a
762 <refclass>Generic Function</refclass>
765 <title>Syntax</title>
767 <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>
770 <title>Arguments and Values</title>
773 <term><parameter>query-expression</parameter></term>
775 <para>An <glossterm linkend="gloss-sql-expression">sql
776 expression</glossterm> that represents an SQL
777 query which is expected to return a (possibly empty)
782 <term><parameter>database</parameter></term>
785 <glossterm linkend="gloss-database-object">database
786 object</glossterm>. This will default to the value
787 of <symbol>*default-database*</symbol>.</para>
791 <term><parameter>flatp</parameter></term>
793 <para>A Boolean whose default value is &nil;.</para>
797 <term><parameter>result-types</parameter></term>
800 <glossterm linkend="gloss-field-types">field type
801 specifier</glossterm>. The default is &nil;.
804 The purpose of this argument is cause &clsql; to
805 import SQL numeric fields into numeric Lisp objects
806 rather than strings. This reduces the cost of
807 allocating a temporary string and the &clsql; users'
808 inconvenience of converting number strings into number
812 A value of <symbol>:auto</symbol> causes &clsql;
813 to automatically convert SQL fields into a
814 numeric format where applicable. The default value of
815 &nil; causes all fields to be returned as strings
816 regardless of the SQL type. Otherwise a list is expected
817 which has a element for each field that specifies the
818 conversion. Valid type identifiers are:
819 <simplelist type="vert">
820 <member><symbol>:int</symbol> Field is imported as a
821 signed integer, from 8-bits to 64-bits depending
824 <member><symbol>:double</symbol> Field is imported as a
827 <member><symbol>t</symbol> Field is imported as a
831 If the list is shorter than the number of fields, the a
832 value of <symbol>t</symbol> is assumed for the field.
833 If the list is longer than the number of fields, the
834 extra elements are ignored.
839 <term><parameter>field-names</parameter></term>
842 A boolean with a default value of &t;. When &t;, this
843 function returns a second value of a list of field
844 names. When &nil;, this function only returns one value -
850 <term><returnvalue>result</returnvalue></term>
852 <para>A list representing the result set obtained. For
853 each tuple in the result set, there is an element in
854 this list, which is itself a list of all the attribute
855 values in the tuple.</para>
861 <title>Description</title>
863 Executes the SQL query expression
864 <parameter>query-expression</parameter>, which may be an SQL
865 expression or a string, on the supplied
866 <parameter>database</parameter> which defaults to
867 <symbol>*default-database*</symbol>. <parameter>result-types</parameter>
868 is a list of symbols which specifies the lisp type for each
869 field returned by <parameter>query-expression</parameter>.
872 If <parameter>result-types</parameter> is &nil; all results
873 are returned as strings whereas the default value of
874 <symbol>:auto</symbol> means that the lisp types are
875 automatically computed for each field.
878 <parameter>field-names</parameter> is &t; by default which
879 means that the second value returned is a list of strings
880 representing the columns selected by
881 <parameter>query-expression</parameter>. If
882 <parameter>field-names</parameter> is &nil;, the list of column
883 names is not returned as a second value.
886 <parameter>flatp</parameter> has a default value of &nil;
887 which means that the results are returned as a list of
888 lists.If FLATP is &t; and only one result is returned for each
889 record selected by <parameter>query-expression</parameter>,
890 the results are returned as elements of a list.
894 <title>Examples</title>
896 (query "select emplid,first_name,last_name,height from employee where emplid = 1")
897 => ((1 "Vladamir" "Lenin" 1.5564661d0)),
898 ("emplid" "first_name" "last_name" "height")
900 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
902 => ((1 "Vladamir" "Lenin" 1.5564661d0))
904 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
907 => (("1" "Vladamir" "Lenin" "1.5564661"))
909 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
911 :result-types '(:int t t :double))
912 => ((1 "Vladamir" "Lenin" 1.5564661))
914 (query "select last_name from employee where emplid > 5" :flatp t)
915 => ("Andropov" "Chernenko" "Gorbachev" "Yeltsin" "Putin"),
918 (query "select last_name from employee where emplid > 10"
925 <title>Side Effects</title>
926 <para>Whatever effects the execution of the SQL query has
927 on the underlying database, if any.</para>
930 <title>Affected by</title>
934 <title>Exceptional Situations</title>
935 <para>If the execution of the SQL query leads to any errors, an
936 error of type <errortype>sql-database-error</errortype> is
940 <title>See Also</title>
942 <member><link linkend="execute-command"><function>execute-command</function></link></member>
943 <member><link linkend="print-query"><function>print-query</function></link></member>
944 <member><link linkend="do-query"><function>do-query</function></link></member>
945 <member><link linkend="map-query"><function>map-query</function></link></member>
946 <member><link linkend="loop-tuples"><function>loop</function></link></member>
947 <member><link linkend="select"><function>select</function></link></member>
952 <para>The <parameter>field-names</parameter> and
953 <parameter>result-types</parameter> keyword arguments are a
954 &clsql; extension.</para>
958 <refentry id="print-query">
960 <refentrytitle>PRINT-QUERY</refentrytitle>
963 <refname><emphasis>Function</emphasis> <emphasis role="bold">PRINT-QUERY</emphasis></refname>
964 <refpurpose>Prints a tabular report of query results.</refpurpose>
965 <refclass>Function</refclass>
968 <title>Syntax</title>
970 <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>
973 <title>Arguments and Values</title>
976 <term><parameter>query-expression</parameter></term>
978 <para>An <glossterm linkend="gloss-sql-expression">sql
979 expression</glossterm> that represents an SQL
980 query which is expected to return a (possibly empty)
985 <term><parameter>database</parameter></term>
988 <glossterm linkend="gloss-database-object">database
989 object</glossterm>. This will default to the value
990 of <symbol>*default-database*</symbol>.</para>
994 <term><parameter>titles</parameter></term>
997 A list of strings or &nil; which is the default value.
1002 <term><parameter>formats</parameter></term>
1005 A list of strings, &nil; or &t; which is the default value.
1010 <term><parameter>sizes</parameter></term>
1013 A list of numbers, &nil; or &t; which is the default value.
1018 <term><parameter>stream</parameter></term>
1021 An output stream or &t; which is the default value.
1028 <title>Description</title>
1029 <para>Prints a tabular report of the results returned by the SQL
1030 query <parameter>query-expression</parameter>, which may be a
1031 symbolic SQL expression or a string, in
1032 <parameter>database</parameter> which defaults to
1033 <symbol>*default-database*</symbol>. The report is printed onto
1034 <parameter>stream</parameter> which has a default value of &t;
1035 which means that <symbol>*standard-output*</symbol> is used. The
1036 <parameter>title</parameter> argument, which defaults to &nil;,
1037 allows the specification of a list of strings to use as column
1038 titles in the tabular output. <parameter>sizes</parameter>
1039 accepts a list of column sizes, one for each column selected by
1040 <parameter>query-expression</parameter>, to use in formatting
1041 the tabular report. The default value of &t; means that minimum
1042 sizes are computed. <parameter>formats</parameter> is a list of
1043 format strings to be used for printing each column selected by
1044 <parameter>query-expression</parameter>. The default value of
1045 <parameter>formats</parameter> is &t; meaning that
1046 <symbol>~A</symbol> is used to format all columns or
1047 <symbol>~VA</symbol> if column sizes are used.
1051 <title>Examples</title>
1053 (print-query [select [emplid] [first-name] [last-name] [email]
1055 :where [< [emplid] 5]]
1056 :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
1057 ID FORENAME SURNAME EMAIL
1058 1 Vladamir Lenin lenin@soviet.org
1059 2 Josef Stalin stalin@soviet.org
1060 3 Leon Trotsky trotsky@soviet.org
1061 4 Nikita Kruschev kruschev@soviet.org
1064 (print-query "select emplid,first_name,last_name,email from employee where emplid >= 5"
1065 :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
1066 ID FORENAME SURNAME EMAIL
1067 5 Leonid Brezhnev brezhnev@soviet.org
1068 6 Yuri Andropov andropov@soviet.org
1069 7 Konstantin Chernenko chernenko@soviet.org
1070 8 Mikhail Gorbachev gorbachev@soviet.org
1071 9 Boris Yeltsin yeltsin@soviet.org
1072 10 Vladamir Putin putin@soviet.org
1077 <title>Side Effects</title>
1083 <title>Affected by</title>
1089 <title>Exceptional Situations</title>
1091 If the execution of the SQL query leads to any errors, an
1092 error of type <errortype>sql-database-error</errortype> is
1097 <title>See Also</title>
1099 <member><link linkend="query"><function>query</function></link></member>
1100 <member><link linkend="do-query"><function>do-query</function></link></member>
1101 <member><link linkend="map-query"><function>map-query</function></link></member>
1102 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1103 <member><link linkend="select"><function>select</function></link></member>
1107 <title>Notes</title>
1114 <refentry id="select">
1116 <refentrytitle>SELECT</refentrytitle>
1119 <refname><emphasis>Function</emphasis> <emphasis role="bold">SELECT</emphasis></refname>
1120 <refpurpose>Executes a query given the supplied constraints.</refpurpose>
1121 <refclass>Function</refclass>
1124 <title>Syntax</title>
1126 <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>
1129 <title>Arguments and Values</title>
1132 <term><parameter>identifiers</parameter></term>
1135 A set of <glossterm linkend="gloss-sql-expression">sql
1136 expressions</glossterm> each of which indicates a column
1142 <term><parameter>all</parameter></term>
1150 <term><parameter>distinct</parameter></term>
1158 <term><parameter>from</parameter></term>
1161 One or more SQL expression representing tables.
1166 <term><parameter>group-by</parameter></term>
1174 <term><parameter>having</parameter></term>
1182 <term><parameter>order-by</parameter></term>
1190 <term><parameter>set-operation</parameter></term>
1198 <term><parameter>where</parameter></term>
1206 <term><parameter>database</parameter></term>
1209 <glossterm linkend="gloss-database-object">database
1210 object</glossterm>. This will default to the value
1211 of <symbol>*default-database*</symbol>.</para>
1215 <term><parameter>flatp</parameter></term>
1217 <para>A Boolean whose default value is &nil;.</para>
1221 <term><parameter>result-types</parameter></term>
1224 <glossterm linkend="gloss-field-types">field type
1225 specifier</glossterm>. The default is &nil;.
1228 The purpose of this argument is cause &clsql; to
1229 import SQL numeric fields into numeric Lisp objects
1230 rather than strings. This reduces the cost of
1231 allocating a temporary string and the &clsql; users'
1232 inconvenience of converting number strings into number
1236 A value of <symbol>:auto</symbol> causes &clsql;
1237 to automatically convert SQL fields into a
1238 numeric format where applicable. The default value of
1239 &nil; causes all fields to be returned as strings
1240 regardless of the SQL type. Otherwise a list is expected
1241 which has a element for each field that specifies the
1242 conversion. Valid type identifiers are:
1243 <simplelist type="vert">
1244 <member><symbol>:int</symbol> Field is imported as a
1245 signed integer, from 8-bits to 64-bits depending
1246 upon the field type.
1248 <member><symbol>:double</symbol> Field is imported as a
1249 double-float number.
1251 <member><symbol>t</symbol> Field is imported as a
1255 If the list is shorter than the number of fields, the a
1256 value of <symbol>t</symbol> is assumed for the field.
1257 If the list is longer than the number of fields, the
1258 extra elements are ignored.
1263 <term><parameter>field-names</parameter></term>
1266 A boolean with a default value of &t;. When &t;, this
1267 function returns a second value of a list of field
1268 names. When &nil;, this function only returns one value -
1274 <term><parameter>result</parameter></term>
1277 A list representing the result set obtained. For each
1278 tuple in the result set, there is an element in this
1279 list, which is itself a list of all the attribute values
1287 <title>Description</title>
1289 Executes a query on <parameter>database</parameter>, which has
1290 a default value of <symbol>*default-database*</symbol>,
1291 specified by the SQL expressions supplied using the remaining
1292 arguments in <parameter>args</parameter>. The
1293 <function>select</function> function can be used to generate
1294 queries in both functional and object oriented contexts.
1297 In the functional case, the required arguments specify the
1298 columns selected by the query and may be symbolic SQL
1299 expressions or strings representing attribute
1300 identifiers. Type modified identifiers indicate that the
1301 values selected from the specified column are converted to the
1302 specified lisp type. The keyword arguments
1303 <parameter>all</parameter>, <parameter>distinct</parameter>,
1304 <parameter>from</parameter>, <parameter>group-by</parameter>,
1305 <parameter>having</parameter>,
1306 <parameter>order-by</parameter>,
1307 <parameter>set-operation</parameter> and
1308 <parameter>where</parameter> are used to specify, using the
1309 symbolic SQL syntax, the corresponding components of the SQL
1310 query generated by the call to
1311 <function>select</function>.
1314 <parameter>result-types</parameter> is a list of symbols which
1315 specifies the lisp type for each field returned by the
1316 query. If <parameter>result-types</parameter> is &nil; all
1317 results are returned as strings whereas the default value of
1318 <symbol>:auto</symbol> means that the lisp types are
1319 automatically computed for each
1320 field. <parameter>field-names</parameter> is &t; by default
1321 which means that the second value returned is a list of
1322 strings representing the columns selected by the query. If
1323 <parameter>field-names</parameter> is &nil;, the list of
1324 column names is not returned as a second value.
1327 In the object oriented case, the required arguments to
1328 <function>select</function> are symbols denoting View Classes
1329 which specify the database tables to query. In this case,
1330 <function>select</function> returns a list of View Class
1331 instances whose slots are set from the attribute values of the
1332 records in the specified table. <symbol>Slot-value</symbol> is
1333 a legal operator which can be employed as part of the symbolic
1334 SQL syntax used in the <parameter>where</parameter> keyword
1335 argument to <function>select</function>.
1336 <parameter>refresh</parameter> is &nil; by default which means
1337 that the View Class instances returned are retrieved from a
1338 cache if an equivalent call to <function>select</function> has
1339 previously been issued. If <parameter>refresh</parameter> is
1340 true, the View Class instances returned are updated as
1341 necessary from the database and the generic function
1342 <function>instance-refreshed</function> is called to perform
1343 any necessary operations on the updated instances.
1346 In both object oriented and functional contexts,
1347 <parameter>flatp</parameter> has a default value of &nil;
1348 which means that the results are returned as a list of
1349 lists. If <parameter>flatp</parameter> is t and only one
1350 result is returned for each record selected in the query, the
1351 results are returned as elements of a list.
1355 <title>Examples</title>
1357 (select [first-name] :from [employee] :flatp t :distinct t
1360 :order-by [first-name])
1361 => ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
1364 (select [first-name] [count [*]] :from [employee]
1366 :group-by [first-name]
1367 :order-by [first-name]
1369 => (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
1370 ("Mikhail" "1") ("Nikita" "1") ("Vladamir" "2") ("Yuri" "1"))
1372 (select [last-name] :from [employee]
1373 :where [like [email] "%org"]
1374 :order-by [last-name]
1378 => ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
1379 "Stalin" "Trotsky" "Yeltsin")
1381 (select [max [emplid]] :from [employee]
1384 :result-types :auto)
1387 (clsql:select [avg [height]] :from [employee] :flatp t :field-names nil)
1390 (select [emplid] [last-name] :from [employee] :where [= [emplid] 1])
1392 ("emplid" "last_name")
1394 (select [emplid :string] :from [employee]
1395 :where [= 1 [emplid]]
1400 (select [emplid] :from [employee] :order-by [emplid]
1401 :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]]
1406 (clsql:select [emplid] :from [employee]
1407 :where [in [emplid] '(1 2 3 4)]
1413 (select [first-name] [last-name] :from [employee]
1415 :order-by '(([first-name] :asc) ([last-name] :desc)))
1416 => (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
1417 ("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
1418 ("Nikita" "Kruschev") ("Vladamir" "Putin") ("Vladamir" "Lenin")
1419 ("Yuri" "Andropov"))
1421 (select [last-name] :from [employee]
1422 :set-operation [union [select [first-name] :from [employee]
1423 :order-by [last-name]]]
1427 => ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
1428 "Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
1429 "Trotsky" "Vladamir" "Yeltsin" "Yuri")
1433 <title>Side Effects</title>
1435 <para>Whatever effects the execution of the SQL query has on
1436 the underlying database, if any.</para>
1440 <title>Affected by</title>
1446 <title>Exceptional Situations</title>
1448 If the execution of the SQL query leads to any errors, an
1449 error of type <errortype>sql-database-error</errortype> is
1454 <title>See Also</title>
1456 <member><link linkend="query"><function>query</function></link></member>
1457 <member><link linkend="print-query"><function>print-query</function></link></member>
1458 <member><link linkend="do-query"><function>do-query</function></link></member>
1459 <member><link linkend="map-query"><function>map-query</function></link></member>
1460 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1461 <member><link linkend="instance-refreshed"><function>instance-refreshed</function></link></member>
1465 <title>Notes</title>
1467 The <parameter>field-names</parameter> and
1468 <parameter>result-types</parameter> keyword arguments are a
1472 <parameter>select</parameter> is common across the functional
1473 and object-oriented data manipulation languages.
1479 <!-- iteration and mapping -->
1481 <refentry id="do-query">
1483 <refentrytitle>DO-QUERY</refentrytitle>
1486 <refname><emphasis>Macro</emphasis> <emphasis role="bold">DO-QUERY</emphasis></refname>
1487 <refpurpose>Iterate over all the tuples of a query.</refpurpose>
1488 <refclass>Macro</refclass>
1491 <title>Syntax</title>
1493 <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>
1496 <title>Arguments and Values</title>
1499 <term><parameter>args</parameter></term>
1501 <para>A list of variable names.</para>
1505 <term><parameter>query-expression</parameter></term>
1507 <para>An <glossterm linkend="gloss-sql-expression">sql
1508 expression</glossterm> that represents an SQL
1509 query which is expected to return a (possibly empty)
1510 result set, where each tuple has as many attributes as
1511 <parameter>function</parameter> takes arguments.</para>
1515 <term><parameter>database</parameter></term>
1518 <glossterm linkend="gloss-database-object">database
1519 object</glossterm>. This will default to
1520 <symbol>*default-database*</symbol>.</para>
1524 <term><parameter>result-types</parameter></term>
1527 A <glossterm linkend="gloss-field-types">field type
1528 specifier</glossterm>. The default is &nil;. See <link
1529 linkend="query"><function>query</function></link> for
1530 the semantics of this argument.
1535 <term><parameter>body</parameter></term>
1537 <para>A body of Lisp code, like in a
1538 <function>destructuring-bind</function> form.</para>
1542 <term><parameter>result</parameter></term>
1544 <para>The result of executing <parameter>body</parameter>.</para>
1550 <title>Description</title>
1552 Repeatedly executes <parameter>body</parameter> within a
1553 binding of <parameter>args</parameter> on the fields of each
1554 row selected by the SQL query
1555 <parameter>query-expression</parameter>, which may be a string
1556 or a symbolic SQL expression, in
1557 <parameter>database</parameter> which defaults to
1558 <symbol>*default-database*</symbol>.
1561 The body of code is executed in a block named
1562 <symbol>nil</symbol> which may be returned from prematurely
1563 via <function>return</function> or
1564 <function>return-from</function>. In this case the result of
1565 evaluating the <function>do-query</function> form will be the
1566 one supplied to <function>return</function> or
1567 <function>return-from</function>. Otherwise the result will
1568 be <symbol>nil</symbol>.
1571 The body of code appears also is if wrapped in a
1572 <function>destructuring-bind</function> form, thus allowing
1573 declarations at the start of the body, especially those
1574 pertaining to the bindings of the variables named in
1575 <parameter>args</parameter>.
1578 <parameter>result-types</parameter> is a list of symbols which
1579 specifies the lisp type for each field returned by
1580 <parameter>query-expression</parameter>. If
1581 <parameter>result-types</parameter> is &nil; all results are
1582 returned as strings whereas the default value of
1583 <symbol>:auto</symbol> means that the lisp types are
1584 automatically computed for each field.
1587 <parameter>query-expression</parameter> may be an object query
1588 (i.e., the selection arguments refer to View Classes), in
1589 which case <parameter>args</parameter> are bound to the tuples
1590 of View Class instances returned by the object oriented query.
1594 <title>Examples</title>
1596 (do-query ((salary name) "select salary,name from simple")
1597 (format t "~30A gets $~2,5$~%" name (read-from-string salary)))
1598 >> Mai, Pierre gets $10000.00
1599 >> Hacker, Random J. gets $08000.50
1602 (do-query ((salary name) "select salary,name from simple")
1603 (return (cons salary name)))
1604 => ("10000.00" . "Mai, Pierre")
1607 (do-query ((name) [select [last-name] :from [employee]
1608 :order-by [last-name]])
1611 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1612 "Chernenko" "Brezhnev" "Andropov")
1615 (do-query ((e) [select 'employee :order-by [last-name]])
1616 (push (slot-value e 'last-name) result))
1618 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1619 "Chernenko" "Brezhnev" "Andropov")
1623 <title>Side Effects</title>
1624 <para>Whatever effects the execution of the SQL query has
1625 on the underlying database, if any.</para>
1628 <title>Affected by</title>
1632 <title>Exceptional Situations</title>
1633 <para>If the execution of the SQL query leads to any
1634 errors, an error of type
1635 <errortype>sql-database-error</errortype> is signalled.</para>
1636 <para>If the number of variable names in
1637 <parameter>args</parameter> and the number of attributes in
1638 the tuples in the result set don't match up, an error is
1642 <title>See Also</title>
1644 <member><link linkend="query"><function>query</function></link></member>
1645 <member><link linkend="map-query"><function>map-query</function></link></member>
1646 <member><link linkend="print-query"><function>print-query</function></link></member>
1647 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1648 <member><link linkend="select"><function>select</function></link></member>
1652 <title>Notes</title>
1653 <para>The <parameter>result-types</parameter> keyword argument
1654 is a &clsql; extension.</para>
1656 <parameter>do-query</parameter> is common across the functional
1657 and object-oriented data manipulation languages.
1662 <refentry id="for-each-row">
1664 <refentrytitle>FOR-EACH-ROW</refentrytitle>
1667 <refname><emphasis>Function</emphasis> <emphasis role="bold">FOR-EACH-ROW</emphasis></refname>
1668 <refpurpose><!-- purpose --></refpurpose>
1669 <refclass>Function</refclass>
1672 <title>Syntax</title>
1674 <function>for-each-row</function> &KEY FROM ORDER-BY WHERE DISTINCT LIMIT &REST FIELDS &body <replaceable>body</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
1677 <title>Arguments and Values</title>
1679 <!-- arguments and values -->
1683 <title>Description</title>
1685 <!-- description -->
1689 <title>Examples</title>
1695 <title>Side Effects</title>
1697 <!-- side effects -->
1701 <title>Affected by</title>
1704 <!-- affected by -->
1709 <title>Exceptional Situations</title>
1711 <!-- execeptional situations -->
1715 <title>See Also</title>
1717 <member><link linkend="query"><function>query</function></link></member>
1718 <member><link linkend="print-query"><function>print-query</function></link></member>
1719 <member><link linkend="do-query"><function>do-query</function></link></member>
1720 <member><link linkend="map-query"><function>map-query</function></link></member>
1721 <member><link linkend="loop-tuples"><function>loop</function></link></member>
1722 <member><link linkend="select"><function>select</function></link></member>
1726 <title>Notes</title>
1733 <refentry id="loop-tuples">
1735 <refentrytitle>LOOP</refentrytitle>
1738 <refname><emphasis>Additional clause</emphasis> for <emphasis role="bold">LOOP</emphasis></refname>
1739 <refpurpose>Iterate over all the tuples of a
1740 query via a loop clause.</refpurpose>
1741 <refclass>Loop Clause</refclass>
1744 <title>Compatibility</title>
1745 <caution><para><function>loop-for-as-tuples</function> only works with &cmucl;.</para></caution>
1748 <title>Syntax</title>
1749 <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>
1752 <title>Arguments and Values</title>
1755 <term><parameter>var</parameter></term>
1757 <para>A <literal>d-var-spec</literal>, as defined in the
1758 grammar for <function>loop</function>-clauses in the ANSI
1759 Standard for Common Lisp. This allows for the usual
1760 loop-style destructuring.</para>
1764 <term><parameter>type-spec</parameter></term>
1766 <para>An optional <literal>type-spec</literal> either
1767 simple or destructured, as defined in the grammar for
1768 <function>loop</function>-clauses in the ANSI Standard for
1773 <term><parameter>query</parameter></term>
1775 <para>An <glossterm linkend="gloss-sql-expression">sql
1776 expression</glossterm> that represents an SQL
1777 query which is expected to return a (possibly empty)
1778 result set, where each tuple has as many attributes as
1779 <parameter>function</parameter> takes arguments.</para>
1783 <term><parameter>database</parameter></term>
1786 <glossterm linkend="gloss-database-object">database
1787 object</glossterm>. This will default to the value
1788 of <symbol>*default-database*</symbol>.</para>
1794 <title>Description</title>
1795 <para>This clause is an iteration driver for
1796 <function>loop</function>, that binds the given variable
1797 (possibly destructured) to the consecutive tuples (which are
1798 represented as lists of attribute values) in the result set
1799 returned by executing the SQL <parameter>query</parameter>
1800 expression on the <parameter>database</parameter>
1803 <parameter>query</parameter> may be an object query (i.e., the
1804 selection arguments refer to View Classes), in which case the
1805 supplied variable is bound to the tuples of View Class
1806 instances returned by the object oriented query.
1810 <title>Examples</title>
1812 (defvar *my-db* (connect '("dent" "newesim" "dent" "dent"))
1815 (loop with time-graph = (make-hash-table :test #'equal)
1816 with event-graph = (make-hash-table :test #'equal)
1817 for (time event) being the tuples of "select time,event from log"
1820 (incf (gethash time time-graph 0))
1821 (incf (gethash event event-graph 0))
1823 (flet ((show-graph (k v) (format t "~40A => ~5D~%" k v)))
1824 (format t "~&Time-Graph:~%===========~%")
1825 (maphash #'show-graph time-graph)
1826 (format t "~&~%Event-Graph:~%============~%")
1827 (maphash #'show-graph event-graph))
1828 (return (values time-graph event-graph)))
1837 >> CLOS Benchmark entry. => 9000
1838 >> Demo Text... => 3
1839 >> doit-text => 3000
1840 >> C Benchmark entry. => 12000
1841 >> CLOS Benchmark entry => 32000
1842 => #<EQUAL hash table, 3 entries {48350A1D}>
1843 => #<EQUAL hash table, 5 entries {48350FCD}>
1845 (loop for (forename surname)
1847 [select [first-name] [last-name] :from [employee]
1848 :order-by [last-name]]
1849 collect (concatenate 'string forename " " surname))
1850 => ("Yuri Andropov" "Leonid Brezhnev" "Konstantin Chernenko" "Mikhail Gorbachev"
1851 "Nikita Kruschev" "Vladamir Lenin" "Vladamir Putin" "Josef Stalin"
1852 "Leon Trotsky" "Boris Yeltsin")
1854 (loop for (e) being the records in
1855 [select 'employee :where [< [emplid] 4] :order-by [emplid]]
1856 collect (slot-value e 'last-name))
1857 => ("Lenin" "Stalin" "Trotsky")
1861 <title>Side Effects</title>
1862 <para>Whatever effects the execution of the SQL query has
1863 on the underlying database, if any.</para>
1866 <title>Affected by</title>
1870 <title>Exceptional Situations</title>
1871 <para>If the execution of the SQL query leads to any
1872 errors, an error of type
1873 <errortype>sql-database-error</errortype> is signalled.</para>
1874 <para>Otherwise, any of the exceptional situations of
1875 <function>loop</function> applies.</para>
1878 <title>See Also</title>
1881 <member><link linkend="query"><function>query</function></link></member>
1882 <member><link linkend="map-query"><function>map-query</function></link></member>
1883 <member><link linkend="do-query"><function>do-query</function></link></member>
1884 <member><link linkend="print-query"><function>print-query</function></link></member>
1885 <member><link linkend="select"><function>select</function></link></member>
1890 <title>Notes</title>
1891 <para>The <parameter>database</parameter> loop keyword is a
1892 &clsql; extension.</para>
1894 The extended <function>loop</function> syntax is common across
1895 the functional and object-oriented data manipulation
1901 <refentry id="map-query">
1903 <refentrytitle>MAP-QUERY</refentrytitle>
1906 <refname><emphasis>Function</emphasis> <emphasis role="bold">MAP-QUERY</emphasis></refname>
1907 <refpurpose>Map a function over all the tuples from a
1909 <refclass>Function</refclass>
1912 <title>Syntax</title>
1913 <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>
1916 <title>Arguments and Values</title>
1919 <term><parameter>output-type-spec</parameter></term>
1921 <para>A sequence type specifier or <symbol>nil</symbol>.</para>
1925 <term><parameter>function</parameter></term>
1927 <para>A function designator.
1928 <parameter>function</parameter> takes a single argument which
1929 is the atom value for a query single with a single column
1930 or is a list of values for a multi-column query.</para>
1934 <term><parameter>query-expression</parameter></term>
1936 <para>An <glossterm linkend="gloss-sql-expression">sql
1937 expression</glossterm> that represents an SQL
1938 query which is expected to return a (possibly empty)
1943 <term><parameter>database</parameter></term>
1946 <glossterm linkend="gloss-database-object">database
1947 object</glossterm>. This will default to the value
1948 of <symbol>*default-database*</symbol>.</para>
1952 <term><parameter>result-types</parameter></term>
1955 A <glossterm linkend="gloss-field-types">field type
1956 specifier</glossterm>. The default is &nil;. See <link
1957 linkend="query"><function>query</function></link> for
1958 the semantics of this argument.
1963 <term><returnvalue>result</returnvalue></term>
1965 <para>If <parameter>output-type-spec</parameter> is a
1966 type specifier other than <symbol>nil</symbol>, then a
1967 sequence of the type it denotes. Otherwise
1968 <symbol>nil</symbol> is returned.</para>
1974 <title>Description</title>
1976 Applies <parameter>function</parameter> to the successive
1977 tuples in the result set returned by executing the SQL
1978 <parameter>query-expression</parameter>. If the
1979 <parameter>output-type-spec</parameter> is
1980 <symbol>nil</symbol>, then the result of each application of
1981 <parameter>function</parameter> is discarded, and
1982 <function>map-query</function> returns <symbol>nil</symbol>.
1983 Otherwise the result of each successive application of
1984 <parameter>function</parameter> is collected in a sequence of
1985 type <parameter>output-type-spec</parameter>, where the jths
1986 element is the result of applying
1987 <parameter>function</parameter> to the attributes of the jths
1988 tuple in the result set. The collected sequence is the result
1989 of the call to <function>map-query</function>.
1992 If the <parameter>output-type-spec</parameter> is a subtype of
1993 <type>list</type>, the result will be a <type>list</type>.
1996 If the <parameter>result-type</parameter> is a subtype of
1997 <type>vector</type>, then if the implementation can determine
1998 the element type specified for the
1999 <parameter>result-type</parameter>, the element type of the
2000 resulting array is the result of
2001 <emphasis>upgrading</emphasis> that element type; or, if the
2002 implementation can determine that the element type is
2003 unspecified (or <symbol>*</symbol>), the element type of the
2004 resulting array is <type>t</type>; otherwise, an error is
2008 If <parameter>result-types</parameter> is &nil; all results
2009 are returned as strings whereas the default value of
2010 <symbol>:auto</symbol> means that the lisp types are
2011 automatically computed for each field.</para>
2013 <parameter>query-expression</parameter> may be an object query
2014 (i.e., the selection arguments refer to View Classes), in
2015 which case the supplied function is applied to the tuples of
2016 View Class instances returned by the object oriented query.
2020 <title>Examples</title>
2022 (map-query 'list #'(lambda (tuple)
2023 (multiple-value-bind (salary name) tuple
2024 (declare (ignorable name))
2025 (read-from-string salary)))
2026 "select salary,name from simple where salary > 8000")
2029 (map-query '(vector double-float)
2031 (multiple-value-bind (salary name) tuple
2032 (declare (ignorable name))
2033 (let ((*read-default-float-format* 'double-float))
2034 (coerce (read-from-string salary) 'double-float))
2035 "select salary,name from simple where salary > 8000")))
2036 => #(10000.0d0 8000.5d0)
2038 => (SIMPLE-ARRAY DOUBLE-FLOAT (2))
2041 (values (map-query nil #'(lambda (tuple)
2042 (multiple-value-bind (salary name) tuple
2043 (push (cons name (read-from-string salary)) list))
2044 "select salary,name from simple where salary > 8000"))
2047 => (("Hacker, Random J." . 8000.5) ("Mai, Pierre" . 10000.0))
2049 (map-query 'vector #'identity
2050 [select [last-name] :from [employee] :flatp t
2051 :order-by [last-name]])
2052 => #("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
2053 "Stalin" "Trotsky" "Yeltsin")
2055 (map-query 'list #'identity
2056 [select [first-name] [last-name] :from [employee]
2057 :order-by [last-name]])
2058 => (("Yuri" "Andropov") ("Leonid" "Brezhnev") ("Konstantin" "Chernenko")
2059 ("Mikhail" "Gorbachev") ("Nikita" "Kruschev") ("Vladamir" "Lenin")
2060 ("Vladamir" "Putin") ("Josef" "Stalin") ("Leon" "Trotsky")
2061 ("Boris" "Yeltsin"))
2063 (map-query 'list #'last-name [select 'employee :order-by [emplid]])
2064 => ("Lenin" "Stalin" "Trotsky" "Kruschev" "Brezhnev" "Andropov" "Chernenko"
2065 "Gorbachev" "Yeltsin" "Putin")
2069 <title>Side Effects</title>
2070 <para>Whatever effects the execution of the SQL query has
2071 on the underlying database, if any.</para>
2074 <title>Affected by</title>
2078 <title>Exceptional Situations</title>
2079 <para>If the execution of the SQL query leads to any
2080 errors, an error of type
2081 <errortype>sql-database-error</errortype> is signalled.</para>
2082 <para>An error of type <errortype>type-error</errortype> must
2083 be signaled if the <parameter>output-type-spec</parameter> is
2084 not a recognizable subtype of <type>list</type>, not a
2085 recognizable subtype of <type>vector</type>, and not
2086 <symbol>nil</symbol>.</para>
2087 <para>An error of type <errortype>type-error</errortype>
2088 should be signaled if
2089 <parameter>output-type-spec</parameter> specifies the number
2090 of elements and the size of the result set is different from
2094 <title>See Also</title>
2096 <member><link linkend="query"><function>query</function></link></member>
2097 <member><link linkend="do-query"><function>do-query</function></link></member>
2098 <member><link linkend="print-query"><function>print-query</function></link></member>
2099 <member><link linkend="loop-tuples"><function>loop</function></link></member>
2100 <member><link linkend="select"><function>select</function></link></member>
2104 <title>Notes</title>
2105 <para>The <parameter>result-types</parameter> keyword argument
2106 is a &clsql; extension.</para>
2108 <parameter>map-query</parameter> is common across the
2109 functional and object-oriented data manipulation languages.
2115 <!-- prepared statements -->
2117 <refentry id="prepare-sql">
2119 <refentrytitle>PREPARE-SQL</refentrytitle>
2122 <refname><emphasis>Function</emphasis> <emphasis role="bold">PREPARE-SQL</emphasis></refname>
2123 <refpurpose>Create a prepared statement.</refpurpose>
2124 <refclass>Function</refclass>
2127 <title>Syntax</title>
2129 <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>
2132 <title>Arguments and Values</title>
2134 <!-- arguments and values -->
2138 <title>Description</title>
2139 <para>Prepares a SQL statement <parameter>sql-stmt</parameter>
2140 for execution. <parameter>types</parameter> contains a list of
2141 types corresponding to the input parameters. Returns a
2142 prepared-statement object.
2153 <title>Examples</title>
2159 <title>Side Effects</title>
2161 <!-- side effects -->
2165 <title>Affected by</title>
2168 <!-- affected by -->
2173 <title>Exceptional Situations</title>
2175 <!-- execeptional situations -->
2179 <title>See Also</title>
2187 <title>Notes</title>
2194 <refentry id="run-prepared-sql">
2196 <refentrytitle>RUN-PREPARED-SQL</refentrytitle>
2199 <refname><emphasis>Function</emphasis> <emphasis role="bold">RUN-PREPARED-SQL</emphasis></refname>
2200 <refpurpose>Execute a prepared statement.</refpurpose>
2201 <refclass>Function</refclass>
2204 <title>Syntax</title>
2206 <function>run-prepared-sql</function> <replaceable>prepared-stmt</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
2209 <title>Arguments and Values</title>
2211 <!-- arguments and values -->
2215 <title>Description</title>
2216 <para>Execute the prepared sql statment. All input
2217 parameters must be bound.
2221 <title>Examples</title>
2227 <title>Side Effects</title>
2229 <!-- side effects -->
2233 <title>Affected by</title>
2236 <!-- affected by -->
2241 <title>Exceptional Situations</title>
2243 <!-- execeptional situations -->
2247 <title>See Also</title>
2255 <title>Notes</title>
2262 <refentry id="free-prepared-sql">
2264 <refentrytitle>FREE-PREPARED-SQL</refentrytitle>
2267 <refname><emphasis>Function</emphasis> <emphasis role="bold">FREE-PREPARED-SQL</emphasis></refname>
2268 <refpurpose>Delete a prepared statement object.</refpurpose>
2269 <refclass>Function</refclass>
2272 <title>Syntax</title>
2274 <function>free-prepared-sql</function> <replaceable>prepared-stmt</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
2277 <title>Arguments and Values</title>
2279 <!-- arguments and values -->
2283 <title>Description</title>
2284 <para>Delete the objects associated with a prepared
2289 <title>Examples</title>
2295 <title>Side Effects</title>
2297 <!-- side effects -->
2301 <title>Affected by</title>
2304 <!-- affected by -->
2309 <title>Exceptional Situations</title>
2311 <!-- execeptional situations -->
2315 <title>See Also</title>
2323 <title>Notes</title>
2330 <refentry id="bind-parameter">
2332 <refentrytitle>BIND-PARAMETER</refentrytitle>
2335 <refname><emphasis>Function</emphasis> <emphasis role="bold">BIND-PARAMETER</emphasis></refname>
2336 <refpurpose>Bind a parameter in a prepared statement.</refpurpose>
2337 <refclass>Function</refclass>
2340 <title>Syntax</title>
2342 <function>bind-parameter</function> <replaceable>prepared-stmt</replaceable> <replaceable>position</replaceable> <replaceable>value</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
2345 <title>Arguments and Values</title>
2347 <!-- arguments and values -->
2351 <title>Description</title>
2352 <para>Sets the value of a parameter in a prepared statement.
2356 <title>Examples</title>
2362 <title>Side Effects</title>
2364 <!-- side effects -->
2368 <title>Affected by</title>
2371 <!-- affected by -->
2376 <title>Exceptional Situations</title>
2378 <!-- execeptional situations -->
2382 <title>See Also</title>
2390 <title>Notes</title>