| blob | e5f66e5406b61d84e46ceb4072fdb2529603d835 |
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.
473 select-p)
479 stmthp
488 stmttype
502 ;; free resources unless a query
511 nil)))))
514 ;; Return a QUERY-CURSOR representing the table returned from the OCI
515 ;; operation done through STMTHP. TYPES is the argument of the same
516 ;; name from the external SQL function, controlling type conversion
517 ;; of the returned arguments.
521 :stmthp stmthp
523 result-types
524 field-names))))
526 qc))
529 ;; the hairy part of MAKE-QUERY-CURSOR: Ask OCI for information
530 ;; about table columns, translate the information into a Lisp
531 ;; vector of column descriptors, and return it.
533 ;; Allegro defines several flavors of type conversion, but this
534 ;; implementation only supports the :AUTO flavor.
536 ;; A note of explanation: OCI's internal number format uses 21
537 ;; bytes (42 decimal digits). 2 separate (?) one-byte fields,
538 ;; scale and precision, are used to deduce the nature of these
539 ;; 21 bytes. See pp. 3-10, 3-26, and 6-13 of OCI documentation
540 ;; for more details.
542 ;; Mac OS X Note: According to table 6-8 in the Oracle 9i OCI
543 ;; documentation, PRECISION may actually be an sb2 instead of a
544 ;; single byte if performing an "implicit describe". Using a
545 ;; signed short instead of an unsigned byte fixes a Mac OS X bug
546 ;; where PRECISION is always zero. -- JJB 20040713
548 ;; When calling OCI C code to handle the conversion, we have
549 ;; only two numeric types available to pass the return value:
550 ;; double-float and signed-long. It would be possible to
551 ;; bypass the OCI conversion functions and write Lisp code
552 ;; which reads the 21-byte field directly and decodes
553 ;; it. However this is left as an exercise for the reader. :-)
555 ;; The following table describes the mapping, based on the implicit
556 ;; assumption that C's "signed long" type is a 32-bit integer.
557 ;;
558 ;; Internal Values SQL Type C Return Type
559 ;; =============== ======== =============
560 ;; Precision > 0 SCALE = -127 FLOAT --> double-float
561 ;; Precision > 0 && <=9 SCALE = 0 INTEGER --> signed-long
562 ;; Precision = 0 || > 9 SCALE = 0 BIG INTEGER --> double-float
563 ;; Precision > 0 SCALE > 0 DECIMAL --> double-float
565 ;; (OCI uses 1-based indexing here.)
567 ;; KLUDGE: This should work for all other data types except those
568 ;; which don't actually fit in their fixed-width field (BLOBs and the
569 ;; like). As Winton says, we (Cadabra) don't need to worry much about
570 ;; those, since we can't reason with them, so we don't use them. But
571 ;; for a more general application it'd be good to have a more
572 ;; selective and rigorously correct test here for whether we can
573 ;; actually handle the given DEREF-DTYPE value. -- WHN 20000106
575 ;; Note: The OCI documentation doesn't seem to say whether the COLNAME
576 ;; value returned here is a newly-allocated copy which we're
577 ;; responsible for freeing, or a pointer into some system copy which
578 ;; will be freed when the system itself is shut down. But judging
579 ;; from the way that the result is used in the cdemodsa.c example
580 ;; program, it looks like the latter: we should make our own copy of
581 ;; the value, but not try to free it.
583 ;; WORKAROUND: OCI seems to return ub2 values for the
584 ;; +oci-attr-data-size+ attribute even though its documentation claims
585 ;; that it returns a ub4, and even though the associated "sizep" value
586 ;; is 4, not 2. In order to make the code here work reliably, without
587 ;; having to patch it later if OCI is ever fixed to match its
588 ;; documentation, we pre-zero COLSIZE before making the call into OCI.
590 ;; To exercise the weird OCI behavior (thereby blowing up the code
591 ;; below, beware!) try setting this value into COLSIZE, calling OCI,
592 ;; then looking at the value in COLSIZE. (setf colsize #x12345678)
593 ;; debugging only
595 ;; Mac OS X Note: This workaround fails on a bigendian platform so
596 ;; I've changed the data type of COLNAME to :unsigned-short as per
597 ;; the Oracle 9i OCI documentation. -- JJB 20040713
624 parmdp
628 ;; Decode type of ICOLUMNth column into a type we're prepared to
629 ;; handle in Lisp.
632 dtype-foreign
639 (#.SQLT-DATE
643 (#.SQLT-NUMBER
646 precision
652 scale
659 ;;(format t "scale=~d, precision=~d~%" *scale *precision)
670 ;; Default to SQL-STR
676 colsize
690 colname
691 colnamelen
698 :sizeof sizeof
699 :buffer buffer
700 :oci-data-type dtype
701 :retcodes retcodes
702 :indicators indicators
709 result-types)))
710 cds-as-reversed-list)
712 defnp
716 sizeof
717 dtype
723 ;; Release the resources associated with a QUERY-CURSOR.
734 ;; Release the resources associated with a column description.
753 connection-spec
758 ;; Requests to allocate environments and handles should never
759 ;; fail in normal operation, and they're done too early to
760 ;; handle errors very gracefully (since they're part of the
761 ;; error-handling mechanism themselves) so we just assert they
762 ;; work.
766 #-oci7
770 #+oci7
787 dblink
796 ;; oci-handle-alloc((dvoid *)encvhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0);
797 ;;#+nil
801 database-type)
802 :connection-spec connection-spec
803 :envhp envhp
804 :errhp errhp
806 :svchp svchp
807 :dsn data-source-name
811 svchp
816 ;; :date-format-length (1+ (length date-format)))))
825 server-version)))
826 db))))
849 ;; Close a database connection.
855 ;; Note: It's neither required nor allowed to explicitly deallocate the
856 ;; ERRHP handle here, since it's owned by the ENVHP deallocated above,
857 ;; and was therefore automatically deallocated at the same time.
858 t)
860 ;;; Do the database operation described in SQL-STMT-STRING on database
861 ;;; DB and, if the command is a SELECT, return a representation of the
862 ;;; resulting table. The representation of the table is controlled by the
863 ;;; QUERY argument:
864 ;;; * If QUERY is NIL, the table is returned as a list of rows, with
865 ;;; each row represented by a list.
866 ;;; * If QUERY is non-NIL, the result is returned as a QUERY-CURSOR
867 ;;; suitable for FETCH-ROW and CLOSE-QUERY
868 ;;; The TYPES argument controls the type conversion method used
869 ;;; to construct the table. The Allegro version supports several possible
870 ;;; values for this argument, but we only support :AUTO.
872 (defmethod database-query (query-expression (database oracle-database) result-types field-names)
874 ;; (declare (type (or query-cursor null) cursor))
922 database))
926 database))))
936 "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~)'"
937 owner)))))
944 t)
949 "a column descriptor: metadata about the data in a table"
951 ;; name of this column
953 ;; the size in bytes of a single element
955 ;; an array of +N-BUF-ROWS+ elements in C representation
957 :type foreign-resource
959 ;; an array of +N-BUF-ROWS+ OCI return codes in C representation.
960 ;; (There must be one return code for every element of every
961 ;; row in order to be able to represent nullness.)
963 :type foreign-resource
966 :type foreign-resource
968 ;; the OCI code for the data type of a single element
970 :type fixnum
980 ":NAME ~S :OCI-DATA-TYPE ~S :OCI-DATA-SIZE ~S"
1009 list)))
1013 ;; Not needed with simple transaction
1014 #+ignore
1018 60
1020 t)
1032 t)
1039 t)
1041 ;; Specifications
1044 nil)
1047 t)
1050 nil)