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">
9 <title><symbol>CLSQL</symbol></title>
11 <para>This part gives a reference to the symbols exported from the
12 package <symbol>CLSQL-SYS</symbol>, which are also re-exported
13 from the package <symbol>CLSQL</symbol>. These symbols constitute
14 the normal user-interface of &clsql;. Currently, the symbols of
15 the &commonsql;-API are not documented here.</para>
18 <refentry id="clsql-condition">
20 <refname>CLSQL-CONDITION</refname>
21 <refpurpose>the super-type of all
23 conditions</refpurpose>
24 <refclass>Condition Type</refclass>
27 <title>Class Precedence List</title>
29 <simplelist type="inline">
30 <member><errortype>clsql-condition</errortype></member>
31 <member><errortype>condition</errortype></member>
32 <member><errortype>t</errortype></member>
37 <title>Description</title>
38 <para>This is the super-type of all
39 &clsql;-specific conditions
40 defined by &clsql;, or any of it's
41 database-specific interfaces. There are no defined
42 initialization arguments nor any accessors.</para>
45 <refentry id="clsql-error">
47 <refname>CLSQL-ERROR</refname>
48 <refpurpose>the super-type of all
51 <refclass>Condition Type</refclass>
54 <title>Class Precedence List</title>
56 <simplelist type="inline">
57 <member><errortype>clsql-error</errortype></member>
58 <member><errortype>error</errortype></member>
59 <member><errortype>serious-condition</errortype></member>
60 <member><errortype>clsql-condition</errortype></member>
61 <member><errortype>condition</errortype></member>
62 <member><errortype>t</errortype></member>
67 <title>Description</title>
68 <para>This is the super-type of all
69 &clsql;-specific conditions that
70 represent errors, as defined by
71 &clsql;, or any of it's
72 database-specific interfaces. There are no defined
73 initialization arguments nor any accessors.</para>
76 <refentry id="clsql-simple-error">
78 <refname>CLSQL-SIMPLE-ERROR</refname>
79 <refpurpose>Unspecific simple
80 &clsql; errors</refpurpose>
81 <refclass>Condition Type</refclass>
84 <title>Class Precedence List</title>
86 <simplelist type="inline">
87 <member><errortype>clsql-simple-error</errortype></member>
88 <member><errortype>simple-condition</errortype></member>
89 <member><errortype>clsql-error</errortype></member>
90 <member><errortype>error</errortype></member>
91 <member><errortype>serious-condition</errortype></member>
92 <member><errortype>clsql-condition</errortype></member>
93 <member><errortype>condition</errortype></member>
94 <member><errortype>t</errortype></member>
99 <title>Description</title>
100 <para>This condition is used in all instances of errors, where
101 there exists no &clsql;-specific
102 condition that is more specific. The valid initialization
103 arguments and accessors are the same as for
104 <errortype>simple-condition</errortype>.</para>
107 <refentry id="clsql-warning">
109 <refname>CLSQL-WARNING</refname>
110 <refpurpose>the super-type of all
112 warnings</refpurpose>
113 <refclass>Condition Type</refclass>
116 <title>Class Precedence List</title>
118 <simplelist type="inline">
119 <member><errortype>clsql-warning</errortype></member>
120 <member><errortype>warning</errortype></member>
121 <member><errortype>clsql-condition</errortype></member>
122 <member><errortype>condition</errortype></member>
123 <member><errortype>t</errortype></member>
128 <title>Description</title>
129 <para>This is the super-type of all
130 &clsql;-specific conditions that
131 represent warnings, as defined by
132 &clsql;, or any of it's
133 database-specific interfaces. There are no defined
134 initialization arguments nor any accessors.</para>
137 <refentry id="clsql-simple-warning">
139 <refname>CLSQL-SIMPLE-WARNING</refname>
140 <refpurpose>Unspecific simple
141 &clsql; warnings</refpurpose>
142 <refclass>Condition Type</refclass>
145 <title>Class Precedence List</title>
147 <simplelist type="inline">
148 <member><errortype>clsql-simple-warning</errortype></member>
149 <member><errortype>simple-condition</errortype></member>
150 <member><errortype>clsql-warning</errortype></member>
151 <member><errortype>warning</errortype></member>
152 <member><errortype>clsql-condition</errortype></member>
153 <member><errortype>condition</errortype></member>
154 <member><errortype>t</errortype></member>
159 <title>Description</title>
160 <para>This condition is used in all instances of warnings,
161 where there exists no
162 &clsql;-specific condition that is
163 more specific. The valid initialization arguments and
164 accessors are the same as for
165 <errortype>simple-condition</errortype>.</para>
168 <!-- Specifc Conditions -->
169 <refentry id="clsql-invalid-spec-error">
171 <refname>CLSQL-INVALID-SPEC-ERROR</refname>
172 <refpurpose>condition representing errors because of invalid
173 connection specifications</refpurpose>
174 <refclass>Condition Type</refclass>
177 <title>Class Precedence List</title>
179 <simplelist type="inline">
180 <member><errortype>clsql-invalid-spec-error</errortype></member>
181 <member><errortype>clsql-error</errortype></member>
182 <member><errortype>error</errortype></member>
183 <member><errortype>serious-condition</errortype></member>
184 <member><errortype>clsql-condition</errortype></member>
185 <member><errortype>condition</errortype></member>
186 <member><errortype>t</errortype></member>
191 <title>Description</title>
192 <para>This condition represents errors that occur because the
193 user supplies an invalid connection specification to either
194 <function>database-name-from-spec</function> or
195 <function>connect</function>. The following initialization
196 arguments and accessors exist:</para>
198 <segtitle>Initarg</segtitle>
199 <segtitle>Accessor</segtitle>
200 <segtitle>Description</segtitle>
202 <seg><symbol>:connection-spec</symbol></seg>
203 <seg><function>clsql-invalid-spec-error-connection-spec</function></seg>
204 <seg>The invalid connection specification used.</seg>
207 <seg><symbol>:database-type</symbol></seg>
208 <seg><function>clsql-invalid-spec-error-database-type</function></seg>
209 <seg>The Database type used in the attempt.</seg>
212 <seg><symbol>:template</symbol></seg>
213 <seg><function>clsql-invalid-spec-error-template</function></seg>
214 <seg>An argument describing the template that a valid
215 connection specification must match for this database type.</seg>
220 <refentry id="clsql-connect-error">
222 <refname>CLSQL-CONNECT-ERROR</refname>
223 <refpurpose>condition representing errors during
224 connection</refpurpose>
225 <refclass>Condition Type</refclass>
228 <title>Class Precedence List</title>
230 <simplelist type="inline">
231 <member><errortype>clsql-connect-error</errortype></member>
232 <member><errortype>clsql-error</errortype></member>
233 <member><errortype>error</errortype></member>
234 <member><errortype>serious-condition</errortype></member>
235 <member><errortype>clsql-condition</errortype></member>
236 <member><errortype>condition</errortype></member>
237 <member><errortype>t</errortype></member>
242 <title>Description</title>
243 <para>This condition represents errors that occur while trying
244 to connect to a database. The following initialization
245 arguments and accessors exist:</para>
247 <segtitle>Initarg</segtitle>
248 <segtitle>Accessor</segtitle>
249 <segtitle>Description</segtitle>
251 <seg><symbol>:database-type</symbol></seg>
252 <seg><function>clsql-connect-error-database-type</function></seg>
253 <seg>Database type for the connection attempt</seg>
256 <seg><symbol>:connection-spec</symbol></seg>
257 <seg><function>clsql-connect-error-connection-spec</function></seg>
258 <seg>The connection specification used in the
259 connection attempt.</seg>
262 <seg><symbol>:errno</symbol></seg>
263 <seg><function>clsql-connect-error-errno</function></seg>
264 <seg>The numeric or symbolic error specification
265 returned by the database back-end. The values and
266 semantics of this are interface specific.</seg>
269 <seg><symbol>:error</symbol></seg>
270 <seg><function>clsql-connect-error-error</function></seg>
271 <seg>A string describing the problem that occurred,
272 possibly one returned by the database back-end.</seg>
277 <refentry id="clsql-sql-error">
279 <refname>CLSQL-SQL-ERROR</refname>
280 <refpurpose>condition representing errors during query or
281 command execution</refpurpose>
282 <refclass>Condition Type</refclass>
285 <title>Class Precedence List</title>
287 <simplelist type="inline">
288 <member><errortype>clsql-sql-error</errortype></member>
289 <member><errortype>clsql-error</errortype></member>
290 <member><errortype>error</errortype></member>
291 <member><errortype>serious-condition</errortype></member>
292 <member><errortype>clsql-condition</errortype></member>
293 <member><errortype>condition</errortype></member>
294 <member><errortype>t</errortype></member>
299 <title>Description</title>
300 <para>This condition represents errors that occur while
301 executing SQL statements, either as part of query operations
302 or command execution, either explicitly or implicitly, as
303 caused e.g. by <function>with-transaction</function>.
304 The following initialization arguments and accessors exist:</para>
306 <segtitle>Initarg</segtitle>
307 <segtitle>Accessor</segtitle>
308 <segtitle>Description</segtitle>
310 <seg><symbol>:database</symbol></seg>
311 <seg><function>clsql-sql-error-database</function></seg>
312 <seg>The database object that was involved in the
316 <seg><symbol>:expression</symbol></seg>
317 <seg><function>clsql-sql-error-expression</function></seg>
318 <seg>The SQL expression whose execution caused the error.</seg>
321 <seg><symbol>:errno</symbol></seg>
322 <seg><function>clsql-sql-error-errno</function></seg>
323 <seg>The numeric or symbolic error specification
324 returned by the database back-end. The values and
325 semantics of this are interface specific.</seg>
328 <seg><symbol>:error</symbol></seg>
329 <seg><function>clsql-sql-error-error</function></seg>
330 <seg>A string describing the problem that occurred,
331 possibly one returned by the database back-end.</seg>
336 <refentry id="clsql-exists-condition">
338 <refname>CLSQL-EXISTS-CONDITION</refname>
339 <refpurpose>condition indicating situations arising because of
340 existing connections</refpurpose>
341 <refclass>Condition Type</refclass>
344 <title>Class Precedence List</title>
346 <simplelist type="inline">
347 <member><errortype>clsql-exists-condition</errortype></member>
348 <member><errortype>clsql-condition</errortype></member>
349 <member><errortype>condition</errortype></member>
350 <member><errortype>t</errortype></member>
355 <title>Description</title>
356 <para>This condition is the super-type of all conditions which
357 represents problems that occur during calls to
358 <function>connect</function>, if a connection to the
359 database exists already. Depending on the value of
360 <parameter>if-exists</parameter> to the call of
361 <function>connect</function>, either a warning, an error or
362 no condition at all is signalled. If a warning or error is
364 <errortype>clsql-exists-warning</errortype> or
365 <errortype>clsql-exists-error</errortype> is signalled,
366 which are subtypes of
367 <errortype>clsql-exists-condition</errortype> and
368 <errortype>clsql-warning</errortype> or
369 <errortype>clsql-error</errortype>.
370 <errortype>clsql-exists-condition</errortype> is never
371 signalled itself.</para>
373 The following initialization arguments and accessors exist:</para>
375 <segtitle>Initarg</segtitle>
376 <segtitle>Accessor</segtitle>
377 <segtitle>Description</segtitle>
379 <seg><symbol>:old-db</symbol></seg>
380 <seg><function>clsql-exists-condition-old-db</function></seg>
381 <seg>The database object that represents the existing
382 connection. This slot is always filled.</seg>
385 <seg><symbol>:new-db</symbol></seg>
386 <seg><function>clsql-exists-condition-new-db</function></seg>
387 <seg>The database object that will be used and returned by
388 this call to connect, if execution continues normally.
389 This can be either <symbol>nil</symbol>, indicating that
390 a new database object is to be created on continuation,
391 or a database object representing the newly created
392 continuation, or the same database object as
393 <symbol>old-db</symbol>, indicating that the existing
394 database object will be reused. This slot is always
395 filled and defaults to <symbol>nil</symbol>.</seg>
400 <refentry id="clsql-exists-warning">
402 <refname>CLSQL-EXISTS-WARNING</refname>
403 <refpurpose>condition representing warnings arising because of
404 existing connections</refpurpose>
405 <refclass>Condition Type</refclass>
408 <title>Class Precedence List</title>
410 <simplelist type="inline">
411 <member><errortype>clsql-exists-warning</errortype></member>
412 <member><errortype>clsql-exists-condition</errortype></member>
413 <member><errortype>clsql-warning</errortype></member>
414 <member><errortype>warning</errortype></member>
415 <member><errortype>clsql-condition</errortype></member>
416 <member><errortype>condition</errortype></member>
417 <member><errortype>t</errortype></member>
422 <title>Description</title>
423 <para>This condition is a subtype of
424 <errortype>clsql-exists-condition</errortype>, and is
425 signalled during calls to <function>connect</function> when
426 there is an existing connection, and
427 <parameter>if-exists</parameter> is either
428 <symbol>:warn-new</symbol> or <symbol>:warn-old</symbol>.
429 In the former case, <symbol>new-db</symbol> will be the
430 newly created database object, in the latter case it will be
431 the existing old database object.</para>
433 The initialization arguments and accessors are the same as
434 for <errortype>clsql-exists-condition</errortype>.</para>
437 <refentry id="clsql-exists-error">
439 <refname>CLSQL-EXISTS-ERROR</refname>
440 <refpurpose>condition representing errors arising because of
441 existing connections</refpurpose>
442 <refclass>Condition Type</refclass>
445 <title>Class Precedence List</title>
447 <simplelist type="inline">
448 <member><errortype>clsql-exists-error</errortype></member>
449 <member><errortype>clsql-exists-condition</errortype></member>
450 <member><errortype>clsql-error</errortype></member>
451 <member><errortype>error</errortype></member>
452 <member><errortype>serious-condition</errortype></member>
453 <member><errortype>clsql-condition</errortype></member>
454 <member><errortype>condition</errortype></member>
455 <member><errortype>t</errortype></member>
460 <title>Description</title>
461 <para>This condition is a subtype of
462 <errortype>clsql-exists-condition</errortype>, and is
463 signalled during calls to <function>connect</function> when
464 there is an existing connection, and
465 <parameter>if-exists</parameter> is <symbol>:error</symbol>.
466 In this case, <symbol>new-db</symbol> will be
467 <symbol>nil</symbol>, indicating that the database object to
468 be returned by <function>connect</function> depends on user
469 action in continuing from this correctable error.</para>
471 The initialization arguments and accessors are the same as
472 for <errortype>clsql-exists-condition</errortype>.</para>
475 <refentry id="clsql-closed-error">
477 <refname>CLSQL-CLOSED-ERROR</refname>
478 <refpurpose>condition representing errors because the database
479 has already been closed</refpurpose>
480 <refclass>Condition Type</refclass>
483 <title>Class Precedence List</title>
485 <simplelist type="inline">
486 <member><errortype>clsql-closed-error</errortype></member>
487 <member><errortype>clsql-error</errortype></member>
488 <member><errortype>error</errortype></member>
489 <member><errortype>serious-condition</errortype></member>
490 <member><errortype>clsql-condition</errortype></member>
491 <member><errortype>condition</errortype></member>
492 <member><errortype>t</errortype></member>
497 <title>Description</title>
498 <para>This condition represents errors that occur because the
499 user invokes an operation on the given database object,
500 although the database is invalid because
501 <function>disconnect</function> has already been called on
502 this database object.</para>
503 <para>Functions which signal this error when called with a
504 closed database will usually provide a
505 <symbol>continue</symbol> restart, that will just return nil
506 from the function.</para>
508 The following initialization arguments and accessors exist:</para>
510 <segtitle>Initarg</segtitle>
511 <segtitle>Accessor</segtitle>
512 <segtitle>Description</segtitle>
514 <seg><symbol>:database</symbol></seg>
515 <seg><function>clsql-closed-error-database</function></seg>
516 <seg>The database object that was involved in the
523 <!-- Database Types -->
524 <refentry id="default-database-type">
526 <refname>*DEFAULT-DATABASE-TYPE*</refname>
527 <refpurpose>The default database type to use</refpurpose>
528 <refclass>Variable</refclass>
531 <title>Value Type</title>
532 <para>Any keyword representing a valid database back-end of
534 <symbol>nil</symbol>.</para>
537 <title>Initial Value</title>
538 <para><symbol>nil</symbol></para>
541 <title>Description</title>
542 <para>The value of this variable is used in calls to
543 <function>initialize-database-type</function> and
544 <function>connect</function> as the default
545 value of the <parameter>database-type</parameter>
548 <para>If the value of this variable is <symbol>nil</symbol>,
550 <function>initialize-database-type</function> or
551 <function>connect</function> will have to specify the
552 <parameter>database-type</parameter> to use, or a
553 general-purpose error will be signalled.</para>
557 <title>Examples</title>
559 (setf *default-database-type* :mysql)
561 (initialize-database-type)
566 <title>Affected By</title>
570 <title>See Also</title>
578 <refentry id="initialized-database-types">
580 <refname>*INITIALIZED-DATABASE-TYPES*</refname>
581 <refpurpose>List of all initialized database types</refpurpose>
582 <refclass>Variable</refclass>
585 <title>Value Type</title>
586 <para>A list of all initialized database types, each of which
587 represented by it's corresponding keyword.</para>
590 <title>Initial Value</title>
591 <para><symbol>nil</symbol></para>
594 <title>Description</title>
595 <para>This variable is updated whenever
596 <function>initialize-database-type</function> is called for a
597 database type which hasn't already been initialized before,
598 as determined by this variable. In that case the keyword
599 representing the database type is pushed onto the list
601 <symbol>*INITIALIZED-DATABASE-TYPES*</symbol>.</para>
603 <para>Attempts to modify the value of this variable will
604 result in undefined behaviour.</para>
608 <title>Examples</title>
610 (setf *default-database-type* :mysql)
612 (initialize-database-type)
614 *initialized-database-types*
619 <title>Affected By</title>
622 <member><function>initialize-database-type</function></member>
627 <title>See Also</title>
632 <para>Direct access to this variable is primarily provided
633 because of compatibility with Harlequin's <application>Common
634 SQL</application>.</para>
637 <refentry id="initialize-database-type">
639 <refname>INITIALIZE-DATABASE-TYPE</refname>
640 <refpurpose>Initializes a database type</refpurpose>
641 <refclass>Function</refclass>
644 <title>Syntax</title>
645 <synopsis><function>initialize-database-type</function> &key <replaceable>database-type</replaceable> => <returnvalue>result</returnvalue></synopsis>
648 <title>Arguments and Values</title>
651 <term><parameter>database-type</parameter></term>
653 <para>The database type to initialize, i.e. a keyword
654 symbol denoting a known database back-end. Defaults to
656 <symbol>*default-database-type*</symbol>.</para>
660 <term><returnvalue>result</returnvalue></term>
662 <para>Either <symbol>nil</symbol> if the initialization
663 attempt fails, or <symbol>t</symbol> otherwise.</para>
669 <title>Description</title>
670 <para>If the back-end specified by
671 <parameter>database-type</parameter> has not already been
672 initialized, as seen from
673 <symbol>*initialized-database-types*</symbol>, an attempt is
674 made to initialize the database. If this attempt succeeds,
675 or the back-end has already been initialized, the function
676 returns t, and places the keyword denoting the database type
677 onto the list stored in
678 <symbol>*initialized-database-types*</symbol>, if not
679 already present.</para>
680 <para>If initialization fails, the function returns
681 <symbol>nil</symbol>, and/or signals an error of type
682 <errortype>clsql-error</errortype>. The kind of action
683 taken depends on the back-end and the cause of the
687 <title>Examples</title>
689 *initialized-database-types*
691 (setf *default-database-type* :mysql)
693 (initialize-database-type)
694 >> Compiling LAMBDA (#:G897 #:G898 #:G901 #:G902):
695 >> Compiling Top-Level Form:
698 *initialized-database-types*
700 (initialize-database-type)
702 *initialized-database-types*
707 <title>Side Effects</title>
708 <para>The database back-end corresponding to the database type
709 specified is initialized, unless it has already been
710 initialized. This can involve any number of other side
711 effects, as determined by the back-end implementation (like
712 e.g. loading of foreign code, calling of foreign code,
713 networking operations, etc.). If initialization is
714 attempted and succeeds, the
715 <parameter>database-type</parameter> is pushed onto the list
717 <symbol>*initialized-database-types*</symbol>.</para>
720 <title>Affected by</title>
723 <member><symbol>*default-database-type*</symbol></member>
724 <member><symbol>*initialized-database-types*</symbol></member>
729 <title>Exceptional Situations</title>
730 <para>If an error is encountered during the initialization
731 attempt, the back-end may signal errors of kind
732 <errortype>clsql-error</errortype>.</para>
735 <title>See Also</title>
743 <!-- Databases Connection and Disconnection -->
744 <refentry id="connect-if-exists">
746 <refname>*CONNECT-IF-EXISTS*</refname>
747 <refpurpose>Default value for the
748 <parameter>if-exists</parameter> parameter of
749 <function>connect</function>.</refpurpose>
750 <refclass>Variable</refclass>
753 <title>Value Type</title>
754 <para>A valid argument to the <parameter>if-exists</parameter>
755 parameter of <function>connect</function>, i.e. one of
756 <simplelist type="inline">
757 <member><symbol>:new</symbol></member>
758 <member><symbol>:warn-new</symbol></member>
759 <member><symbol>:error</symbol></member>
760 <member><symbol>:warn-old</symbol></member>
761 <member><symbol>:old</symbol></member>
766 <title>Initial Value</title>
767 <para><symbol>:error</symbol></para>
770 <title>Description</title>
771 <para>The value of this variable is used in calls to
772 <function>connect</function> as the default
773 value of the <parameter>if-exists</parameter>
775 linkend="connect"><function>connect</function></link> for
776 the semantics of the valid values for this variable.</para>
779 <title>Examples</title>
783 <title>Affected By</title>
787 <title>See Also</title>
791 linkend="connect"><function>connect</function></link></member>
800 <refentry id="connected-databases">
802 <refname>CONNECTED-DATABASES</refname>
803 <refpurpose>Return the list of active database
804 objects.</refpurpose>
805 <refclass>Function</refclass>
808 <title>Syntax</title>
809 <synopsis><function>connected-databases</function> => <returnvalue>databases</returnvalue></synopsis>
812 <title>Arguments and Values</title>
815 <term><returnvalue>databases</returnvalue></term>
817 <para>The list of active database objects.</para>
823 <title>Description</title>
824 <para>This function returns the list of active database
825 objects, i.e. all those database objects created by calls to
826 <function>connect</function>, which have not been closed by
827 calling <function>disconnect</function> on them.</para>
829 <para>The consequences of modifying the list returned by
830 <function>connected-databases</function> are
835 <title>Examples</title>
837 (connected-databases)
839 (connect '(nil "template1" "dent" nil) :database-type :postgresql)
840 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {4830BC65}>
841 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql)
842 => #<CLSQL-MYSQL:MYSQL-DATABASE {4830C5AD}>
843 (connected-databases)
844 => (#<CLSQL-MYSQL:MYSQL-DATABASE {4830C5AD}>
845 #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {4830BC65}>)
848 (connected-databases)
849 => (#<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {4830BC65}>)
852 (connected-databases)
857 <title>Side Effects</title>
861 <title>Affected By</title>
864 <member><function>connect</function></member>
865 <member><function>disconnect</function></member>
870 <title>Exceptional Situations</title>
874 <title>See Also</title>
882 <refentry id="default-database">
884 <refname>*DEFAULT-DATABASE*</refname>
885 <refpurpose>The default database object to use</refpurpose>
886 <refclass>Variable</refclass>
889 <title>Value Type</title>
890 <para>Any object of type <type>database</type>, or nil to
891 indicate no default database.</para>
894 <title>Initial Value</title>
895 <para><symbol>nil</symbol></para>
898 <title>Description</title>
899 <para>Any function or macro in
900 &clsql; that operates on a
901 database uses the value of this variable as the default
902 value for it's <parameter>database</parameter>
904 <para>The value of this parameter is changed by calls to
905 <function>connect</function>, which sets
906 <symbol>*default-database*</symbol> to the database object
907 it returns. It is also changed by calls to
908 <function>disconnect</function>, when the database object
909 being disconnected is the same as the value of
910 <symbol>*default-database*</symbol>. In this case
911 <function>disconnect</function> sets
912 <symbol>*default-database*</symbol> to the first database
913 that remains in the list of active databases as returned by
914 <function>connected-databases</function>, or
915 <symbol>nil</symbol> if no further active databases
917 <para>The user may change <symbol>*default-database*</symbol>
918 at any time to a valid value of his choice.</para>
920 <para>If the value of <symbol>*default-database*</symbol> is
921 <symbol>nil</symbol>, then all calls to
922 &clsql; functions on databases
923 must provide a suitable <parameter>database</parameter>
924 parameter, or an error will be signalled.</para>
928 <title>Examples</title>
930 (connected-databases)
932 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql)
933 => #<CLSQL-MYSQL:MYSQL-DATABASE {48385F55}>
934 (connect '(nil "template1" "dent" nil) :database-type :postgresql)
935 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {483868FD}>
936 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql :if-exists :new)
937 => #<CLSQL-MYSQL:MYSQL-DATABASE {48387265}>
939 => #<CLSQL-MYSQL:MYSQL-DATABASE {48387265}>
943 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {483868FD}>
947 => #<CLSQL-MYSQL:MYSQL-DATABASE {48385F55}>
952 (connected-databases)
957 <title>Affected By</title>
960 <member><link linkend="connect"><function>connect</function></link></member>
961 <member><link linkend="disconnect"><function>disconnect</function></link></member>
966 <title>See Also</title>
969 <member><link linkend="connected-databases"><function>connected-databases</function></link></member>
976 <para>This variable is intended to facilitate working with
977 &clsql; in an interactive
978 fashion at the top-level loop, and because of this,
979 <function>connect</function> and
980 <function>disconnect</function> provide some fairly
981 complex behaviour to keep
982 <symbol>*default-database*</symbol> set to useful values.
983 Programmatic use of &clsql;
984 should never depend on the value of
985 <symbol>*default-database*</symbol> and should provide
986 correct database objects via the
987 <parameter>database</parameter> parameter to functions
993 <refentry id="database">
995 <refname>DATABASE</refname>
996 <refpurpose>The super-type of all
997 &clsql; databases</refpurpose>
998 <refclass>Class</refclass>
1001 <title>Class Precedence List</title>
1003 <simplelist type="inline">
1004 <member><type>database</type></member>
1005 <member><type>standard-object</type></member>
1006 <member><type>t</type></member>
1011 <title>Description</title>
1012 <para>This class is the superclass of all
1013 &clsql; databases. The different
1014 database back-ends derive subclasses of this class to
1015 implement their databases. No instances of this class are
1016 ever created by &clsql;.</para>
1019 <refentry id="closed-database">
1021 <refname>CLOSED-DATABASE</refname>
1022 <refpurpose>The class representing all closed
1023 &clsql; databases</refpurpose>
1024 <refclass>Class</refclass>
1027 <title>Class Precedence List</title>
1029 <simplelist type="inline">
1030 <member><type>closed-database</type></member>
1031 <member><type>standard-object</type></member>
1032 <member><type>t</type></member>
1037 <title>Description</title>
1038 <para>&clsql; <type>database</type>
1039 instances are changed to this class via
1040 <function>change-class</function> after they are closed via
1041 <function>disconnect</function>. All functions and generic
1042 functions that take database objects as arguments will
1043 signal errors of type
1044 <errortype>clsql-closed-error</errortype> when they are
1045 called on instances of <type>closed-database</type>, with
1046 the exception of <function>database-name</function>, which
1047 will continue to work as for instances of
1048 <type>database</type>.</para>
1052 <refentry id="database-name">
1054 <refname>DATABASE-NAME</refname>
1055 <refpurpose>Get the name of a database object</refpurpose>
1056 <refclass>Generic Function</refclass>
1059 <title>Syntax</title>
1060 <synopsis><function>database-name</function> <replaceable>database</replaceable> => <returnvalue>name</returnvalue></synopsis>
1063 <title>Arguments and Values</title>
1066 <term><parameter>database</parameter></term>
1068 <para>A database object, either of type
1069 <type>database</type> or of type
1070 <type>closed-database</type>.</para>
1074 <term><returnvalue>name</returnvalue></term>
1076 <para>A string describing the identity of the database
1077 to which this database object is connected to.</para>
1083 <title>Description</title>
1084 <para>This function returns the database name of the given
1085 database. The database name is a string which somehow
1086 describes the identity of the database to which this
1087 database object is or has been connected. The database name
1088 of a database object is determined at
1089 <function>connect</function> time, when a call to
1090 <function>database-name-from-spec</function> derives the
1091 database name from the connection specification passed to
1092 <function>connect</function> in the
1093 <parameter>connection-spec</parameter> parameter.</para>
1094 <para>The database name is used via
1095 <function>find-database</function> in
1096 <function>connect</function> to determine whether database
1097 connections to the specified database exist already.</para>
1098 <para>Usually the database name string will include
1099 indications of the host, database name, user, or port that
1100 where used during the connection attempt. The only
1101 important thing is that this string shall try to identify
1102 the database at the other end of the connection. Connection
1103 specifications parts like passwords and credentials shall
1104 not be used as part of the database name.</para>
1107 <title>Examples</title>
1109 (database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql)
1110 => "dent/newesim/dent"
1111 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql)
1112 => #<CLSQL-MYSQL:MYSQL-DATABASE {48391DCD}>
1113 (database-name *default-database*)
1114 => "dent/newesim/dent"
1116 (database-name-from-spec '(nil "template1" "dent" nil) :postgresql)
1117 => "/template1/dent"
1118 (connect '(nil "template1" "dent" nil) :database-type :postgresql)
1119 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}>
1120 (database-name *default-database*)
1121 => "/template1/dent"
1123 (database-name-from-spec '("www.pmsf.de" "template1" "dent" nil) :postgresql)
1124 => "www.pmsf.de/template1/dent"
1128 <title>Side Effects</title>
1132 <title>Affected By</title>
1135 <member><link linkend="database-name-from-spec"><function>database-name-from-spec</function></link></member>
1140 <title>Exceptional Situations</title>
1141 <para>Will signal an error if the object passed as the
1142 <parameter>database</parameter> parameter is neither of type
1143 <type>database</type> nor of type
1144 <type>closed-database</type>.</para>
1147 <title>See Also</title>
1151 linkend="connect"><function>connect</function></link></member>
1153 linkend="find-database"><function>find-database</function></link></member>
1158 <title>Notes</title>
1162 <refentry id="find-database">
1164 <refname>FIND-DATABASE</refname>
1165 <refpurpose>Locate a database object through it's
1167 <refclass>Function</refclass>
1170 <title>Syntax</title>
1171 <synopsis><function>find-database</function> <replaceable>database</replaceable> &optional <replaceable>errorp</replaceable> => <returnvalue>result</returnvalue></synopsis>
1174 <title>Arguments and Values</title>
1177 <term><parameter>database</parameter></term>
1179 <para>A database object or a string, denoting a database
1184 <term><parameter>errorp</parameter></term>
1186 <para>A generalized boolean. Defaults to
1187 <symbol>t</symbol>.</para>
1191 <term><returnvalue>result</returnvalue></term>
1193 <para>Either a database object, or, if
1194 <parameter>errorp</parameter> is <symbol>nil</symbol>,
1195 possibly <symbol>nil</symbol>.</para>
1201 <title>Description</title>
1202 <para><function>find-database</function> locates an active
1203 database object given the specification in
1204 <parameter>database</parameter>. If
1205 <parameter>database</parameter> is an object of type
1206 <type>database</type>, <function>find-database</function>
1207 returns this. Otherwise it will search the active databases
1208 as indicated by the list returned by
1209 <function>connected-databases</function> for a database
1210 whose name (as returned by
1211 <function>database-name</function> is equal as per
1212 <function>string=</function> to the string passed as
1213 <parameter>database</parameter>. If it succeeds, it returns
1214 the first database found.</para>
1215 <para>If it fails to find a matching database, it will signal
1216 an error of type <errortype>clsql-error</errortype> if
1217 <parameter>errorp</parameter> is true. If
1218 <parameter>errorp</parameter> is <symbol>nil</symbol>, it
1219 will return <symbol>nil</symbol> instead.</para>
1222 <title>Examples</title>
1224 (database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql)
1225 => "dent/newesim/dent"
1226 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql)
1227 => #<CLSQL-MYSQL:MYSQL-DATABASE {48391DCD}>
1228 (database-name *default-database*)
1229 => "dent/newesim/dent"
1231 (database-name-from-spec '(nil "template1" "dent" nil) :postgresql)
1232 => "/template1/dent"
1233 (connect '(nil "template1" "dent" nil) :database-type :postgresql)
1234 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}>
1235 (database-name *default-database*)
1236 => "/template1/dent"
1238 (database-name-from-spec '("www.pmsf.de" "template1" "dent" nil) :postgresql)
1239 => "www.pmsf.de/template1/dent"
1241 (find-database "dent/newesim/dent")
1242 => #<CLSQL-MYSQL:MYSQL-DATABASE {484E91C5}>
1243 (find-database "/template1/dent")
1244 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}>
1245 (find-database "www.pmsf.de/template1/dent" nil)
1248 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}>
1252 <title>Side Effects</title>
1256 <title>Affected By</title>
1259 <member><link linkend="connected-databases"><function>connected-databases</function></link></member>
1264 <title>Exceptional Situations</title>
1265 <para>Will signal an error of type
1266 <errortype>clsql-error</errortype> if no matching database
1267 can be found, and <parameter>errorp</parameter> is true.
1268 Will signal an error if the value of
1269 <parameter>database</parameter> is neither an object of type
1270 <type>database</type> nor a string.</para>
1273 <title>See Also</title>
1277 linkend="database-name"><function>database-name</function></link></member>
1279 linkend="database-name-from-spec"><function>database-name-from-spec</function></link></member>
1284 <title>Notes</title>
1289 <refentry id="connect">
1291 <refname>CONNECT</refname>
1292 <refpurpose>create a connection to a database</refpurpose>
1293 <refclass>Function</refclass>
1296 <title>Syntax</title>
1297 <synopsis><function>connect</function> <replaceable>connection-spec</replaceable> &key <replaceable>if-exists</replaceable> <replaceable>database-type</replaceable> <replaceable>pool</replaceable> => <returnvalue>database</returnvalue></synopsis>
1300 <title>Arguments and Values</title>
1303 <term><parameter>connection-spec</parameter></term>
1305 <para>A connection specification</para>
1309 <term><parameter>if-exists</parameter></term>
1311 <para>This indicates the action to take if a connection
1312 to the same database exists already. See below for the
1313 legal values and actions. It defaults to the value of
1314 <symbol>*connect-if-exists*</symbol>.</para>
1318 <term><parameter>database-type</parameter></term>
1320 <para>A database type specifier, i.e. a keyword.
1321 This defaults to the value of
1322 <symbol>*default-database-type*</symbol></para>
1326 <term><parameter>pool</parameter></term>
1328 <para>A boolean flag. If &t;, acquire connection from a
1329 pool of open connections. If the pool is empty, a new
1330 connection is created. The default is &nil;.
1335 <term><returnvalue>database</returnvalue></term>
1337 <para>The database object representing the connection.</para>
1343 <title>Description</title>
1344 <para>This function takes a connection specification and
1345 a database type and creates a connection to the database
1346 specified by those. The type and structure of the
1347 connection specification depend on the database type.</para>
1348 <para>The parameter <parameter>if-exists</parameter> specifies
1349 what to do if a connection to the database specified exists
1350 already, which is checked by calling
1351 <function>find-database</function> on the database name
1352 returned by <function>database-name-from-spec</function>
1353 when called with the <parameter>connection-spec</parameter>
1354 and <parameter>database-type</parameter> parameters. The
1355 possible values of <parameter>if-exists</parameter> are:
1358 <term><symbol>:new</symbol></term>
1360 <para>Go ahead and create a new connection.</para>
1364 <term><symbol>:warn-new</symbol></term>
1366 <para>This is just like <symbol>:new</symbol>, but
1367 also signals a warning of type
1368 <errortype>clsql-exists-warning</errortype>,
1369 indicating the old and newly created
1374 <term><symbol>:error</symbol></term>
1376 <para>This will cause <function>connect</function> to
1377 signal a correctable error of type
1378 <errortype>clsql-exists-error</errortype>. The
1379 user may choose to proceed, either by indicating
1380 that a new connection shall be created, via the
1381 restart <symbol>create-new</symbol>, or by
1382 indicating that the existing connection shall be
1383 used, via the restart
1384 <symbol>use-old</symbol>.</para>
1388 <term><symbol>:old</symbol></term>
1390 <para>This will cause <function>connect</function> to
1391 use an old connection if one exists.</para>
1395 <term><symbol>:warn-old</symbol></term>
1397 <para>This is just like <symbol>:old</symbol>, but
1398 also signals a warning of type
1399 <errortype>clsql-exists-warning</errortype>,
1400 indicating the old database used, via the slots
1401 <symbol>old-db</symbol> and
1402 <symbol>new-db</symbol></para>
1407 <para>The database name of the returned database object will
1408 be the same under <function>string=</function> as that which
1409 would be returned by a call to
1410 <function>database-name-from-spec</function> with the given
1411 <parameter>connection-spec</parameter> and
1412 <parameter>database-type</parameter> parameters.</para>
1415 <title>Examples</title>
1417 (database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql)
1418 => "dent/newesim/dent"
1419 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql)
1420 => #<CLSQL-MYSQL:MYSQL-DATABASE {48036F6D}>
1422 => "dent/newesim/dent"
1424 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql)
1425 >> In call to CONNECT:
1426 >> There is an existing connection #<CLSQL-MYSQL:MYSQL-DATABASE {48036F6D}> to database dent/newesim/dent.
1429 >> 0: [CREATE-NEW] Create a new connection.
1430 >> 1: [USE-OLD ] Use the existing connection.
1431 >> 2: [ABORT ] Return to Top-Level.
1433 >> Debug (type H for help)
1435 >> (CONNECT ("dent" "newesim" "dent" "dent") :IF-EXISTS NIL :DATABASE-TYPE ...)
1437 >> ; File: /prj/CLSQL/sql/sql.cl
1438 >> (RESTART-CASE (ERROR 'CLSQL-EXISTS-ERROR :OLD-DB OLD-DB)
1439 >> (CREATE-NEW NIL :REPORT "Create a new connection."
1441 >> (USE-OLD NIL :REPORT "Use the existing connection."
1442 >> (SETQ RESULT OLD-DB)))
1444 => #<CLSQL-MYSQL:MYSQL-DATABASE {480451F5}>
1448 <title>Side Effects</title>
1449 <para>A database connection is established, and the resultant
1450 database object is registered, so as to appear in the list
1451 returned by <function>connected-databases</function>.</para>
1454 <title>Affected by</title>
1457 <member><symbol>*default-database-type*</symbol></member>
1458 <member><symbol>*connect-if-exists*</symbol></member>
1463 <title>Exceptional Situations</title>
1464 <para>If the connection specification is not syntactically or
1465 semantically correct for the given database type, an error
1466 of type <errortype>clsql-invalid-spec-error</errortype> is
1467 signalled. If during the connection attempt an error is
1468 detected (e.g. because of permission problems, network
1469 trouble or any other cause), an error of type
1470 <errortype>clsql-connect-error</errortype> is
1472 <para>If a connection to the database specified by
1473 <parameter>connection-spec</parameter> exists already,
1474 conditions are signalled according to the
1475 <parameter>if-exists</parameter> parameter, as described
1479 <title>See Also</title>
1482 <member><function>connected-databases</function></member>
1483 <member><link linkend="disconnect"><function>disconnect</function></link></member>
1488 <title>Notes</title>
1493 <refentry id="disconnect">
1495 <refname>DISCONNECT</refname>
1496 <refpurpose>close a database connection</refpurpose>
1497 <refclass>Function</refclass>
1500 <title>Syntax</title>
1501 <synopsis><function>disconnect</function> &key <replaceable>database</replaceable> <replaceable>pool</replaceable> => <returnvalue>t</returnvalue></synopsis>
1504 <title>Arguments and Values</title>
1507 <term><parameter>pool</parameter></term>
1509 <para>A boolean flag indicating whether to put the database into a
1510 pool of opened databases. If &t;, rather than terminating the database connection, the
1511 connection is left open and the connection is placed into a pool of connections. Subsequent
1512 calls to <link linkend="connect"><function>connect</function></link> can then reuse this connection.
1513 The default is &nil;.
1518 <term><parameter>database</parameter></term>
1520 <para>The database to disconnect, which defaults to the
1521 database indicated by
1522 <symbol>*default-database*</symbol>.</para>
1528 <title>Description</title>
1529 <para>This function takes a <type>database</type> object as
1530 returned by <function>connect</function>, and closes the
1531 connection. The class of the object passed is changed to
1532 <type>closed-database</type> after the disconnection
1533 succeeds, thereby preventing further use of the object as
1534 an argument to &clsql; functions,
1535 with the exception of <function>database-name</function>.
1536 If the user does pass a closed database object to any other
1537 &clsql; function, an error of type
1538 <errortype>clsql-closed-error</errortype> is
1542 <title>Examples</title>
1544 (disconnect :database (find-database "dent/newesim/dent"))
1549 <title>Side Effects</title>
1550 <para>The database connection is closed, and the database
1551 object is removed from the list of connected databases as
1552 returned by <function>connected-databases</function>.</para>
1553 <para>The class of the database object is changed to
1554 <type>closed-database</type>.</para>
1555 <para>If the database object passed is the same under
1556 <function>eq</function> as the value of
1557 <symbol>*default-database*</symbol>, then
1558 <symbol>*default-database*</symbol> is set to the first
1559 remaining database from
1560 <function>connected-databases</function> or to nil if no
1561 further active database exists.</para>
1564 <title>Affected by</title>
1567 <member><symbol>*default-database*</symbol></member>
1572 <title>Exceptional Situations</title>
1573 <para>If during the disconnection attempt an error is
1574 detected (e.g. because of network trouble or any other
1575 cause), an error of type <errortype>clsql-error</errortype>
1576 might be signalled.</para>
1579 <title>See Also</title>
1582 <member><link linkend="connect"><function>connect</function></link></member>
1583 <member><link linkend="connect"><function>closed-database</function></link></member>
1588 <title>Notes</title>
1593 <refentry id="disconnect-pooled">
1595 <refname>DISCONNECT-POOLED</refname>
1596 <refpurpose>closes all pooled database connections</refpurpose>
1597 <refclass>Function</refclass>
1600 <title>Syntax</title>
1601 <synopsis><function>disconnect-pool</function> => <returnvalue>t</returnvalue></synopsis>
1604 <title>Description</title>
1605 <para>This function disconnects all database connections
1606 that have been placed into the pool. Connections are placed
1607 in the pool by calling <link
1608 linkend="disconnect"><function>disconnection</function></link>.
1612 <title>Examples</title>
1619 <title>Side Effects</title>
1620 <para>Database connections will be closed and entries in the pool are removed.
1624 <title>Affected by</title>
1627 <member><function>disconnect</function></member>
1632 <title>Exceptional Situations</title>
1633 <para>If during the disconnection attempt an error is
1634 detected (e.g. because of network trouble or any other
1635 cause), an error of type <errortype>clsql-error</errortype>
1636 might be signalled.</para>
1639 <title>See Also</title>
1642 <member><link linkend="connect"><function>connect</function></link></member>
1643 <member><link linkend="connect"><function>closed-database</function></link></member>
1648 <title>Notes</title>
1653 <refentry id="create_db">
1655 <refname>CREATE-DATABASE</refname>
1656 <refpurpose>create a database</refpurpose>
1657 <refclass>Function</refclass>
1660 <title>Syntax</title>
1661 <synopsis><function>create-database</function> <replaceable>connection-spec</replaceable> &key <replaceable>database-type</replaceable> => <returnvalue>success</returnvalue></synopsis>
1664 <title>Arguments and Values</title>
1667 <term><parameter>connection-spec</parameter></term>
1669 <para>A connection specification</para>
1673 <term><parameter>database-type</parameter></term>
1675 <para>A database type specifier, i.e. a keyword.
1676 This defaults to the value of
1677 <symbol>*default-database-type*</symbol></para>
1681 <term><parameter>success</parameter></term>
1683 <para>A boolean flag. If &t;, a new database was
1684 successfully created.
1691 <title>Description</title>
1692 <para>This function creates a database in the database system
1693 specified by <parameter>database-type</parameter>.
1697 <title>Examples</title>
1699 (create-database '("localhost" "new" "dent" "dent") :database-type :mysql)
1702 (create-database '("localhost" "new" "dent" "badpasswd") :database-type :mysql)
1704 Error: While trying to access database localhost/new/dent
1705 using database-type MYSQL:
1706 Error database-create failed: mysqladmin: connect to server at 'localhost' failed
1707 error: 'Access denied for user: 'root@localhost' (Using password: YES)'
1709 [condition type: CLSQL-ACCESS-ERROR]
1713 <title>Side Effects</title>
1714 <para>A database will be created on the filesystem of the host.</para>
1717 <title>Exceptional Situations</title>
1718 <para>An exception will be thrown if the database system does
1719 not allow new databases to be created or if database creation
1723 <title>Notes</title>
1724 <para>This function may invoke the operating systems
1725 functions. Thus, some database systems may require the
1726 administration functions to be available in the current
1727 <symbol>PATH</symbol>. At this time, the
1728 <symbol>:mysql</symbol> backend requires
1729 <filename>mysqladmin</filename> and the
1730 <symbol>:postgresql</symbol> backend requires
1731 <filename>createdb</filename>.</para>
1735 <refentry id="destroy_db">
1737 <refname>DESTROY-DATABASE</refname>
1738 <refpurpose>destroys a database</refpurpose>
1739 <refclass>Function</refclass>
1742 <title>Syntax</title>
1743 <synopsis><function>destroy-database</function> <replaceable>connection-spec</replaceable> &key <replaceable>database-type</replaceable> => <returnvalue>success</returnvalue></synopsis>
1746 <title>Arguments and Values</title>
1749 <term><parameter>connection-spec</parameter></term>
1751 <para>A connection specification</para>
1755 <term><parameter>database-type</parameter></term>
1757 <para>A database type specifier, i.e. a keyword.
1758 This defaults to the value of
1759 <symbol>*default-database-type*</symbol></para>
1763 <term><parameter>success</parameter></term>
1765 <para>A boolean flag. If &t;, the database was
1766 successfully destroyed.
1773 <title>Description</title>
1774 <para>This function destroy a database in the database system
1775 specified by <parameter>database-type</parameter>.
1779 <title>Examples</title>
1781 (destroy-database '("localhost" "new" "dent" "dent") :database-type :postgresql)
1784 (destroy-database '("localhost" "new" "dent" "dent") :database-type :postgresql)
1786 Error: While trying to access database localhost/test2/root
1787 using database-type POSTGRESQL:
1788 Error database-destory failed: dropdb: database removal failed: ERROR: database "test2" does not exist
1790 [condition type: CLSQL-ACCESS-ERROR]
1794 <title>Side Effects</title>
1795 <para>A database will be removed from the filesystem of the host.</para>
1798 <title>Exceptional Situations</title>
1799 <para>An exception will be thrown if the database system does not
1800 allow databases to be removed, the database does not exist, or
1801 if database removal fails.</para>
1804 <title>Notes</title>
1805 <para>This function may invoke the operating systems
1806 functions. Thus, some database systems may require the
1807 administration functions to be available in the current
1808 <symbol>PATH</symbol>. At this time, the
1809 <symbol>:mysql</symbol> backend requires
1810 <filename>mysqladmin</filename> and the
1811 <symbol>:postgresql</symbol> backend requires
1812 <filename>dropdb</filename>.</para>
1816 <refentry id="probe_db">
1818 <refname>PROBE-DATABASE</refname>
1819 <refpurpose>tests for existence of a database</refpurpose>
1820 <refclass>Function</refclass>
1823 <title>Syntax</title>
1824 <synopsis><function>probe-database</function> <replaceable>connection-spec</replaceable> &key <replaceable>database-type</replaceable> => <returnvalue>success</returnvalue></synopsis>
1827 <title>Arguments and Values</title>
1830 <term><parameter>connection-spec</parameter></term>
1832 <para>A connection specification</para>
1836 <term><parameter>database-type</parameter></term>
1838 <para>A database type specifier, i.e. a keyword.
1839 This defaults to the value of
1840 <symbol>*default-database-type*</symbol></para>
1844 <term><parameter>success</parameter></term>
1846 <para>A boolean flag. If &t;, the database exists
1847 in the database system.
1854 <title>Description</title>
1855 <para>This function tests for the existence of a database in
1856 the database system specified by
1857 <parameter>database-type</parameter>.
1861 <title>Examples</title>
1863 (probe-database '("localhost" "new" "dent" "dent") :database-type :postgresql)
1868 <title>Side Effects</title>
1872 <title>Exceptional Situations</title>
1873 <para>An exception maybe thrown if the database system does
1874 not receive administrator-level authentication since function
1875 may need to read the administrative database of the database
1879 <title>Notes</title>
1884 <refentry id="database-name-from-spec">
1886 <refname>DATABASE-NAME-FROM-SPEC</refname>
1887 <refpurpose>Return the database name string corresponding to
1888 the given connection specification.</refpurpose>
1889 <refclass>Generic Function</refclass>
1892 <title>Syntax</title>
1894 <function>database-name-from-spec</function> <replaceable>connection-spec</replaceable> <replaceable>database-type</replaceable> => <returnvalue>name</returnvalue></synopsis>
1897 <title>Arguments and Values</title>
1900 <term><parameter>connection-spec</parameter></term>
1902 <para>A connection specification, whose structure and
1903 interpretation are dependent on the
1904 <parameter>database-type</parameter>.</para>
1908 <term><parameter>database-type</parameter></term>
1910 <para>A database type specifier, i.e. a keyword.</para>
1914 <term><returnvalue>name</returnvalue></term>
1916 <para>A string denoting a database name.</para>
1922 <title>Description</title>
1923 <para>This generic function takes a connection specification
1924 and a database type and returns the database name of the
1925 database object that would be created had
1926 <function>connect</function> been called with the given
1927 connection specification and database types.</para>
1928 <para>This function is useful in determining a database name
1929 from the connection specification, since the way the
1930 connection specification is converted into a database name
1931 is dependent on the database type.</para>
1934 <title>Examples</title>
1936 (database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql)
1937 => "dent/newesim/dent"
1938 (connect '("dent" "newesim" "dent" "dent") :database-type :mysql)
1939 => #<CLSQL-MYSQL:MYSQL-DATABASE {48391DCD}>
1940 (database-name *default-database*)
1941 => "dent/newesim/dent"
1943 (database-name-from-spec '(nil "template1" "dent" nil) :postgresql)
1944 => "/template1/dent"
1945 (connect '(nil "template1" "dent" nil) :database-type :postgresql)
1946 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}>
1947 (database-name *default-database*)
1948 => "/template1/dent"
1950 (database-name-from-spec '("www.pmsf.de" "template1" "dent" nil) :postgresql)
1951 => "www.pmsf.de/template1/dent"
1953 (find-database "dent/newesim/dent")
1954 => #<CLSQL-MYSQL:MYSQL-DATABASE {484E91C5}>
1955 (find-database "/template1/dent")
1956 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}>
1957 (find-database "www.pmsf.de/template1/dent" nil)
1960 => #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}>
1964 <title>Side Effects</title>
1968 <title>Affected by</title>
1972 <title>Exceptional Situations</title>
1973 <para>If the value of <parameter>connection-spec</parameter>
1974 is not a valid connection specification for the given
1975 database type, an error of type
1976 <errortype>clsql-invalid-spec-error</errortype> might be
1980 <title>See Also</title>
1983 <member><link linkend="connect"><function>connect</function></link></member>
1988 <title>Notes</title>
1992 <!-- Querying Operations -->
1993 <refentry id="execute-command">
1995 <refname>EXECUTE-COMMAND</refname>
1996 <refpurpose>Execute an SQL command which returns no
1997 values.</refpurpose>
1998 <refclass>Function</refclass>
2001 <title>Syntax</title>
2002 <synopsis><function>execute-command</function> <replaceable>sql-expression</replaceable> &key <replaceable>database</replaceable> => <returnvalue>t</returnvalue></synopsis>
2005 <title>Arguments and Values</title>
2008 <term><parameter>sql-expression</parameter></term>
2010 <para>An <glossterm linkend="gloss-sql-expression">sql
2011 expression</glossterm> that represents an SQL
2012 statement which will return no values.</para>
2016 <term><parameter>database</parameter></term>
2019 <glossterm linkend="gloss-database-object">database
2020 object</glossterm>. This will default to the value
2021 of <symbol>*default-database*</symbol>.</para>
2027 <title>Description</title>
2028 <para>This will execute the command given by
2029 <parameter>sql-expression</parameter> in the
2030 <parameter>database</parameter> specified. If the execution
2031 succeeds it will return <symbol>t</symbol>, otherwise an
2032 error of type <errortype>clsql-sql-error</errortype> will
2033 be signalled.</para>
2036 <title>Examples</title>
2038 (execute-command "create table eventlog (time char(30),event char(70))")
2041 (execute-command "create table eventlog (time char(30),event char(70))")
2043 >> While accessing database #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {480B2B6D}>
2044 >> with expression "create table eventlog (time char(30),event char(70))":
2045 >> Error NIL: ERROR: amcreate: eventlog relation already exists
2049 >> 0: [ABORT] Return to Top-Level.
2051 >> Debug (type H for help)
2053 >> (CLSQL-POSTGRESQL::|(PCL::FAST-METHOD DATABASE-EXECUTE-COMMAND (T POSTGRESQL-DATABASE))|
2056 >> #<unavailable-arg>
2057 >> #<unavailable-arg>)
2058 >> Source: (ERROR 'CLSQL-SQL-ERROR :DATABASE DATABASE :EXPRESSION ...)
2061 (execute-command "drop table eventlog")
2066 <title>Side Effects</title>
2067 <para>Whatever effects the execution of the SQL statement has
2068 on the underlying database, if any.</para>
2071 <title>Affected by</title>
2075 <title>Exceptional Situations</title>
2076 <para>If the execution of the SQL statement leads to any
2077 errors, an error of type
2078 <errortype>clsql-sql-error</errortype> is signalled.</para>
2081 <title>See Also</title>
2084 <member><link linkend="query"><function>query</function></link></member>
2089 <title>Notes</title>
2093 <refentry id="query">
2095 <refname>QUERY</refname>
2096 <refpurpose>Execute an SQL query and return the tuples as a
2098 <refclass>Function</refclass>
2101 <title>Syntax</title>
2102 <synopsis><function>query</function> <replaceable>query-expression</replaceable> &key <replaceable>database</replaceable> <replaceable>result-types</replaceable> => <returnvalue>result</returnvalue></synopsis>
2105 <title>Arguments and Values</title>
2108 <term><parameter>query-expression</parameter></term>
2110 <para>An <glossterm linkend="gloss-sql-expression">sql
2111 expression</glossterm> that represents an SQL
2112 query which is expected to return a (possibly empty)
2117 <term><parameter>database</parameter></term>
2120 <glossterm linkend="gloss-database-object">database
2121 object</glossterm>. This will default to the value
2122 of <symbol>*default-database*</symbol>.</para>
2126 <term><parameter>result-types</parameter></term>
2129 <glossterm linkend="gloss-field-types">field type
2130 specifier</glossterm>. The default is &nil;.
2133 The purpose of this argument is cause &clsql; to
2134 import SQL numeric fields into numeric Lisp objects
2135 rather than strings. This reduces the cost of
2136 allocating a temporary string and the &clsql; users'
2137 inconvenience of converting number strings into number
2141 A value of <symbol>:auto</symbol> causes &clsql;
2142 to automatically convert SQL fields into a
2143 numeric format where applicable. The default value of
2144 &nil; causes all fields to be returned as strings
2145 regardless of the SQL type. Otherwise a list is expected
2146 which has a element for each field that specifies the
2147 conversion. If the list is shorter than the number
2148 of fields, the a value of <symbol>t</symbol> is
2149 assumed for the field. If the list is longer than
2150 the number of fields, the extra elements are
2152 <simplelist type="vert">
2153 <member><symbol>:int</symbol> Field is imported as a
2154 signed integer, from 8-bits to 64-bits depending
2155 upon the field type.
2157 <member><symbol>:double</symbol> Field is imported as a
2158 double-float number.
2160 <member><symbol>t</symbol> Field is imported as a
2168 <term><returnvalue>result</returnvalue></term>
2170 <para>A list representing the result set obtained. For
2171 each tuple in the result set, there is an element in
2172 this list, which is itself a list of all the attribute
2173 values in the tuple.</para>
2179 <title>Description</title>
2180 <para>This will execute the query given by
2181 <parameter>query-expression</parameter> in the
2182 <parameter>database</parameter> specified. If the execution
2183 succeeds it will return the result set returned by the
2184 database, otherwise an error of type
2185 <errortype>clsql-sql-error</errortype> will
2186 be signalled.</para>
2189 <title>Examples</title>
2191 (execute-command "create table simple (name char(50), salary numeric(10,2))")
2193 (execute-command "insert into simple values ('Mai, Pierre',10000)")
2195 (execute-command "insert into simple values ('Hacker, Random J.',8000.50)")
2197 (query "select * from simple")
2198 => (("Mai, Pierre" "10000.00") ("Hacker, Random J." "8000.50"))
2199 (query "select salary from simple")
2200 => (("10000.00") ("8000.50"))
2201 (query "select salary from simple where salary > 10000")
2203 (query "select salary,name from simple where salary > 10000")
2205 (query "select salary,name from simple where salary > 9000")
2206 => (("10000.00" "Mai, Pierre"))
2207 (query "select salary,name from simple where salary > 8000")
2208 => (("10000.00" "Mai, Pierre") ("8000.50" "Hacker, Random J."))
2211 (query "show tables")
2212 => (("demo") ("log") ("newlog") ("simple") ("spacetrial"))
2216 <title>Side Effects</title>
2217 <para>Whatever effects the execution of the SQL query has
2218 on the underlying database, if any.</para>
2221 <title>Affected by</title>
2225 <title>Exceptional Situations</title>
2226 <para>If the execution of the SQL query leads to any
2227 errors, an error of type
2228 <errortype>clsql-sql-error</errortype> is signalled.</para>
2231 <title>See Also</title>
2234 <member><link linkend="execute-command"><function>execute-command</function></link></member>
2239 <title>Notes</title>
2244 <refentry id="map-query">
2246 <refname>MAP-QUERY</refname>
2247 <refpurpose>Map a function over all the tuples from a
2249 <refclass>Function</refclass>
2252 <title>Syntax</title>
2253 <synopsis><function>map-query</function> <replaceable>output-type-spec</replaceable> <replaceable>function</replaceable> <replaceable>query-expression</replaceable> &key <replaceable>database</replaceable> <replaceable>result-types</replaceable> => <returnvalue>result</returnvalue></synopsis>
2256 <title>Arguments and Values</title>
2259 <term><parameter>output-type-spec</parameter></term>
2261 <para>A sequence type specifier or <symbol>nil</symbol>.</para>
2265 <term><parameter>function</parameter></term>
2267 <para>A function designator.
2268 <parameter>function</parameter> must take as many
2269 arguments as are attributes in the result set returned
2270 by executing the SQL
2271 <parameter>query-expression</parameter>.</para>
2275 <term><parameter>query-expression</parameter></term>
2277 <para>An <glossterm linkend="gloss-sql-expression">sql
2278 expression</glossterm> that represents an SQL
2279 query which is expected to return a (possibly empty)
2280 result set, where each tuple has as many attributes as
2281 <parameter>function</parameter> takes arguments.</para>
2285 <term><parameter>database</parameter></term>
2288 <glossterm linkend="gloss-database-object">database
2289 object</glossterm>. This will default to the value
2290 of <symbol>*default-database*</symbol>.</para>
2294 <term><parameter>result-types</parameter></term>
2297 A <glossterm linkend="gloss-field-types">field type specifier</glossterm>.
2298 The default is &nil;. See <link
2299 linkend="query"><function>query</function></link>
2300 for the semantics of this argument.
2305 <term><returnvalue>result</returnvalue></term>
2307 <para>If <parameter>output-type-spec</parameter> is a
2308 type specifier other than <symbol>nil</symbol>, then a
2309 sequence of the type it denotes. Otherwise
2310 <symbol>nil</symbol> is returned.</para>
2316 <title>Description</title>
2317 <para>Applies <parameter>function</parameter> to the
2318 attributes of successive tuples in the result set returned
2319 by executing the SQL
2320 <parameter>query-expression</parameter>. If the
2321 <parameter>output-type-spec</parameter> is
2322 <symbol>nil</symbol>, then the result of each application
2323 of <parameter>function</parameter> is discarded, and
2324 <function>map-query</function> returns
2325 <symbol>nil</symbol>. Otherwise the result of each
2326 successive application of <parameter>function</parameter> is
2327 collected in a sequence of type
2328 <parameter>output-type-spec</parameter>, where the jths
2329 element is the result of applying
2330 <parameter>function</parameter> to the attributes of the
2331 jths tuple in the result set. The collected sequence is the
2332 result of the call to <function>map-query</function>.
2334 <para>If the <parameter>output-type-spec</parameter> is a
2335 subtype of <type>list</type>, the result will be a
2336 <type>list</type>.</para>
2337 <para>If the <parameter>result-type</parameter> is a subtype
2338 of <type>vector</type>, then if the implementation can
2339 determine the element type specified for the
2340 <parameter>result-type</parameter>, the element type of the
2341 resulting array is the result of
2342 <emphasis>upgrading</emphasis> that element type; or, if the
2343 implementation can determine that the element type is
2344 unspecified (or <symbol>*</symbol>), the element type of the
2345 resulting array is <type>t</type>; otherwise, an error is
2349 <title>Examples</title>
2351 (map-query 'list #'(lambda (salary name)
2352 (declare (ignorable name))
2353 (read-from-string salary))
2354 "select salary,name from simple where salary > 8000")
2357 (map-query '(vector double-float)
2358 #'(lambda (salary name)
2359 (declare (ignorable name))
2360 (let ((*read-default-float-format* 'double-float))
2361 (coerce (read-from-string salary) 'double-float))
2362 "select salary,name from simple where salary > 8000"))
2363 => #(10000.0d0 8000.5d0)
2365 => (SIMPLE-ARRAY DOUBLE-FLOAT (2))
2368 (values (map-query nil #'(lambda (salary name)
2369 (push (cons name (read-from-string salary)) list))
2370 "select salary,name from simple where salary > 8000")
2373 => (("Hacker, Random J." . 8000.5) ("Mai, Pierre" . 10000.0))
2377 <title>Side Effects</title>
2378 <para>Whatever effects the execution of the SQL query has
2379 on the underlying database, if any.</para>
2382 <title>Affected by</title>
2386 <title>Exceptional Situations</title>
2387 <para>If the execution of the SQL query leads to any
2388 errors, an error of type
2389 <errortype>clsql-sql-error</errortype> is signalled.</para>
2390 <para>An error of type <errortype>type-error</errortype> must
2391 be signaled if the <parameter>output-type-spec</parameter> is
2392 not a recognizable subtype of <type>list</type>, not a
2393 recognizable subtype of <type>vector</type>, and not
2394 <symbol>nil</symbol>.</para>
2395 <para>An error of type <errortype>type-error</errortype>
2396 should be signaled if
2397 <parameter>output-type-spec</parameter> specifies the number
2398 of elements and the size of the result set is different from
2402 <title>See Also</title>
2405 <member><link linkend="query"><function>query</function></link></member>
2406 <member><link linkend="do-query"><function>do-query</function></link></member>
2411 <title>Notes</title>
2415 <refentry id="do-query">
2417 <refname>DO-QUERY</refname>
2418 <refpurpose>Iterate over all the tuples of a
2420 <refclass>Macro</refclass>
2423 <title>Syntax</title>
2424 <synopsis><function>do-query</function> ((&rest <replaceable>args</replaceable>) <replaceable>query-expression</replaceable> &key <replaceable>database</replaceable> <replaceable>result-types</replaceable>) &body <replaceable>body</replaceable> => <returnvalue>nil</returnvalue></synopsis>
2427 <title>Arguments and Values</title>
2430 <term><parameter>args</parameter></term>
2432 <para>A list of variable names.</para>
2436 <term><parameter>query-expression</parameter></term>
2438 <para>An <glossterm linkend="gloss-sql-expression">sql
2439 expression</glossterm> that represents an SQL
2440 query which is expected to return a (possibly empty)
2441 result set, where each tuple has as many attributes as
2442 <parameter>function</parameter> takes arguments.</para>
2446 <term><parameter>database</parameter></term>
2449 <glossterm linkend="gloss-database-object">database
2450 object</glossterm>. This will default to
2451 <symbol>*default-database*</symbol>.</para>
2455 <term><parameter>result-types</parameter></term>
2458 A <glossterm linkend="gloss-field-types">field type specifier</glossterm>.
2459 The default is &nil;. See <link
2460 linkend="query"><function>query</function></link>
2461 for the semantics of this argument.
2466 <term><parameter>body</parameter></term>
2468 <para>A body of Lisp code, like in a
2469 <function>destructuring-bind</function> form.</para>
2475 <title>Description</title>
2476 <para>Executes the <parameter>body</parameter> of code
2477 repeatedly with the variable names in
2478 <parameter>args</parameter> bound to the attributes of each
2479 tuple in the result set returned by executing the SQL
2480 <parameter>query-expression</parameter> on the
2481 <parameter>database</parameter> specified.</para>
2482 <para>The body of code is executed in a block named
2483 <symbol>nil</symbol> which may be returned from prematurely
2484 via <function>return</function> or
2485 <function>return-from</function>. In this case the result
2486 of evaluating the <function>do-query</function> form will be
2487 the one supplied to <function>return</function> or
2488 <function>return-from</function>. Otherwise the result will
2489 be <symbol>nil</symbol>.</para>
2490 <para>The body of code appears also is if wrapped in a
2491 <function>destructuring-bind</function> form, thus allowing
2492 declarations at the start of the body, especially those
2493 pertaining to the bindings of the variables named in
2494 <parameter>args</parameter>.</para>
2497 <title>Examples</title>
2499 (do-query ((salary name) "select salary,name from simple")
2500 (format t "~30A gets $~2,5$~%" name (read-from-string salary)))
2501 >> Mai, Pierre gets $10000.00
2502 >> Hacker, Random J. gets $08000.50
2505 (do-query ((salary name) "select salary,name from simple")
2506 (return (cons salary name)))
2507 => ("10000.00" . "Mai, Pierre")
2511 <title>Side Effects</title>
2512 <para>Whatever effects the execution of the SQL query has
2513 on the underlying database, if any.</para>
2516 <title>Affected by</title>
2520 <title>Exceptional Situations</title>
2521 <para>If the execution of the SQL query leads to any
2522 errors, an error of type
2523 <errortype>clsql-sql-error</errortype> is signalled.</para>
2524 <para>If the number of variable names in
2525 <parameter>args</parameter> and the number of attributes in
2526 the tuples in the result set don't match up, an error is
2530 <title>See Also</title>
2533 <member><link linkend="query"><function>query</function></link></member>
2534 <member><link linkend="map-query"><function>map-query</function></link></member>
2539 <title>Notes</title>
2543 <refentry id="loop-tuples">
2545 <refname>LOOP-FOR-AS-TUPLES</refname>
2546 <refpurpose>Iterate over all the tuples of a
2547 query via a loop clause</refpurpose>
2548 <refclass>Loop Clause</refclass>
2551 <title>Compatibility</title>
2552 <caution><para><function>loop-for-as-tuples</function> only works with &cmucl;.</para></caution>
2555 <title>Syntax</title>
2556 <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>
2559 <title>Arguments and Values</title>
2562 <term><parameter>var</parameter></term>
2564 <para>A <literal>d-var-spec</literal>, as defined in the
2565 grammar for <function>loop</function>-clauses in the
2566 ANSI Standard for Common Lisp. This allows for the
2567 usual loop-style destructuring.</para>
2571 <term><parameter>type-spec</parameter></term>
2573 <para>An optional <literal>type-spec</literal> either
2574 simple or destructured, as defined in the grammar for
2575 <function>loop</function>-clauses in the ANSI Standard
2576 for Common Lisp.</para>
2580 <term><parameter>query</parameter></term>
2582 <para>An <glossterm linkend="gloss-sql-expression">sql
2583 expression</glossterm> that represents an SQL
2584 query which is expected to return a (possibly empty)
2585 result set, where each tuple has as many attributes as
2586 <parameter>function</parameter> takes arguments.</para>
2590 <term><parameter>database</parameter></term>
2593 <glossterm linkend="gloss-database-object">database
2594 object</glossterm>. This will default to the value
2595 of <symbol>*default-database*</symbol>.</para>
2601 <title>Description</title>
2602 <para>This clause is an iteration driver for
2603 <function>loop</function>, that binds the given variable
2604 (possibly destructured) to the consecutive tuples (which are
2605 represented as lists of attribute values) in the result set
2606 returned by executing the SQL <parameter>query</parameter>
2607 expression on the <parameter>database</parameter>
2611 <title>Examples</title>
2613 (defvar *my-db* (connect '("dent" "newesim" "dent" "dent"))
2616 (loop with time-graph = (make-hash-table :test #'equal)
2617 with event-graph = (make-hash-table :test #'equal)
2618 for (time event) being the tuples of "select time,event from log"
2621 (incf (gethash time time-graph 0))
2622 (incf (gethash event event-graph 0))
2624 (flet ((show-graph (k v) (format t "~40A => ~5D~%" k v)))
2625 (format t "~&Time-Graph:~%===========~%")
2626 (maphash #'show-graph time-graph)
2627 (format t "~&~%Event-Graph:~%============~%")
2628 (maphash #'show-graph event-graph))
2629 (return (values time-graph event-graph)))
2638 >> CLOS Benchmark entry. => 9000
2639 >> Demo Text... => 3
2640 >> doit-text => 3000
2641 >> C Benchmark entry. => 12000
2642 >> CLOS Benchmark entry => 32000
2643 => #<EQUAL hash table, 3 entries {48350A1D}>
2644 => #<EQUAL hash table, 5 entries {48350FCD}>
2648 <title>Side Effects</title>
2649 <para>Whatever effects the execution of the SQL query has
2650 on the underlying database, if any.</para>
2653 <title>Affected by</title>
2657 <title>Exceptional Situations</title>
2658 <para>If the execution of the SQL query leads to any
2659 errors, an error of type
2660 <errortype>clsql-sql-error</errortype> is signalled.</para>
2661 <para>Otherwise, any of the exceptional situations of
2662 <function>loop</function> applies.</para>
2665 <title>See Also</title>
2668 <member><link linkend="query"><function>query</function></link></member>
2669 <member><link linkend="map-query"><function>map-query</function></link></member>
2670 <member><link linkend="do-query"><function>do-query</function></link></member>
2675 <title>Notes</title>