1 <!-- -*- DocBook -*- -->
4 <title><symbol>CLSQL</symbol></title>
6 <para>This part gives a reference to all the symbols exported
7 from the package <symbol>CLSQL-SYS</symbol>, which are also
8 re-exported from the package <symbol>CLSQL</symbol>. These
9 symbols constitute the normal user-interface of
13 <refentry id="clsql-condition">
15 <refname>CLSQL-CONDITION</refname>
16 <refpurpose>the super-type of all
18 conditions</refpurpose>
19 <refclass>Condition Type</refclass>
22 <title>Class Precedence List</title>
24 <simplelist type="inline">
25 <member><errortype>clsql-condition</errortype></member>
26 <member><errortype>condition</errortype></member>
27 <member><errortype>t</errortype></member>
32 <title>Description</title>
33 <para>This is the super-type of all
34 &clsql;-specific conditions
35 defined by &clsql;, or any of it's
36 database-specific interfaces. There are no defined
37 initialization arguments nor any accessors.</para>
40 <refentry id="clsql-error">
42 <refname>CLSQL-ERROR</refname>
43 <refpurpose>the super-type of all
46 <refclass>Condition Type</refclass>
49 <title>Class Precedence List</title>
51 <simplelist type="inline">
52 <member><errortype>clsql-error</errortype></member>
53 <member><errortype>error</errortype></member>
54 <member><errortype>serious-condition</errortype></member>
55 <member><errortype>clsql-condition</errortype></member>
56 <member><errortype>condition</errortype></member>
57 <member><errortype>t</errortype></member>
62 <title>Description</title>
63 <para>This is the super-type of all
64 &clsql;-specific conditions that
65 represent errors, as defined by
66 &clsql;, or any of it's
67 database-specific interfaces. There are no defined
68 initialization arguments nor any accessors.</para>
71 <refentry id="clsql-simple-error">
73 <refname>CLSQL-SIMPLE-ERROR</refname>
74 <refpurpose>Unspecific simple
75 &clsql; errors</refpurpose>
76 <refclass>Condition Type</refclass>
79 <title>Class Precedence List</title>
81 <simplelist type="inline">
82 <member><errortype>clsql-simple-error</errortype></member>
83 <member><errortype>simple-condition</errortype></member>
84 <member><errortype>clsql-error</errortype></member>
85 <member><errortype>error</errortype></member>
86 <member><errortype>serious-condition</errortype></member>
87 <member><errortype>clsql-condition</errortype></member>
88 <member><errortype>condition</errortype></member>
89 <member><errortype>t</errortype></member>
94 <title>Description</title>
95 <para>This condition is used in all instances of errors, where
96 there exists no &clsql;-specific
97 condition that is more specific. The valid initialization
98 arguments and accessors are the same as for
99 <errortype>simple-condition</errortype>.</para>
102 <refentry id="clsql-warning">
104 <refname>CLSQL-WARNING</refname>
105 <refpurpose>the super-type of all
107 warnings</refpurpose>
108 <refclass>Condition Type</refclass>
111 <title>Class Precedence List</title>
113 <simplelist type="inline">
114 <member><errortype>clsql-warning</errortype></member>
115 <member><errortype>warning</errortype></member>
116 <member><errortype>clsql-condition</errortype></member>
117 <member><errortype>condition</errortype></member>
118 <member><errortype>t</errortype></member>
123 <title>Description</title>
124 <para>This is the super-type of all
125 &clsql;-specific conditions that
126 represent warnings, as defined by
127 &clsql;, or any of it's
128 database-specific interfaces. There are no defined
129 initialization arguments nor any accessors.</para>
132 <refentry id="clsql-simple-warning">
134 <refname>CLSQL-SIMPLE-WARNING</refname>
135 <refpurpose>Unspecific simple
136 &clsql; warnings</refpurpose>
137 <refclass>Condition Type</refclass>
140 <title>Class Precedence List</title>
142 <simplelist type="inline">
143 <member><errortype>clsql-simple-warning</errortype></member>
144 <member><errortype>simple-condition</errortype></member>
145 <member><errortype>clsql-warning</errortype></member>
146 <member><errortype>warning</errortype></member>
147 <member><errortype>clsql-condition</errortype></member>
148 <member><errortype>condition</errortype></member>
149 <member><errortype>t</errortype></member>
154 <title>Description</title>
155 <para>This condition is used in all instances of warnings,
156 where there exists no
157 &clsql;-specific condition that is
158 more specific. The valid initialization arguments and
159 accessors are the same as for
160 <errortype>simple-condition</errortype>.</para>
163 <!-- Specifc Conditions -->
164 <refentry id="clsql-invalid-spec-error">
166 <refname>CLSQL-INVALID-SPEC-ERROR</refname>
167 <refpurpose>condition representing errors because of invalid
168 connection specifications</refpurpose>
169 <refclass>Condition Type</refclass>
172 <title>Class Precedence List</title>
174 <simplelist type="inline">
175 <member><errortype>clsql-invalid-spec-error</errortype></member>
176 <member><errortype>clsql-error</errortype></member>
177 <member><errortype>error</errortype></member>
178 <member><errortype>serious-condition</errortype></member>
179 <member><errortype>clsql-condition</errortype></member>
180 <member><errortype>condition</errortype></member>
181 <member><errortype>t</errortype></member>
186 <title>Description</title>
187 <para>This condition represents errors that occur because the
188 user supplies an invalid connection specification to either
189 <function>database-name-from-spec</function> or
190 <function>connect</function>. The following initialization
191 arguments and accessors exist:</para>
193 <segtitle>Initarg</segtitle>
194 <segtitle>Accessor</segtitle>
195 <segtitle>Description</segtitle>
197 <seg><symbol>:connection-spec</symbol></seg>
198 <seg><function>clsql-invalid-spec-error-connection-spec</function></seg>
199 <seg>The invalid connection specification used.</seg>
202 <seg><symbol>:database-type</symbol></seg>
203 <seg><function>clsql-invalid-spec-error-database-type</function></seg>
204 <seg>The Database type used in the attempt.</seg>
207 <seg><symbol>:template</symbol></seg>
208 <seg><function>clsql-invalid-spec-error-template</function></seg>
209 <seg>An argument describing the template that a valid
210 connection specification must match for this database type.</seg>
215 <refentry id="clsql-connect-error">
217 <refname>CLSQL-CONNECT-ERROR</refname>
218 <refpurpose>condition representing errors during
219 connection</refpurpose>
220 <refclass>Condition Type</refclass>
223 <title>Class Precedence List</title>
225 <simplelist type="inline">
226 <member><errortype>clsql-connect-error</errortype></member>
227 <member><errortype>clsql-error</errortype></member>
228 <member><errortype>error</errortype></member>
229 <member><errortype>serious-condition</errortype></member>
230 <member><errortype>clsql-condition</errortype></member>
231 <member><errortype>condition</errortype></member>
232 <member><errortype>t</errortype></member>
237 <title>Description</title>
238 <para>This condition represents errors that occur while trying
239 to connect to a database. The following initialization
240 arguments and accessors exist:</para>
242 <segtitle>Initarg</segtitle>
243 <segtitle>Accessor</segtitle>
244 <segtitle>Description</segtitle>
246 <seg><symbol>:database-type</symbol></seg>
247 <seg><function>clsql-connect-error-database-type</function></seg>
248 <seg>Database type for the connection attempt</seg>
251 <seg><symbol>:connection-spec</symbol></seg>
252 <seg><function>clsql-connect-error-connection-spec</function></seg>
253 <seg>The connection specification used in the
254 connection attempt.</seg>
257 <seg><symbol>:errno</symbol></seg>
258 <seg><function>clsql-connect-error-errno</function></seg>
259 <seg>The numeric or symbolic error specification
260 returned by the database back-end. The values and
261 semantics of this are interface specific.</seg>
264 <seg><symbol>:error</symbol></seg>
265 <seg><function>clsql-connect-error-error</function></seg>
266 <seg>A string describing the problem that occurred,
267 possibly one returned by the database back-end.</seg>
272 <refentry id="clsql-sql-error">
274 <refname>CLSQL-SQL-ERROR</refname>
275 <refpurpose>condition representing errors during query or
276 command execution</refpurpose>
277 <refclass>Condition Type</refclass>
280 <title>Class Precedence List</title>
282 <simplelist type="inline">
283 <member><errortype>clsql-sql-error</errortype></member>
284 <member><errortype>clsql-error</errortype></member>
285 <member><errortype>error</errortype></member>
286 <member><errortype>serious-condition</errortype></member>
287 <member><errortype>clsql-condition</errortype></member>
288 <member><errortype>condition</errortype></member>
289 <member><errortype>t</errortype></member>
294 <title>Description</title>
295 <para>This condition represents errors that occur while
296 executing SQL statements, either as part of query operations
297 or command execution, either explicitly or implicitly, as
298 caused e.g. by <function>with-transaction</function>.
299 The following initialization arguments and accessors exist:</para>
301 <segtitle>Initarg</segtitle>
302 <segtitle>Accessor</segtitle>
303 <segtitle>Description</segtitle>
305 <seg><symbol>:database</symbol></seg>
306 <seg><function>clsql-sql-error-database</function></seg>
307 <seg>The database object that was involved in the
311 <seg><symbol>:expression</symbol></seg>
312 <seg><function>clsql-sql-error-expression</function></seg>
313 <seg>The SQL expression whose execution caused the error.</seg>
316 <seg><symbol>:errno</symbol></seg>
317 <seg><function>clsql-sql-error-errno</function></seg>
318 <seg>The numeric or symbolic error specification
319 returned by the database back-end. The values and
320 semantics of this are interface specific.</seg>
323 <seg><symbol>:error</symbol></seg>
324 <seg><function>clsql-sql-error-error</function></seg>
325 <seg>A string describing the problem that occurred,
326 possibly one returned by the database back-end.</seg>
331 <refentry id="clsql-exists-condition">
333 <refname>CLSQL-EXISTS-CONDITION</refname>
334 <refpurpose>condition indicating situations arising because of
335 existing connections</refpurpose>
336 <refclass>Condition Type</refclass>
339 <title>Class Precedence List</title>
341 <simplelist type="inline">
342 <member><errortype>clsql-exists-condition</errortype></member>
343 <member><errortype>clsql-condition</errortype></member>
344 <member><errortype>condition</errortype></member>
345 <member><errortype>t</errortype></member>
350 <title>Description</title>
351 <para>This condition is the super-type of all conditions which
352 represents problems that occur during calls to
353 <function>connect</function>, if a connection to the
354 database exists already. Depending on the value of
355 <parameter>if-exists</parameter> to the call of
356 <function>connect</function>, either a warning, an error or
357 no condition at all is signalled. If a warning or error is
359 <errortype>clsql-exists-warning</errortype> or
360 <errortype>clsql-exists-error</errortype> is signalled,
361 which are subtypes of
362 <errortype>clsql-exists-condition</errortype> and
363 <errortype>clsql-warning</errortype> or
364 <errortype>clsql-error</errortype>.
365 <errortype>clsql-exists-condition</errortype> is never
366 signalled itself.</para>
368 The following initialization arguments and accessors exist:</para>
370 <segtitle>Initarg</segtitle>
371 <segtitle>Accessor</segtitle>
372 <segtitle>Description</segtitle>
374 <seg><symbol>:old-db</symbol></seg>
375 <seg><function>clsql-exists-condition-old-db</function></seg>
376 <seg>The database object that represents the existing
377 connection. This slot is always filled.</seg>
380 <seg><symbol>:new-db</symbol></seg>
381 <seg><function>clsql-exists-condition-new-db</function></seg>
382 <seg>The database object that will be used and returned by
383 this call to connect, if execution continues normally.
384 This can be either <symbol>nil</symbol>, indicating that
385 a new database object is to be created on continuation,
386 or a database object representing the newly created
387 continuation, or the same database object as
388 <symbol>old-db</symbol>, indicating that the existing
389 database object will be reused. This slot is always
390 filled and defaults to <symbol>nil</symbol>.</seg>
395 <refentry id="clsql-exists-warning">
397 <refname>CLSQL-EXISTS-WARNING</refname>
398 <refpurpose>condition representing warnings arising because of
399 existing connections</refpurpose>
400 <refclass>Condition Type</refclass>
403 <title>Class Precedence List</title>
405 <simplelist type="inline">
406 <member><errortype>clsql-exists-warning</errortype></member>
407 <member><errortype>clsql-exists-condition</errortype></member>
408 <member><errortype>clsql-warning</errortype></member>
409 <member><errortype>warning</errortype></member>
410 <member><errortype>clsql-condition</errortype></member>
411 <member><errortype>condition</errortype></member>
412 <member><errortype>t</errortype></member>
417 <title>Description</title>
418 <para>This condition is a subtype of
419 <errortype>clsql-exists-condition</errortype>, and is
420 signalled during calls to <function>connect</function> when
421 there is an existing connection, and
422 <parameter>if-exists</parameter> is either
423 <symbol>:warn-new</symbol> or <symbol>:warn-old</symbol>.
424 In the former case, <symbol>new-db</symbol> will be the
425 newly created database object, in the latter case it will be
426 the existing old database object.</para>
428 The initialization arguments and accessors are the same as
429 for <errortype>clsql-exists-condition</errortype>.</para>
432 <refentry id="clsql-exists-error">
434 <refname>CLSQL-EXISTS-ERROR</refname>
435 <refpurpose>condition representing errors arising because of
436 existing connections</refpurpose>
437 <refclass>Condition Type</refclass>
440 <title>Class Precedence List</title>
442 <simplelist type="inline">
443 <member><errortype>clsql-exists-error</errortype></member>
444 <member><errortype>clsql-exists-condition</errortype></member>
445 <member><errortype>clsql-error</errortype></member>
446 <member><errortype>error</errortype></member>
447 <member><errortype>serious-condition</errortype></member>
448 <member><errortype>clsql-condition</errortype></member>
449 <member><errortype>condition</errortype></member>
450 <member><errortype>t</errortype></member>
455 <title>Description</title>
456 <para>This condition is a subtype of
457 <errortype>clsql-exists-condition</errortype>, and is
458 signalled during calls to <function>connect</function> when
459 there is an existing connection, and
460 <parameter>if-exists</parameter> is <symbol>:error</symbol>.
461 In this case, <symbol>new-db</symbol> will be
462 <symbol>nil</symbol>, indicating that the database object to
463 be returned by <function>connect</function> depends on user
464 action in continuing from this correctable error.</para>
466 The initialization arguments and accessors are the same as
467 for <errortype>clsql-exists-condition</errortype>.</para>
470 <refentry id="clsql-closed-error">
472 <refname>CLSQL-CLOSED-ERROR</refname>
473 <refpurpose>condition representing errors because the database
474 has already been closed</refpurpose>
475 <refclass>Condition Type</refclass>
478 <title>Class Precedence List</title>
480 <simplelist type="inline">
481 <member><errortype>clsql-closed-error</errortype></member>
482 <member><errortype>clsql-error</errortype></member>
483 <member><errortype>error</errortype></member>
484 <member><errortype>serious-condition</errortype></member>
485 <member><errortype>clsql-condition</errortype></member>
486 <member><errortype>condition</errortype></member>
487 <member><errortype>t</errortype></member>
492 <title>Description</title>
493 <para>This condition represents errors that occur because the
494 user invokes an operation on the given database object,
495 although the database is invalid because
496 <function>disconnect</function> has already been called on
497 this database object.</para>
498 <para>Functions which signal this error when called with a
499 closed database will usually provide a
500 <symbol>continue</symbol> restart, that will just return nil
501 from the function.</para>
503 The following initialization arguments and accessors exist:</para>
505 <segtitle>Initarg</segtitle>
506 <segtitle>Accessor</segtitle>
507 <segtitle>Description</segtitle>
509 <seg><symbol>:database</symbol></seg>
510 <seg><function>clsql-closed-error-database</function></seg>
511 <seg>The database object that was involved in the
518 <!-- Database Types -->
519 <refentry id="default-database-type">
521 <refname>*DEFAULT-DATABASE-TYPE*</refname>
522 <refpurpose>The default database type to use</refpurpose>
523 <refclass>Variable</refclass>
526 <title>Value Type</title>
527 <para>Any keyword representing a valid database back-end of
529 <symbol>nil</symbol>.</para>
532 <title>Initial Value</title>
533 <para><symbol>nil</symbol></para>
536 <title>Description</title>
537 <para>The value of this variable is used in calls to
538 <function>initialize-database-type</function> and
539 <function>connect</function> as the default
540 value of the <parameter>database-type</parameter>
543 <para>If the value of this variable is <symbol>nil</symbol>,
545 <function>initialize-database-type</function> or
546 <function>connect</function> will have to specify the
547 <parameter>database-type</parameter> to use, or a
548 general-purpose error will be signalled.</para>
552 <title>Examples</title>
554 (setf *default-database-type* :mysql)
556 (initialize-database-type)
561 <title>Affected By</title>
565 <title>See Also</title>
573 <refentry id="initialized-database-types">
575 <refname>*INITIALIZED-DATABASE-TYPES*</refname>
576 <refpurpose>List of all initialized database types</refpurpose>
577 <refclass>Variable</refclass>
580 <title>Value Type</title>
581 <para>A list of all initialized database types, each of which
582 represented by it's corresponding keyword.</para>
585 <title>Initial Value</title>
586 <para><symbol>nil</symbol></para>
589 <title>Description</title>
590 <para>This variable is updated whenever
591 <function>initialize-database-type</function> is called for a
592 database type which hasn't already been initialized before,
593 as determined by this variable. In that case the keyword
594 representing the database type is pushed onto the list
596 <symbol>*INITIALIZED-DATABASE-TYPES*</symbol>.</para>
598 <para>Attempts to modify the value of this variable will
599 result in undefined behaviour.</para>
603 <title>Examples</title>
605 (setf *default-database-type* :mysql)
607 (initialize-database-type)
609 *initialized-database-types*
614 <title>Affected By</title>
617 <member><function>initialize-database-type</function></member>
622 <title>See Also</title>
627 <para>Direct access to this variable is primarily provided
628 because of compatibility with Harlequin's <application>Common
629 SQL</application>.</para>
632 <refentry id="initialize-database-type">
634 <refname>INITIALIZE-DATABASE-TYPE</refname>
635 <refpurpose>Initializes a database type</refpurpose>
636 <refclass>Function</refclass>
639 <title>Syntax</title>
640 <synopsis><function>initialize-database-type</function> &key <replaceable>database-type</replaceable> => <returnvalue>result</returnvalue></synopsis>
643 <title>Arguments and Values</title>
646 <term><parameter>database-type</parameter></term>
648 <para>The database type to initialize, i.e. a keyword
649 symbol denoting a known database back-end. Defaults to
651 <symbol>*default-database-type*</symbol>.</para>
655 <term><returnvalue>result</returnvalue></term>
657 <para>Either <symbol>nil</symbol> if the initialization
658 attempt fails, or <symbol>t</symbol> otherwise.</para>
664 <title>Description</title>
665 <para>If the back-end specified by
666 <parameter>database-type</parameter> has not already been
667 initialized, as seen from
668 <symbol>*initialized-database-types*</symbol>, an attempt is
669 made to initialize the database. If this attempt succeeds,
670 or the back-end has already been initialized, the function
671 returns t, and places the keyword denoting the database type
672 onto the list stored in
673 <symbol>*initialized-database-types*</symbol>, if not
674 already present.</para>
675 <para>If initialization fails, the function returns
676 <symbol>nil</symbol>, and/or signals an error of type
677 <errortype>clsql-error</errortype>. The kind of action
678 taken depends on the back-end and the cause of the
682 <title>Examples</title>
684 *initialized-database-types*
686 (setf *default-database-type* :mysql)
688 (initialize-database-type)
689 >> Compiling LAMBDA (#:G897 #:G898 #:G901 #:G902):
690 >> Compiling Top-Level Form:
693 *initialized-database-types*
695 (initialize-database-type)
697 *initialized-database-types*
702 <title>Side Effects</title>
703 <para>The database back-end corresponding to the database type
704 specified is initialized, unless it has already been
705 initialized. This can involve any number of other side
706 effects, as determined by the back-end implementation (like
707 e.g. loading of foreign code, calling of foreign code,
708 networking operations, etc.). If initialization is
709 attempted and succeeds, the
710 <parameter>database-type</parameter> is pushed onto the list
712 <symbol>*initialized-database-types*</symbol>.</para>
715 <title>Affected by</title>
718 <member><symbol>*default-database-type*</symbol></member>
719 <member><symbol>*initialized-database-types*</symbol></member>
724 <title>Exceptional Situations</title>
725 <para>If an error is encountered during the initialization
726 attempt, the back-end may signal errors of kind
727 <errortype>clsql-error</errortype>.</para>
730 <title>See Also</title>
738 <!-- Databases Connection and Disconnection -->
739 <refentry id="connect-if-exists">
741 <refname>*CONNECT-IF-EXISTS*</refname>
742 <refpurpose>Default value for the
743 <parameter>if-exists</parameter> parameter of
744 <function>connect</function>.</refpurpose>
745 <refclass>Variable</refclass>
748 <title>Value Type</title>
749 <para>A valid argument to the <parameter>if-exists</parameter>
750 parameter of <function>connect</function>, i.e. one of
751 <simplelist type="inline">
752 <member><symbol>:new</symbol></member>
753 <member><symbol>:warn-new</symbol></member>
754 <member><symbol>:error</symbol></member>
755 <member><symbol>:warn-old</symbol></member>
756 <member><symbol>:old</symbol></member>
761 <title>Initial Value</title>
762 <para><symbol>:error</symbol></para>
765 <title>Description</title>
766 <para>The value of this variable is used in calls to
767 <function>connect</function> as the default
768 value of the <parameter>if-exists</parameter>
770 linkend="connect"><function>connect</function></link> for
771 the semantics of the valid values for this variable.</para>
774 <title>Examples</title>
778 <title>Affected By</title>
782 <title>See Also</title>
786 linkend="connect"><function>connect</function></link></member>
795 <refentry id="connected-databases">
797 <refname>CONNECTED-DATABASES</refname>
798 <refpurpose>Return the list of active database
799 objects.</refpurpose>
800 <refclass>Function</refclass>
803 <title>Syntax</title>
804 <synopsis><function>connected-databases</function> => <returnvalue>databases</returnvalue></synopsis>
807 <title>Arguments and Values</title>
810 <term><returnvalue>databases</returnvalue></term>
812 <para>The list of active database objects.</para>
818 <title>Description</title>
819 <para>This function returns the list of active database
820 objects, i.e. all those database objects created by calls to
821 <function>connect</function>, which have not been closed by
822 calling <function>disconnect</function> on them.</para>
824 <para>The consequences of modifying the list returned by
825 <function>connected-databases</function> are
830 <title>Examples</title>
832 (connected-databases)
834 (connect '(nil "template1" "dent" nil) :database-type :postgresql)
835 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {4830BC65}>
836 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql)
837 => #<CLSQL-MYSQL:MYSQL-DATABASE {4830C5AD}>
838 (connected-databases)
839 => (#<CLSQL-MYSQL:MYSQL-DATABASE {4830C5AD}>
840 #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {4830BC65}>)
843 (connected-databases)
844 => (#<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {4830BC65}>)
847 (connected-databases)
852 <title>Side Effects</title>
856 <title>Affected By</title>
859 <member><function>connect</function></member>
860 <member><function>disconnect</function></member>
865 <title>Exceptional Situations</title>
869 <title>See Also</title>
877 <refentry id="default-database">
879 <refname>*DEFAULT-DATABASE*</refname>
880 <refpurpose>The default database object to use</refpurpose>
881 <refclass>Variable</refclass>
884 <title>Value Type</title>
885 <para>Any object of type <type>database</type>, or nil to
886 indicate no default database.</para>
889 <title>Initial Value</title>
890 <para><symbol>nil</symbol></para>
893 <title>Description</title>
894 <para>Any function or macro in
895 &clsql; that operates on a
896 database uses the value of this variable as the default
897 value for it's <parameter>database</parameter>
899 <para>The value of this parameter is changed by calls to
900 <function>connect</function>, which sets
901 <symbol>*default-database*</symbol> to the database object
902 it returns. It is also changed by calls to
903 <function>disconnect</function>, when the database object
904 being disconnected is the same as the value of
905 <symbol>*default-database*</symbol>. In this case
906 <function>disconnect</function> sets
907 <symbol>*default-database*</symbol> to the first database
908 that remains in the list of active databases as returned by
909 <function>connected-databases</function>, or
910 <symbol>nil</symbol> if no further active databases
912 <para>The user may change <symbol>*default-database*</symbol>
913 at any time to a valid value of his choice.</para>
915 <para>If the value of <symbol>*default-database*</symbol> is
916 <symbol>nil</symbol>, then all calls to
917 &clsql; functions on databases
918 must provide a suitable <parameter>database</parameter>
919 parameter, or an error will be signalled.</para>
923 <title>Examples</title>
925 (connected-databases)
927 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql)
928 => #<CLSQL-MYSQL:MYSQL-DATABASE {48385F55}>
929 (connect '(nil "template1" "dent" nil) :database-type :postgresql)
930 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {483868FD}>
931 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql :if-exists :new)
932 => #<CLSQL-MYSQL:MYSQL-DATABASE {48387265}>
934 => #<CLSQL-MYSQL:MYSQL-DATABASE {48387265}>
938 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {483868FD}>
942 => #<CLSQL-MYSQL:MYSQL-DATABASE {48385F55}>
947 (connected-databases)
952 <title>Affected By</title>
955 <member><link linkend="connect"><function>connect</function></link></member>
956 <member><link linkend="disconnect"><function>disconnect</function></link></member>
961 <title>See Also</title>
964 <member><link linkend="connected-databases"><function>connected-databases</function></link></member>
971 <para>This variable is intended to facilitate working with
972 &clsql; in an interactive
973 fashion at the top-level loop, and because of this,
974 <function>connect</function> and
975 <function>disconnect</function> provide some fairly
976 complex behaviour to keep
977 <symbol>*default-database*</symbol> set to useful values.
978 Programmatic use of &clsql;
979 should never depend on the value of
980 <symbol>*default-database*</symbol> and should provide
981 correct database objects via the
982 <parameter>database</parameter> parameter to functions
988 <refentry id="database">
990 <refname>DATABASE</refname>
991 <refpurpose>The super-type of all
992 &clsql; databases</refpurpose>
993 <refclass>Class</refclass>
996 <title>Class Precedence List</title>
998 <simplelist type="inline">
999 <member><type>database</type></member>
1000 <member><type>standard-object</type></member>
1001 <member><type>t</type></member>
1006 <title>Description</title>
1007 <para>This class is the superclass of all
1008 &clsql; databases. The different
1009 database back-ends derive subclasses of this class to
1010 implement their databases. No instances of this class are
1011 ever created by &clsql;.</para>
1014 <refentry id="closed-database">
1016 <refname>CLOSED-DATABASE</refname>
1017 <refpurpose>The class representing all closed
1018 &clsql; databases</refpurpose>
1019 <refclass>Class</refclass>
1022 <title>Class Precedence List</title>
1024 <simplelist type="inline">
1025 <member><type>closed-database</type></member>
1026 <member><type>standard-object</type></member>
1027 <member><type>t</type></member>
1032 <title>Description</title>
1033 <para>&clsql; <type>database</type>
1034 instances are changed to this class via
1035 <function>change-class</function> after they are closed via
1036 <function>disconnect</function>. All functions and generic
1037 functions that take database objects as arguments will
1038 signal errors of type
1039 <errortype>clsql-closed-error</errortype> when they are
1040 called on instances of <type>closed-database</type>, with
1041 the exception of <function>database-name</function>, which
1042 will continue to work as for instances of
1043 <type>database</type>.</para>
1047 <refentry id="database-name">
1049 <refname>DATABASE-NAME</refname>
1050 <refpurpose>Get the name of a database object</refpurpose>
1051 <refclass>Generic Function</refclass>
1054 <title>Syntax</title>
1055 <synopsis><function>database-name</function> <replaceable>database</replaceable> => <returnvalue>name</returnvalue></synopsis>
1058 <title>Arguments and Values</title>
1061 <term><parameter>database</parameter></term>
1063 <para>A database object, either of type
1064 <type>database</type> or of type
1065 <type>closed-database</type>.</para>
1069 <term><returnvalue>name</returnvalue></term>
1071 <para>A string describing the identity of the database
1072 to which this database object is connected to.</para>
1078 <title>Description</title>
1079 <para>This function returns the database name of the given
1080 database. The database name is a string which somehow
1081 describes the identity of the database to which this
1082 database object is or has been connected. The database name
1083 of a database object is determined at
1084 <function>connect</function> time, when a call to
1085 <function>database-name-from-spec</function> derives the
1086 database name from the connection specification passed to
1087 <function>connect</function> in the
1088 <parameter>connection-spec</parameter> parameter.</para>
1089 <para>The database name is used via
1090 <function>find-database</function> in
1091 <function>connect</function> to determine whether database
1092 connections to the specified database exist already.</para>
1093 <para>Usually the database name string will include
1094 indications of the host, database name, user, or port that
1095 where used during the connection attempt. The only
1096 important thing is that this string shall try to identify
1097 the database at the other end of the connection. Connection
1098 specifications parts like passwords and credentials shall
1099 not be used as part of the database name.</para>
1102 <title>Examples</title>
1104 (database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql)
1105 => "dent/newesim/dent"
1106 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql)
1107 => #<CLSQL-MYSQL:MYSQL-DATABASE {48391DCD}>
1108 (database-name *default-database*)
1109 => "dent/newesim/dent"
1111 (database-name-from-spec '(nil "template1" "dent" nil) :postgresql)
1112 => "/template1/dent"
1113 (connect '(nil "template1" "dent" nil) :database-type :postgresql)
1114 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}>
1115 (database-name *default-database*)
1116 => "/template1/dent"
1118 (database-name-from-spec '("www.pmsf.de" "template1" "dent" nil) :postgresql)
1119 => "www.pmsf.de/template1/dent"
1123 <title>Side Effects</title>
1127 <title>Affected By</title>
1130 <member><link linkend="database-name-from-spec"><function>database-name-from-spec</function></link></member>
1135 <title>Exceptional Situations</title>
1136 <para>Will signal an error if the object passed as the
1137 <parameter>database</parameter> parameter is neither of type
1138 <type>database</type> nor of type
1139 <type>closed-database</type>.</para>
1142 <title>See Also</title>
1146 linkend="connect"><function>connect</function></link></member>
1148 linkend="find-database"><function>find-database</function></link></member>
1153 <title>Notes</title>
1157 <refentry id="find-database">
1159 <refname>FIND-DATABASE</refname>
1160 <refpurpose>Locate a database object through it's
1162 <refclass>Function</refclass>
1165 <title>Syntax</title>
1166 <synopsis><function>find-database</function> <replaceable>database</replaceable> &optional <replaceable>errorp</replaceable> => <returnvalue>result</returnvalue></synopsis>
1169 <title>Arguments and Values</title>
1172 <term><parameter>database</parameter></term>
1174 <para>A database object or a string, denoting a database
1179 <term><parameter>errorp</parameter></term>
1181 <para>A generalized boolean. Defaults to
1182 <symbol>t</symbol>.</para>
1186 <term><returnvalue>result</returnvalue></term>
1188 <para>Either a database object, or, if
1189 <parameter>errorp</parameter> is <symbol>nil</symbol>,
1190 possibly <symbol>nil</symbol>.</para>
1196 <title>Description</title>
1197 <para><function>find-database</function> locates an active
1198 database object given the specification in
1199 <parameter>database</parameter>. If
1200 <parameter>database</parameter> is an object of type
1201 <type>database</type>, <function>find-database</function>
1202 returns this. Otherwise it will search the active databases
1203 as indicated by the list returned by
1204 <function>connected-databases</function> for a database
1205 whose name (as returned by
1206 <function>database-name</function> is equal as per
1207 <function>string=</function> to the string passed as
1208 <parameter>database</parameter>. If it succeeds, it returns
1209 the first database found.</para>
1210 <para>If it fails to find a matching database, it will signal
1211 an error of type <errortype>clsql-error</errortype> if
1212 <parameter>errorp</parameter> is true. If
1213 <parameter>errorp</parameter> is <symbol>nil</symbol>, it
1214 will return <symbol>nil</symbol> instead.</para>
1217 <title>Examples</title>
1219 (database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql)
1220 => "dent/newesim/dent"
1221 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql)
1222 => #<CLSQL-MYSQL:MYSQL-DATABASE {48391DCD}>
1223 (database-name *default-database*)
1224 => "dent/newesim/dent"
1226 (database-name-from-spec '(nil "template1" "dent" nil) :postgresql)
1227 => "/template1/dent"
1228 (connect '(nil "template1" "dent" nil) :database-type :postgresql)
1229 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}>
1230 (database-name *default-database*)
1231 => "/template1/dent"
1233 (database-name-from-spec '("www.pmsf.de" "template1" "dent" nil) :postgresql)
1234 => "www.pmsf.de/template1/dent"
1236 (find-database "dent/newesim/dent")
1237 => #<CLSQL-MYSQL:MYSQL-DATABASE {484E91C5}>
1238 (find-database "/template1/dent")
1239 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}>
1240 (find-database "www.pmsf.de/template1/dent" nil)
1243 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}>
1247 <title>Side Effects</title>
1251 <title>Affected By</title>
1254 <member><link linkend="connected-databases"><function>connected-databases</function></link></member>
1259 <title>Exceptional Situations</title>
1260 <para>Will signal an error of type
1261 <errortype>clsql-error</errortype> if no matching database
1262 can be found, and <parameter>errorp</parameter> is true.
1263 Will signal an error if the value of
1264 <parameter>database</parameter> is neither an object of type
1265 <type>database</type> nor a string.</para>
1268 <title>See Also</title>
1272 linkend="database-name"><function>database-name</function></link></member>
1274 linkend="database-name-from-spec"><function>database-name-from-spec</function></link></member>
1279 <title>Notes</title>
1284 <refentry id="connect">
1286 <refname>CONNECT</refname>
1287 <refpurpose>create a connection to a database</refpurpose>
1288 <refclass>Function</refclass>
1291 <title>Syntax</title>
1292 <synopsis><function>connect</function> <replaceable>connection-spec</replaceable> &key <replaceable>if-exists</replaceable> <replaceable>database-type</replaceable> <replaceable>pool</replaceable> => <returnvalue>database</returnvalue></synopsis>
1295 <title>Arguments and Values</title>
1298 <term><parameter>connection-spec</parameter></term>
1300 <para>A connection specification</para>
1304 <term><parameter>if-exists</parameter></term>
1306 <para>This indicates the action to take if a connection
1307 to the same database exists already. See below for the
1308 legal values and actions. It defaults to the value of
1309 <symbol>*connect-if-exists*</symbol>.</para>
1313 <term><parameter>database-type</parameter></term>
1315 <para>A database type specifier, i.e. a keyword.
1316 This defaults to the value of
1317 <symbol>*default-database-type*</symbol></para>
1321 <term><parameter>pool</parameter></term>
1323 <para>A boolean flag. If &t;, acquire connection from a
1324 pool of open connections. If the pool is empty, a new
1325 connection is created. The default is &nil;.
1329 <term><returnvalue>database</returnvalue></term>
1331 <para>The database object representing the connection.</para>
1337 <title>Description</title>
1338 <para>This function takes a connection specification and
1339 a database type and creates a connection to the database
1340 specified by those. The type and structure of the
1341 connection specification depend on the database type.</para>
1342 <para>The parameter <parameter>if-exists</parameter> specifies
1343 what to do if a connection to the database specified exists
1344 already, which is checked by calling
1345 <function>find-database</function> on the database name
1346 returned by <function>database-name-from-spec</function>
1347 when called with the <parameter>connection-spec</parameter>
1348 and <parameter>database-type</parameter> parameters. The
1349 possible values of <parameter>if-exists</parameter> are:
1352 <term><symbol>:new</symbol></term>
1354 <para>Go ahead and create a new connection.</para>
1358 <term><symbol>:warn-new</symbol></term>
1360 <para>This is just like <symbol>:new</symbol>, but
1361 also signals a warning of type
1362 <errortype>clsql-exists-warning</errortype>,
1363 indicating the old and newly created
1368 <term><symbol>:error</symbol></term>
1370 <para>This will cause <function>connect</function> to
1371 signal a correctable error of type
1372 <errortype>clsql-exists-error</errortype>. The
1373 user may choose to proceed, either by indicating
1374 that a new connection shall be created, via the
1375 restart <symbol>create-new</symbol>, or by
1376 indicating that the existing connection shall be
1377 used, via the restart
1378 <symbol>use-old</symbol>.</para>
1382 <term><symbol>:old</symbol></term>
1384 <para>This will cause <function>connect</function> to
1385 use an old connection if one exists.</para>
1389 <term><symbol>:warn-old</symbol></term>
1391 <para>This is just like <symbol>:old</symbol>, but
1392 also signals a warning of type
1393 <errortype>clsql-exists-warning</errortype>,
1394 indicating the old database used, via the slots
1395 <symbol>old-db</symbol> and
1396 <symbol>new-db</symbol></para>
1401 <para>The database name of the returned database object will
1402 be the same under <function>string=</function> as that which
1403 would be returned by a call to
1404 <function>database-name-from-spec</function> with the given
1405 <parameter>connection-spec</parameter> and
1406 <parameter>database-type</parameter> parameters.</para>
1409 <title>Examples</title>
1411 (database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql)
1412 => "dent/newesim/dent"
1413 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql)
1414 => #<CLSQL-MYSQL:MYSQL-DATABASE {48036F6D}>
1416 => "dent/newesim/dent"
1418 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql)
1419 >> In call to CONNECT:
1420 >> There is an existing connection #<CLSQL-MYSQL:MYSQL-DATABASE {48036F6D}> to database dent/newesim/dent.
1423 >> 0: [CREATE-NEW] Create a new connection.
1424 >> 1: [USE-OLD ] Use the existing connection.
1425 >> 2: [ABORT ] Return to Top-Level.
1427 >> Debug (type H for help)
1429 >> (CONNECT ("dent" "newesim" "dent" "dent") :IF-EXISTS NIL :DATABASE-TYPE ...)
1431 >> ; File: /prj/CLSQL/sql/sql.cl
1432 >> (RESTART-CASE (ERROR 'CLSQL-EXISTS-ERROR :OLD-DB OLD-DB)
1433 >> (CREATE-NEW NIL :REPORT "Create a new connection."
1435 >> (USE-OLD NIL :REPORT "Use the existing connection."
1436 >> (SETQ RESULT OLD-DB)))
1438 => #<CLSQL-MYSQL:MYSQL-DATABASE {480451F5}>
1442 <title>Side Effects</title>
1443 <para>A database connection is established, and the resultant
1444 database object is registered, so as to appear in the list
1445 returned by <function>connected-databases</function>.</para>
1448 <title>Affected by</title>
1451 <member><symbol>*default-database-type*</symbol></member>
1452 <member><symbol>*connect-if-exists*</symbol></member>
1457 <title>Exceptional Situations</title>
1458 <para>If the connection specification is not syntactically or
1459 semantically correct for the given database type, an error
1460 of type <errortype>clsql-invalid-spec-error</errortype> is
1461 signalled. If during the connection attempt an error is
1462 detected (e.g. because of permission problems, network
1463 trouble or any other cause), an error of type
1464 <errortype>clsql-connect-error</errortype> is
1466 <para>If a connection to the database specified by
1467 <parameter>connection-spec</parameter> exists already,
1468 conditions are signalled according to the
1469 <parameter>if-exists</parameter> parameter, as described
1473 <title>See Also</title>
1476 <member><function>connected-databases</function></member>
1477 <member><link linkend="disconnect"><function>disconnect</function></link></member>
1482 <title>Notes</title>
1487 <refentry id="disconnect">
1489 <refname>DISCONNECT</refname>
1490 <refpurpose>close a database connection</refpurpose>
1491 <refclass>Function</refclass>
1494 <title>Syntax</title>
1495 <synopsis><function>disconnect</function> &key <replaceable>database</replaceable> <replaceable>pool</replaceable> => <returnvalue>t</returnvalue></synopsis>
1498 <title>Arguments and Values</title>
1501 <term><parameter>pool</parameter></term>
1503 <para>A boolean flag indicating whether to put the database into a
1504 pool of opened databases. If &t;, rather than terminating the database connection, the
1505 connection is left open and the connection is placed into a pool of connections. Subsequent
1506 calls to <link linkend="connect"><function>connect</function></link> can then reuse this connection.
1507 The default is &nil;.
1511 <term><parameter>database</parameter></term>
1513 <para>The database to disconnect, which defaults to the
1514 database indicated by
1515 <symbol>*default-database*</symbol>.</para>
1521 <title>Description</title>
1522 <para>This function takes a <type>database</type> object as
1523 returned by <function>connect</function>, and closes the
1524 connection. The class of the object passed is changed to
1525 <type>closed-database</type> after the disconnection
1526 succeeds, thereby preventing further use of the object as
1527 an argument to &clsql; functions,
1528 with the exception of <function>database-name</function>.
1529 If the user does pass a closed database object to any other
1530 &clsql; function, an error of type
1531 <errortype>clsql-closed-error</errortype> is
1535 <title>Examples</title>
1537 (disconnect :database (find-database "dent/newesim/dent"))
1542 <title>Side Effects</title>
1543 <para>The database connection is closed, and the database
1544 object is removed from the list of connected databases as
1545 returned by <function>connected-databases</function>.</para>
1546 <para>The class of the database object is changed to
1547 <type>closed-database</type>.</para>
1548 <para>If the database object passed is the same under
1549 <function>eq</function> as the value of
1550 <symbol>*default-database*</symbol>, then
1551 <symbol>*default-database*</symbol> is set to the first
1552 remaining database from
1553 <function>connected-databases</function> or to nil if no
1554 further active database exists.</para>
1557 <title>Affected by</title>
1560 <member><symbol>*default-database*</symbol></member>
1565 <title>Exceptional Situations</title>
1566 <para>If during the disconnection attempt an error is
1567 detected (e.g. because of network trouble or any other
1568 cause), an error of type <errortype>clsql-error</errortype>
1569 might be signalled.</para>
1572 <title>See Also</title>
1575 <member><link linkend="connect"><function>connect</function></link></member>
1576 <member><link linkend="connect"><function>closed-database</function></link></member>
1581 <title>Notes</title>
1586 <refentry id="disconnect-pooled">
1588 <refname>DISCONNECT-POOLED</refname>
1589 <refpurpose>closes all pooled database connections</refpurpose>
1590 <refclass>Function</refclass>
1593 <title>Syntax</title>
1594 <synopsis><function>disconnect-pool</function> => <returnvalue>t</returnvalue></synopsis>
1597 <title>Description</title>
1598 <para>This function disconnects all database connections
1599 that have been placed into the pool. Connections are placed
1600 in the pool by calling
1601 <link linkend="disconnect"><function>disconnection</function></link>.
1604 <title>Examples</title>
1611 <title>Side Effects</title>
1612 <para>Database connections will be closed and entries in the pool are removed.
1615 <title>Affected by</title>
1618 <member><function>disconnect</function></member>
1623 <title>Exceptional Situations</title>
1624 <para>If during the disconnection attempt an error is
1625 detected (e.g. because of network trouble or any other
1626 cause), an error of type <errortype>clsql-error</errortype>
1627 might be signalled.</para>
1630 <title>See Also</title>
1633 <member><link linkend="connect"><function>connect</function></link></member>
1634 <member><link linkend="connect"><function>closed-database</function></link></member>
1639 <title>Notes</title>
1644 <refentry id="database-name-from-spec">
1646 <refname>DATABASE-NAME-FROM-SPEC</refname>
1647 <refpurpose>Return the database name string corresponding to
1648 the given connection specification.</refpurpose>
1649 <refclass>Generic Function</refclass>
1652 <title>Syntax</title>
1654 <function>database-name-from-spec</function> <replaceable>connection-spec</replaceable> <replaceable>database-type</replaceable> => <returnvalue>name</returnvalue></synopsis>
1657 <title>Arguments and Values</title>
1660 <term><parameter>connection-spec</parameter></term>
1662 <para>A connection specification, whose structure and
1663 interpretation are dependent on the
1664 <parameter>database-type</parameter>.</para>
1668 <term><parameter>database-type</parameter></term>
1670 <para>A database type specifier, i.e. a keyword.</para>
1674 <term><returnvalue>name</returnvalue></term>
1676 <para>A string denoting a database name.</para>
1682 <title>Description</title>
1683 <para>This generic function takes a connection specification
1684 and a database type and returns the database name of the
1685 database object that would be created had
1686 <function>connect</function> been called with the given
1687 connection specification and database types.</para>
1688 <para>This function is useful in determining a database name
1689 from the connection specification, since the way the
1690 connection specification is converted into a database name
1691 is dependent on the database type.</para>
1694 <title>Examples</title>
1696 (database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql)
1697 => "dent/newesim/dent"
1698 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql)
1699 => #<CLSQL-MYSQL:MYSQL-DATABASE {48391DCD}>
1700 (database-name *default-database*)
1701 => "dent/newesim/dent"
1703 (database-name-from-spec '(nil "template1" "dent" nil) :postgresql)
1704 => "/template1/dent"
1705 (connect '(nil "template1" "dent" nil) :database-type :postgresql)
1706 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}>
1707 (database-name *default-database*)
1708 => "/template1/dent"
1710 (database-name-from-spec '("www.pmsf.de" "template1" "dent" nil) :postgresql)
1711 => "www.pmsf.de/template1/dent"
1713 (find-database "dent/newesim/dent")
1714 => #<CLSQL-MYSQL:MYSQL-DATABASE {484E91C5}>
1715 (find-database "/template1/dent")
1716 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}>
1717 (find-database "www.pmsf.de/template1/dent" nil)
1720 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}>
1724 <title>Side Effects</title>
1728 <title>Affected by</title>
1732 <title>Exceptional Situations</title>
1733 <para>If the value of <parameter>connection-spec</parameter>
1734 is not a valid connection specification for the given
1735 database type, an error of type
1736 <errortype>clsql-invalid-spec-error</errortype> might be
1740 <title>See Also</title>
1743 <member><link linkend="connect"><function>connect</function></link></member>
1748 <title>Notes</title>
1752 <!-- Querying Operations -->
1753 <refentry id="execute-command">
1755 <refname>EXECUTE-COMMAND</refname>
1756 <refpurpose>Execute an SQL command which returns no
1757 values.</refpurpose>
1758 <refclass>Function</refclass>
1761 <title>Syntax</title>
1762 <synopsis><function>execute-command</function> <replaceable>sql-expression</replaceable> &key <replaceable>database</replaceable> => <returnvalue>t</returnvalue></synopsis>
1765 <title>Arguments and Values</title>
1768 <term><parameter>sql-expression</parameter></term>
1770 <para>An <glossterm linkend="gloss-sql-expression">sql
1771 expression</glossterm> that represents an SQL
1772 statement which will return no values.</para>
1776 <term><parameter>database</parameter></term>
1779 <glossterm linkend="gloss-database-object">database
1780 object</glossterm>. This will default to the value
1781 of <symbol>*default-database*</symbol>.</para>
1787 <title>Description</title>
1788 <para>This will execute the command given by
1789 <parameter>sql-expression</parameter> in the
1790 <parameter>database</parameter> specified. If the execution
1791 succeeds it will return <symbol>t</symbol>, otherwise an
1792 error of type <errortype>clsql-sql-error</errortype> will
1793 be signalled.</para>
1796 <title>Examples</title>
1798 (execute-command "create table eventlog (time char(30),event char(70))")
1801 (execute-command "create table eventlog (time char(30),event char(70))")
1803 >> While accessing database #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {480B2B6D}>
1804 >> with expression "create table eventlog (time char(30),event char(70))":
1805 >> Error NIL: ERROR: amcreate: eventlog relation already exists
1809 >> 0: [ABORT] Return to Top-Level.
1811 >> Debug (type H for help)
1813 >> (CLSQL-POSTGRESQL::|(PCL::FAST-METHOD DATABASE-EXECUTE-COMMAND (T POSTGRESQL-DATABASE))|
1816 >> #<unavailable-arg>
1817 >> #<unavailable-arg>)
1818 >> Source: (ERROR 'CLSQL-SQL-ERROR :DATABASE DATABASE :EXPRESSION ...)
1821 (execute-command "drop table eventlog")
1826 <title>Side Effects</title>
1827 <para>Whatever effects the execution of the SQL statement has
1828 on the underlying database, if any.</para>
1831 <title>Affected by</title>
1835 <title>Exceptional Situations</title>
1836 <para>If the execution of the SQL statement leads to any
1837 errors, an error of type
1838 <errortype>clsql-sql-error</errortype> is signalled.</para>
1841 <title>See Also</title>
1844 <member><link linkend="query"><function>query</function></link></member>
1849 <title>Notes</title>
1853 <refentry id="query">
1855 <refname>QUERY</refname>
1856 <refpurpose>Execute an SQL query and return the tuples as a
1858 <refclass>Function</refclass>
1861 <title>Syntax</title>
1862 <synopsis><function>query</function> <replaceable>query-expression</replaceable> &key <replaceable>database</replaceable> <replaceable>types</replaceable> => <returnvalue>result</returnvalue></synopsis>
1865 <title>Arguments and Values</title>
1868 <term><parameter>query-expression</parameter></term>
1870 <para>An <glossterm linkend="gloss-sql-expression">sql
1871 expression</glossterm> that represents an SQL
1872 query which is expected to return a (possibly empty)
1877 <term><parameter>database</parameter></term>
1880 <glossterm linkend="gloss-database-object">database
1881 object</glossterm>. This will default to the value
1882 of <symbol>*default-database*</symbol>.</para>
1886 <term><parameter>types</parameter></term>
1889 <glossterm linkend="gloss-field-types">field type
1890 specififier</glossterm>. The default is &nil;.
1893 The purpose of this argument is cause &clsql; to
1894 import SQL numeric fields into numeric Lisp objects
1895 rather than strings. This reduces the cost of
1896 allocating a temporary string and the &clsql; users'
1897 inconvenience of converting number strings into number
1901 A value of <symbol>:auto</symbol> causes &clsql;
1902 to automatically convert SQL fields into a
1903 numeric format where applicable. The default value of
1904 &nil; causes all fields to be returned as strings
1905 regardless of the SQL type. Otherwise a list is expected
1906 which has a element for each field that specifies the
1907 conversion. If the list is shorter than the number
1908 of fields, the a value of <symbol>t</symbol> is
1909 assumed for the field. If the list is longer than
1910 the number of fields, the extra elements are
1912 <simplelist type="vert">
1913 <member><symbol>:int</symbol> Field is imported as a
1914 signed integer, from 8-bits to 64-bits depending
1915 upon the field type.
1917 <member><symbol>:double</symbol> Field is imported as a
1918 double-float number.
1920 <member><symbol>t</symbol> Field is imported as a
1928 <term><returnvalue>result</returnvalue></term>
1930 <para>A list representing the result set obtained. For
1931 each tuple in the result set, there is an element in
1932 this list, which is itself a list of all the attribute
1933 values in the tuple.</para>
1939 <title>Description</title>
1940 <para>This will execute the query given by
1941 <parameter>query-expression</parameter> in the
1942 <parameter>database</parameter> specified. If the execution
1943 succeeds it will return the result set returned by the
1944 database, otherwise an error of type
1945 <errortype>clsql-sql-error</errortype> will
1946 be signalled.</para>
1949 <title>Examples</title>
1951 (execute-command "create table simple (name char(50), salary numeric(10,2))")
1953 (execute-command "insert into simple values ('Mai, Pierre',10000)")
1955 (execute-command "insert into simple values ('Hacker, Random J.',8000.50)")
1957 (query "select * from simple")
1958 => (("Mai, Pierre" "10000.00") ("Hacker, Random J." "8000.50"))
1959 (query "select salary from simple")
1960 => (("10000.00") ("8000.50"))
1961 (query "select salary from simple where salary > 10000")
1963 (query "select salary,name from simple where salary > 10000")
1965 (query "select salary,name from simple where salary > 9000")
1966 => (("10000.00" "Mai, Pierre"))
1967 (query "select salary,name from simple where salary > 8000")
1968 => (("10000.00" "Mai, Pierre") ("8000.50" "Hacker, Random J."))
1971 (query "show tables")
1972 => (("demo") ("log") ("newlog") ("simple") ("spacetrial"))
1976 <title>Side Effects</title>
1977 <para>Whatever effects the execution of the SQL query has
1978 on the underlying database, if any.</para>
1981 <title>Affected by</title>
1985 <title>Exceptional Situations</title>
1986 <para>If the execution of the SQL query leads to any
1987 errors, an error of type
1988 <errortype>clsql-sql-error</errortype> is signalled.</para>
1991 <title>See Also</title>
1994 <member><link linkend="execute-command"><function>execute-command</function></link></member>
1999 <title>Notes</title>
2004 <refentry id="map-query">
2006 <refname>MAP-QUERY</refname>
2007 <refpurpose>Map a function over all the tuples from a
2009 <refclass>Function</refclass>
2012 <title>Syntax</title>
2013 <synopsis><function>map-query</function> <replaceable>output-type-spec</replaceable> <replaceable>function</replaceable> <replaceable>query-expression</replaceable> &key <replaceable>database</replaceable> <replaceable>types</replaceable> => <returnvalue>result</returnvalue></synopsis>
2016 <title>Arguments and Values</title>
2019 <term><parameter>output-type-spec</parameter></term>
2021 <para>A sequence type specifier or <symbol>nil</symbol>.</para>
2025 <term><parameter>function</parameter></term>
2027 <para>A function designator.
2028 <parameter>function</parameter> must take as many
2029 arguments as are attributes in the result set returned
2030 by executing the SQL
2031 <parameter>query-expression</parameter>.</para>
2035 <term><parameter>query-expression</parameter></term>
2037 <para>An <glossterm linkend="gloss-sql-expression">sql
2038 expression</glossterm> that represents an SQL
2039 query which is expected to return a (possibly empty)
2040 result set, where each tuple has as many attributes as
2041 <parameter>function</parameter> takes arguments.</para>
2045 <term><parameter>database</parameter></term>
2048 <glossterm linkend="gloss-database-object">database
2049 object</glossterm>. This will default to the value
2050 of <symbol>*default-database*</symbol>.</para>
2054 <term><parameter>types</parameter></term>
2057 A <glossterm linkend="gloss-field-types">field type specififier</glossterm>.
2058 The default is &nil;. See <link
2059 linkend="query"><function>query</function></link>
2060 for the semantics of this argument.
2065 <term><returnvalue>result</returnvalue></term>
2067 <para>If <parameter>output-type-spec</parameter> is a
2068 type specifier other than <symbol>nil</symbol>, then a
2069 sequence of the type it denotes. Otherwise
2070 <symbol>nil</symbol> is returned.</para>
2076 <title>Description</title>
2077 <para>Applies <parameter>function</parameter> to the
2078 attributes of successive tuples in the result set returned
2079 by executing the SQL
2080 <parameter>query-expression</parameter>. If the
2081 <parameter>output-type-spec</parameter> is
2082 <symbol>nil</symbol>, then the result of each application
2083 of <parameter>function</parameter> is discarded, and
2084 <function>map-query</function> returns
2085 <symbol>nil</symbol>. Otherwise the result of each
2086 successive application of <parameter>function</parameter> is
2087 collected in a sequence of type
2088 <parameter>output-type-spec</parameter>, where the jths
2089 element is the result of applying
2090 <parameter>function</parameter> to the attributes of the
2091 jths tuple in the result set. The collected sequence is the
2092 result of the call to <function>map-query</function>.
2094 <para>If the <parameter>output-type-spec</parameter> is a
2095 subtype of <type>list</type>, the result will be a
2096 <type>list</type>.</para>
2097 <para>If the <parameter>result-type</parameter> is a subtype
2098 of <type>vector</type>, then if the implementation can
2099 determine the element type specified for the
2100 <parameter>result-type</parameter>, the element type of the
2101 resulting array is the result of
2102 <emphasis>upgrading</emphasis> that element type; or, if the
2103 implementation can determine that the element type is
2104 unspecified (or <symbol>*</symbol>), the element type of the
2105 resulting array is <type>t</type>; otherwise, an error is
2109 <title>Examples</title>
2111 (map-query 'list #'(lambda (salary name)
2112 (declare (ignorable name))
2113 (read-from-string salary))
2114 "select salary,name from simple where salary > 8000")
2117 (map-query '(vector double-float)
2118 #'(lambda (salary name)
2119 (declare (ignorable name))
2120 (let ((*read-default-float-format* 'double-float))
2121 (coerce (read-from-string salary) 'double-float))
2122 "select salary,name from simple where salary > 8000"))
2123 => #(10000.0d0 8000.5d0)
2125 => (SIMPLE-ARRAY DOUBLE-FLOAT (2))
2128 (values (map-query nil #'(lambda (salary name)
2129 (push (cons name (read-from-string salary)) list))
2130 "select salary,name from simple where salary > 8000")
2133 => (("Hacker, Random J." . 8000.5) ("Mai, Pierre" . 10000.0))
2137 <title>Side Effects</title>
2138 <para>Whatever effects the execution of the SQL query has
2139 on the underlying database, if any.</para>
2142 <title>Affected by</title>
2146 <title>Exceptional Situations</title>
2147 <para>If the execution of the SQL query leads to any
2148 errors, an error of type
2149 <errortype>clsql-sql-error</errortype> is signalled.</para>
2150 <para>An error of type <errortype>type-error</errortype> must
2151 be signaled if the <parameter>output-type-spec</parameter> is
2152 not a recognizable subtype of <type>list</type>, not a
2153 recognizable subtype of <type>vector</type>, and not
2154 <symbol>nil</symbol>.</para>
2155 <para>An error of type <errortype>type-error</errortype>
2156 should be signaled if
2157 <parameter>output-type-spec</parameter> specifies the number
2158 of elements and the size of the result set is different from
2162 <title>See Also</title>
2165 <member><link linkend="query"><function>query</function></link></member>
2166 <member><link linkend="do-query"><function>do-query</function></link></member>
2171 <title>Notes</title>
2175 <refentry id="do-query">
2177 <refname>DO-QUERY</refname>
2178 <refpurpose>Iterate over all the tuples of a
2180 <refclass>Macro</refclass>
2183 <title>Syntax</title>
2184 <synopsis><function>do-query</function> ((&rest <replaceable>args</replaceable>) <replaceable>query-expression</replaceable> &key <replaceable>database</replaceable> <replaceable>types</replaceable>) &body <replaceable>body</replaceable> => <returnvalue>nil</returnvalue></synopsis>
2187 <title>Arguments and Values</title>
2190 <term><parameter>args</parameter></term>
2192 <para>A list of variable names.</para>
2196 <term><parameter>query-expression</parameter></term>
2198 <para>An <glossterm linkend="gloss-sql-expression">sql
2199 expression</glossterm> that represents an SQL
2200 query which is expected to return a (possibly empty)
2201 result set, where each tuple has as many attributes as
2202 <parameter>function</parameter> takes arguments.</para>
2206 <term><parameter>database</parameter></term>
2209 <glossterm linkend="gloss-database-object">database
2210 object</glossterm>. This will default to
2211 <symbol>*default-database*</symbol>.</para>
2215 <term><parameter>types</parameter></term>
2218 A <glossterm linkend="gloss-field-types">field type specififier</glossterm>.
2219 The default is &nil;. See <link
2220 linkend="query"><function>query</function></link>
2221 for the semantics of this argument.
2226 <term><parameter>body</parameter></term>
2228 <para>A body of Lisp code, like in a
2229 <function>destructuring-bind</function> form.</para>
2235 <title>Description</title>
2236 <para>Executes the <parameter>body</parameter> of code
2237 repeatedly with the variable names in
2238 <parameter>args</parameter> bound to the attributes of each
2239 tuple in the result set returned by executing the SQL
2240 <parameter>query-expression</parameter> on the
2241 <parameter>database</parameter> specified.</para>
2242 <para>The body of code is executed in a block named
2243 <symbol>nil</symbol> which may be returned from prematurely
2244 via <function>return</function> or
2245 <function>return-from</function>. In this case the result
2246 of evaluating the <function>do-query</function> form will be
2247 the one supplied to <function>return</function> or
2248 <function>return-from</function>. Otherwise the result will
2249 be <symbol>nil</symbol>.</para>
2250 <para>The body of code appears also is if wrapped in a
2251 <function>destructuring-bind</function> form, thus allowing
2252 declarations at the start of the body, especially those
2253 pertaining to the bindings of the variables named in
2254 <parameter>args</parameter>.</para>
2257 <title>Examples</title>
2259 (do-query ((salary name) "select salary,name from simple")
2260 (format t "~30A gets $~2,5$~%" name (read-from-string salary)))
2261 >> Mai, Pierre gets $10000.00
2262 >> Hacker, Random J. gets $08000.50
2265 (do-query ((salary name) "select salary,name from simple")
2266 (return (cons salary name)))
2267 => ("10000.00" . "Mai, Pierre")
2271 <title>Side Effects</title>
2272 <para>Whatever effects the execution of the SQL query has
2273 on the underlying database, if any.</para>
2276 <title>Affected by</title>
2280 <title>Exceptional Situations</title>
2281 <para>If the execution of the SQL query leads to any
2282 errors, an error of type
2283 <errortype>clsql-sql-error</errortype> is signalled.</para>
2284 <para>If the number of variable names in
2285 <parameter>args</parameter> and the number of attributes in
2286 the tuples in the result set don't match up, an error is
2290 <title>See Also</title>
2293 <member><link linkend="query"><function>query</function></link></member>
2294 <member><link linkend="map-query"><function>map-query</function></link></member>
2299 <title>Notes</title>
2303 <refentry id="loop-tuples">
2305 <refname>LOOP-FOR-AS-TUPLES</refname>
2306 <refpurpose>Iterate over all the tuples of a
2307 query via a loop clause</refpurpose>
2308 <refclass>Loop Clause</refclass>
2311 <title>Compatibility</title>
2312 <caution><para><function>loop-for-as-tuples</function> only works with &cmucl;.</para></caution>
2315 <title>Syntax</title>
2316 <synopsis><replaceable>var</replaceable> [<replaceable>type-spec</replaceable>] being {each | the} {record | records | tuple | tuples} {in | of} <replaceable>query</replaceable> [from <replaceable>database</replaceable>]</synopsis>
2319 <title>Arguments and Values</title>
2322 <term><parameter>var</parameter></term>
2324 <para>A <literal>d-var-spec</literal>, as defined in the
2325 grammar for <function>loop</function>-clauses in the
2326 ANSI Standard for Common Lisp. This allows for the
2327 usual loop-style destructuring.</para>
2331 <term><parameter>type-spec</parameter></term>
2333 <para>An optional <literal>type-spec</literal> either
2334 simple or destructured, as defined in the grammar for
2335 <function>loop</function>-clauses in the ANSI Standard
2336 for Common Lisp.</para>
2340 <term><parameter>query</parameter></term>
2342 <para>An <glossterm linkend="gloss-sql-expression">sql
2343 expression</glossterm> that represents an SQL
2344 query which is expected to return a (possibly empty)
2345 result set, where each tuple has as many attributes as
2346 <parameter>function</parameter> takes arguments.</para>
2350 <term><parameter>database</parameter></term>
2353 <glossterm linkend="gloss-database-object">database
2354 object</glossterm>. This will default to the value
2355 of <symbol>*default-database*</symbol>.</para>
2361 <title>Description</title>
2362 <para>This clause is an iteration driver for
2363 <function>loop</function>, that binds the given variable
2364 (possibly destructured) to the consecutive tuples (which are
2365 represented as lists of attribute values) in the result set
2366 returned by executing the SQL <parameter>query</parameter>
2367 expression on the <parameter>database</parameter>
2371 <title>Examples</title>
2373 (defvar *my-db* (connect '("dent" "newesim" "dent" "dent"))
2376 (loop with time-graph = (make-hash-table :test #'equal)
2377 with event-graph = (make-hash-table :test #'equal)
2378 for (time event) being the tuples of "select time,event from log"
2381 (incf (gethash time time-graph 0))
2382 (incf (gethash event event-graph 0))
2384 (flet ((show-graph (k v) (format t "~40A => ~5D~%" k v)))
2385 (format t "~&Time-Graph:~%===========~%")
2386 (maphash #'show-graph time-graph)
2387 (format t "~&~%Event-Graph:~%============~%")
2388 (maphash #'show-graph event-graph))
2389 (return (values time-graph event-graph)))
2398 >> CLOS Benchmark entry. => 9000
2399 >> Demo Text... => 3
2400 >> doit-text => 3000
2401 >> C Benchmark entry. => 12000
2402 >> CLOS Benchmark entry => 32000
2403 => #<EQUAL hash table, 3 entries {48350A1D}>
2404 => #<EQUAL hash table, 5 entries {48350FCD}>
2408 <title>Side Effects</title>
2409 <para>Whatever effects the execution of the SQL query has
2410 on the underlying database, if any.</para>
2413 <title>Affected by</title>
2417 <title>Exceptional Situations</title>
2418 <para>If the execution of the SQL query leads to any
2419 errors, an error of type
2420 <errortype>clsql-sql-error</errortype> is signalled.</para>
2421 <para>Otherwise, any of the exceptional situations of
2422 <function>loop</function> applies.</para>
2425 <title>See Also</title>
2428 <member><link linkend="query"><function>query</function></link></member>
2429 <member><link linkend="map-query"><function>map-query</function></link></member>
2430 <member><link linkend="do-query"><function>do-query</function></link></member>
2435 <title>Notes</title>
2442 <title><symbol>CLSQL-SYS</symbol></title>
2444 <para>This part gives a reference to all the symbols exported
2445 from the package <symbol>CLSQL-SYS</symbol>, which are not also
2446 exported from <symbol>CLSQL</symbol>. These symbols are part of
2447 the interface for database back-ends, but not part of the normal
2448 user-interface of &clsql;.</para>
2450 <refentry id="database-initialize-database-type">
2452 <refname>DATABASE-INITIALIZE-DATABASE-TYPE</refname>
2453 <refpurpose>Back-end part of <link
2454 linkend="initialize-database-type"><function>initialize-database-type</function></link>.</refpurpose>
2455 <refclass>Generic Function</refclass>
2458 <title>Syntax</title>
2459 <synopsis><function>database-initialize-database-type</function> <replaceable>database-type</replaceable> => <returnvalue>result</returnvalue></synopsis>
2462 <title>Arguments and Values</title>
2465 <term><parameter>database-type</parameter></term>
2467 <para>A keyword indicating the database type to
2472 <term><returnvalue>result</returnvalue></term>
2474 <para>Either <symbol>t</symbol> if the initialization
2475 succeeds or <symbol>nil</symbol> if it fails.</para>
2481 <title>Description</title>
2482 <para>This generic function implements the main part of the
2483 database type initialization performed by
2484 <function>initialize-database-type</function>. After
2485 <function>initialize-database-type</function> has checked
2486 that the given database type has not been initialized
2487 before, as indicated by
2488 <symbol>*initialized-database-types*</symbol>, it will call
2489 this function with the database type as it's sole
2490 parameter. Database back-ends are required to define a
2491 method on this generic function which is specialized via an
2492 eql-specializer to the keyword representing their database
2494 <para>Database back-ends shall indicate successful
2495 initialization by returning <symbol>t</symbol> from their
2496 method, and <symbol>nil</symbol> otherwise. Methods for
2497 this generic function are allowed to signal errors of type
2498 <errortype>clsql-error</errortype> or subtypes thereof.
2499 They may also signal other types of conditions, if
2500 appropriate, but have to document this.</para>
2503 <title>Examples</title>
2507 <title>Side Effects</title>
2508 <para>All necessary side effects to initialize the database
2512 <title>Affected By</title>
2516 <title>Exceptional Situations</title>
2517 <para>Conditions of type <errortype>clsql-error</errortype>
2518 or other conditions may be signalled, depending on the
2519 database back-end.</para>
2522 <title>See Also</title>
2526 linkend="initialize-database-type"><function>initialize-database-type</function></link></member>
2527 <member><link linkend="initialized-database-types"><symbol>*initialized-database-types*</symbol></link></member>
2532 <title>Notes</title>