doc/ref-fdml.xml

   1 <?xml version='1.0' ?>
   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">
   5 %myents;
   6 ]>
   7
   8 <!-- Functional Data Manipulation Language -->
   9 <reference id="ref-fdml">
  10   <title>Functional Data Manipulation Language (FDML)</title>
  11   <partintro>
  12     <para>
  13       <!-- introduction -->
  14     </para>
  15   </partintro>
  16
  17
  18   <!-- Caching table queries -->
  19
  20   <refentry id="cache-table-queries-default">
  21     <refmeta>
  22       <refentrytitle>*CACHE-TABLE-QUERIES-DEFAULT*</refentrytitle>
  23     </refmeta>
  24     <refnamediv>
  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>
  29     </refnamediv>
  30     <refsect1>
  31       <title>Value Type</title>
  32       <para>
  33         A valid argument to the <parameter>action</parameter>
  34         parameter of <function>cache-table-queries</function>,
  35         i.e. one of
  36         <simplelist type="inline">
  37         <member>&t;</member>
  38         <member>&nil;</member>
  39         <member><symbol>:flush</symbol></member>
  40         </simplelist>.
  41       </para>
  42     </refsect1>
  43     <refsect1>
  44       <title>Initial Value</title>
  45       <para><symbol>nil</symbol></para>
  46     </refsect1>
  47     <refsect1>
  48       <title>Description</title>
  49       <para>
  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>.
  55       </para>
  56     </refsect1>
  57     <refsect1>
  58       <title>Examples</title>
  59       <para>None.</para>
  60     </refsect1>
  61     <refsect1>
  62       <title>Affected By</title>
  63       <para>None.</para>
  64     </refsect1>
  65     <refsect1>
  66       <title>See Also</title>
  67       <simplelist>
  68         <member><link linkend="cache-table-queries"><function>cache-table-queries</function></link></member>
  69       </simplelist>
  70     </refsect1>
  71     <refsect1>
  72       <title>Notes</title>
  73       <para>None.</para>
  74     </refsect1>
  75   </refentry>
  76
  77   <refentry id="cache-table-queries">
  78     <refmeta>
  79       <refentrytitle>CACHE-TABLE-QUERIES</refentrytitle>
  80     </refmeta>
  81     <refnamediv>
  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>
  85     </refnamediv>
  86     <refsect1>
  87       <title>Syntax</title>
  88       <synopsis>
  89       <function>cache-table-queries</function> <replaceable>table</replaceable> &amp;key <replaceable>action</replaceable> <replaceable>database</replaceable>) => <returnvalue></returnvalue></synopsis>
  90     </refsect1>
  91     <refsect1>
  92       <title>Arguments and Values</title>
  93       <variablelist>
  94         <varlistentry>
  95           <term><parameter>table</parameter></term>
  96           <listitem>
  97             <para>
  98               A string representing a database table, &t; or
  99               <symbol>:default</symbol>.
 100             </para>
 101           </listitem>
 102         </varlistentry>
 103         <varlistentry>
 104           <term><parameter>action</parameter></term>
 105           <listitem>
 106             <para>
 107               &t;, &nil; or <symbol>:flush</symbol>.
 108             </para>
 109           </listitem>
 110         </varlistentry>
 111         <varlistentry>
 112           <term><parameter>database</parameter></term>
 113           <listitem>
 114             <para>A
 115             <glossterm linkend="gloss-database-object">database
 116             object</glossterm>. This will default to the value
 117             of <symbol>*default-database*</symbol>.</para>
 118           </listitem>
 119       </varlistentry>
 120       </variablelist>
 121     </refsect1>
 122     <refsect1>
 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.
 142       </para>
 143     </refsect1>
 144     <refsect1>
 145       <title>Examples</title>
 146       <screen>
 147 (setf *cache-table-queries-default* t)
 148 => T
 149 (create-table [foo]
 150               '(([id] integer)
 151                 ([height] float)
 152                 ([name] (string 24))
 153                 ([comments] varchar)))
 154 =>
 155 (cache-table-queries "foo")
 156 =>
 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))
 160 (drop-table "foo")
 161 =>
 162 (create-table [foo]
 163               '(([id] integer)
 164                 ([height] float)
 165                 ([name] (string 36))
 166                 ([comments] (string 100))))
 167 =>
 168 (cache-table-queries "foo" :action :flush)
 169 =>
 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))
 173       </screen>
 174     </refsect1>
 175     <refsect1>
 176       <title>Side Effects</title>
 177       <para>
 178         The internal attribute cache for
 179         <parameter>database</parameter> is modified.
 180       </para>
 181     </refsect1>
 182     <refsect1>
 183       <title>Affected by</title>
 184       <simplelist>
 185         <member><link linkend="cache-table-queries-default"><symbol>*cache-table-queries-default*</symbol></link></member>
 186       </simplelist>
 187     </refsect1>
 188     <refsect1>
 189       <title>Exceptional Situations</title>
 190       <para>
 191         None.
 192       </para>
 193     </refsect1>
 194     <refsect1>
 195       <title>See Also</title>
 196       <simplelist>
 197         <member><link linkend="cache-table-queries-default"><symbol>*cache-table-queries-default*</symbol></link></member>
 198       </simplelist>
 199     </refsect1>
 200     <refsect1>
 201       <title>Notes</title>
 202       <para>
 203         None.
 204       </para>
 205     </refsect1>
 206   </refentry>
 207
 208
 209   <!-- insert/update/delete records -->
 210
 211   <refentry id="insert-records">
 212     <refmeta>
 213       <refentrytitle>INSERT-RECORDS</refentrytitle>
 214     </refmeta>
 215     <refnamediv>
 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>
 219     </refnamediv>
 220     <refsect1>
 221       <title>Syntax</title>
 222       <synopsis>
 223       <function>insert-records</function> &amp;key <replaceable>into</replaceable> <replaceable>attributes</replaceable> <replaceable>values</replaceable> <replaceable>av-pairs</replaceable> <replaceable>query</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
 224     </refsect1>
 225     <refsect1>
 226       <title>Arguments and Values</title>
 227       <variablelist>
 228         <varlistentry>
 229           <term><parameter>into</parameter></term>
 230           <listitem>
 231             <para>
 232               A string, symbol or symbolic SQL expression representing
 233               the name of a table existing in
 234               <parameter>database</parameter>.
 235             </para>
 236           </listitem>
 237         </varlistentry>
 238         <varlistentry>
 239           <term><parameter>attributes</parameter></term>
 240           <listitem>
 241             <para>
 242               A list of attribute identifiers or &nil;.
 243             </para>
 244           </listitem>
 245         </varlistentry>
 246         <varlistentry>
 247           <term><parameter>values</parameter></term>
 248           <listitem>
 249             <para>
 250               A list of attribute values or &nil;.
 251             </para>
 252           </listitem>
 253         </varlistentry>
 254         <varlistentry>
 255           <term><parameter>av-pairs</parameter></term>
 256           <listitem>
 257             <para>
 258               A list of attribute identifier/value pairs or &nil;.
 259             </para>
 260           </listitem>
 261         </varlistentry>
 262         <varlistentry>
 263           <term><parameter>query</parameter></term>
 264           <listitem>
 265             <para>
 266               A query expression or &nil;.
 267             </para>
 268           </listitem>
 269         </varlistentry>
 270         <varlistentry>
 271           <term><parameter>database</parameter></term>
 272           <listitem>
 273             <para>A
 274             <glossterm linkend="gloss-database-object">database
 275             object</glossterm>. This will default to the value
 276             of <symbol>*default-database*</symbol>.</para>
 277           </listitem>
 278       </varlistentry>
 279       </variablelist>
 280     </refsect1>
 281     <refsect1>
 282       <title>Description</title>
 283       <para>
 284         Inserts records into the table specified by
 285         <parameter>into</parameter> in <parameter>database</parameter>
 286         which defaults to <symbol>*default-database*</symbol>.
 287       </para>
 288       <para>
 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.
 315       </para>
 316     </refsect1>
 317     <refsect1>
 318       <title>Examples</title>
 319       <screen>
 320 (select [first-name] [last-name] [email]
 321         :from [employee]
 322         :where [= [emplid] 11]
 323         :field-names nil)
 324 => NIL
 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"
 329                           1 1))
 330 =>
 331 (select [first-name] [last-name] [email]
 332         :from [employee]
 333         :where [= [emplid] 11]
 334         :field-names nil)
 335 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
 336       </screen>
 337     </refsect1>
 338     <refsect1>
 339       <title>Side Effects</title>
 340       <para>
 341         Modifications are made to the underlying database.
 342       </para>
 343     </refsect1>
 344     <refsect1>
 345       <title>Affected by</title>
 346       <para>
 347         None.
 348       </para>
 349     </refsect1>
 350     <refsect1>
 351       <title>Exceptional Situations</title>
 352       <para>
 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.
 357       </para>
 358     </refsect1>
 359     <refsect1>
 360       <title>See Also</title>
 361       <simplelist>
 362         <member><link linkend="update-records"><function>update-records</function></link></member>
 363         <member><link linkend="delete-records"><function>delete-records</function></link></member>
 364       </simplelist>
 365     </refsect1>
 366     <refsect1>
 367       <title>Notes</title>
 368       <para>
 369         None.
 370       </para>
 371     </refsect1>
 372   </refentry>
 373
 374   <refentry id="update-records">
 375     <refmeta>
 376       <refentrytitle>UPDATE-RECORDS</refentrytitle>
 377     </refmeta>
 378     <refnamediv>
 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>
 382     </refnamediv>
 383     <refsect1>
 384       <title>Syntax</title>
 385       <synopsis>
 386       <function>update-records</function> <replaceable>table</replaceable> &amp;key  <replaceable>attributes</replaceable> <replaceable>values</replaceable> <replaceable>av-pairs</replaceable> <replaceable>where</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
 387     </refsect1>
 388     <refsect1>
 389       <title>Arguments and Values</title>
 390       <variablelist>
 391         <varlistentry>
 392           <term><parameter>table</parameter></term>
 393           <listitem>
 394             <para>
 395               A string, symbol or symbolic SQL expression representing
 396               the name of a table existing in
 397               <parameter>database</parameter>.
 398             </para>
 399           </listitem>
 400         </varlistentry>
 401         <varlistentry>
 402           <term><parameter>attributes</parameter></term>
 403           <listitem>
 404             <para>
 405               A list of attribute identifiers or &nil;.
 406             </para>
 407           </listitem>
 408         </varlistentry>
 409         <varlistentry>
 410           <term><parameter>values</parameter></term>
 411           <listitem>
 412             <para>
 413               A list of attribute values or &nil;.
 414             </para>
 415           </listitem>
 416         </varlistentry>
 417         <varlistentry>
 418           <term><parameter>av-pairs</parameter></term>
 419           <listitem>
 420             <para>
 421               A list of attribute identifier/value pairs or &nil;.
 422             </para>
 423           </listitem>
 424         </varlistentry>
 425         <varlistentry>
 426           <term><parameter>where</parameter></term>
 427           <listitem>
 428             <para>
 429               A symbolic SQL expression.
 430             </para>
 431           </listitem>
 432         </varlistentry>
 433         <varlistentry>
 434           <term><parameter>database</parameter></term>
 435           <listitem>
 436             <para>A
 437             <glossterm linkend="gloss-database-object">database
 438             object</glossterm>. This will default to the value
 439             of <symbol>*default-database*</symbol>.</para>
 440           </listitem>
 441       </varlistentry>
 442       </variablelist>
 443     </refsect1>
 444     <refsect1>
 445       <title>Description</title>
 446       <para>
 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>.
 452       </para>
 453       <para>
 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
 467         value) pairs.
 468       </para>
 469     </refsect1>
 470     <refsect1>
 471       <title>Examples</title>
 472       <screen>
 473 (select [first-name] [last-name] [email]
 474         :from [employee]
 475         :where [= [emplid] 1]
 476         :field-names nil)
 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])
 483 =>
 484 (select [first-name] [last-name] [email]
 485         :from [employee]
 486         :where [= [emplid] 1]
 487         :field-names nil)
 488 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
 489       </screen>
 490     </refsect1>
 491     <refsect1>
 492       <title>Side Effects</title>
 493       <para>
 494         Modifications are made to the underlying database.
 495       </para>
 496     </refsect1>
 497     <refsect1>
 498       <title>Affected by</title>
 499       <para>
 500         None.
 501       </para>
 502     </refsect1>
 503     <refsect1>
 504       <title>Exceptional Situations</title>
 505       <para>
 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.
 512       </para>
 513       <para>If the execution of the SQL query leads to any errors, an
 514       error of type <errortype>sql-database-error</errortype> is
 515       signalled.</para>
 516     </refsect1>
 517     <refsect1>
 518       <title>See Also</title>
 519       <para>
 520         <simplelist>
 521           <member><link linkend="insert-records"><function>insert-records</function></link></member>
 522           <member><link linkend="delete-records"><function>delete-records</function></link></member>
 523         </simplelist>
 524       </para>
 525     </refsect1>
 526     <refsect1>
 527       <title>Notes</title>
 528       <para>
 529         None.
 530       </para>
 531     </refsect1>
 532   </refentry>
 533
 534   <refentry id="delete-records">
 535     <refmeta>
 536       <refentrytitle>DELETE-RECORDS</refentrytitle>
 537     </refmeta>
 538     <refnamediv>
 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>
 542     </refnamediv>
 543     <refsect1>
 544       <title>Syntax</title>
 545       <synopsis>
 546       <function>delete-records</function> &amp;key <replaceable>from</replaceable> <replaceable>where</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
 547     </refsect1>
 548     <refsect1>
 549       <title>Arguments and Values</title>
 550       <variablelist>
 551         <varlistentry>
 552           <term><parameter>from</parameter></term>
 553           <listitem>
 554             <para>
 555               A string, symbol or symbolic SQL expression representing
 556               the name of a table existing in
 557               <parameter>database</parameter>.
 558             </para>
 559           </listitem>
 560         </varlistentry>
 561                 <varlistentry>
 562           <term><parameter>where</parameter></term>
 563           <listitem>
 564             <para>
 565               A symbolic SQL expression.
 566             </para>
 567           </listitem>
 568         </varlistentry>
 569         <varlistentry>
 570           <term><parameter>database</parameter></term>
 571           <listitem>
 572             <para>A
 573             <glossterm linkend="gloss-database-object">database
 574             object</glossterm>. This will default to the value
 575             of <symbol>*default-database*</symbol>.</para>
 576           </listitem>
 577         </varlistentry>
 578       </variablelist>
 579     </refsect1>
 580     <refsect1>
 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>.
 587       </para>
 588     </refsect1>
 589     <refsect1>
 590       <title>Examples</title>
 591       <screen>
 592 (select [first-name] [last-name] [email]
 593         :from [employee]
 594         :where [= [emplid] 11]
 595         :field-names nil)
 596 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
 597 (delete-records :from [employee] :where [= [emplid] 11])
 598 =>
 599 (select [first-name] [last-name] [email]
 600         :from [employee]
 601         :where [= [emplid] 11]
 602         :field-names nil)
 603 => NIL
 604       </screen>
 605     </refsect1>
 606     <refsect1>
 607       <title>Side Effects</title>
 608       <para>
 609         Modifications are made to the underlying database.
 610       </para>
 611     </refsect1>
 612     <refsect1>
 613       <title>Affected by</title>
 614       <para>
 615         None.
 616       </para>
 617     </refsect1>
 618     <refsect1>
 619       <title>Exceptional Situations</title>
 620       <para>
 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.
 626       </para>
 627     </refsect1>
 628     <refsect1>
 629       <title>See Also</title>
 630       <para>
 631         <simplelist>
 632           <member><link linkend="insert-records"><function>insert-records</function></link></member>
 633           <member><link linkend="update-records"><function>update-records</function></link></member>
 634         </simplelist>
 635       </para>
 636     </refsect1>
 637     <refsect1>
 638       <title>Notes</title>
 639       <para>
 640         None.
 641       </para>
 642     </refsect1>
 643   </refentry>
 644
 645
 646   <!-- executing SQL commands and queries -->
 647
 648   <refentry id="execute-command">
 649     <refmeta>
 650       <refentrytitle>EXECUTE-COMMAND</refentrytitle>
 651     </refmeta>
 652     <refnamediv>
 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>
 656     </refnamediv>
 657     <refsect1>
 658       <title>Syntax</title>
 659       <synopsis>
 660       <function>execute-command</function> <replaceable>sql-expression</replaceable> &amp;key <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
 661     </refsect1>
 662     <refsect1>
 663       <title>Arguments and Values</title>
 664       <variablelist>
 665         <varlistentry>
 666           <term><parameter>sql-expression</parameter></term>
 667           <listitem>
 668             <para>An <glossterm linkend="gloss-sql-expression">sql
 669             expression</glossterm> that represents an SQL
 670             statement which will return no values.</para>
 671           </listitem>
 672         </varlistentry>
 673         <varlistentry>
 674           <term><parameter>database</parameter></term>
 675           <listitem>
 676             <para>A
 677             <glossterm linkend="gloss-database-object">database
 678             object</glossterm>. This will default to the value
 679             of <symbol>*default-database*</symbol>.</para>
 680           </listitem>
 681       </varlistentry></variablelist>
 682     </refsect1>
 683     <refsect1>
 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>.
 690       </para>
 691     </refsect1>
 692     <refsect1>
 693       <title>Examples</title>
 694       <screen>
 695         (execute-command "create table eventlog (time char(30),event char(70))")
 696         =>
 697
 698         (execute-command "create table eventlog (time char(30),event char(70))")
 699         >>
 700         >> While accessing database #&lt;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
 703         >>   has occurred.
 704         >>
 705         >> Restarts:
 706         >>   0: [ABORT] Return to Top-Level.
 707         >>
 708         >> Debug  (type H for help)
 709         >>
 710         >> (CLSQL-POSTGRESQL::|(PCL::FAST-METHOD DATABASE-EXECUTE-COMMAND (T POSTGRESQL-DATABASE))|
 711         >>  #&lt;unused-arg>
 712         >>  #&lt;unused-arg>
 713         >>  #&lt;unavailable-arg>
 714         >>  #&lt;unavailable-arg>)
 715         >> Source: (ERROR 'SQL-DATABASE-ERROR :DATABASE DATABASE :EXPRESSION ...)
 716         >> 0] 0
 717
 718         (execute-command "drop table eventlog")
 719         =>
 720       </screen>
 721     </refsect1>
 722     <refsect1>
 723       <title>Side Effects</title>
 724       <para>Whatever effects the execution of the SQL statement has
 725       on the underlying database, if any.</para>
 726     </refsect1>
 727     <refsect1>
 728       <title>Affected by</title>
 729       <para>None.</para>
 730     </refsect1>
 731     <refsect1>
 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
 735       signalled.</para>
 736     </refsect1>
 737     <refsect1>
 738       <title>See Also</title>
 739       <para>
 740         <simplelist>
 741           <member><link linkend="query"><function>query</function></link></member>
 742         </simplelist>
 743       </para>
 744     </refsect1>
 745     <refsect1>
 746       <title>Notes</title>
 747       <para>None.</para>
 748     </refsect1>
 749   </refentry>
 750
 751
 752   <refentry id="query">
 753     <refmeta>
 754       <refentrytitle>QUERY</refentrytitle>
 755     </refmeta>
 756     <refnamediv>
 757       <refname><emphasis>Generic Function</emphasis> <emphasis role="bold">QUERY</emphasis></refname>
 758       <refpurpose>Execute an SQL query and return the tuples as a
 759       list.</refpurpose>
 760       <refclass>Generic Function</refclass>
 761     </refnamediv>
 762     <refsect1>
 763       <title>Syntax</title>
 764       <synopsis>
 765       <function>query</function> <replaceable>query-expression</replaceable> &amp;key <replaceable>database</replaceable> <replaceable>result-types</replaceable> <replaceable>flatp</replaceable> <replaceable>field-names</replaceable> => <returnvalue>result</returnvalue></synopsis>
 766     </refsect1>
 767     <refsect1>
 768       <title>Arguments and Values</title>
 769       <variablelist>
 770         <varlistentry>
 771           <term><parameter>query-expression</parameter></term>
 772           <listitem>
 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)
 776             result set.</para>
 777           </listitem>
 778         </varlistentry>
 779         <varlistentry>
 780           <term><parameter>database</parameter></term>
 781           <listitem>
 782             <para>A
 783             <glossterm linkend="gloss-database-object">database
 784             object</glossterm>. This will default to the value
 785             of <symbol>*default-database*</symbol>.</para>
 786           </listitem>
 787         </varlistentry>
 788         <varlistentry>
 789           <term><parameter>flatp</parameter></term>
 790           <listitem>
 791             <para>A Boolean whose default value is &nil;.</para>
 792           </listitem>
 793         </varlistentry>
 794         <varlistentry>
 795           <term><parameter>result-types</parameter></term>
 796           <listitem>
 797             <para>A
 798             <glossterm linkend="gloss-field-types">field type
 799             specifier</glossterm>. The default is &nil;.
 800             </para>
 801             <para>
 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
 807               objects.
 808             </para>
 809             <para>
 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
 820                 upon the field type.
 821                 </member>
 822                 <member><symbol>:double</symbol> Field is imported as a
 823                 double-float number.
 824                 </member>
 825                 <member><symbol>t</symbol> Field is imported as a
 826                 string.
 827                 </member>
 828               </simplelist>
 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.
 833             </para>
 834           </listitem>
 835         </varlistentry>
 836         <varlistentry>
 837           <term><parameter>field-names</parameter></term>
 838           <listitem>
 839           <para>
 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 -
 843             the list of rows.
 844           </para>
 845           </listitem>
 846         </varlistentry>
 847         <varlistentry>
 848           <term><returnvalue>result</returnvalue></term>
 849           <listitem>
 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>
 854           </listitem>
 855         </varlistentry>
 856       </variablelist>
 857     </refsect1>
 858     <refsect1>
 859       <title>Description</title>
 860       <para>
 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>.
 868       </para>
 869       <para>
 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.
 874       </para>
 875       <para>
 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.
 882       </para>
 883       <para>
 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.
 889       </para>
 890     </refsect1>
 891     <refsect1>
 892       <title>Examples</title>
 893       <screen>
 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")
 897
 898 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
 899        :field-names nil)
 900 => ((1 "Vladamir" "Lenin" 1.5564661d0))
 901
 902 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
 903        :field-names nil
 904        :result-types nil)
 905 => (("1" "Vladamir" "Lenin" "1.5564661"))
 906
 907 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
 908        :field-names nil
 909        :result-types '(:int t t :double))
 910 => ((1 "Vladamir" "Lenin" 1.5564661))
 911
 912 (query "select last_name from employee where emplid > 5" :flatp t)
 913 => ("Andropov" "Chernenko" "Gorbachev" "Yeltsin" "Putin"),
 914    ("last_name")
 915
 916 (query "select last_name from employee where emplid > 10"
 917        :flatp t
 918        :field-names nil)
 919 => NIL
 920       </screen>
 921     </refsect1>
 922     <refsect1>
 923       <title>Side Effects</title>
 924       <para>Whatever effects the execution of the SQL query has
 925       on the underlying database, if any.</para>
 926     </refsect1>
 927     <refsect1>
 928       <title>Affected by</title>
 929       <para>None.</para>
 930     </refsect1>
 931     <refsect1>
 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
 935       signalled.</para>
 936     </refsect1>
 937     <refsect1>
 938       <title>See Also</title>
 939       <simplelist>
 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>
 946       </simplelist>
 947     </refsect1>
 948     <refsect1>
 949       <title>Notes</title>
 950       <para>The <parameter>field-names</parameter> and
 951       <parameter>result-types</parameter> keyword arguments are a
 952       &clsql; extension.</para>
 953     </refsect1>
 954   </refentry>
 955
 956   <refentry id="print-query">
 957     <refmeta>
 958       <refentrytitle>PRINT-QUERY</refentrytitle>
 959     </refmeta>
 960     <refnamediv>
 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>
 964     </refnamediv>
 965     <refsect1>
 966       <title>Syntax</title>
 967       <synopsis>
 968       <function>print-query</function> <replaceable>query-expression</replaceable> &amp;key <replaceable>titles</replaceable> <replaceable>formats</replaceable> <replaceable>sizes</replaceable> <replaceable>stream</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
 969     </refsect1>
 970     <refsect1>
 971       <title>Arguments and Values</title>
 972       <variablelist>
 973         <varlistentry>
 974           <term><parameter>query-expression</parameter></term>
 975           <listitem>
 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)
 979             result set.</para>
 980           </listitem>
 981         </varlistentry>
 982         <varlistentry>
 983           <term><parameter>database</parameter></term>
 984           <listitem>
 985             <para>A
 986             <glossterm linkend="gloss-database-object">database
 987             object</glossterm>. This will default to the value
 988             of <symbol>*default-database*</symbol>.</para>
 989           </listitem>
 990         </varlistentry>
 991         <varlistentry>
 992           <term><parameter>titles</parameter></term>
 993           <listitem>
 994             <para>
 995               A list of strings or &nil; which is the default value.
 996             </para>
 997           </listitem>
 998         </varlistentry>
 999         <varlistentry>
1000           <term><parameter>formats</parameter></term>
1001           <listitem>
1002             <para>
1003               A list of strings, &nil; or &t; which is the default value.
1004             </para>
1005           </listitem>
1006         </varlistentry>
1007         <varlistentry>
1008           <term><parameter>sizes</parameter></term>
1009           <listitem>
1010             <para>
1011               A list of numbers, &nil; or &t; which is the default value.
1012             </para>
1013           </listitem>
1014         </varlistentry>
1015         <varlistentry>
1016           <term><parameter>stream</parameter></term>
1017           <listitem>
1018             <para>
1019               An output stream or &t; which is the default value.
1020             </para>
1021           </listitem>
1022         </varlistentry>
1023       </variablelist>
1024     </refsect1>
1025     <refsect1>
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.
1046       </para>
1047     </refsect1>
1048     <refsect1>
1049       <title>Examples</title>
1050       <screen>
1051 (print-query [select [emplid] [first-name] [last-name] [email]
1052                      :from [employee]
1053                      :where [&lt; [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
1060 =>
1061
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
1071 =>
1072 </screen>
1073     </refsect1>
1074     <refsect1>
1075       <title>Side Effects</title>
1076       <para>
1077         None.
1078       </para>
1079     </refsect1>
1080     <refsect1>
1081       <title>Affected by</title>
1082       <para>
1083         None.
1084       </para>
1085     </refsect1>
1086     <refsect1>
1087       <title>Exceptional Situations</title>
1088       <para>
1089         If the execution of the SQL query leads to any errors, an
1090         error of type <errortype>sql-database-error</errortype> is
1091         signalled.
1092       </para>
1093     </refsect1>
1094     <refsect1>
1095       <title>See Also</title>
1096       <simplelist>
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>
1102       </simplelist>
1103     </refsect1>
1104     <refsect1>
1105       <title>Notes</title>
1106       <para>
1107         None.
1108       </para>
1109     </refsect1>
1110   </refentry>
1111
1112   <refentry id="select">
1113     <refmeta>
1114       <refentrytitle>SELECT</refentrytitle>
1115     </refmeta>
1116     <refnamediv>
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>
1120     </refnamediv>
1121     <refsect1>
1122       <title>Syntax</title>
1123       <synopsis>
1124       <function>select</function> &amp;rest <replaceable>identifiers</replaceable> &amp;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>
1125     </refsect1>
1126     <refsect1>
1127       <title>Arguments and Values</title>
1128       <variablelist>
1129         <varlistentry>
1130           <term><parameter>identifiers</parameter></term>
1131           <listitem>
1132             <para>
1133               A set of <glossterm linkend="gloss-sql-expression">sql
1134               expressions</glossterm> each of which indicates a column
1135               to query.
1136             </para>
1137           </listitem>
1138         </varlistentry>
1139         <varlistentry>
1140           <term><parameter>all</parameter></term>
1141           <listitem>
1142             <para>
1143               A Boolean.
1144             </para>
1145           </listitem>
1146         </varlistentry>
1147         <varlistentry>
1148           <term><parameter>distinct</parameter></term>
1149           <listitem>
1150             <para>
1151               A Boolean.
1152             </para>
1153           </listitem>
1154         </varlistentry>
1155         <varlistentry>
1156           <term><parameter>from</parameter></term>
1157           <listitem>
1158             <para>
1159               One or more SQL expression representing tables.
1160             </para>
1161           </listitem>
1162         </varlistentry>
1163         <varlistentry>
1164           <term><parameter>group-by</parameter></term>
1165           <listitem>
1166             <para>
1167               An SQL expression.
1168             </para>
1169           </listitem>
1170         </varlistentry>
1171         <varlistentry>
1172           <term><parameter>having</parameter></term>
1173           <listitem>
1174             <para>
1175               An SQL expression.
1176             </para>
1177           </listitem>
1178         </varlistentry>
1179         <varlistentry>
1180           <term><parameter>order-by</parameter></term>
1181           <listitem>
1182             <para>
1183               An SQL expression.
1184             </para>
1185           </listitem>
1186         </varlistentry>
1187         <varlistentry>
1188           <term><parameter>set-operation</parameter></term>
1189           <listitem>
1190             <para>
1191               An SQL expression.
1192             </para>
1193           </listitem>
1194         </varlistentry>
1195         <varlistentry>
1196           <term><parameter>where</parameter></term>
1197           <listitem>
1198             <para>
1199               An SQL expression.
1200             </para>
1201           </listitem>
1202         </varlistentry>
1203         <varlistentry>
1204           <term><parameter>database</parameter></term>
1205           <listitem>
1206             <para>A
1207             <glossterm linkend="gloss-database-object">database
1208             object</glossterm>. This will default to the value
1209             of <symbol>*default-database*</symbol>.</para>
1210           </listitem>
1211         </varlistentry>
1212         <varlistentry>
1213           <term><parameter>flatp</parameter></term>
1214           <listitem>
1215             <para>A Boolean whose default value is &nil;.</para>
1216           </listitem>
1217         </varlistentry>
1218         <varlistentry>
1219           <term><parameter>result-types</parameter></term>
1220           <listitem>
1221             <para>A
1222             <glossterm linkend="gloss-field-types">field type
1223             specifier</glossterm>. The default is &nil;.
1224             </para>
1225             <para>
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
1231               objects.
1232             </para>
1233             <para>
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.
1245                 </member>
1246                 <member><symbol>:double</symbol> Field is imported as a
1247                 double-float number.
1248                 </member>
1249                 <member><symbol>t</symbol> Field is imported as a
1250                 string.
1251                 </member>
1252               </simplelist>
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.
1257             </para>
1258           </listitem>
1259         </varlistentry>
1260         <varlistentry>
1261           <term><parameter>field-names</parameter></term>
1262           <listitem>
1263           <para>
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 -
1267             the list of rows.
1268           </para>
1269           </listitem>
1270         </varlistentry>
1271         <varlistentry>
1272           <term><parameter>result</parameter></term>
1273           <listitem>
1274             <para>
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
1278               in the tuple.
1279             </para>
1280           </listitem>
1281         </varlistentry>
1282       </variablelist>
1283     </refsect1>
1284     <refsect1>
1285       <title>Description</title>
1286       <para>
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.
1293       </para>
1294       <para>
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>.
1310       </para>
1311       <para>
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.
1323       </para>
1324       <para>
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.
1342       </para>
1343       <para>
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.
1350       </para>
1351     </refsect1>
1352     <refsect1>
1353       <title>Examples</title>
1354       <screen>
1355 (select [first-name] :from [employee] :flatp t :distinct t
1356                      :field-names nil
1357                      :result-types nil
1358                      :order-by [first-name])
1359 => ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
1360     "Yuri")
1361
1362 (select [first-name] [count [*]] :from [employee]
1363                                  :result-types nil
1364                                  :group-by [first-name]
1365                                  :order-by [first-name]
1366                                  :field-names nil)
1367 => (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
1368     ("Mikhail" "1") ("Nikita" "1") ("Vladamir" "2") ("Yuri" "1"))
1369
1370 (select [last-name] :from [employee]
1371                     :where [like [email] "%org"]
1372                     :order-by [last-name]
1373                     :field-names nil
1374                     :result-types nil
1375                     :flatp t)
1376 => ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
1377     "Stalin" "Trotsky" "Yeltsin")
1378
1379 (select [max [emplid]] :from [employee]
1380                        :flatp t
1381                        :field-names nil
1382                        :result-types :auto)
1383 => (10)
1384
1385 (clsql:select [avg [height]] :from [employee] :flatp t :field-names nil)
1386 => (1.58999584d0)
1387
1388 (select [emplid] [last-name] :from [employee] :where [= [emplid] 1])
1389 => ((1 "Lenin")),
1390    ("emplid" "last_name")
1391
1392 (select [emplid :string] :from [employee]
1393                          :where [= 1 [emplid]]
1394                          :field-names nil
1395                          :flatp t)
1396 => ("1")
1397
1398 (select [emplid] :from [employee] :order-by [emplid]
1399                  :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]]
1400                  :field-names nil
1401                  :flatp t)
1402 => (1 2 3 4)
1403
1404 (clsql:select [emplid] :from [employee]
1405                        :where [in [emplid] '(1 2 3 4)]
1406                        :flatp t
1407                        :order-by [emplid]
1408                        :field-names nil)
1409 => (1 2 3 4)
1410
1411 (select [first-name] [last-name] :from [employee]
1412         :field-names nil
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"))
1418
1419 (select [last-name] :from [employee]
1420                  :set-operation [union [select [first-name] :from [employee]
1421                                                :order-by [last-name]]]
1422                  :flatp t
1423                  :result-types nil
1424                  :field-names nil)
1425 => ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
1426     "Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
1427     "Trotsky" "Vladamir" "Yeltsin" "Yuri")
1428       </screen>
1429     </refsect1>
1430     <refsect1>
1431       <title>Side Effects</title>
1432       <para>
1433         <para>Whatever effects the execution of the SQL query has on
1434         the underlying database, if any.</para>
1435       </para>
1436     </refsect1>
1437     <refsect1>
1438       <title>Affected by</title>
1439       <para>
1440         None.
1441       </para>
1442     </refsect1>
1443     <refsect1>
1444       <title>Exceptional Situations</title>
1445       <para>
1446         If the execution of the SQL query leads to any errors, an
1447         error of type <errortype>sql-database-error</errortype> is
1448         signalled.
1449       </para>
1450     </refsect1>
1451     <refsect1>
1452       <title>See Also</title>
1453       <simplelist>
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>
1460       </simplelist>
1461     </refsect1>
1462     <refsect1>
1463       <title>Notes</title>
1464       <para>
1465         The <parameter>field-names</parameter> and
1466         <parameter>result-types</parameter> keyword arguments are a
1467         &clsql; extension.
1468       </para>
1469       <para>
1470         <parameter>select</parameter> is common across the functional
1471         and object-oriented data manipulation languages.
1472       </para>
1473     </refsect1>
1474   </refentry>
1475
1476
1477   <!-- iteration and mapping -->
1478
1479   <refentry id="do-query">
1480     <refmeta>
1481       <refentrytitle>DO-QUERY</refentrytitle>
1482     </refmeta>
1483     <refnamediv>
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>
1487     </refnamediv>
1488     <refsect1>
1489       <title>Syntax</title>
1490       <synopsis>
1491       <function>do-query</function> ((&amp;rest <replaceable>args</replaceable>) <replaceable>query-expression</replaceable> &amp;key <replaceable>database</replaceable> <replaceable>result-types</replaceable> &amp;body <replaceable>body</replaceable> => <returnvalue>result</returnvalue></synopsis>
1492     </refsect1>
1493     <refsect1>
1494       <title>Arguments and Values</title>
1495       <variablelist>
1496         <varlistentry>
1497           <term><parameter>args</parameter></term>
1498           <listitem>
1499             <para>A list of variable names.</para>
1500           </listitem>
1501         </varlistentry>
1502         <varlistentry>
1503           <term><parameter>query-expression</parameter></term>
1504           <listitem>
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>
1510           </listitem>
1511         </varlistentry>
1512         <varlistentry>
1513           <term><parameter>database</parameter></term>
1514           <listitem>
1515             <para>A
1516             <glossterm linkend="gloss-database-object">database
1517             object</glossterm>. This will default to
1518             <symbol>*default-database*</symbol>.</para>
1519           </listitem>
1520         </varlistentry>
1521         <varlistentry>
1522           <term><parameter>result-types</parameter></term>
1523           <listitem>
1524             <para>
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.
1529             </para>
1530           </listitem>
1531         </varlistentry>
1532         <varlistentry>
1533           <term><parameter>body</parameter></term>
1534           <listitem>
1535             <para>A body of Lisp code, like in a
1536             <function>destructuring-bind</function> form.</para>
1537           </listitem>
1538         </varlistentry>
1539         <varlistentry>
1540           <term><parameter>result</parameter></term>
1541           <listitem>
1542             <para>The result of executing <parameter>body</parameter>.</para>
1543           </listitem>
1544         </varlistentry>
1545       </variablelist>
1546     </refsect1>
1547     <refsect1>
1548       <title>Description</title>
1549       <para>
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>.
1557       </para>
1558       <para>
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>.
1567       </para>
1568       <para>
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>.
1574       </para>
1575       <para>
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.
1583       </para>
1584       <para>
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.
1589       </para>
1590     </refsect1>
1591     <refsect1>
1592       <title>Examples</title>
1593       <screen>
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
1598 => NIL
1599
1600 (do-query ((salary name) "select salary,name from simple")
1601   (return (cons salary name)))
1602 => ("10000.00" . "Mai, Pierre")
1603
1604 (let ((result '()))
1605   (do-query ((name) [select [last-name] :from [employee]
1606                             :order-by [last-name]])
1607     (push name result))
1608   result)
1609 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1610     "Chernenko" "Brezhnev" "Andropov")
1611
1612 (let ((result '()))
1613   (do-query ((e) [select 'employee :order-by [last-name]])
1614     (push (slot-value e 'last-name) result))
1615   result)
1616 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1617     "Chernenko" "Brezhnev" "Andropov")
1618       </screen>
1619     </refsect1>
1620     <refsect1>
1621       <title>Side Effects</title>
1622       <para>Whatever effects the execution of the SQL query has
1623       on the underlying database, if any.</para>
1624     </refsect1>
1625     <refsect1>
1626       <title>Affected by</title>
1627       <para>None.</para>
1628     </refsect1>
1629     <refsect1>
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
1637       signalled.</para>
1638     </refsect1>
1639     <refsect1>
1640       <title>See Also</title>
1641       <simplelist>
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>
1647       </simplelist>
1648     </refsect1>
1649     <refsect1>
1650       <title>Notes</title>
1651       <para>The <parameter>result-types</parameter> keyword argument
1652       is a &clsql; extension.</para>
1653       <para>
1654         <parameter>do-query</parameter> is common across the functional
1655         and object-oriented data manipulation languages.
1656       </para>
1657     </refsect1>
1658   </refentry>
1659
1660   <refentry id="for-each-row">
1661     <refmeta>
1662       <refentrytitle>FOR-EACH-ROW</refentrytitle>
1663     </refmeta>
1664     <refnamediv>
1665       <refname><emphasis>Function</emphasis> <emphasis role="bold">FOR-EACH-ROW</emphasis></refname>
1666       <refpurpose><!-- purpose --></refpurpose>
1667       <refclass>Function</refclass>
1668     </refnamediv>
1669     <refsect1>
1670       <title>Syntax</title>
1671       <synopsis>
1672       <function>for-each-row</function> &amp;KEY FROM ORDER-BY WHERE DISTINCT LIMIT &amp;REST FIELDS &amp;body <replaceable>body</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
1673     </refsect1>
1674     <refsect1>
1675       <title>Arguments and Values</title>
1676       <variablelist>
1677         <!-- arguments and values -->
1678       </variablelist>
1679     </refsect1>
1680     <refsect1>
1681       <title>Description</title>
1682       <para>
1683         <!-- description -->
1684       </para>
1685     </refsect1>
1686     <refsect1>
1687       <title>Examples</title>
1688       <screen>
1689         <!-- examples -->
1690       </screen>
1691     </refsect1>
1692     <refsect1>
1693       <title>Side Effects</title>
1694       <para>
1695         <!-- side effects -->
1696       </para>
1697     </refsect1>
1698     <refsect1>
1699       <title>Affected by</title>
1700       <para>
1701         <simplelist>
1702           <!-- affected by -->
1703         </simplelist>
1704       </para>
1705     </refsect1>
1706     <refsect1>
1707       <title>Exceptional Situations</title>
1708       <para>
1709         <!-- execeptional situations -->
1710       </para>
1711     </refsect1>
1712     <refsect1>
1713       <title>See Also</title>
1714       <simplelist>
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>
1721         </simplelist>
1722     </refsect1>
1723     <refsect1>
1724       <title>Notes</title>
1725       <para>
1726         <!-- notes here -->
1727       </para>
1728     </refsect1>
1729   </refentry>
1730
1731   <refentry id="loop-tuples">
1732     <refmeta>
1733       <refentrytitle>LOOP</refentrytitle>
1734     </refmeta>
1735     <refnamediv>
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>
1740     </refnamediv>
1741     <!-- refsect1>
1742       <title>Compatibility</title>
1743       <caution><para><function>loop-for-as-tuples</function> only works with &cmucl;.</para></caution>
1744     </refsect1 -->
1745     <refsect1>
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>
1748     </refsect1>
1749     <refsect1>
1750       <title>Arguments and Values</title>
1751       <variablelist>
1752         <varlistentry>
1753           <term><parameter>var</parameter></term>
1754           <listitem>
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>
1759           </listitem>
1760         </varlistentry>
1761         <varlistentry>
1762           <term><parameter>type-spec</parameter></term>
1763           <listitem>
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
1767             Common Lisp.</para>
1768           </listitem>
1769         </varlistentry>
1770         <varlistentry>
1771           <term><parameter>query</parameter></term>
1772           <listitem>
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>
1778           </listitem>
1779         </varlistentry>
1780         <varlistentry>
1781           <term><parameter>database</parameter></term>
1782           <listitem>
1783             <para>An optional
1784             <glossterm linkend="gloss-database-object">database
1785             object</glossterm>. This will default to the value
1786             of <symbol>*default-database*</symbol>.</para>
1787           </listitem>
1788         </varlistentry>
1789       </variablelist>
1790     </refsect1>
1791     <refsect1>
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>
1799       specified.</para>
1800       <para>
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.
1805       </para>
1806     </refsect1>
1807     <refsect1>
1808       <title>Examples</title>
1809       <screen>
1810 (defvar *my-db* (connect '("dent" "newesim" "dent" "dent"))
1811 "My database"
1812 => *MY-DB*
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"
1816    from *my-db*
1817    do
1818      (incf (gethash time time-graph 0))
1819      (incf (gethash event event-graph 0))
1820    finally
1821      (flet ((show-graph (k v) (format t "~40A => ~5D~%" k v)))
1822        (format t "~&amp;Time-Graph:~%===========~%")
1823        (maphash #'show-graph time-graph)
1824        (format t "~&amp;~%Event-Graph:~%============~%")
1825        (maphash #'show-graph event-graph))
1826      (return (values time-graph event-graph)))
1827 >> Time-Graph:
1828 >> ===========
1829 >> D                                        => 53000
1830 >> X                                        =>     3
1831 >> test-me                                  =>  3000
1832 >>
1833 >> Event-Graph:
1834 >> ============
1835 >> CLOS Benchmark entry.                    =>  9000
1836 >> Demo Text...                             =>     3
1837 >> doit-text                                =>  3000
1838 >> C    Benchmark entry.                    => 12000
1839 >> CLOS Benchmark entry                     => 32000
1840 => #&lt;EQUAL hash table, 3 entries {48350A1D}>
1841 => #&lt;EQUAL hash table, 5 entries {48350FCD}>
1842
1843 (loop for (forename surname)
1844       being each tuple in
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")
1851
1852 (loop for (e) being the records in
1853      [select 'employee :where [&lt; [emplid] 4] :order-by [emplid]]
1854   collect (slot-value e 'last-name))
1855 => ("Lenin" "Stalin" "Trotsky")
1856       </screen>
1857     </refsect1>
1858     <refsect1>
1859       <title>Side Effects</title>
1860       <para>Whatever effects the execution of the SQL query has
1861       on the underlying database, if any.</para>
1862     </refsect1>
1863     <refsect1>
1864       <title>Affected by</title>
1865       <para>None.</para>
1866     </refsect1>
1867     <refsect1>
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>
1874     </refsect1>
1875     <refsect1>
1876       <title>See Also</title>
1877       <para>
1878         <simplelist>
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>
1884         </simplelist>
1885       </para>
1886     </refsect1>
1887     <refsect1>
1888       <title>Notes</title>
1889       <para>The <parameter>database</parameter> loop keyword is a
1890       &clsql; extension.</para>
1891       <para>
1892         The extended <function>loop</function> syntax is common across
1893         the functional and object-oriented data manipulation
1894         languages.
1895       </para>
1896     </refsect1>
1897   </refentry>
1898
1899   <refentry id="map-query">
1900     <refmeta>
1901       <refentrytitle>MAP-QUERY</refentrytitle>
1902     </refmeta>
1903     <refnamediv>
1904       <refname><emphasis>Function</emphasis> <emphasis role="bold">MAP-QUERY</emphasis></refname>
1905       <refpurpose>Map a function over all the tuples from a
1906       query</refpurpose>
1907       <refclass>Function</refclass>
1908     </refnamediv>
1909     <refsect1>
1910       <title>Syntax</title>
1911       <synopsis><function>map-query</function> <replaceable>output-type-spec</replaceable> <replaceable>function</replaceable> <replaceable>query-expression</replaceable> &amp;key <replaceable>database</replaceable> <replaceable>result-types</replaceable> => <returnvalue>result</returnvalue></synopsis>
1912     </refsect1>
1913     <refsect1>
1914       <title>Arguments and Values</title>
1915       <variablelist>
1916         <varlistentry>
1917           <term><parameter>output-type-spec</parameter></term>
1918           <listitem>
1919             <para>A sequence type specifier or <symbol>nil</symbol>.</para>
1920           </listitem>
1921         </varlistentry>
1922         <varlistentry>
1923           <term><parameter>function</parameter></term>
1924           <listitem>
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>
1929           </listitem>
1930         </varlistentry>
1931         <varlistentry>
1932           <term><parameter>query-expression</parameter></term>
1933           <listitem>
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)
1937             result set.</para>
1938           </listitem>
1939         </varlistentry>
1940         <varlistentry>
1941           <term><parameter>database</parameter></term>
1942           <listitem>
1943             <para>A
1944             <glossterm linkend="gloss-database-object">database
1945             object</glossterm>. This will default to the value
1946             of <symbol>*default-database*</symbol>.</para>
1947           </listitem>
1948         </varlistentry>
1949         <varlistentry>
1950           <term><parameter>result-types</parameter></term>
1951           <listitem>
1952             <para>
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.
1957             </para>
1958           </listitem>
1959         </varlistentry>
1960         <varlistentry>
1961           <term><returnvalue>result</returnvalue></term>
1962           <listitem>
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>
1967           </listitem>
1968         </varlistentry>
1969       </variablelist>
1970     </refsect1>
1971     <refsect1>
1972       <title>Description</title>
1973       <para>
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>.
1988       </para>
1989       <para>
1990         If the <parameter>output-type-spec</parameter> is a subtype of
1991         <type>list</type>, the result will be a <type>list</type>.
1992       </para>
1993       <para>
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
2003         signaled.
2004       </para>
2005       <para>
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>
2010       <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.
2015       </para>
2016     </refsect1>
2017     <refsect1>
2018       <title>Examples</title>
2019       <screen>
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")
2025 => (10000.0 8000.5)
2026
2027 (map-query '(vector double-float)
2028            #'(lambda (tuple)
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)
2035 (type-of *)
2036 => (SIMPLE-ARRAY DOUBLE-FLOAT (2))
2037
2038 (let (list)
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"))
2043           list))
2044 => NIL
2045 => (("Hacker, Random J." . 8000.5) ("Mai, Pierre" . 10000.0))
2046
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")
2052
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"))
2060
2061 (map-query 'list #'last-name [select 'employee :order-by [emplid]])
2062 => ("Lenin" "Stalin" "Trotsky" "Kruschev" "Brezhnev" "Andropov" "Chernenko"
2063     "Gorbachev" "Yeltsin" "Putin")
2064       </screen>
2065     </refsect1>
2066     <refsect1>
2067       <title>Side Effects</title>
2068       <para>Whatever effects the execution of the SQL query has
2069       on the underlying database, if any.</para>
2070     </refsect1>
2071     <refsect1>
2072       <title>Affected by</title>
2073       <para>None.</para>
2074     </refsect1>
2075     <refsect1>
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
2089       that number.</para>
2090     </refsect1>
2091     <refsect1>
2092       <title>See Also</title>
2093       <simplelist>
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>
2099       </simplelist>
2100     </refsect1>
2101     <refsect1>
2102       <title>Notes</title>
2103       <para>The <parameter>result-types</parameter> keyword argument
2104       is a &clsql; extension.</para>
2105       <para>
2106         <parameter>map-query</parameter> is common across the
2107         functional and object-oriented data manipulation languages.
2108       </para>
2109     </refsect1>
2110   </refentry>
2111
2112
2113   <!-- prepared statements -->
2114
2115   <refentry id="prepare-sql">
2116     <refmeta>
2117       <refentrytitle>PREPARE-SQL</refentrytitle>
2118     </refmeta>
2119     <refnamediv>
2120       <refname><emphasis>Function</emphasis> <emphasis role="bold">PREPARE-SQL</emphasis></refname>
2121       <refpurpose>Create a prepared statement.</refpurpose>
2122       <refclass>Function</refclass>
2123     </refnamediv>
2124     <refsect1>
2125       <title>Syntax</title>
2126       <synopsis>
2127       <function>prepare-sql</function> <replaceable>sql-stmt types</replaceable> &amp;key <replaceable>database</replaceable> <replaceable>result-types</replaceable> <replaceable>field-names</replaceable> => <returnvalue>result</returnvalue></synopsis>
2128     </refsect1>
2129     <refsect1>
2130       <title>Arguments and Values</title>
2131       <variablelist>
2132         <!-- arguments and values -->
2133       </variablelist>
2134     </refsect1>
2135     <refsect1>
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.
2141
2142       A type can be
2143       :int
2144       :double
2145       :null
2146       (:string n)
2147
2148       </para>
2149     </refsect1>
2150     <refsect1>
2151       <title>Examples</title>
2152       <screen>
2153         <!-- examples -->
2154       </screen>
2155     </refsect1>
2156     <refsect1>
2157       <title>Side Effects</title>
2158       <para>
2159         <!-- side effects -->
2160       </para>
2161     </refsect1>
2162     <refsect1>
2163       <title>Affected by</title>
2164       <para>
2165         <simplelist>
2166           <!-- affected by -->
2167         </simplelist>
2168       </para>
2169     </refsect1>
2170     <refsect1>
2171       <title>Exceptional Situations</title>
2172       <para>
2173         <!-- execeptional situations -->
2174       </para>
2175     </refsect1>
2176     <refsect1>
2177       <title>See Also</title>
2178       <para>
2179         <simplelist>
2180           <!-- see also -->
2181         </simplelist>
2182       </para>
2183     </refsect1>
2184     <refsect1>
2185       <title>Notes</title>
2186       <para>
2187         <!-- notes -->
2188       </para>
2189     </refsect1>
2190   </refentry>
2191
2192   <refentry id="run-prepared-sql">
2193     <refmeta>
2194       <refentrytitle>RUN-PREPARED-SQL</refentrytitle>
2195     </refmeta>
2196     <refnamediv>
2197       <refname><emphasis>Function</emphasis> <emphasis role="bold">RUN-PREPARED-SQL</emphasis></refname>
2198       <refpurpose>Execute a prepared statement.</refpurpose>
2199       <refclass>Function</refclass>
2200     </refnamediv>
2201     <refsect1>
2202       <title>Syntax</title>
2203       <synopsis>
2204       <function>run-prepared-sql</function> <replaceable>prepared-stmt</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
2205     </refsect1>
2206     <refsect1>
2207       <title>Arguments and Values</title>
2208       <variablelist>
2209         <!-- arguments and values -->
2210       </variablelist>
2211     </refsect1>
2212     <refsect1>
2213       <title>Description</title>
2214       <para>Execute the prepared sql statment. All input
2215       parameters must be bound.
2216       </para>
2217     </refsect1>
2218     <refsect1>
2219       <title>Examples</title>
2220       <screen>
2221         <!-- examples -->
2222       </screen>
2223     </refsect1>
2224     <refsect1>
2225       <title>Side Effects</title>
2226       <para>
2227         <!-- side effects -->
2228       </para>
2229     </refsect1>
2230     <refsect1>
2231       <title>Affected by</title>
2232       <para>
2233         <simplelist>
2234           <!-- affected by -->
2235         </simplelist>
2236       </para>
2237     </refsect1>
2238     <refsect1>
2239       <title>Exceptional Situations</title>
2240       <para>
2241         <!-- execeptional situations -->
2242       </para>
2243     </refsect1>
2244     <refsect1>
2245       <title>See Also</title>
2246       <para>
2247         <simplelist>
2248           <!-- see also -->
2249         </simplelist>
2250       </para>
2251     </refsect1>
2252     <refsect1>
2253       <title>Notes</title>
2254       <para>
2255         <!-- notes -->
2256       </para>
2257     </refsect1>
2258   </refentry>
2259
2260   <refentry id="free-prepared-sql">
2261     <refmeta>
2262       <refentrytitle>FREE-PREPARED-SQL</refentrytitle>
2263     </refmeta>
2264     <refnamediv>
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>
2268     </refnamediv>
2269     <refsect1>
2270       <title>Syntax</title>
2271       <synopsis>
2272       <function>free-prepared-sql</function> <replaceable>prepared-stmt</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
2273     </refsect1>
2274     <refsect1>
2275       <title>Arguments and Values</title>
2276       <variablelist>
2277         <!-- arguments and values -->
2278       </variablelist>
2279     </refsect1>
2280     <refsect1>
2281       <title>Description</title>
2282       <para>Delete the objects associated with a prepared
2283       statement.
2284       </para>
2285     </refsect1>
2286     <refsect1>
2287       <title>Examples</title>
2288       <screen>
2289         <!-- examples -->
2290       </screen>
2291     </refsect1>
2292     <refsect1>
2293       <title>Side Effects</title>
2294       <para>
2295         <!-- side effects -->
2296       </para>
2297     </refsect1>
2298     <refsect1>
2299       <title>Affected by</title>
2300       <para>
2301         <simplelist>
2302           <!-- affected by -->
2303         </simplelist>
2304       </para>
2305     </refsect1>
2306     <refsect1>
2307       <title>Exceptional Situations</title>
2308       <para>
2309         <!-- execeptional situations -->
2310       </para>
2311     </refsect1>
2312     <refsect1>
2313       <title>See Also</title>
2314       <para>
2315         <simplelist>
2316           <!-- see also -->
2317         </simplelist>
2318       </para>
2319     </refsect1>
2320     <refsect1>
2321       <title>Notes</title>
2322       <para>
2323         <!-- notes -->
2324       </para>
2325     </refsect1>
2326   </refentry>
2327
2328   <refentry id="bind-parameter">
2329     <refmeta>
2330       <refentrytitle>BIND-PARAMETER</refentrytitle>
2331     </refmeta>
2332     <refnamediv>
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>
2336     </refnamediv>
2337     <refsect1>
2338       <title>Syntax</title>
2339       <synopsis>
2340       <function>bind-parameter</function> <replaceable>prepared-stmt</replaceable> <replaceable>position</replaceable> <replaceable>value</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
2341     </refsect1>
2342     <refsect1>
2343       <title>Arguments and Values</title>
2344       <variablelist>
2345         <!-- arguments and values -->
2346       </variablelist>
2347     </refsect1>
2348     <refsect1>
2349       <title>Description</title>
2350       <para>Sets the value of a parameter in a prepared statement.
2351       </para>
2352     </refsect1>
2353     <refsect1>
2354       <title>Examples</title>
2355       <screen>
2356         <!-- examples -->
2357       </screen>
2358     </refsect1>
2359     <refsect1>
2360       <title>Side Effects</title>
2361       <para>
2362         <!-- side effects -->
2363       </para>
2364     </refsect1>
2365     <refsect1>
2366       <title>Affected by</title>
2367       <para>
2368         <simplelist>
2369           <!-- affected by -->
2370         </simplelist>
2371       </para>
2372     </refsect1>
2373     <refsect1>
2374       <title>Exceptional Situations</title>
2375       <para>
2376         <!-- execeptional situations -->
2377       </para>
2378     </refsect1>
2379     <refsect1>
2380       <title>See Also</title>
2381       <para>
2382         <simplelist>
2383           <!-- see also -->
2384         </simplelist>
2385       </para>
2386     </refsect1>
2387     <refsect1>
2388       <title>Notes</title>
2389       <para>
2390         <!-- notes -->
2391       </para>
2392     </refsect1>
2393   </refentry>
2394
2395 </reference>