| blob | b0f5457417af9918874c9a138a4d830604d05bce |
1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
2 ;;;; *************************************************************************
3 ;;;; FILE IDENTIFICATION
4 ;;;;
5 ;;;; Name: oracle-sql.lisp
6 ;;;;
7 ;;;; $Id$
8 ;;;;
9 ;;;; This file is part of CLSQL.
10 ;;;;
11 ;;;; CLSQL users are granted the rights to distribute and use this software
12 ;;;; as governed by the terms of the Lisp Lesser GNU Public License
13 ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
14 ;;;; *************************************************************************
19 t)
21 ;;;; arbitrary parameters, tunable for performance or other reasons
27 "the number of table rows that we buffer at once when reading a table.
28 CMUCL has a compiled-in limit on how much C data can be allocated
29 (through malloc() and friends) at any given time, typically 8 Mb.
30 Setting this constant to a moderate value should make it less
47 ;; constants - from OCI?
61 ;;; Note that despite the suggestive class name (and the way that the
62 ;;; *DEFAULT-DATABASE* variable holds an object of this class), a DB
63 ;;; object is not actually a database but is instead a connection to a
64 ;;; database. Thus, there's no obstacle to having any number of DB
65 ;;; objects referring to the same database.
71 :reader envhp
73 :type pointer-pointer-void
74 :documentation
77 :reader errhp
79 :type pointer-pointer-void
80 :documentation
83 :reader svchp
85 :type pointer-pointer-void
86 :documentation
90 :initform nil
91 :documentation
95 :reader user
96 :type string
97 :documentation
101 :reader date-format
104 :type number
105 :documentation
106 "Each database connection can be configured with its own date
107 output format. In order to extract date strings from output buffers
108 holding multiple date strings in fixed-width fields, we need to know
113 :reader server-version
114 :documentation
119 :reader major-server-version
120 :documentation
123 ;;; Handle a non-successful result from an OCI function.
146 :message
149 ;;; Handle the messy case of return code=+oci-error+, querying the
150 ;;; system for subcodes and reporting them as appropriate. ERRHP and
151 ;;; NULLS-OK are as in the OERR function.
159 ;; ensure errbuf empty string
166 sqlstate
167 errcode
176 :database database
177 :error-id subcode
181 :database database
185 :database database
188 ;;; Require an OCI success code.
189 ;;;
190 ;;; (The ordinary OCI error reporting mechanisms uses a fair amount of
191 ;;; machinery (environments and other handles). In order to get to
192 ;;; where we can use these mechanisms, we have to be able to allocate
193 ;;; the machinery. The functions for allocating the machinery can
194 ;;; return errors (e.g. out of memory) but shouldn't. Wrapping this function
195 ;;; around function calls to such have-to-succeed functions enforces
196 ;;; this condition.)
205 ;;; Enabling this can be handy for low-level debugging.
206 #+nil
209 oci-error-get oci-stmt-prepare oci-stmt-execute
210 oci-param-get oci-logon oci-attr-get oci-define-by-pos oci-stmt-fetch)
214 ;; Return the INDEXth string of the OCI array, represented as Lisp
215 ;; SIMPLE-STRING. SIZE is the size of the fixed-width fields used by
216 ;; Oracle to store strings within the array.
230 ;; the OCI library, part Z: no-longer used logic to convert from
231 ;; Oracle's binary date representation to Common Lisp's native date
232 ;; representation
234 #+nil
237 ;;; Return the INDEXth date in the OCI array, represented as
238 ;;; a Common Lisp "universal time" (i.e. seconds since 1900).
240 #+nil
246 #+nil
271 "select user_tables.table_name from user_tables,all_tables where user_tables.table_name=all_tables.table_name and all_tables.owner='~:@(~A~)'"
272 owner)))))
284 "select user_views.view_name from user_views,all_views where user_views.view_name=all_views.view_name and all_views.owner='~:@(~A~)'"
285 owner)))))
297 "select user_indexes.index_name from user_indexes,all_indexes where user_indexes.index_name=all_indexes.index_name and all_indexes.owner='~:@(~A~)'"
298 owner)))))
306 table))
309 table))
312 "select user_indexes.index_name from user_indexes,all_indexes where user_indexes.table_name='~A' and user_indexes.index_name=all_indexes.index_name and all_indexes.owner='~:@(~A~)'"
313 table owner)))))
321 table))
324 table))
327 "select user_tab_columns.column_name from user_tab_columns,all_tables where user_tab_columns.table_name='~A' and all_tables.table_name=user_tab_columns.table_name and all_tables.owner='~:@(~A~)'"
328 table owner)))))
337 "select data_type,data_length,data_scale,nullable from user_tab_columns where table_name='~A' and column_name='~A'"
338 table attribute))
341 "select data_type,data_length,data_scale,nullable from all_tab_columns where table_name='~A' and column_name='~A'"
342 table attribute))
345 "select data_type,data_length,data_scale,nullable from user_tab_columns,all_tables where user_tab_columns.table_name='~A' and column_name='~A' and all_tables.table_name=user_tab_columns.table_name and all_tables.owner='~:@(~A~)'"
346 table attribute owner)))))
347 (destructuring-bind (type length scale nullable) (car (database-query query database :auto nil))
351 ;; Return one row of the table referred to by QC, represented as a
352 ;; list; or if there are no more rows, signal an error if EOF-ERRORP,
353 ;; or return EOF-VALUE otherwise.
355 ;; KLUDGE: This CASE statement is a strong sign that the code would be
356 ;; cleaner if CD were made into an abstract class, we made variant
357 ;; classes for CD-for-column-of-strings, CD-for-column-of-floats,
358 ;; etc., and defined virtual functions to handle operations like
359 ;; get-an-element-from-column. (For a small special purpose module
360 ;; like this, would arguably be overkill, so I'm not going to do it
361 ;; now, but if this code ends up getting more complicated in
362 ;; maintenance, it would become a really good idea.)
364 ;; Arguably this would be a good place to signal END-OF-FILE, but
365 ;; since the ANSI spec specifically says that END-OF-FILE means a
366 ;; STREAM which has no more data, and QC is not a STREAM, we signal
367 ;; DBI-ERROR instead.
375 ;;; the result of a database query: a cursor through a table
380 :type oracle-database
383 ;; :type alien ; this table. owned by the QUERY-CURSOR
386 ; :type (simple-array cd 1)
387 ; :read-only t)
394 ; of the next row which hasn't already
395 ; been returned
400 ; data, i.e. OCI returning
401 ; less data than we requested?
402 ; OCI doesn't seem to like us
403 ; to try to read more data
404 ; from it after that..
413 eof-value))
431 (#.SQLT-STR
433 (#.SQLT-FLT
437 (#.SQLT-INT
443 (#.SQLT-DATE
446 value
473 rowcount
486 ;; the guts of the SQL function
487 ;;
488 ;; (like the SQL function, but with the QUERY argument hardwired to T, so
489 ;; that the return value is always a cursor instead of a list)
491 ;; Is this a SELECT statement? SELECT statements are handled
492 ;; specially by OCIStmtExecute(). (Non-SELECT statements absolutely
493 ;; require a nonzero iteration count, while the ordinary choice for a
494 ;; SELECT statement is a zero iteration count.
496 ;; SELECT statements are the only statements which return tables. We
497 ;; don't free STMTHP in this case, but instead give it to the new
498 ;; QUERY-CURSOR, and the new QUERY-CURSOR becomes responsible for
499 ;; freeing the STMTHP when it is no longer needed.
505 select-p)
511 stmthp
515 c-stmt-string
520 stmttype
534 ;; free resources unless a query
543 nil))))))
546 ;; Return a QUERY-CURSOR representing the table returned from the OCI
547 ;; operation done through STMTHP. TYPES is the argument of the same
548 ;; name from the external SQL function, controlling type conversion
549 ;; of the returned arguments.
553 :stmthp stmthp
555 result-types
556 field-names))))
558 qc))
561 ;; the hairy part of MAKE-QUERY-CURSOR: Ask OCI for information
562 ;; about table columns, translate the information into a Lisp
563 ;; vector of column descriptors, and return it.
565 ;; Allegro defines several flavors of type conversion, but this
566 ;; implementation only supports the :AUTO flavor.
568 ;; A note of explanation: OCI's internal number format uses 21
569 ;; bytes (42 decimal digits). 2 separate (?) one-byte fields,
570 ;; scale and precision, are used to deduce the nature of these
571 ;; 21 bytes. See pp. 3-10, 3-26, and 6-13 of OCI documentation
572 ;; for more details.
574 ;; Mac OS X Note: According to table 6-8 in the Oracle 9i OCI
575 ;; documentation, PRECISION may actually be an sb2 instead of a
576 ;; single byte if performing an "implicit describe". Using a
577 ;; signed short instead of an unsigned byte fixes a Mac OS X bug
578 ;; where PRECISION is always zero. -- JJB 20040713
580 ;; When calling OCI C code to handle the conversion, we have
581 ;; only two numeric types available to pass the return value:
582 ;; double-float and signed-long. It would be possible to
583 ;; bypass the OCI conversion functions and write Lisp code
584 ;; which reads the 21-byte field directly and decodes
585 ;; it. However this is left as an exercise for the reader. :-)
587 ;; The following table describes the mapping, based on the implicit
588 ;; assumption that C's "signed long" type is a 32-bit integer.
589 ;;
590 ;; Internal Values SQL Type C Return Type
591 ;; =============== ======== =============
592 ;; Precision > 0 SCALE = -127 FLOAT --> double-float
593 ;; Precision > 0 && <=9 SCALE = 0 INTEGER --> signed-long
594 ;; Precision = 0 || > 9 SCALE = 0 BIG INTEGER --> double-float
595 ;; Precision > 0 SCALE > 0 DECIMAL --> double-float
597 ;; (OCI uses 1-based indexing here.)
599 ;; KLUDGE: This should work for all other data types except those
600 ;; which don't actually fit in their fixed-width field (BLOBs and the
601 ;; like). As Winton says, we (Cadabra) don't need to worry much about
602 ;; those, since we can't reason with them, so we don't use them. But
603 ;; for a more general application it'd be good to have a more
604 ;; selective and rigorously correct test here for whether we can
605 ;; actually handle the given DEREF-DTYPE value. -- WHN 20000106
607 ;; Note: The OCI documentation doesn't seem to say whether the COLNAME
608 ;; value returned here is a newly-allocated copy which we're
609 ;; responsible for freeing, or a pointer into some system copy which
610 ;; will be freed when the system itself is shut down. But judging
611 ;; from the way that the result is used in the cdemodsa.c example
612 ;; program, it looks like the latter: we should make our own copy of
613 ;; the value, but not try to free it.
615 ;; WORKAROUND: OCI seems to return ub2 values for the
616 ;; +oci-attr-data-size+ attribute even though its documentation claims
617 ;; that it returns a ub4, and even though the associated "sizep" value
618 ;; is 4, not 2. In order to make the code here work reliably, without
619 ;; having to patch it later if OCI is ever fixed to match its
620 ;; documentation, we pre-zero COLSIZE before making the call into OCI.
622 ;; To exercise the weird OCI behavior (thereby blowing up the code
623 ;; below, beware!) try setting this value into COLSIZE, calling OCI,
624 ;; then looking at the value in COLSIZE. (setf colsize #x12345678)
625 ;; debugging only
627 ;; Mac OS X Note: This workaround fails on a bigendian platform so
628 ;; I've changed the data type of COLNAME to :unsigned-short as per
629 ;; the Oracle 9i OCI documentation. -- JJB 20040713
654 parmdp
658 ;; Decode type of ICOLUMNth column into a type we're prepared to
659 ;; handle in Lisp.
662 dtype-foreign
669 (#.SQLT-DATE
673 (#.SQLT-NUMBER
676 precision
682 scale
689 ;;(format t "scale=~d, precision=~d~%" *scale *precision)
700 ;; Default to SQL-STR
706 colsize
720 colname
721 colnamelen
728 :sizeof sizeof
729 :buffer buffer
730 :oci-data-type dtype
731 :retcodes retcodes
732 :indicators indicators
739 result-types)))
740 cds-as-reversed-list)
742 defnp
746 sizeof
747 dtype
753 ;; Release the resources associated with a QUERY-CURSOR.
764 ;; Release the resources associated with a column description.
783 connection-spec
788 ;; Requests to allocate environments and handles should never
789 ;; fail in normal operation, and they're done too early to
790 ;; handle errors very gracefully (since they're part of the
791 ;; error-handling mechanism themselves) so we just assert they
792 ;; work.
796 #-oci7
801 #+oci7
817 database-type)
818 :connection-spec connection-spec
819 :envhp envhp
820 :errhp errhp
822 :svchp svchp
823 :dsn data-source-name
830 svchp
835 ;; :date-format-length (1+ (length date-format)))))
844 server-version)))
845 db))))
868 ;; Close a database connection.
874 ;; Note: It's neither required nor allowed to explicitly deallocate the
875 ;; ERRHP handle here, since it's owned by the ENVHP deallocated above,
876 ;; and was therefore automatically deallocated at the same time.
877 t)
879 ;;; Do the database operation described in SQL-STMT-STRING on database
880 ;;; DB and, if the command is a SELECT, return a representation of the
881 ;;; resulting table. The representation of the table is controlled by the
882 ;;; QUERY argument:
883 ;;; * If QUERY is NIL, the table is returned as a list of rows, with
884 ;;; each row represented by a list.
885 ;;; * If QUERY is non-NIL, the result is returned as a QUERY-CURSOR
886 ;;; suitable for FETCH-ROW and CLOSE-QUERY
887 ;;; The TYPES argument controls the type conversion method used
888 ;;; to construct the table. The Allegro version supports several possible
889 ;;; values for this argument, but we only support :AUTO.
891 (defmethod database-query (query-expression (database oracle-database) result-types field-names)
893 ;; (declare (type (or query-cursor null) cursor))
941 database))
945 database))))
955 "select user_sequences.sequence_name from user_sequences,all_sequences where user_sequences.sequence_name=all_sequences.sequence_name and all_sequences.sequence_owner='~:@(~A~)'"
956 owner)))))
963 t)
968 "a column descriptor: metadata about the data in a table"
970 ;; name of this column
972 ;; the size in bytes of a single element
974 ;; an array of +N-BUF-ROWS+ elements in C representation
976 :type foreign-resource
978 ;; an array of +N-BUF-ROWS+ OCI return codes in C representation.
979 ;; (There must be one return code for every element of every
980 ;; row in order to be able to represent nullness.)
982 :type foreign-resource
985 :type foreign-resource
987 ;; the OCI code for the data type of a single element
989 :type fixnum
999 ":NAME ~S :OCI-DATA-TYPE ~S :OCI-DATA-SIZE ~S"
1028 list)))
1032 ;; Not needed with simple transaction
1033 #+ignore
1037 60
1039 t)
1051 t)
1058 t)
1060 ;; Specifications
1063 nil)
1066 t)
1069 nil)