| blob | dc2c98bd51c7836ec6bc812e7858571851977079 |
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
148 :database database
149 :error-id subcode
154 :database database
158 :database database
161 ;;; Require an OCI success code.
162 ;;;
163 ;;; (The ordinary OCI error reporting mechanisms uses a fair amount of
164 ;;; machinery (environments and other handles). In order to get to
165 ;;; where we can use these mechanisms, we have to be able to allocate
166 ;;; the machinery. The functions for allocating the machinery can
167 ;;; return errors (e.g. out of memory) but shouldn't. Wrapping this function
168 ;;; around function calls to such have-to-succeed functions enforces
169 ;;; this condition.)
178 ;;; Enabling this can be handy for low-level debugging.
179 #+nil
182 oci-error-get oci-stmt-prepare oci-stmt-execute
183 oci-param-get oci-logon oci-attr-get oci-define-by-pos oci-stmt-fetch)
187 ;; Return the INDEXth string of the OCI array, represented as Lisp
188 ;; SIMPLE-STRING. SIZE is the size of the fixed-width fields used by
189 ;; Oracle to store strings within the array.
203 ;; the OCI library, part Z: no-longer used logic to convert from
204 ;; Oracle's binary date representation to Common Lisp's native date
205 ;; representation
207 #+nil
210 ;;; Return the INDEXth date in the OCI array, represented as
211 ;;; a Common Lisp "universal time" (i.e. seconds since 1900).
213 #+nil
219 #+nil
244 "select user_tables.table_name from user_tables,all_tables where user_tables.table_name=all_tables.table_name and all_tables.owner='~:@(~A~)'"
245 owner)))))
257 "select user_views.view_name from user_views,all_views where user_views.view_name=all_views.view_name and all_views.owner='~:@(~A~)'"
258 owner)))))
270 "select user_indexes.index_name from user_indexes,all_indexes where user_indexes.index_name=all_indexes.index_name and all_indexes.owner='~:@(~A~)'"
271 owner)))))
279 table))
282 table))
285 "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~)'"
286 table owner)))))
294 table))
297 table))
300 "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~)'"
301 table owner)))))
310 "select data_type,data_length,data_scale,nullable from user_tab_columns where table_name='~A' and column_name='~A'"
311 table attribute))
314 "select data_type,data_length,data_scale,nullable from all_tab_columns where table_name='~A' and column_name='~A'"
315 table attribute))
318 "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~)'"
319 table attribute owner)))))
320 (destructuring-bind (type length scale nullable) (car (database-query query database :auto nil))
324 ;; Return one row of the table referred to by QC, represented as a
325 ;; list; or if there are no more rows, signal an error if EOF-ERRORP,
326 ;; or return EOF-VALUE otherwise.
328 ;; KLUDGE: This CASE statement is a strong sign that the code would be
329 ;; cleaner if CD were made into an abstract class, we made variant
330 ;; classes for CD-for-column-of-strings, CD-for-column-of-floats,
331 ;; etc., and defined virtual functions to handle operations like
332 ;; get-an-element-from-column. (For a small special purpose module
333 ;; like this, would arguably be overkill, so I'm not going to do it
334 ;; now, but if this code ends up getting more complicated in
335 ;; maintenance, it would become a really good idea.)
337 ;; Arguably this would be a good place to signal END-OF-FILE, but
338 ;; since the ANSI spec specifically says that END-OF-FILE means a
339 ;; STREAM which has no more data, and QC is not a STREAM, we signal
340 ;; DBI-ERROR instead.
346 ;;; the result of a database query: a cursor through a table
351 :type oracle-database
354 ;; :type alien ; this table. owned by the QUERY-CURSOR
357 ; :type (simple-array cd 1)
358 ; :read-only t)
365 ; of the next row which hasn't already
366 ; been returned
371 ; data, i.e. OCI returning
372 ; less data than we requested?
373 ; OCI doesn't seem to like us
374 ; to try to read more data
375 ; from it after that..
379 ;;(declare (optimize (speed 3)))
384 eof-value))
399 ;;(declare (type short-array arb))
402 (#.SQLT-STR
404 (#.SQLT-FLT
406 (#.SQLT-INT
410 (#.SQLT-DATE
413 value
440 rowcount
453 ;; the guts of the SQL function
454 ;;
455 ;; (like the SQL function, but with the QUERY argument hardwired to T, so
456 ;; that the return value is always a cursor instead of a list)
458 ;; Is this a SELECT statement? SELECT statements are handled
459 ;; specially by OCIStmtExecute(). (Non-SELECT statements absolutely
460 ;; require a nonzero iteration count, while the ordinary choice for a
461 ;; SELECT statement is a zero iteration count.
463 ;; SELECT statements are the only statements which return tables. We
464 ;; don't free STMTHP in this case, but instead give it to the new
465 ;; QUERY-CURSOR, and the new QUERY-CURSOR becomes responsible for
466 ;; freeing the STMTHP when it is no longer needed.
470 db
475 stmthp
484 stmttype
501 nil)))))))
504 ;; Return a QUERY-CURSOR representing the table returned from the OCI
505 ;; operation done through STMTHP. TYPES is the argument of the same
506 ;; name from the external SQL function, controlling type conversion
507 ;; of the returned arguments.
511 :stmthp stmthp
513 result-types
514 field-names))))
516 qc))
519 ;; the hairy part of MAKE-QUERY-CURSOR: Ask OCI for information
520 ;; about table columns, translate the information into a Lisp
521 ;; vector of column descriptors, and return it.
523 ;; Allegro defines several flavors of type conversion, but this
524 ;; implementation only supports the :AUTO flavor.
526 ;; A note of explanation: OCI's internal number format uses 21
527 ;; bytes (42 decimal digits). 2 separate (?) one-byte fields,
528 ;; scale and precision, are used to deduce the nature of these
529 ;; 21 bytes. See pp. 3-10, 3-26, and 6-13 of OCI documentation
530 ;; for more details.
532 ;; Mac OS X Note: According to table 6-8 in the Oracle 9i OCI
533 ;; documentation, PRECISION may actually be an sb2 instead of a
534 ;; single byte if performing an "implicit describe". Using a
535 ;; signed short instead of an unsigned byte fixes a Mac OS X bug
536 ;; where PRECISION is always zero. -- JJB 20040713
538 ;; When calling OCI C code to handle the conversion, we have
539 ;; only two numeric types available to pass the return value:
540 ;; double-float and signed-long. It would be possible to
541 ;; bypass the OCI conversion functions and write Lisp code
542 ;; which reads the 21-byte field directly and decodes
543 ;; it. However this is left as an exercise for the reader. :-)
545 ;; The following table describes the mapping, based on the implicit
546 ;; assumption that C's "signed long" type is a 32-bit integer.
547 ;;
548 ;; Internal Values SQL Type C Return Type
549 ;; =============== ======== =============
550 ;; Precision > 0 SCALE = -127 FLOAT --> double-float
551 ;; Precision > 0 && <=9 SCALE = 0 INTEGER --> signed-long
552 ;; Precision = 0 || > 9 SCALE = 0 BIG INTEGER --> double-float
553 ;; Precision > 0 SCALE > 0 DECIMAL --> double-float
555 ;; (OCI uses 1-based indexing here.)
557 ;; KLUDGE: This should work for all other data types except those
558 ;; which don't actually fit in their fixed-width field (BLOBs and the
559 ;; like). As Winton says, we (Cadabra) don't need to worry much about
560 ;; those, since we can't reason with them, so we don't use them. But
561 ;; for a more general application it'd be good to have a more
562 ;; selective and rigorously correct test here for whether we can
563 ;; actually handle the given DEREF-DTYPE value. -- WHN 20000106
565 ;; Note: The OCI documentation doesn't seem to say whether the COLNAME
566 ;; value returned here is a newly-allocated copy which we're
567 ;; responsible for freeing, or a pointer into some system copy which
568 ;; will be freed when the system itself is shut down. But judging
569 ;; from the way that the result is used in the cdemodsa.c example
570 ;; program, it looks like the latter: we should make our own copy of
571 ;; the value, but not try to free it.
573 ;; WORKAROUND: OCI seems to return ub2 values for the
574 ;; +oci-attr-data-size+ attribute even though its documentation claims
575 ;; that it returns a ub4, and even though the associated "sizep" value
576 ;; is 4, not 2. In order to make the code here work reliably, without
577 ;; having to patch it later if OCI is ever fixed to match its
578 ;; documentation, we pre-zero COLSIZE before making the call into OCI.
580 ;; To exercise the weird OCI behavior (thereby blowing up the code
581 ;; below, beware!) try setting this value into COLSIZE, calling OCI,
582 ;; then looking at the value in COLSIZE. (setf colsize #x12345678)
583 ;; debugging only
585 ;; Mac OS X Note: This workaround fails on a bigendian platform so
586 ;; I've changed the data type of COLNAME to :unsigned-short as per
587 ;; the Oracle 9i OCI documentation. -- JJB 20040713
614 parmdp
618 ;; Decode type of ICOLUMNth column into a type we're prepared to
619 ;; handle in Lisp.
622 dtype-foreign
629 (#.SQLT-DATE
633 (#.SQLT-NUMBER
636 precision
642 scale
649 ;;(format t "scale=~d, precision=~d~%" *scale *precision)
660 ;; Default to SQL-STR
666 colsize
680 colname
681 colnamelen
688 :sizeof sizeof
689 :buffer buffer
690 :oci-data-type dtype
691 :retcodes retcodes
692 :indicators indicators
699 result-types)))
700 cds-as-reversed-list)
702 defnp
706 sizeof
707 dtype
713 ;; Release the resources associated with a QUERY-CURSOR.
723 ;; Release the resources associated with a column description.
742 connection-spec
747 ;; Requests to allocate environments and handles should never
748 ;; fail in normal operation, and they're done too early to
749 ;; handle errors very gracefully (since they're part of the
750 ;; error-handling mechanism themselves) so we just assert they
751 ;; work.
755 #-oci7
759 #+oci7
776 dblink
785 ;; oci-handle-alloc((dvoid *)encvhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0);
786 ;;#+nil
790 database-type)
791 :connection-spec connection-spec
792 :envhp envhp
793 :errhp errhp
795 :svchp svchp
796 :dsn data-source-name
800 svchp
805 ;; :date-format-length (1+ (length date-format)))))
814 server-version)))
815 db))))
838 ;; Close a database connection.
844 ;; Note: It's neither required nor allowed to explicitly deallocate the
845 ;; ERRHP handle here, since it's owned by the ENVHP deallocated above,
846 ;; and was therefore automatically deallocated at the same time.
847 t)
849 ;;; Do the database operation described in SQL-STMT-STRING on database
850 ;;; DB and, if the command is a SELECT, return a representation of the
851 ;;; resulting table. The representation of the table is controlled by the
852 ;;; QUERY argument:
853 ;;; * If QUERY is NIL, the table is returned as a list of rows, with
854 ;;; each row represented by a list.
855 ;;; * If QUERY is non-NIL, the result is returned as a QUERY-CURSOR
856 ;;; suitable for FETCH-ROW and CLOSE-QUERY
857 ;;; The TYPES argument controls the type conversion method used
858 ;;; to construct the table. The Allegro version supports several possible
859 ;;; values for this argument, but we only support :AUTO.
861 (defmethod database-query (query-expression (database oracle-database) result-types field-names)
863 ;; (declare (type (or query-cursor null) cursor))
895 ".NEXTVAL FROM dual"
896 )
905 database)
909 database))))
919 "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~)'"
920 owner)))))
927 t)
932 "a column descriptor: metadata about the data in a table"
934 ;; name of this column
936 ;; the size in bytes of a single element
938 ;; an array of +N-BUF-ROWS+ elements in C representation
940 :type foreign-resource
942 ;; an array of +N-BUF-ROWS+ OCI return codes in C representation.
943 ;; (There must be one return code for every element of every
944 ;; row in order to be able to represent nullness.)
946 :type foreign-resource
949 :type foreign-resource
951 ;; the OCI code for the data type of a single element
953 :type fixnum
963 ":NAME ~S :OCI-DATA-TYPE ~S :OCI-DATA-SIZE ~S"
992 list)))
996 ;; Not needed with simple transaction
997 #+ignore
1001 60
1003 t)
1015 t)
1022 t)
1024 ;; Specifications
1027 nil)
1030 t)
1033 nil)