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       <para>
 362         <simplelist>
 363           <member><link linkend="update-records"><function>update-records</function></link></member>
 364           <member><link linkend="delete-records"><function>delete-records</function></link></member>
 365         </simplelist>
 366       </para>
 367     </refsect1>
 368     <refsect1>
 369       <title>Notes</title>
 370       <para>
 371         None.
 372       </para>
 373     </refsect1>
 374   </refentry>
 375
 376   <refentry id="update-records">
 377     <refmeta>
 378       <refentrytitle>UPDATE-RECORDS</refentrytitle>
 379     </refmeta>
 380     <refnamediv>
 381       <refname><emphasis>Function</emphasis> <emphasis role="bold">UPDATE-RECORDS</emphasis></refname>
 382       <refpurpose>Updates the values of existing records.</refpurpose>
 383       <refclass>Function</refclass>
 384     </refnamediv>
 385     <refsect1>
 386       <title>Syntax</title>
 387       <synopsis>
 388       <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>
 389     </refsect1>
 390     <refsect1>
 391       <title>Arguments and Values</title>
 392       <variablelist>
 393         <varlistentry>
 394           <term><parameter>table</parameter></term>
 395           <listitem>
 396             <para>
 397               A string, symbol or symbolic SQL expression representing
 398               the name of a table existing in
 399               <parameter>database</parameter>.
 400             </para>
 401           </listitem>
 402         </varlistentry>
 403         <varlistentry>
 404           <term><parameter>attributes</parameter></term>
 405           <listitem>
 406             <para>
 407               A list of attribute identifiers or &nil;.
 408             </para>
 409           </listitem>
 410         </varlistentry>
 411         <varlistentry>
 412           <term><parameter>values</parameter></term>
 413           <listitem>
 414             <para>
 415               A list of attribute values or &nil;.
 416             </para>
 417           </listitem>
 418         </varlistentry>
 419         <varlistentry>
 420           <term><parameter>av-pairs</parameter></term>
 421           <listitem>
 422             <para>
 423               A list of attribute identifier/value pairs or &nil;.
 424             </para>
 425           </listitem>
 426         </varlistentry>
 427         <varlistentry>
 428           <term><parameter>where</parameter></term>
 429           <listitem>
 430             <para>
 431               A symbolic SQL expression.
 432             </para>
 433           </listitem>
 434         </varlistentry>
 435         <varlistentry>
 436           <term><parameter>database</parameter></term>
 437           <listitem>
 438             <para>A
 439             <glossterm linkend="gloss-database-object">database
 440             object</glossterm>. This will default to the value
 441             of <symbol>*default-database*</symbol>.</para>
 442           </listitem>
 443       </varlistentry>
 444       </variablelist>
 445     </refsect1>
 446     <refsect1>
 447       <title>Description</title>
 448       <para>
 449         Updates the attribute values of existing records satsifying
 450         the SQL expression <parameter>where</parameter> in the table
 451         specified by <parameter>table</parameter> in
 452         <parameter>database</parameter> which defaults to
 453         <symbol>*default-database*</symbol>.
 454       </para>
 455       <para>
 456         There are three ways of specifying the values to update for
 457         each row. In the first, <parameter>values</parameter> contains
 458         a list of values to use in the update and
 459         <parameter>attributes</parameter> and
 460         <parameter>av-pairs</parameter> are &nil;. This can be used
 461         when values are supplied for all attributes in
 462         <parameter>table</parameter>. In the second,
 463         <parameter>attributes</parameter> is a list of column names,
 464         <parameter>values</parameter> is a corresponding list of
 465         values and <parameter>av-pairs</parameter> is &nil;. In the
 466         third, <parameter>attributes</parameter> and
 467         <parameter>values</parameter> are &nil; and
 468         <parameter>av-pairs</parameter> is an alist of (attribute
 469         value) pairs.
 470       </para>
 471     </refsect1>
 472     <refsect1>
 473       <title>Examples</title>
 474       <screen>
 475 (select [first-name] [last-name] [email]
 476         :from [employee]
 477         :where [= [emplid] 1]
 478         :field-names nil)
 479 => (("Vladamir" "Lenin" "lenin@soviet.org"))
 480 (update-records [employee]
 481                 :av-pairs'((first_name "Yuri")
 482                            (last_name "Gagarin")
 483                            (email "gagarin@soviet.org"))
 484                 :where [= [emplid] 1])
 485 =>
 486 (select [first-name] [last-name] [email]
 487         :from [employee]
 488         :where [= [emplid] 1]
 489         :field-names nil)
 490 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
 491       </screen>
 492     </refsect1>
 493     <refsect1>
 494       <title>Side Effects</title>
 495       <para>
 496         Modifications are made to the underlying database.
 497       </para>
 498     </refsect1>
 499     <refsect1>
 500       <title>Affected by</title>
 501       <para>
 502         None.
 503       </para>
 504     </refsect1>
 505     <refsect1>
 506       <title>Exceptional Situations</title>
 507       <para>
 508         An error of type <symbol>sql-database-data-error</symbol> is
 509         signalled if <parameter>table</parameter> is not an existing
 510         table in <parameter>database</parameter>, if the specified
 511         attributes are not found or if the SQL statement resulting
 512         from the symbolic expression <parameter>where</parameter> does
 513         not return a Boolean value.
 514       </para>
 515       <para>If the execution of the SQL query leads to any errors, an
 516       error of type <errortype>sql-database-error</errortype> is
 517       signalled.</para>
 518     </refsect1>
 519     <refsect1>
 520       <title>See Also</title>
 521       <para>
 522         <simplelist>
 523           <member><link linkend="insert-records"><function>insert-records</function></link></member>
 524           <member><link linkend="delete-records"><function>delete-records</function></link></member>
 525         </simplelist>
 526       </para>
 527     </refsect1>
 528     <refsect1>
 529       <title>Notes</title>
 530       <para>
 531         None.
 532       </para>
 533     </refsect1>
 534   </refentry>
 535
 536   <refentry id="delete-records">
 537     <refmeta>
 538       <refentrytitle>DELETE-RECORDS</refentrytitle>
 539     </refmeta>
 540     <refnamediv>
 541       <refname><emphasis>Function</emphasis> <emphasis role="bold">DELETE-RECORDS</emphasis></refname>
 542       <refpurpose>Delete records from a database table.</refpurpose>
 543       <refclass>Function</refclass>
 544     </refnamediv>
 545     <refsect1>
 546       <title>Syntax</title>
 547       <synopsis>
 548       <function>delete-records</function> &amp;key <replaceable>from</replaceable> <replaceable>where</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
 549     </refsect1>
 550     <refsect1>
 551       <title>Arguments and Values</title>
 552       <variablelist>
 553         <varlistentry>
 554           <term><parameter>from</parameter></term>
 555           <listitem>
 556             <para>
 557               A string, symbol or symbolic SQL expression representing
 558               the name of a table existing in
 559               <parameter>database</parameter>.
 560             </para>
 561           </listitem>
 562         </varlistentry>
 563                 <varlistentry>
 564           <term><parameter>where</parameter></term>
 565           <listitem>
 566             <para>
 567               A symbolic SQL expression.
 568             </para>
 569           </listitem>
 570         </varlistentry>
 571         <varlistentry>
 572           <term><parameter>database</parameter></term>
 573           <listitem>
 574             <para>A
 575             <glossterm linkend="gloss-database-object">database
 576             object</glossterm>. This will default to the value
 577             of <symbol>*default-database*</symbol>.</para>
 578           </listitem>
 579         </varlistentry>
 580       </variablelist>
 581     </refsect1>
 582     <refsect1>
 583       <title>Description</title>
 584       <para>Deletes records satisfying the SQL expression
 585       <parameter>where</parameter> from the table specified by
 586       <parameter>from</parameter> in <parameter>database</parameter>
 587       specifies a database which defaults to
 588       <symbol>*default-database*</symbol>.
 589       </para>
 590     </refsect1>
 591     <refsect1>
 592       <title>Examples</title>
 593       <screen>
 594 (select [first-name] [last-name] [email]
 595         :from [employee]
 596         :where [= [emplid] 11]
 597         :field-names nil)
 598 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
 599 (delete-records :from [employee] :where [= [emplid] 11])
 600 =>
 601 (select [first-name] [last-name] [email]
 602         :from [employee]
 603         :where [= [emplid] 11]
 604         :field-names nil)
 605 => NIL
 606       </screen>
 607     </refsect1>
 608     <refsect1>
 609       <title>Side Effects</title>
 610       <para>
 611         Modifications are made to the underlying database.
 612       </para>
 613     </refsect1>
 614     <refsect1>
 615       <title>Affected by</title>
 616       <para>
 617         None.
 618       </para>
 619     </refsect1>
 620     <refsect1>
 621       <title>Exceptional Situations</title>
 622       <para>
 623         An error of type <symbol>sql-database-data-error</symbol> is
 624         signalled if <parameter>from</parameter> is not an existing
 625         table in <parameter>database</parameter> or if the SQL
 626         statement resulting from the symbolic expression
 627         <parameter>where</parameter> does not return a Boolean value.
 628       </para>
 629     </refsect1>
 630     <refsect1>
 631       <title>See Also</title>
 632       <para>
 633         <simplelist>
 634           <member><link linkend="insert-records"><function>insert-records</function></link></member>
 635           <member><link linkend="update-records"><function>update-records</function></link></member>
 636         </simplelist>
 637       </para>
 638     </refsect1>
 639     <refsect1>
 640       <title>Notes</title>
 641       <para>
 642         None.
 643       </para>
 644     </refsect1>
 645   </refentry>
 646
 647
 648   <!-- executing SQL commands and queries -->
 649
 650   <refentry id="execute-command">
 651     <refmeta>
 652       <refentrytitle>EXECUTE-COMMAND</refentrytitle>
 653     </refmeta>
 654     <refnamediv>
 655       <refname><emphasis>Generic Function</emphasis> <emphasis role="bold">EXECUTE-COMMAND</emphasis></refname>
 656       <refpurpose>Execute an SQL command which returns no values.</refpurpose>
 657       <refclass>Generic Function</refclass>
 658     </refnamediv>
 659     <refsect1>
 660       <title>Syntax</title>
 661       <synopsis>
 662       <function>execute-command</function> <replaceable>sql-expression</replaceable> &amp;key <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
 663     </refsect1>
 664     <refsect1>
 665       <title>Arguments and Values</title>
 666       <variablelist>
 667         <varlistentry>
 668           <term><parameter>sql-expression</parameter></term>
 669           <listitem>
 670             <para>An <glossterm linkend="gloss-sql-expression">sql
 671             expression</glossterm> that represents an SQL
 672             statement which will return no values.</para>
 673           </listitem>
 674         </varlistentry>
 675         <varlistentry>
 676           <term><parameter>database</parameter></term>
 677           <listitem>
 678             <para>A
 679             <glossterm linkend="gloss-database-object">database
 680             object</glossterm>. This will default to the value
 681             of <symbol>*default-database*</symbol>.</para>
 682           </listitem>
 683       </varlistentry></variablelist>
 684     </refsect1>
 685     <refsect1>
 686       <title>Description</title>
 687       <para>Executes the SQL command
 688       <parameter>sql-expression</parameter>, which may be a symbolic
 689       SQL expression or a string representing any SQL statement apart
 690       from a query, on the supplied <parameter>database</parameter>
 691       which defaults to <symbol>*default-database*</symbol>.
 692       </para>
 693     </refsect1>
 694     <refsect1>
 695       <title>Examples</title>
 696       <screen>
 697         (execute-command "create table eventlog (time char(30),event char(70))")
 698         =>
 699
 700         (execute-command "create table eventlog (time char(30),event char(70))")
 701         >>
 702         >> While accessing database #&lt;CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {480B2B6D}>
 703         >>   with expression "create table eventlog (time char(30),event char(70))":
 704         >>   Error NIL: ERROR:  amcreate: eventlog relation already exists
 705         >>   has occurred.
 706         >>
 707         >> Restarts:
 708         >>   0: [ABORT] Return to Top-Level.
 709         >>
 710         >> Debug  (type H for help)
 711         >>
 712         >> (CLSQL-POSTGRESQL::|(PCL::FAST-METHOD DATABASE-EXECUTE-COMMAND (T POSTGRESQL-DATABASE))|
 713         >>  #&lt;unused-arg>
 714         >>  #&lt;unused-arg>
 715         >>  #&lt;unavailable-arg>
 716         >>  #&lt;unavailable-arg>)
 717         >> Source: (ERROR 'SQL-DATABASE-ERROR :DATABASE DATABASE :EXPRESSION ...)
 718         >> 0] 0
 719
 720         (execute-command "drop table eventlog")
 721         =>
 722       </screen>
 723     </refsect1>
 724     <refsect1>
 725       <title>Side Effects</title>
 726       <para>Whatever effects the execution of the SQL statement has
 727       on the underlying database, if any.</para>
 728     </refsect1>
 729     <refsect1>
 730       <title>Affected by</title>
 731       <para>None.</para>
 732     </refsect1>
 733     <refsect1>
 734       <title>Exceptional Situations</title>
 735       <para>If the execution of the SQL statement leads to any errors,
 736       an error of type <errortype>sql-database-error</errortype> is
 737       signalled.</para>
 738     </refsect1>
 739     <refsect1>
 740       <title>See Also</title>
 741       <para>
 742         <simplelist>
 743           <member><link linkend="query"><function>query</function></link></member>
 744         </simplelist>
 745       </para>
 746     </refsect1>
 747     <refsect1>
 748       <title>Notes</title>
 749       <para>None.</para>
 750     </refsect1>
 751   </refentry>
 752
 753
 754   <refentry id="query">
 755     <refmeta>
 756       <refentrytitle>QUERY</refentrytitle>
 757     </refmeta>
 758     <refnamediv>
 759       <refname><emphasis>Generic Function</emphasis> <emphasis role="bold">QUERY</emphasis></refname>
 760       <refpurpose>Execute an SQL query and return the tuples as a
 761       list.</refpurpose>
 762       <refclass>Generic Function</refclass>
 763     </refnamediv>
 764     <refsect1>
 765       <title>Syntax</title>
 766       <synopsis>
 767       <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>
 768     </refsect1>
 769     <refsect1>
 770       <title>Arguments and Values</title>
 771       <variablelist>
 772         <varlistentry>
 773           <term><parameter>query-expression</parameter></term>
 774           <listitem>
 775             <para>An <glossterm linkend="gloss-sql-expression">sql
 776             expression</glossterm> that represents an SQL
 777             query which is expected to return a (possibly empty)
 778             result set.</para>
 779           </listitem>
 780         </varlistentry>
 781         <varlistentry>
 782           <term><parameter>database</parameter></term>
 783           <listitem>
 784             <para>A
 785             <glossterm linkend="gloss-database-object">database
 786             object</glossterm>. This will default to the value
 787             of <symbol>*default-database*</symbol>.</para>
 788           </listitem>
 789         </varlistentry>
 790         <varlistentry>
 791           <term><parameter>flatp</parameter></term>
 792           <listitem>
 793             <para>A Boolean whose default value is &nil;.</para>
 794           </listitem>
 795         </varlistentry>
 796         <varlistentry>
 797           <term><parameter>result-types</parameter></term>
 798           <listitem>
 799             <para>A
 800             <glossterm linkend="gloss-field-types">field type
 801             specifier</glossterm>. The default is &nil;.
 802             </para>
 803             <para>
 804               The purpose of this argument is cause &clsql; to
 805               import SQL numeric fields into numeric Lisp objects
 806               rather than strings. This reduces the cost of
 807               allocating a temporary string and the &clsql; users'
 808               inconvenience of converting number strings into number
 809               objects.
 810             </para>
 811             <para>
 812               A value of <symbol>:auto</symbol> causes &clsql;
 813               to automatically convert SQL fields into a
 814               numeric format where applicable. The default value of
 815               &nil; causes all fields to be returned as strings
 816               regardless of the SQL type. Otherwise a list is expected
 817               which has a element for each field that specifies the
 818               conversion. Valid type identifiers are:
 819               <simplelist type="vert">
 820                 <member><symbol>:int</symbol> Field is imported as a
 821                 signed integer, from 8-bits to 64-bits depending
 822                 upon the field type.
 823                 </member>
 824                 <member><symbol>:double</symbol> Field is imported as a
 825                 double-float number.
 826                 </member>
 827                 <member><symbol>t</symbol> Field is imported as a
 828                 string.
 829                 </member>
 830               </simplelist>
 831               If the list is shorter than the number of fields, the a
 832               value of <symbol>t</symbol> is assumed for the field.
 833               If the list is longer than the number of fields, the
 834               extra elements are ignored.
 835             </para>
 836           </listitem>
 837         </varlistentry>
 838         <varlistentry>
 839           <term><parameter>field-names</parameter></term>
 840           <listitem>
 841           <para>
 842             A boolean with a default value of &t;. When &t;, this
 843             function returns a second value of a list of field
 844             names. When &nil;, this function only returns one value -
 845             the list of rows.
 846           </para>
 847           </listitem>
 848         </varlistentry>
 849         <varlistentry>
 850           <term><returnvalue>result</returnvalue></term>
 851           <listitem>
 852             <para>A list representing the result set obtained.  For
 853             each tuple in the result set, there is an element in
 854             this list, which is itself a list of all the attribute
 855             values in the tuple.</para>
 856           </listitem>
 857         </varlistentry>
 858       </variablelist>
 859     </refsect1>
 860     <refsect1>
 861       <title>Description</title>
 862       <para>
 863         Executes the SQL query expression
 864         <parameter>query-expression</parameter>, which may be an SQL
 865         expression or a string, on the supplied
 866         <parameter>database</parameter> which defaults to
 867         <symbol>*default-database*</symbol>. <parameter>result-types</parameter>
 868         is a list of symbols which specifies the lisp type for each
 869         field returned by <parameter>query-expression</parameter>.
 870       </para>
 871       <para>
 872         If <parameter>result-types</parameter> is &nil; all results
 873         are returned as strings whereas the default value of
 874         <symbol>:auto</symbol> means that the lisp types are
 875         automatically computed for each field.
 876       </para>
 877       <para>
 878         <parameter>field-names</parameter> is &t; by default which
 879         means that the second value returned is a list of strings
 880         representing the columns selected by
 881         <parameter>query-expression</parameter>. If
 882         <parameter>field-names</parameter> is &nil;, the list of column
 883         names is not returned as a second value.
 884       </para>
 885       <para>
 886         <parameter>flatp</parameter> has a default value of &nil;
 887         which means that the results are returned as a list of
 888         lists.If FLATP is &t; and only one result is returned for each
 889         record selected by <parameter>query-expression</parameter>,
 890         the results are returned as elements of a list.
 891       </para>
 892     </refsect1>
 893     <refsect1>
 894       <title>Examples</title>
 895       <screen>
 896 (query "select emplid,first_name,last_name,height from employee where emplid = 1")
 897 => ((1 "Vladamir" "Lenin" 1.5564661d0)),
 898    ("emplid" "first_name" "last_name" "height")
 899
 900 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
 901        :field-names nil)
 902 => ((1 "Vladamir" "Lenin" 1.5564661d0))
 903
 904 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
 905        :field-names nil
 906        :result-types nil)
 907 => (("1" "Vladamir" "Lenin" "1.5564661"))
 908
 909 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
 910        :field-names nil
 911        :result-types '(:int t t :double))
 912 => ((1 "Vladamir" "Lenin" 1.5564661))
 913
 914 (query "select last_name from employee where emplid > 5" :flatp t)
 915 => ("Andropov" "Chernenko" "Gorbachev" "Yeltsin" "Putin"),
 916    ("last_name")
 917
 918 (query "select last_name from employee where emplid > 10"
 919        :flatp t
 920        :field-names nil)
 921 => NIL
 922       </screen>
 923     </refsect1>
 924     <refsect1>
 925       <title>Side Effects</title>
 926       <para>Whatever effects the execution of the SQL query has
 927       on the underlying database, if any.</para>
 928     </refsect1>
 929     <refsect1>
 930       <title>Affected by</title>
 931       <para>None.</para>
 932     </refsect1>
 933     <refsect1>
 934       <title>Exceptional Situations</title>
 935       <para>If the execution of the SQL query leads to any errors, an
 936       error of type <errortype>sql-database-error</errortype> is
 937       signalled.</para>
 938     </refsect1>
 939     <refsect1>
 940       <title>See Also</title>
 941       <simplelist>
 942         <member><link linkend="execute-command"><function>execute-command</function></link></member>
 943         <member><link linkend="print-query"><function>print-query</function></link></member>
 944         <member><link linkend="do-query"><function>do-query</function></link></member>
 945         <member><link linkend="map-query"><function>map-query</function></link></member>
 946         <member><link linkend="loop-tuples"><function>loop</function></link></member>
 947         <member><link linkend="select"><function>select</function></link></member>
 948       </simplelist>
 949     </refsect1>
 950     <refsect1>
 951       <title>Notes</title>
 952       <para>The <parameter>field-names</parameter> and
 953       <parameter>result-types</parameter> keyword arguments are a
 954       &clsql; extension.</para>
 955     </refsect1>
 956   </refentry>
 957
 958   <refentry id="print-query">
 959     <refmeta>
 960       <refentrytitle>PRINT-QUERY</refentrytitle>
 961     </refmeta>
 962     <refnamediv>
 963       <refname><emphasis>Function</emphasis> <emphasis role="bold">PRINT-QUERY</emphasis></refname>
 964       <refpurpose>Prints a tabular report of query results.</refpurpose>
 965       <refclass>Function</refclass>
 966     </refnamediv>
 967     <refsect1>
 968       <title>Syntax</title>
 969       <synopsis>
 970       <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>
 971     </refsect1>
 972     <refsect1>
 973       <title>Arguments and Values</title>
 974       <variablelist>
 975         <varlistentry>
 976           <term><parameter>query-expression</parameter></term>
 977           <listitem>
 978             <para>An <glossterm linkend="gloss-sql-expression">sql
 979             expression</glossterm> that represents an SQL
 980             query which is expected to return a (possibly empty)
 981             result set.</para>
 982           </listitem>
 983         </varlistentry>
 984         <varlistentry>
 985           <term><parameter>database</parameter></term>
 986           <listitem>
 987             <para>A
 988             <glossterm linkend="gloss-database-object">database
 989             object</glossterm>. This will default to the value
 990             of <symbol>*default-database*</symbol>.</para>
 991           </listitem>
 992         </varlistentry>
 993         <varlistentry>
 994           <term><parameter>titles</parameter></term>
 995           <listitem>
 996             <para>
 997               A list of strings or &nil; which is the default value.
 998             </para>
 999           </listitem>
1000         </varlistentry>
1001         <varlistentry>
1002           <term><parameter>formats</parameter></term>
1003           <listitem>
1004             <para>
1005               A list of strings, &nil; or &t; which is the default value.
1006             </para>
1007           </listitem>
1008         </varlistentry>
1009         <varlistentry>
1010           <term><parameter>sizes</parameter></term>
1011           <listitem>
1012             <para>
1013               A list of numbers, &nil; or &t; which is the default value.
1014             </para>
1015           </listitem>
1016         </varlistentry>
1017         <varlistentry>
1018           <term><parameter>stream</parameter></term>
1019           <listitem>
1020             <para>
1021               An output stream or &t; which is the default value.
1022             </para>
1023           </listitem>
1024         </varlistentry>
1025       </variablelist>
1026     </refsect1>
1027     <refsect1>
1028       <title>Description</title>
1029       <para>Prints a tabular report of the results returned by the SQL
1030       query <parameter>query-expression</parameter>, which may be a
1031       symbolic SQL expression or a string, in
1032       <parameter>database</parameter> which defaults to
1033       <symbol>*default-database*</symbol>. The report is printed onto
1034       <parameter>stream</parameter> which has a default value of &t;
1035       which means that <symbol>*standard-output*</symbol> is used. The
1036       <parameter>title</parameter> argument, which defaults to &nil;,
1037       allows the specification of a list of strings to use as column
1038       titles in the tabular output. <parameter>sizes</parameter>
1039       accepts a list of column sizes, one for each column selected by
1040       <parameter>query-expression</parameter>, to use in formatting
1041       the tabular report. The default value of &t; means that minimum
1042       sizes are computed. <parameter>formats</parameter> is a list of
1043       format strings to be used for printing each column selected by
1044       <parameter>query-expression</parameter>. The default value of
1045       <parameter>formats</parameter> is &t; meaning that
1046       <symbol>~A</symbol> is used to format all columns or
1047       <symbol>~VA</symbol> if column sizes are used.
1048       </para>
1049     </refsect1>
1050     <refsect1>
1051       <title>Examples</title>
1052       <screen>
1053 (print-query [select [emplid] [first-name] [last-name] [email]
1054                      :from [employee]
1055                      :where [&lt; [emplid] 5]]
1056               :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
1057 ID FORENAME SURNAME  EMAIL
1058 1  Vladamir Lenin    lenin@soviet.org
1059 2  Josef    Stalin   stalin@soviet.org
1060 3  Leon     Trotsky  trotsky@soviet.org
1061 4  Nikita   Kruschev kruschev@soviet.org
1062 =>
1063
1064 (print-query "select emplid,first_name,last_name,email from employee where emplid >= 5"
1065              :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
1066 ID FORENAME   SURNAME   EMAIL
1067 5  Leonid     Brezhnev  brezhnev@soviet.org
1068 6  Yuri       Andropov  andropov@soviet.org
1069 7  Konstantin Chernenko chernenko@soviet.org
1070 8  Mikhail    Gorbachev gorbachev@soviet.org
1071 9  Boris      Yeltsin   yeltsin@soviet.org
1072 10 Vladamir   Putin     putin@soviet.org
1073 =>
1074 </screen>
1075     </refsect1>
1076     <refsect1>
1077       <title>Side Effects</title>
1078       <para>
1079         None.
1080       </para>
1081     </refsect1>
1082     <refsect1>
1083       <title>Affected by</title>
1084       <para>
1085         None.
1086       </para>
1087     </refsect1>
1088     <refsect1>
1089       <title>Exceptional Situations</title>
1090       <para>
1091         If the execution of the SQL query leads to any errors, an
1092         error of type <errortype>sql-database-error</errortype> is
1093         signalled.
1094       </para>
1095     </refsect1>
1096     <refsect1>
1097       <title>See Also</title>
1098       <simplelist>
1099         <member><link linkend="query"><function>query</function></link></member>
1100         <member><link linkend="do-query"><function>do-query</function></link></member>
1101         <member><link linkend="map-query"><function>map-query</function></link></member>
1102         <member><link linkend="loop-tuples"><function>loop</function></link></member>
1103         <member><link linkend="select"><function>select</function></link></member>
1104       </simplelist>
1105     </refsect1>
1106     <refsect1>
1107       <title>Notes</title>
1108       <para>
1109         None.
1110       </para>
1111     </refsect1>
1112   </refentry>
1113
1114   <refentry id="select">
1115     <refmeta>
1116       <refentrytitle>SELECT</refentrytitle>
1117     </refmeta>
1118     <refnamediv>
1119       <refname><emphasis>Function</emphasis> <emphasis role="bold">SELECT</emphasis></refname>
1120       <refpurpose>Executes a query given the supplied constraints.</refpurpose>
1121       <refclass>Function</refclass>
1122     </refnamediv>
1123     <refsect1>
1124       <title>Syntax</title>
1125       <synopsis>
1126       <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>
1127     </refsect1>
1128     <refsect1>
1129       <title>Arguments and Values</title>
1130       <variablelist>
1131         <varlistentry>
1132           <term><parameter>identifiers</parameter></term>
1133           <listitem>
1134             <para>
1135               A set of <glossterm linkend="gloss-sql-expression">sql
1136               expressions</glossterm> each of which indicates a column
1137               to query.
1138             </para>
1139           </listitem>
1140         </varlistentry>
1141         <varlistentry>
1142           <term><parameter>all</parameter></term>
1143           <listitem>
1144             <para>
1145               A Boolean.
1146             </para>
1147           </listitem>
1148         </varlistentry>
1149         <varlistentry>
1150           <term><parameter>distinct</parameter></term>
1151           <listitem>
1152             <para>
1153               A Boolean.
1154             </para>
1155           </listitem>
1156         </varlistentry>
1157         <varlistentry>
1158           <term><parameter>from</parameter></term>
1159           <listitem>
1160             <para>
1161               One or more SQL expression representing tables.
1162             </para>
1163           </listitem>
1164         </varlistentry>
1165         <varlistentry>
1166           <term><parameter>group-by</parameter></term>
1167           <listitem>
1168             <para>
1169               An SQL expression.
1170             </para>
1171           </listitem>
1172         </varlistentry>
1173         <varlistentry>
1174           <term><parameter>having</parameter></term>
1175           <listitem>
1176             <para>
1177               An SQL expression.
1178             </para>
1179           </listitem>
1180         </varlistentry>
1181         <varlistentry>
1182           <term><parameter>order-by</parameter></term>
1183           <listitem>
1184             <para>
1185               An SQL expression.
1186             </para>
1187           </listitem>
1188         </varlistentry>
1189         <varlistentry>
1190           <term><parameter>set-operation</parameter></term>
1191           <listitem>
1192             <para>
1193               An SQL expression.
1194             </para>
1195           </listitem>
1196         </varlistentry>
1197         <varlistentry>
1198           <term><parameter>where</parameter></term>
1199           <listitem>
1200             <para>
1201               An SQL expression.
1202             </para>
1203           </listitem>
1204         </varlistentry>
1205         <varlistentry>
1206           <term><parameter>database</parameter></term>
1207           <listitem>
1208             <para>A
1209             <glossterm linkend="gloss-database-object">database
1210             object</glossterm>. This will default to the value
1211             of <symbol>*default-database*</symbol>.</para>
1212           </listitem>
1213         </varlistentry>
1214         <varlistentry>
1215           <term><parameter>flatp</parameter></term>
1216           <listitem>
1217             <para>A Boolean whose default value is &nil;.</para>
1218           </listitem>
1219         </varlistentry>
1220         <varlistentry>
1221           <term><parameter>result-types</parameter></term>
1222           <listitem>
1223             <para>A
1224             <glossterm linkend="gloss-field-types">field type
1225             specifier</glossterm>. The default is &nil;.
1226             </para>
1227             <para>
1228               The purpose of this argument is cause &clsql; to
1229               import SQL numeric fields into numeric Lisp objects
1230               rather than strings. This reduces the cost of
1231               allocating a temporary string and the &clsql; users'
1232               inconvenience of converting number strings into number
1233               objects.
1234             </para>
1235             <para>
1236               A value of <symbol>:auto</symbol> causes &clsql;
1237               to automatically convert SQL fields into a
1238               numeric format where applicable. The default value of
1239               &nil; causes all fields to be returned as strings
1240               regardless of the SQL type. Otherwise a list is expected
1241               which has a element for each field that specifies the
1242               conversion. Valid type identifiers are:
1243               <simplelist type="vert">
1244                 <member><symbol>:int</symbol> Field is imported as a
1245                 signed integer, from 8-bits to 64-bits depending
1246                 upon the field type.
1247                 </member>
1248                 <member><symbol>:double</symbol> Field is imported as a
1249                 double-float number.
1250                 </member>
1251                 <member><symbol>t</symbol> Field is imported as a
1252                 string.
1253                 </member>
1254               </simplelist>
1255               If the list is shorter than the number of fields, the a
1256               value of <symbol>t</symbol> is assumed for the field.
1257               If the list is longer than the number of fields, the
1258               extra elements are ignored.
1259             </para>
1260           </listitem>
1261         </varlistentry>
1262         <varlistentry>
1263           <term><parameter>field-names</parameter></term>
1264           <listitem>
1265           <para>
1266             A boolean with a default value of &t;. When &t;, this
1267             function returns a second value of a list of field
1268             names. When &nil;, this function only returns one value -
1269             the list of rows.
1270           </para>
1271           </listitem>
1272         </varlistentry>
1273         <varlistentry>
1274           <term><parameter>result</parameter></term>
1275           <listitem>
1276             <para>
1277               A list representing the result set obtained.  For each
1278               tuple in the result set, there is an element in this
1279               list, which is itself a list of all the attribute values
1280               in the tuple.
1281             </para>
1282           </listitem>
1283         </varlistentry>
1284       </variablelist>
1285     </refsect1>
1286     <refsect1>
1287       <title>Description</title>
1288       <para>
1289         Executes a query on <parameter>database</parameter>, which has
1290         a default value of <symbol>*default-database*</symbol>,
1291         specified by the SQL expressions supplied using the remaining
1292         arguments in <parameter>args</parameter>. The
1293         <function>select</function> function can be used to generate
1294         queries in both functional and object oriented contexts.
1295       </para>
1296       <para>
1297         In the functional case, the required arguments specify the
1298         columns selected by the query and may be symbolic SQL
1299         expressions or strings representing attribute
1300         identifiers. Type modified identifiers indicate that the
1301         values selected from the specified column are converted to the
1302         specified lisp type. The keyword arguments
1303         <parameter>all</parameter>, <parameter>distinct</parameter>,
1304         <parameter>from</parameter>, <parameter>group-by</parameter>,
1305         <parameter>having</parameter>,
1306         <parameter>order-by</parameter>,
1307         <parameter>set-operation</parameter> and
1308         <parameter>where</parameter> are used to specify, using the
1309         symbolic SQL syntax, the corresponding components of the SQL
1310         query generated by the call to
1311         <function>select</function>.
1312       </para>
1313       <para>
1314         <parameter>result-types</parameter> is a list of symbols which
1315         specifies the lisp type for each field returned by the
1316         query. If <parameter>result-types</parameter> is &nil; all
1317         results are returned as strings whereas the default value of
1318         <symbol>:auto</symbol> means that the lisp types are
1319         automatically computed for each
1320         field. <parameter>field-names</parameter> is &t; by default
1321         which means that the second value returned is a list of
1322         strings representing the columns selected by the query. If
1323         <parameter>field-names</parameter> is &nil;, the list of
1324         column names is not returned as a second value.
1325       </para>
1326       <para>
1327         In the object oriented case, the required arguments to
1328         <function>select</function> are symbols denoting View Classes
1329         which specify the database tables to query. In this case,
1330         <function>select</function> returns a list of View Class
1331         instances whose slots are set from the attribute values of the
1332         records in the specified table. <symbol>Slot-value</symbol> is
1333         a legal operator which can be employed as part of the symbolic
1334         SQL syntax used in the <parameter>where</parameter> keyword
1335         argument to <function>select</function>.
1336         <parameter>refresh</parameter> is &nil; by default which means
1337         that the View Class instances returned are retrieved from a
1338         cache if an equivalent call to <function>select</function> has
1339         previously been issued. If <parameter>refresh</parameter> is
1340         true, the View Class instances returned are updated as
1341         necessary from the database and the generic function
1342         <function>instance-refreshed</function> is called to perform
1343         any necessary operations on the updated instances.
1344       </para>
1345       <para>
1346         In both object oriented and functional contexts,
1347         <parameter>flatp</parameter> has a default value of &nil;
1348         which means that the results are returned as a list of
1349         lists. If <parameter>flatp</parameter> is t and only one
1350         result is returned for each record selected in the query, the
1351         results are returned as elements of a list.
1352       </para>
1353     </refsect1>
1354     <refsect1>
1355       <title>Examples</title>
1356       <screen>
1357 (select [first-name] :from [employee] :flatp t :distinct t
1358                      :field-names nil
1359                      :result-types nil
1360                      :order-by [first-name])
1361 => ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
1362     "Yuri")
1363
1364 (select [first-name] [count [*]] :from [employee]
1365                                  :result-types nil
1366                                  :group-by [first-name]
1367                                  :order-by [first-name]
1368                                  :field-names nil)
1369 => (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
1370     ("Mikhail" "1") ("Nikita" "1") ("Vladamir" "2") ("Yuri" "1"))
1371
1372 (select [last-name] :from [employee]
1373                     :where [like [email] "%org"]
1374                     :order-by [last-name]
1375                     :field-names nil
1376                     :result-types nil
1377                     :flatp t)
1378 => ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
1379     "Stalin" "Trotsky" "Yeltsin")
1380
1381 (select [max [emplid]] :from [employee]
1382                        :flatp t
1383                        :field-names nil
1384                        :result-types :auto)
1385 => (10)
1386
1387 (clsql:select [avg [height]] :from [employee] :flatp t :field-names nil)
1388 => (1.58999584d0)
1389
1390 (select [emplid] [last-name] :from [employee] :where [= [emplid] 1])
1391 => ((1 "Lenin")),
1392    ("emplid" "last_name")
1393
1394 (select [emplid :string] :from [employee]
1395                          :where [= 1 [emplid]]
1396                          :field-names nil
1397                          :flatp t)
1398 => ("1")
1399
1400 (select [emplid] :from [employee] :order-by [emplid]
1401                  :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]]
1402                  :field-names nil
1403                  :flatp t)
1404 => (1 2 3 4)
1405
1406 (clsql:select [emplid] :from [employee]
1407                        :where [in [emplid] '(1 2 3 4)]
1408                        :flatp t
1409                        :order-by [emplid]
1410                        :field-names nil)
1411 => (1 2 3 4)
1412
1413 (select [first-name] [last-name] :from [employee]
1414         :field-names nil
1415         :order-by '(([first-name] :asc) ([last-name] :desc)))
1416 => (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
1417     ("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
1418     ("Nikita" "Kruschev") ("Vladamir" "Putin") ("Vladamir" "Lenin")
1419     ("Yuri" "Andropov"))
1420
1421 (select [last-name] :from [employee]
1422                  :set-operation [union [select [first-name] :from [employee]
1423                                                :order-by [last-name]]]
1424                  :flatp t
1425                  :result-types nil
1426                  :field-names nil)
1427 => ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
1428     "Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
1429     "Trotsky" "Vladamir" "Yeltsin" "Yuri")
1430       </screen>
1431     </refsect1>
1432     <refsect1>
1433       <title>Side Effects</title>
1434       <para>
1435         <para>Whatever effects the execution of the SQL query has on
1436         the underlying database, if any.</para>
1437       </para>
1438     </refsect1>
1439     <refsect1>
1440       <title>Affected by</title>
1441       <para>
1442         None.
1443       </para>
1444     </refsect1>
1445     <refsect1>
1446       <title>Exceptional Situations</title>
1447       <para>
1448         If the execution of the SQL query leads to any errors, an
1449         error of type <errortype>sql-database-error</errortype> is
1450         signalled.
1451       </para>
1452     </refsect1>
1453     <refsect1>
1454       <title>See Also</title>
1455       <simplelist>
1456         <member><link linkend="query"><function>query</function></link></member>
1457         <member><link linkend="print-query"><function>print-query</function></link></member>
1458         <member><link linkend="do-query"><function>do-query</function></link></member>
1459         <member><link linkend="map-query"><function>map-query</function></link></member>
1460         <member><link linkend="loop-tuples"><function>loop</function></link></member>
1461         <member><link linkend="instance-refreshed"><function>instance-refreshed</function></link></member>
1462       </simplelist>
1463     </refsect1>
1464     <refsect1>
1465       <title>Notes</title>
1466       <para>
1467         The <parameter>field-names</parameter> and
1468         <parameter>result-types</parameter> keyword arguments are a
1469         &clsql; extension.
1470       </para>
1471       <para>
1472         <parameter>select</parameter> is common across the functional
1473         and object-oriented data manipulation languages.
1474       </para>
1475     </refsect1>
1476   </refentry>
1477
1478
1479   <!-- iteration and mapping -->
1480
1481   <refentry id="do-query">
1482     <refmeta>
1483       <refentrytitle>DO-QUERY</refentrytitle>
1484     </refmeta>
1485     <refnamediv>
1486       <refname><emphasis>Macro</emphasis> <emphasis role="bold">DO-QUERY</emphasis></refname>
1487       <refpurpose>Iterate over all the tuples of a query.</refpurpose>
1488       <refclass>Macro</refclass>
1489     </refnamediv>
1490     <refsect1>
1491       <title>Syntax</title>
1492       <synopsis>
1493       <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>
1494     </refsect1>
1495     <refsect1>
1496       <title>Arguments and Values</title>
1497       <variablelist>
1498         <varlistentry>
1499           <term><parameter>args</parameter></term>
1500           <listitem>
1501             <para>A list of variable names.</para>
1502           </listitem>
1503         </varlistentry>
1504         <varlistentry>
1505           <term><parameter>query-expression</parameter></term>
1506           <listitem>
1507             <para>An <glossterm linkend="gloss-sql-expression">sql
1508             expression</glossterm> that represents an SQL
1509             query which is expected to return a (possibly empty)
1510             result set, where each tuple has as many attributes as
1511             <parameter>function</parameter> takes arguments.</para>
1512           </listitem>
1513         </varlistentry>
1514         <varlistentry>
1515           <term><parameter>database</parameter></term>
1516           <listitem>
1517             <para>A
1518             <glossterm linkend="gloss-database-object">database
1519             object</glossterm>. This will default to
1520             <symbol>*default-database*</symbol>.</para>
1521           </listitem>
1522         </varlistentry>
1523         <varlistentry>
1524           <term><parameter>result-types</parameter></term>
1525           <listitem>
1526             <para>
1527               A <glossterm linkend="gloss-field-types">field type
1528               specifier</glossterm>.  The default is &nil;. See <link
1529               linkend="query"><function>query</function></link> for
1530               the semantics of this argument.
1531             </para>
1532           </listitem>
1533         </varlistentry>
1534         <varlistentry>
1535           <term><parameter>body</parameter></term>
1536           <listitem>
1537             <para>A body of Lisp code, like in a
1538             <function>destructuring-bind</function> form.</para>
1539           </listitem>
1540         </varlistentry>
1541         <varlistentry>
1542           <term><parameter>result</parameter></term>
1543           <listitem>
1544             <para>The result of executing <parameter>body</parameter>.</para>
1545           </listitem>
1546         </varlistentry>
1547       </variablelist>
1548     </refsect1>
1549     <refsect1>
1550       <title>Description</title>
1551       <para>
1552         Repeatedly executes <parameter>body</parameter> within a
1553         binding of <parameter>args</parameter> on the fields of each
1554         row selected by the SQL query
1555         <parameter>query-expression</parameter>, which may be a string
1556         or a symbolic SQL expression, in
1557         <parameter>database</parameter> which defaults to
1558         <symbol>*default-database*</symbol>.
1559       </para>
1560       <para>
1561         The body of code is executed in a block named
1562         <symbol>nil</symbol> which may be returned from prematurely
1563         via <function>return</function> or
1564         <function>return-from</function>.  In this case the result of
1565         evaluating the <function>do-query</function> form will be the
1566         one supplied to <function>return</function> or
1567         <function>return-from</function>.  Otherwise the result will
1568         be <symbol>nil</symbol>.
1569       </para>
1570       <para>
1571         The body of code appears also is if wrapped in a
1572         <function>destructuring-bind</function> form, thus allowing
1573         declarations at the start of the body, especially those
1574         pertaining to the bindings of the variables named in
1575         <parameter>args</parameter>.
1576       </para>
1577       <para>
1578         <parameter>result-types</parameter> is a list of symbols which
1579         specifies the lisp type for each field returned by
1580         <parameter>query-expression</parameter>. If
1581         <parameter>result-types</parameter> is &nil; all results are
1582         returned as strings whereas the default value of
1583         <symbol>:auto</symbol> means that the lisp types are
1584         automatically computed for each field.
1585       </para>
1586       <para>
1587         <parameter>query-expression</parameter> may be an object query
1588         (i.e., the selection arguments refer to View Classes), in
1589         which case <parameter>args</parameter> are bound to the tuples
1590         of View Class instances returned by the object oriented query.
1591       </para>
1592     </refsect1>
1593     <refsect1>
1594       <title>Examples</title>
1595       <screen>
1596 (do-query ((salary name) "select salary,name from simple")
1597   (format t "~30A gets $~2,5$~%" name (read-from-string salary)))
1598 >> Mai, Pierre                    gets $10000.00
1599 >> Hacker, Random J.              gets $08000.50
1600 => NIL
1601
1602 (do-query ((salary name) "select salary,name from simple")
1603   (return (cons salary name)))
1604 => ("10000.00" . "Mai, Pierre")
1605
1606 (let ((result '()))
1607   (do-query ((name) [select [last-name] :from [employee]
1608                             :order-by [last-name]])
1609     (push name result))
1610   result)
1611 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1612     "Chernenko" "Brezhnev" "Andropov")
1613
1614 (let ((result '()))
1615   (do-query ((e) [select 'employee :order-by [last-name]])
1616     (push (slot-value e 'last-name) result))
1617   result)
1618 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1619     "Chernenko" "Brezhnev" "Andropov")
1620       </screen>
1621     </refsect1>
1622     <refsect1>
1623       <title>Side Effects</title>
1624       <para>Whatever effects the execution of the SQL query has
1625       on the underlying database, if any.</para>
1626     </refsect1>
1627     <refsect1>
1628       <title>Affected by</title>
1629       <para>None.</para>
1630     </refsect1>
1631     <refsect1>
1632       <title>Exceptional Situations</title>
1633       <para>If the execution of  the SQL query leads to any
1634       errors, an error of type
1635       <errortype>sql-database-error</errortype> is signalled.</para>
1636       <para>If the number of variable names in
1637       <parameter>args</parameter> and the number of attributes in
1638       the tuples in the result set don't match up, an error is
1639       signalled.</para>
1640     </refsect1>
1641     <refsect1>
1642       <title>See Also</title>
1643       <simplelist>
1644         <member><link linkend="query"><function>query</function></link></member>
1645         <member><link linkend="map-query"><function>map-query</function></link></member>
1646         <member><link linkend="print-query"><function>print-query</function></link></member>
1647         <member><link linkend="loop-tuples"><function>loop</function></link></member>
1648         <member><link linkend="select"><function>select</function></link></member>
1649       </simplelist>
1650     </refsect1>
1651     <refsect1>
1652       <title>Notes</title>
1653       <para>The <parameter>result-types</parameter> keyword argument
1654       is a &clsql; extension.</para>
1655       <para>
1656         <parameter>do-query</parameter> is common across the functional
1657         and object-oriented data manipulation languages.
1658       </para>
1659     </refsect1>
1660   </refentry>
1661
1662   <refentry id="for-each-row">
1663     <refmeta>
1664       <refentrytitle>FOR-EACH-ROW</refentrytitle>
1665     </refmeta>
1666     <refnamediv>
1667       <refname><emphasis>Function</emphasis> <emphasis role="bold">FOR-EACH-ROW</emphasis></refname>
1668       <refpurpose><!-- purpose --></refpurpose>
1669       <refclass>Function</refclass>
1670     </refnamediv>
1671     <refsect1>
1672       <title>Syntax</title>
1673       <synopsis>
1674       <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>
1675     </refsect1>
1676     <refsect1>
1677       <title>Arguments and Values</title>
1678       <variablelist>
1679         <!-- arguments and values -->
1680       </variablelist>
1681     </refsect1>
1682     <refsect1>
1683       <title>Description</title>
1684       <para>
1685         <!-- description -->
1686       </para>
1687     </refsect1>
1688     <refsect1>
1689       <title>Examples</title>
1690       <screen>
1691         <!-- examples -->
1692       </screen>
1693     </refsect1>
1694     <refsect1>
1695       <title>Side Effects</title>
1696       <para>
1697         <!-- side effects -->
1698       </para>
1699     </refsect1>
1700     <refsect1>
1701       <title>Affected by</title>
1702       <para>
1703         <simplelist>
1704           <!-- affected by -->
1705         </simplelist>
1706       </para>
1707     </refsect1>
1708     <refsect1>
1709       <title>Exceptional Situations</title>
1710       <para>
1711         <!-- execeptional situations -->
1712       </para>
1713     </refsect1>
1714     <refsect1>
1715       <title>See Also</title>
1716       <simplelist>
1717         <member><link linkend="query"><function>query</function></link></member>
1718         <member><link linkend="print-query"><function>print-query</function></link></member>
1719         <member><link linkend="do-query"><function>do-query</function></link></member>
1720         <member><link linkend="map-query"><function>map-query</function></link></member>
1721         <member><link linkend="loop-tuples"><function>loop</function></link></member>
1722         <member><link linkend="select"><function>select</function></link></member>
1723         </simplelist>
1724     </refsect1>
1725     <refsect1>
1726       <title>Notes</title>
1727       <para>
1728         <!-- notes here -->
1729       </para>
1730     </refsect1>
1731   </refentry>
1732
1733   <refentry id="loop-tuples">
1734     <refmeta>
1735       <refentrytitle>LOOP</refentrytitle>
1736     </refmeta>
1737     <refnamediv>
1738       <refname><emphasis>Additional clause</emphasis> for <emphasis role="bold">LOOP</emphasis></refname>
1739       <refpurpose>Iterate over all the tuples of a
1740       query via a loop clause.</refpurpose>
1741       <refclass>Loop Clause</refclass>
1742     </refnamediv>
1743     <!-- refsect1>
1744       <title>Compatibility</title>
1745       <caution><para><function>loop-for-as-tuples</function> only works with &cmucl;.</para></caution>
1746     </refsect1 -->
1747     <refsect1>
1748       <title>Syntax</title>
1749       <synopsis>{as | for} <replaceable>var</replaceable> [<replaceable>type-spec</replaceable>] being {each | the} {record | records | tuple | tuples} {in | of} <replaceable>query</replaceable> [from <replaceable>database</replaceable>]</synopsis>
1750     </refsect1>
1751     <refsect1>
1752       <title>Arguments and Values</title>
1753       <variablelist>
1754         <varlistentry>
1755           <term><parameter>var</parameter></term>
1756           <listitem>
1757             <para>A <literal>d-var-spec</literal>, as defined in the
1758             grammar for <function>loop</function>-clauses in the ANSI
1759             Standard for Common Lisp.  This allows for the usual
1760             loop-style destructuring.</para>
1761           </listitem>
1762         </varlistentry>
1763         <varlistentry>
1764           <term><parameter>type-spec</parameter></term>
1765           <listitem>
1766             <para>An optional <literal>type-spec</literal> either
1767             simple or destructured, as defined in the grammar for
1768             <function>loop</function>-clauses in the ANSI Standard for
1769             Common Lisp.</para>
1770           </listitem>
1771         </varlistentry>
1772         <varlistentry>
1773           <term><parameter>query</parameter></term>
1774           <listitem>
1775             <para>An <glossterm linkend="gloss-sql-expression">sql
1776             expression</glossterm> that represents an SQL
1777             query which is expected to return a (possibly empty)
1778             result set, where each tuple has as many attributes as
1779             <parameter>function</parameter> takes arguments.</para>
1780           </listitem>
1781         </varlistentry>
1782         <varlistentry>
1783           <term><parameter>database</parameter></term>
1784           <listitem>
1785             <para>An optional
1786             <glossterm linkend="gloss-database-object">database
1787             object</glossterm>. This will default to the value
1788             of <symbol>*default-database*</symbol>.</para>
1789           </listitem>
1790         </varlistentry>
1791       </variablelist>
1792     </refsect1>
1793     <refsect1>
1794       <title>Description</title>
1795       <para>This clause is an iteration driver for
1796       <function>loop</function>, that binds the given variable
1797       (possibly destructured) to the consecutive tuples (which are
1798       represented as lists of attribute values) in the result set
1799       returned by executing the SQL <parameter>query</parameter>
1800       expression on the <parameter>database</parameter>
1801       specified.</para>
1802       <para>
1803         <parameter>query</parameter> may be an object query (i.e., the
1804         selection arguments refer to View Classes), in which case the
1805         supplied variable is bound to the tuples of View Class
1806         instances returned by the object oriented query.
1807       </para>
1808     </refsect1>
1809     <refsect1>
1810       <title>Examples</title>
1811       <screen>
1812 (defvar *my-db* (connect '("dent" "newesim" "dent" "dent"))
1813 "My database"
1814 => *MY-DB*
1815 (loop with time-graph = (make-hash-table :test #'equal)
1816    with event-graph = (make-hash-table :test #'equal)
1817    for (time event) being the tuples of "select time,event from log"
1818    from *my-db*
1819    do
1820      (incf (gethash time time-graph 0))
1821      (incf (gethash event event-graph 0))
1822    finally
1823      (flet ((show-graph (k v) (format t "~40A => ~5D~%" k v)))
1824        (format t "~&amp;Time-Graph:~%===========~%")
1825        (maphash #'show-graph time-graph)
1826        (format t "~&amp;~%Event-Graph:~%============~%")
1827        (maphash #'show-graph event-graph))
1828      (return (values time-graph event-graph)))
1829 >> Time-Graph:
1830 >> ===========
1831 >> D                                        => 53000
1832 >> X                                        =>     3
1833 >> test-me                                  =>  3000
1834 >>
1835 >> Event-Graph:
1836 >> ============
1837 >> CLOS Benchmark entry.                    =>  9000
1838 >> Demo Text...                             =>     3
1839 >> doit-text                                =>  3000
1840 >> C    Benchmark entry.                    => 12000
1841 >> CLOS Benchmark entry                     => 32000
1842 => #&lt;EQUAL hash table, 3 entries {48350A1D}>
1843 => #&lt;EQUAL hash table, 5 entries {48350FCD}>
1844
1845 (loop for (forename surname)
1846       being each tuple in
1847         [select [first-name] [last-name] :from [employee]
1848                 :order-by [last-name]]
1849       collect (concatenate 'string forename " " surname))
1850 => ("Yuri Andropov" "Leonid Brezhnev" "Konstantin Chernenko" "Mikhail Gorbachev"
1851     "Nikita Kruschev" "Vladamir Lenin" "Vladamir Putin" "Josef Stalin"
1852     "Leon Trotsky" "Boris Yeltsin")
1853
1854 (loop for (e) being the records in
1855      [select 'employee :where [&lt; [emplid] 4] :order-by [emplid]]
1856   collect (slot-value e 'last-name))
1857 => ("Lenin" "Stalin" "Trotsky")
1858       </screen>
1859     </refsect1>
1860     <refsect1>
1861       <title>Side Effects</title>
1862       <para>Whatever effects the execution of the SQL query has
1863       on the underlying database, if any.</para>
1864     </refsect1>
1865     <refsect1>
1866       <title>Affected by</title>
1867       <para>None.</para>
1868     </refsect1>
1869     <refsect1>
1870       <title>Exceptional Situations</title>
1871       <para>If the execution of  the SQL query leads to any
1872       errors, an error of type
1873       <errortype>sql-database-error</errortype> is signalled.</para>
1874       <para>Otherwise, any of the exceptional situations of
1875       <function>loop</function> applies.</para>
1876     </refsect1>
1877     <refsect1>
1878       <title>See Also</title>
1879       <para>
1880         <simplelist>
1881           <member><link linkend="query"><function>query</function></link></member>
1882           <member><link linkend="map-query"><function>map-query</function></link></member>
1883           <member><link linkend="do-query"><function>do-query</function></link></member>
1884         <member><link linkend="print-query"><function>print-query</function></link></member>
1885         <member><link linkend="select"><function>select</function></link></member>
1886         </simplelist>
1887       </para>
1888     </refsect1>
1889     <refsect1>
1890       <title>Notes</title>
1891       <para>The <parameter>database</parameter> loop keyword is a
1892       &clsql; extension.</para>
1893       <para>
1894         The extended <function>loop</function> syntax is common across
1895         the functional and object-oriented data manipulation
1896         languages.
1897       </para>
1898     </refsect1>
1899   </refentry>
1900
1901   <refentry id="map-query">
1902     <refmeta>
1903       <refentrytitle>MAP-QUERY</refentrytitle>
1904     </refmeta>
1905     <refnamediv>
1906       <refname><emphasis>Function</emphasis> <emphasis role="bold">MAP-QUERY</emphasis></refname>
1907       <refpurpose>Map a function over all the tuples from a
1908       query</refpurpose>
1909       <refclass>Function</refclass>
1910     </refnamediv>
1911     <refsect1>
1912       <title>Syntax</title>
1913       <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>
1914     </refsect1>
1915     <refsect1>
1916       <title>Arguments and Values</title>
1917       <variablelist>
1918         <varlistentry>
1919           <term><parameter>output-type-spec</parameter></term>
1920           <listitem>
1921             <para>A sequence type specifier or <symbol>nil</symbol>.</para>
1922           </listitem>
1923         </varlistentry>
1924         <varlistentry>
1925           <term><parameter>function</parameter></term>
1926           <listitem>
1927             <para>A function designator.
1928             <parameter>function</parameter> takes a single argument which
1929             is the atom value for a query single with a single column
1930             or is a list of values for a multi-column query.</para>
1931           </listitem>
1932         </varlistentry>
1933         <varlistentry>
1934           <term><parameter>query-expression</parameter></term>
1935           <listitem>
1936             <para>An <glossterm linkend="gloss-sql-expression">sql
1937             expression</glossterm> that represents an SQL
1938             query which is expected to return a (possibly empty)
1939             result set.</para>
1940           </listitem>
1941         </varlistentry>
1942         <varlistentry>
1943           <term><parameter>database</parameter></term>
1944           <listitem>
1945             <para>A
1946             <glossterm linkend="gloss-database-object">database
1947             object</glossterm>. This will default to the value
1948             of <symbol>*default-database*</symbol>.</para>
1949           </listitem>
1950         </varlistentry>
1951         <varlistentry>
1952           <term><parameter>result-types</parameter></term>
1953           <listitem>
1954             <para>
1955               A <glossterm linkend="gloss-field-types">field type
1956               specifier</glossterm>.  The default is &nil;. See <link
1957               linkend="query"><function>query</function></link> for
1958               the semantics of this argument.
1959             </para>
1960           </listitem>
1961         </varlistentry>
1962         <varlistentry>
1963           <term><returnvalue>result</returnvalue></term>
1964           <listitem>
1965             <para>If <parameter>output-type-spec</parameter> is a
1966             type specifier other than <symbol>nil</symbol>, then a
1967             sequence of the type it denotes.  Otherwise
1968             <symbol>nil</symbol> is returned.</para>
1969           </listitem>
1970         </varlistentry>
1971       </variablelist>
1972     </refsect1>
1973     <refsect1>
1974       <title>Description</title>
1975       <para>
1976         Applies <parameter>function</parameter> to the successive
1977         tuples in the result set returned by executing the SQL
1978         <parameter>query-expression</parameter>.  If the
1979         <parameter>output-type-spec</parameter> is
1980         <symbol>nil</symbol>, then the result of each application of
1981         <parameter>function</parameter> is discarded, and
1982         <function>map-query</function> returns <symbol>nil</symbol>.
1983         Otherwise the result of each successive application of
1984         <parameter>function</parameter> is collected in a sequence of
1985         type <parameter>output-type-spec</parameter>, where the jths
1986         element is the result of applying
1987         <parameter>function</parameter> to the attributes of the jths
1988         tuple in the result set.  The collected sequence is the result
1989         of the call to <function>map-query</function>.
1990       </para>
1991       <para>
1992         If the <parameter>output-type-spec</parameter> is a subtype of
1993         <type>list</type>, the result will be a <type>list</type>.
1994       </para>
1995       <para>
1996         If the <parameter>result-type</parameter> is a subtype of
1997         <type>vector</type>, then if the implementation can determine
1998         the element type specified for the
1999         <parameter>result-type</parameter>, the element type of the
2000         resulting array is the result of
2001         <emphasis>upgrading</emphasis> that element type; or, if the
2002         implementation can determine that the element type is
2003         unspecified (or <symbol>*</symbol>), the element type of the
2004         resulting array is <type>t</type>; otherwise, an error is
2005         signaled.
2006       </para>
2007       <para>
2008         If <parameter>result-types</parameter> is &nil; all results
2009         are returned as strings whereas the default value of
2010         <symbol>:auto</symbol> means that the lisp types are
2011         automatically computed for each field.</para>
2012       <para>
2013         <parameter>query-expression</parameter> may be an object query
2014         (i.e., the selection arguments refer to View Classes), in
2015         which case the supplied function is applied to the tuples of
2016         View Class instances returned by the object oriented query.
2017       </para>
2018     </refsect1>
2019     <refsect1>
2020       <title>Examples</title>
2021       <screen>
2022 (map-query 'list #'(lambda (tuple)
2023                      (multiple-value-bind (salary name) tuple
2024                         (declare (ignorable name))
2025                         (read-from-string salary)))
2026             "select salary,name from simple where salary > 8000")
2027 => (10000.0 8000.5)
2028
2029 (map-query '(vector double-float)
2030            #'(lambda (tuple)
2031                (multiple-value-bind (salary name) tuple
2032                   (declare (ignorable name))
2033                   (let ((*read-default-float-format* 'double-float))
2034                     (coerce (read-from-string salary) 'double-float))
2035            "select salary,name from simple where salary > 8000")))
2036 => #(10000.0d0 8000.5d0)
2037 (type-of *)
2038 => (SIMPLE-ARRAY DOUBLE-FLOAT (2))
2039
2040 (let (list)
2041   (values (map-query nil #'(lambda (tuple)
2042                              (multiple-value-bind (salary name) tuple
2043                                (push (cons name (read-from-string salary)) list))
2044                          "select salary,name from simple where salary > 8000"))
2045           list))
2046 => NIL
2047 => (("Hacker, Random J." . 8000.5) ("Mai, Pierre" . 10000.0))
2048
2049 (map-query 'vector #'identity
2050            [select [last-name] :from [employee] :flatp t
2051                    :order-by [last-name]])
2052 => #("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
2053      "Stalin" "Trotsky" "Yeltsin")
2054
2055 (map-query 'list #'identity
2056            [select [first-name] [last-name] :from [employee]
2057                    :order-by [last-name]])
2058 => (("Yuri" "Andropov") ("Leonid" "Brezhnev") ("Konstantin" "Chernenko")
2059     ("Mikhail" "Gorbachev") ("Nikita" "Kruschev") ("Vladamir" "Lenin")
2060     ("Vladamir" "Putin") ("Josef" "Stalin") ("Leon" "Trotsky")
2061     ("Boris" "Yeltsin"))
2062
2063 (map-query 'list #'last-name [select 'employee :order-by [emplid]])
2064 => ("Lenin" "Stalin" "Trotsky" "Kruschev" "Brezhnev" "Andropov" "Chernenko"
2065     "Gorbachev" "Yeltsin" "Putin")
2066       </screen>
2067     </refsect1>
2068     <refsect1>
2069       <title>Side Effects</title>
2070       <para>Whatever effects the execution of the SQL query has
2071       on the underlying database, if any.</para>
2072     </refsect1>
2073     <refsect1>
2074       <title>Affected by</title>
2075       <para>None.</para>
2076     </refsect1>
2077     <refsect1>
2078       <title>Exceptional Situations</title>
2079       <para>If the execution of  the SQL query leads to any
2080       errors, an error of type
2081       <errortype>sql-database-error</errortype> is signalled.</para>
2082       <para>An error of type <errortype>type-error</errortype> must
2083       be signaled if the <parameter>output-type-spec</parameter> is
2084       not a recognizable subtype of <type>list</type>, not a
2085       recognizable subtype of <type>vector</type>, and not
2086       <symbol>nil</symbol>.</para>
2087       <para>An error of type <errortype>type-error</errortype>
2088       should be signaled if
2089       <parameter>output-type-spec</parameter> specifies the number
2090       of elements and the size of the result set is different from
2091       that number.</para>
2092     </refsect1>
2093     <refsect1>
2094       <title>See Also</title>
2095       <simplelist>
2096         <member><link linkend="query"><function>query</function></link></member>
2097         <member><link linkend="do-query"><function>do-query</function></link></member>
2098         <member><link linkend="print-query"><function>print-query</function></link></member>
2099         <member><link linkend="loop-tuples"><function>loop</function></link></member>
2100         <member><link linkend="select"><function>select</function></link></member>
2101       </simplelist>
2102     </refsect1>
2103     <refsect1>
2104       <title>Notes</title>
2105       <para>The <parameter>result-types</parameter> keyword argument
2106       is a &clsql; extension.</para>
2107       <para>
2108         <parameter>map-query</parameter> is common across the
2109         functional and object-oriented data manipulation languages.
2110       </para>
2111     </refsect1>
2112   </refentry>
2113
2114
2115   <!-- prepared statements -->
2116
2117   <refentry id="prepare-sql">
2118     <refmeta>
2119       <refentrytitle>PREPARE-SQL</refentrytitle>
2120     </refmeta>
2121     <refnamediv>
2122       <refname><emphasis>Function</emphasis> <emphasis role="bold">PREPARE-SQL</emphasis></refname>
2123       <refpurpose>Create a prepared statement.</refpurpose>
2124       <refclass>Function</refclass>
2125     </refnamediv>
2126     <refsect1>
2127       <title>Syntax</title>
2128       <synopsis>
2129       <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>
2130     </refsect1>
2131     <refsect1>
2132       <title>Arguments and Values</title>
2133       <variablelist>
2134         <!-- arguments and values -->
2135       </variablelist>
2136     </refsect1>
2137     <refsect1>
2138       <title>Description</title>
2139       <para>Prepares a SQL statement <parameter>sql-stmt</parameter>
2140       for execution. <parameter>types</parameter> contains a list of
2141       types corresponding to the input parameters. Returns a
2142       prepared-statement object.
2143
2144       A type can be
2145       :int
2146       :double
2147       :null
2148       (:string n)
2149
2150       </para>
2151     </refsect1>
2152     <refsect1>
2153       <title>Examples</title>
2154       <screen>
2155         <!-- examples -->
2156       </screen>
2157     </refsect1>
2158     <refsect1>
2159       <title>Side Effects</title>
2160       <para>
2161         <!-- side effects -->
2162       </para>
2163     </refsect1>
2164     <refsect1>
2165       <title>Affected by</title>
2166       <para>
2167         <simplelist>
2168           <!-- affected by -->
2169         </simplelist>
2170       </para>
2171     </refsect1>
2172     <refsect1>
2173       <title>Exceptional Situations</title>
2174       <para>
2175         <!-- execeptional situations -->
2176       </para>
2177     </refsect1>
2178     <refsect1>
2179       <title>See Also</title>
2180       <para>
2181         <simplelist>
2182           <!-- see also -->
2183         </simplelist>
2184       </para>
2185     </refsect1>
2186     <refsect1>
2187       <title>Notes</title>
2188       <para>
2189         <!-- notes -->
2190       </para>
2191     </refsect1>
2192   </refentry>
2193
2194   <refentry id="run-prepared-sql">
2195     <refmeta>
2196       <refentrytitle>RUN-PREPARED-SQL</refentrytitle>
2197     </refmeta>
2198     <refnamediv>
2199       <refname><emphasis>Function</emphasis> <emphasis role="bold">RUN-PREPARED-SQL</emphasis></refname>
2200       <refpurpose>Execute a prepared statement.</refpurpose>
2201       <refclass>Function</refclass>
2202     </refnamediv>
2203     <refsect1>
2204       <title>Syntax</title>
2205       <synopsis>
2206       <function>run-prepared-sql</function> <replaceable>prepared-stmt</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
2207     </refsect1>
2208     <refsect1>
2209       <title>Arguments and Values</title>
2210       <variablelist>
2211         <!-- arguments and values -->
2212       </variablelist>
2213     </refsect1>
2214     <refsect1>
2215       <title>Description</title>
2216       <para>Execute the prepared sql statment. All input
2217       parameters must be bound.
2218       </para>
2219     </refsect1>
2220     <refsect1>
2221       <title>Examples</title>
2222       <screen>
2223         <!-- examples -->
2224       </screen>
2225     </refsect1>
2226     <refsect1>
2227       <title>Side Effects</title>
2228       <para>
2229         <!-- side effects -->
2230       </para>
2231     </refsect1>
2232     <refsect1>
2233       <title>Affected by</title>
2234       <para>
2235         <simplelist>
2236           <!-- affected by -->
2237         </simplelist>
2238       </para>
2239     </refsect1>
2240     <refsect1>
2241       <title>Exceptional Situations</title>
2242       <para>
2243         <!-- execeptional situations -->
2244       </para>
2245     </refsect1>
2246     <refsect1>
2247       <title>See Also</title>
2248       <para>
2249         <simplelist>
2250           <!-- see also -->
2251         </simplelist>
2252       </para>
2253     </refsect1>
2254     <refsect1>
2255       <title>Notes</title>
2256       <para>
2257         <!-- notes -->
2258       </para>
2259     </refsect1>
2260   </refentry>
2261
2262   <refentry id="free-prepared-sql">
2263     <refmeta>
2264       <refentrytitle>FREE-PREPARED-SQL</refentrytitle>
2265     </refmeta>
2266     <refnamediv>
2267       <refname><emphasis>Function</emphasis> <emphasis role="bold">FREE-PREPARED-SQL</emphasis></refname>
2268       <refpurpose>Delete a prepared statement object.</refpurpose>
2269       <refclass>Function</refclass>
2270     </refnamediv>
2271     <refsect1>
2272       <title>Syntax</title>
2273       <synopsis>
2274       <function>free-prepared-sql</function> <replaceable>prepared-stmt</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
2275     </refsect1>
2276     <refsect1>
2277       <title>Arguments and Values</title>
2278       <variablelist>
2279         <!-- arguments and values -->
2280       </variablelist>
2281     </refsect1>
2282     <refsect1>
2283       <title>Description</title>
2284       <para>Delete the objects associated with a prepared
2285       statement.
2286       </para>
2287     </refsect1>
2288     <refsect1>
2289       <title>Examples</title>
2290       <screen>
2291         <!-- examples -->
2292       </screen>
2293     </refsect1>
2294     <refsect1>
2295       <title>Side Effects</title>
2296       <para>
2297         <!-- side effects -->
2298       </para>
2299     </refsect1>
2300     <refsect1>
2301       <title>Affected by</title>
2302       <para>
2303         <simplelist>
2304           <!-- affected by -->
2305         </simplelist>
2306       </para>
2307     </refsect1>
2308     <refsect1>
2309       <title>Exceptional Situations</title>
2310       <para>
2311         <!-- execeptional situations -->
2312       </para>
2313     </refsect1>
2314     <refsect1>
2315       <title>See Also</title>
2316       <para>
2317         <simplelist>
2318           <!-- see also -->
2319         </simplelist>
2320       </para>
2321     </refsect1>
2322     <refsect1>
2323       <title>Notes</title>
2324       <para>
2325         <!-- notes -->
2326       </para>
2327     </refsect1>
2328   </refentry>
2329
2330   <refentry id="bind-parameter">
2331     <refmeta>
2332       <refentrytitle>BIND-PARAMETER</refentrytitle>
2333     </refmeta>
2334     <refnamediv>
2335       <refname><emphasis>Function</emphasis> <emphasis role="bold">BIND-PARAMETER</emphasis></refname>
2336       <refpurpose>Bind a parameter in a prepared statement.</refpurpose>
2337       <refclass>Function</refclass>
2338     </refnamediv>
2339     <refsect1>
2340       <title>Syntax</title>
2341       <synopsis>
2342       <function>bind-parameter</function> <replaceable>prepared-stmt</replaceable> <replaceable>position</replaceable> <replaceable>value</replaceable> => <returnvalue><!-- result --></returnvalue></synopsis>
2343     </refsect1>
2344     <refsect1>
2345       <title>Arguments and Values</title>
2346       <variablelist>
2347         <!-- arguments and values -->
2348       </variablelist>
2349     </refsect1>
2350     <refsect1>
2351       <title>Description</title>
2352       <para>Sets the value of a parameter in a prepared statement.
2353       </para>
2354     </refsect1>
2355     <refsect1>
2356       <title>Examples</title>
2357       <screen>
2358         <!-- examples -->
2359       </screen>
2360     </refsect1>
2361     <refsect1>
2362       <title>Side Effects</title>
2363       <para>
2364         <!-- side effects -->
2365       </para>
2366     </refsect1>
2367     <refsect1>
2368       <title>Affected by</title>
2369       <para>
2370         <simplelist>
2371           <!-- affected by -->
2372         </simplelist>
2373       </para>
2374     </refsect1>
2375     <refsect1>
2376       <title>Exceptional Situations</title>
2377       <para>
2378         <!-- execeptional situations -->
2379       </para>
2380     </refsect1>
2381     <refsect1>
2382       <title>See Also</title>
2383       <para>
2384         <simplelist>
2385           <!-- see also -->
2386         </simplelist>
2387       </para>
2388     </refsect1>
2389     <refsect1>
2390       <title>Notes</title>
2391       <para>
2392         <!-- notes -->
2393       </para>
2394     </refsect1>
2395   </refentry>
2396
2397 </reference>