| blob | f73b4fb5879ee1d7e6f48db1703f39340c2ca64e |
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
124 ;;; Handle the messy case of return code=+oci-error+, querying the
125 ;;; system for subcodes and reporting them as appropriate. ERRHP and
126 ;;; NULLS-OK are as in the OERR function.
134 ;; ensure errbuf empty string
141 sqlstate
142 errcode
151 :database database
152 :error-id subcode
156 :database database
160 :database database
163 ;;; Require an OCI success code.
164 ;;;
165 ;;; (The ordinary OCI error reporting mechanisms uses a fair amount of
166 ;;; machinery (environments and other handles). In order to get to
167 ;;; where we can use these mechanisms, we have to be able to allocate
168 ;;; the machinery. The functions for allocating the machinery can
169 ;;; return errors (e.g. out of memory) but shouldn't. Wrapping this function
170 ;;; around function calls to such have-to-succeed functions enforces
171 ;;; this condition.)
180 ;;; Enabling this can be handy for low-level debugging.
181 #+nil
184 oci-error-get oci-stmt-prepare oci-stmt-execute
185 oci-param-get oci-logon oci-attr-get oci-define-by-pos oci-stmt-fetch)
189 ;; Return the INDEXth string of the OCI array, represented as Lisp
190 ;; SIMPLE-STRING. SIZE is the size of the fixed-width fields used by
191 ;; Oracle to store strings within the array.
205 ;; the OCI library, part Z: no-longer used logic to convert from
206 ;; Oracle's binary date representation to Common Lisp's native date
207 ;; representation
209 #+nil
212 ;;; Return the INDEXth date in the OCI array, represented as
213 ;;; a Common Lisp "universal time" (i.e. seconds since 1900).
215 #+nil
221 #+nil
246 "select user_tables.table_name from user_tables,all_tables where user_tables.table_name=all_tables.table_name and all_tables.owner='~:@(~A~)'"
247 owner)))))
259 "select user_views.view_name from user_views,all_views where user_views.view_name=all_views.view_name and all_views.owner='~:@(~A~)'"
260 owner)))))
272 "select user_indexes.index_name from user_indexes,all_indexes where user_indexes.index_name=all_indexes.index_name and all_indexes.owner='~:@(~A~)'"
273 owner)))))
281 table))
284 table))
287 "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~)'"
288 table owner)))))
296 table))
299 table))
302 "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~)'"
303 table owner)))))
312 "select data_type,data_length,data_scale,nullable from user_tab_columns where table_name='~A' and column_name='~A'"
313 table attribute))
316 "select data_type,data_length,data_scale,nullable from all_tab_columns where table_name='~A' and column_name='~A'"
317 table attribute))
320 "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~)'"
321 table attribute owner)))))
322 (destructuring-bind (type length scale nullable) (car (database-query query database :auto nil))
326 ;; Return one row of the table referred to by QC, represented as a
327 ;; list; or if there are no more rows, signal an error if EOF-ERRORP,
328 ;; or return EOF-VALUE otherwise.
330 ;; KLUDGE: This CASE statement is a strong sign that the code would be
331 ;; cleaner if CD were made into an abstract class, we made variant
332 ;; classes for CD-for-column-of-strings, CD-for-column-of-floats,
333 ;; etc., and defined virtual functions to handle operations like
334 ;; get-an-element-from-column. (For a small special purpose module
335 ;; like this, would arguably be overkill, so I'm not going to do it
336 ;; now, but if this code ends up getting more complicated in
337 ;; maintenance, it would become a really good idea.)
339 ;; Arguably this would be a good place to signal END-OF-FILE, but
340 ;; since the ANSI spec specifically says that END-OF-FILE means a
341 ;; STREAM which has no more data, and QC is not a STREAM, we signal
342 ;; DBI-ERROR instead.
348 ;;; the result of a database query: a cursor through a table
353 :type oracle-database
356 ;; :type alien ; this table. owned by the QUERY-CURSOR
359 ; :type (simple-array cd 1)
360 ; :read-only t)
367 ; of the next row which hasn't already
368 ; been returned
373 ; data, i.e. OCI returning
374 ; less data than we requested?
375 ; OCI doesn't seem to like us
376 ; to try to read more data
377 ; from it after that..
381 ;;(declare (optimize (speed 3)))
386 eof-value))
401 ;;(declare (type short-array arb))
404 (#.SQLT-STR
406 (#.SQLT-FLT
408 (#.SQLT-INT
412 (#.SQLT-DATE
415 value
442 rowcount
455 ;; the guts of the SQL function
456 ;;
457 ;; (like the SQL function, but with the QUERY argument hardwired to T, so
458 ;; that the return value is always a cursor instead of a list)
460 ;; Is this a SELECT statement? SELECT statements are handled
461 ;; specially by OCIStmtExecute(). (Non-SELECT statements absolutely
462 ;; require a nonzero iteration count, while the ordinary choice for a
463 ;; SELECT statement is a zero iteration count.
465 ;; SELECT statements are the only statements which return tables. We
466 ;; don't free STMTHP in this case, but instead give it to the new
467 ;; QUERY-CURSOR, and the new QUERY-CURSOR becomes responsible for
468 ;; freeing the STMTHP when it is no longer needed.
478 stmthp
487 stmttype
501 ;; free resources unless a query
510 nil)))))
513 ;; Return a QUERY-CURSOR representing the table returned from the OCI
514 ;; operation done through STMTHP. TYPES is the argument of the same
515 ;; name from the external SQL function, controlling type conversion
516 ;; of the returned arguments.
520 :stmthp stmthp
522 result-types
523 field-names))))
525 qc))
528 ;; the hairy part of MAKE-QUERY-CURSOR: Ask OCI for information
529 ;; about table columns, translate the information into a Lisp
530 ;; vector of column descriptors, and return it.
532 ;; Allegro defines several flavors of type conversion, but this
533 ;; implementation only supports the :AUTO flavor.
535 ;; A note of explanation: OCI's internal number format uses 21
536 ;; bytes (42 decimal digits). 2 separate (?) one-byte fields,
537 ;; scale and precision, are used to deduce the nature of these
538 ;; 21 bytes. See pp. 3-10, 3-26, and 6-13 of OCI documentation
539 ;; for more details.
541 ;; Mac OS X Note: According to table 6-8 in the Oracle 9i OCI
542 ;; documentation, PRECISION may actually be an sb2 instead of a
543 ;; single byte if performing an "implicit describe". Using a
544 ;; signed short instead of an unsigned byte fixes a Mac OS X bug
545 ;; where PRECISION is always zero. -- JJB 20040713
547 ;; When calling OCI C code to handle the conversion, we have
548 ;; only two numeric types available to pass the return value:
549 ;; double-float and signed-long. It would be possible to
550 ;; bypass the OCI conversion functions and write Lisp code
551 ;; which reads the 21-byte field directly and decodes
552 ;; it. However this is left as an exercise for the reader. :-)
554 ;; The following table describes the mapping, based on the implicit
555 ;; assumption that C's "signed long" type is a 32-bit integer.
556 ;;
557 ;; Internal Values SQL Type C Return Type
558 ;; =============== ======== =============
559 ;; Precision > 0 SCALE = -127 FLOAT --> double-float
560 ;; Precision > 0 && <=9 SCALE = 0 INTEGER --> signed-long
561 ;; Precision = 0 || > 9 SCALE = 0 BIG INTEGER --> double-float
562 ;; Precision > 0 SCALE > 0 DECIMAL --> double-float
564 ;; (OCI uses 1-based indexing here.)
566 ;; KLUDGE: This should work for all other data types except those
567 ;; which don't actually fit in their fixed-width field (BLOBs and the
568 ;; like). As Winton says, we (Cadabra) don't need to worry much about
569 ;; those, since we can't reason with them, so we don't use them. But
570 ;; for a more general application it'd be good to have a more
571 ;; selective and rigorously correct test here for whether we can
572 ;; actually handle the given DEREF-DTYPE value. -- WHN 20000106
574 ;; Note: The OCI documentation doesn't seem to say whether the COLNAME
575 ;; value returned here is a newly-allocated copy which we're
576 ;; responsible for freeing, or a pointer into some system copy which
577 ;; will be freed when the system itself is shut down. But judging
578 ;; from the way that the result is used in the cdemodsa.c example
579 ;; program, it looks like the latter: we should make our own copy of
580 ;; the value, but not try to free it.
582 ;; WORKAROUND: OCI seems to return ub2 values for the
583 ;; +oci-attr-data-size+ attribute even though its documentation claims
584 ;; that it returns a ub4, and even though the associated "sizep" value
585 ;; is 4, not 2. In order to make the code here work reliably, without
586 ;; having to patch it later if OCI is ever fixed to match its
587 ;; documentation, we pre-zero COLSIZE before making the call into OCI.
589 ;; To exercise the weird OCI behavior (thereby blowing up the code
590 ;; below, beware!) try setting this value into COLSIZE, calling OCI,
591 ;; then looking at the value in COLSIZE. (setf colsize #x12345678)
592 ;; debugging only
594 ;; Mac OS X Note: This workaround fails on a bigendian platform so
595 ;; I've changed the data type of COLNAME to :unsigned-short as per
596 ;; the Oracle 9i OCI documentation. -- JJB 20040713
623 parmdp
627 ;; Decode type of ICOLUMNth column into a type we're prepared to
628 ;; handle in Lisp.
631 dtype-foreign
638 (#.SQLT-DATE
642 (#.SQLT-NUMBER
645 precision
651 scale
658 ;;(format t "scale=~d, precision=~d~%" *scale *precision)
669 ;; Default to SQL-STR
675 colsize
689 colname
690 colnamelen
697 :sizeof sizeof
698 :buffer buffer
699 :oci-data-type dtype
700 :retcodes retcodes
701 :indicators indicators
708 result-types)))
709 cds-as-reversed-list)
711 defnp
715 sizeof
716 dtype
722 ;; Release the resources associated with a QUERY-CURSOR.
733 ;; Release the resources associated with a column description.
752 connection-spec
757 ;; Requests to allocate environments and handles should never
758 ;; fail in normal operation, and they're done too early to
759 ;; handle errors very gracefully (since they're part of the
760 ;; error-handling mechanism themselves) so we just assert they
761 ;; work.
765 #-oci7
769 #+oci7
786 dblink
795 ;; oci-handle-alloc((dvoid *)encvhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0);
796 ;;#+nil
800 database-type)
801 :connection-spec connection-spec
802 :envhp envhp
803 :errhp errhp
805 :svchp svchp
806 :dsn data-source-name
810 svchp
815 ;; :date-format-length (1+ (length date-format)))))
824 server-version)))
825 db))))
848 ;; Close a database connection.
854 ;; Note: It's neither required nor allowed to explicitly deallocate the
855 ;; ERRHP handle here, since it's owned by the ENVHP deallocated above,
856 ;; and was therefore automatically deallocated at the same time.
857 t)
859 ;;; Do the database operation described in SQL-STMT-STRING on database
860 ;;; DB and, if the command is a SELECT, return a representation of the
861 ;;; resulting table. The representation of the table is controlled by the
862 ;;; QUERY argument:
863 ;;; * If QUERY is NIL, the table is returned as a list of rows, with
864 ;;; each row represented by a list.
865 ;;; * If QUERY is non-NIL, the result is returned as a QUERY-CURSOR
866 ;;; suitable for FETCH-ROW and CLOSE-QUERY
867 ;;; The TYPES argument controls the type conversion method used
868 ;;; to construct the table. The Allegro version supports several possible
869 ;;; values for this argument, but we only support :AUTO.
871 (defmethod database-query (query-expression (database oracle-database) result-types field-names)
873 ;; (declare (type (or query-cursor null) cursor))
921 database))
925 database))))
935 "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~)'"
936 owner)))))
943 t)
948 "a column descriptor: metadata about the data in a table"
950 ;; name of this column
952 ;; the size in bytes of a single element
954 ;; an array of +N-BUF-ROWS+ elements in C representation
956 :type foreign-resource
958 ;; an array of +N-BUF-ROWS+ OCI return codes in C representation.
959 ;; (There must be one return code for every element of every
960 ;; row in order to be able to represent nullness.)
962 :type foreign-resource
965 :type foreign-resource
967 ;; the OCI code for the data type of a single element
969 :type fixnum
979 ":NAME ~S :OCI-DATA-TYPE ~S :OCI-DATA-SIZE ~S"
1008 list)))
1012 ;; Not needed with simple transaction
1013 #+ignore
1017 60
1019 t)
1031 t)
1038 t)
1040 ;; Specifications
1043 nil)
1046 t)
1049 nil)