From: Kevin M. Rosenberg Date: Sat, 29 May 2004 15:31:36 +0000 (+0000) Subject: r9517: initial rename to db2 files X-Git-Tag: v3.8.6~355 X-Git-Url: http://git.kpe.io/?p=clsql.git;a=commitdiff_plain;h=3770e7b13ca9fe756505f9cd90636ea18a5b5d63 r9517: initial rename to db2 files --- diff --git a/db-db2/Makefile b/db-db2/Makefile index ae4912b..8a26499 100644 --- a/db-db2/Makefile +++ b/db-db2/Makefile @@ -1,7 +1,7 @@ # FILE IDENTIFICATION # # Name: Makefile -# Purpose: Makefile for CLSQL Oracle interface +# Purpose: Makefile for CLSQL Db2 interface # Author: Kevin M. Rosenberg # Created: May 2004 # diff --git a/db-db2/README b/db-db2/README deleted file mode 100644 index 3bef886..0000000 --- a/db-db2/README +++ /dev/null @@ -1,24 +0,0 @@ -This is the header of the cadabra source file. - -;;;; a CMUCL/OCI implementation of a subset of the DBI interface -;;;; -;;;; The original version of this code was copyright (c) 1999-2000 Cadabra Inc. -;;;; It was placed in the public domain by Cadabra in January 2000. -;;;; -;;;; The implementors of the original version were Winton Davies -;;;; and William Newman . -;;;; The code will be maintained by Winton Davies. - -;;;; known issues: -;;;; * The code will leak C resources if errors occur in the the wrong -;;;; places, since it doesn't wrap its allocation/deallocation -;;;; logic in the necessary EVAL-WHENs to prevent this. (This could be -;;;; easily be an issue for long-running processes which recover from -;;;; database errors instead of simply terminating when they occur. It's -;;;; not an issue for programs which consider database errors so abnormal -;;;; that they die immediately when they encounter one.) -;;;; * Instead of reading Oracle header files automatically, this code -;;;; uses constants, types, and function signatures manually transcribed -;;;; from the Oracle header files. Thus, changes in the header files -;;;; may require manual maintenance of the code. (This version was written -;;;; for Oracle 8.1.5.) diff --git a/db-db2/db2-api.lisp b/db-db2/db2-api.lisp new file mode 100644 index 0000000..61de25c --- /dev/null +++ b/db-db2/db2-api.lisp @@ -0,0 +1,366 @@ +;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- +;;;; ************************************************************************* +;;;; FILE IDENTIFICATION +;;;; +;;;; Name: db2.lisp +;;;; Purpose: Package definition for CLSQL Db2 interface +;;;; +;;;; $Id$ +;;;; +;;;; This file is part of CLSQL. +;;;; +;;;; CLSQL users are granted the rights to distribute and use this software +;;;; as governed by the terms of the Lisp Lesser GNU Public License +;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. +;;;; ************************************************************************* + +(in-package #:clsql-db2) + + +;; +;; Opaque pointer types +;; + +(uffi:def-foreign-type void-pointer :pointer-void) +(uffi:def-foreign-type oci-env :pointer-void) +(uffi:def-foreign-type oci-server :pointer-void) +(uffi:def-foreign-type oci-error :pointer-void) +(uffi:def-foreign-type oci-svc-ctx :pointer-void) +(uffi:def-foreign-type oci-stmt :pointer-void) + + +(defvar +null-void-pointer+ (uffi:make-null-pointer :void)) +(defvar +null-void-pointer-pointer+ (uffi:make-null-pointer :pointer-void)) + +;;; Check an OCI return code for erroricity and signal a reasonably +;;; informative condition if so. +;;; +;;; ERRHP provides an error handle which can be used to find +;;; subconditions; if it's not provided, subcodes won't be checked. +;;; +;;; NULLS-OK says that a NULL-VALUE-RETURNED subcondition condition is +;;; normal and needn't cause any signal. An error handle is required +;;; to detect this subcondition, so it doesn't make sense to set ERRHP +;;; unless NULLS-OK is set. + +(defmacro def-oci-routine ((c-oci-symbol lisp-oci-fn) c-return &rest c-parms) + (let ((ll (mapcar (lambda (x) (declare (ignore x)) (gensym)) c-parms))) + `(let ((%lisp-oci-fn (uffi:def-function + (,c-oci-symbol ,(intern (concatenate 'string "%" (symbol-name lisp-oci-fn)))) + ,c-parms + :returning ,c-return))) + (defun ,lisp-oci-fn (,@ll &key database nulls-ok) + (let ((result (funcall %lisp-oci-fn ,@ll))) + (case result + (#.+oci-success+ + +oci-success+) + (#.+oci-error+ + (handle-oci-error :database database :nulls-ok nulls-ok)) + (#.+oci-no-data+ + (error 'sql-database-error :message "OCI No Data Found")) + (#.+oci-success-with-info+ + (error 'sql-database-error :message "internal error: unexpected +oci-success-with-info")) + (#.+oci-no-data+ + (error 'sql-database-error :message "OCI No Data")) + (#.+oci-invalid-handle+ + (error 'sql-database-error :message "OCI Invalid Handle")) + (#.+oci-need-data+ + (error 'sql-database-error :message "OCI Need Data")) + (#.+oci-still-executing+ + (error 'sql-temporary-error :message "OCI Still Executing")) + (#.+oci-continue+ + (error 'sql-database-error :message "OCI Continue")) + (1804 + (error 'sql-database-error :message "Check DB2_HOME and NLS settings.")) + (t + (error 'sql-database-error + :message + (format nil "OCI unknown error, code=~A" result))))))))) + + +(defmacro def-raw-oci-routine + ((c-oci-symbol lisp-oci-fn) c-return &rest c-parms) + (let ((ll (mapcar (lambda (x) (declare (ignore x)) (gensym)) c-parms))) + `(let ((%lisp-oci-fn (uffi:def-function (,c-oci-symbol ,(intern (concatenate 'string "%" (symbol-name lisp-oci-fn)))) + ,c-parms + :returning ,c-return))) + (defun ,lisp-oci-fn (,@ll &key database nulls-ok) + (funcall %lisp-oci-fn ,@ll))))) + + +(def-oci-routine ("OCIInitialize" oci-initialize) + :int + (mode :unsigned-long) ; ub4 + (ctxp :pointer-void) ; dvoid * + (malocfp :pointer-void) ; dvoid *(*) + (ralocfp :pointer-void) ; dvoid *(*) + (mfreefp (* :pointer-void))) ; void *(*) + + +(def-oci-routine ("OCIEnvInit" oci-env-init) + :int + (envpp :pointer-void) ; OCIEnv ** + (mode :unsigned-long) ; ub4 + (xtramem-sz :unsigned-long) ; size_t + (usermempp (* :pointer-void))) ; dvoid ** + +#-oci7 +(def-oci-routine ("OCIEnvCreate" oci-env-create) + :int + (envhpp (* :pointer-void)) + (mode :unsigned-int) + (ctxp :pointer-void) + (malocfp :pointer-void) + (ralocfp :pointer-void) + (mfreefp :pointer-void) + (xtramemsz :unsigned-long) + (usrmempp (* :pointer-void))) + +(def-oci-routine ("OCIHandleAlloc" oci-handle-alloc) + :int + (parenth :pointer-void) ; const dvoid * + (hndlpp (* :pointer-void)) ; dvoid ** + (type :unsigned-long) ; ub4 + (xtramem_sz :unsigned-long) ; size_t + (usrmempp (* :pointer-void))) ; dvoid ** + +(def-oci-routine ("OCIServerAttach" oci-server-attach) + :int + (srvhp :pointer-void) ; oci-server + (errhp :pointer-void) ; oci-error + (dblink :cstring) ; :in + (dblink-len :unsigned-long) ; int + (mode :unsigned-long)) ; int + + +(def-oci-routine ("OCIHandleFree" oci-handle-free) + :int + (p0 :pointer-void) ;; handle + (p1 :unsigned-long)) ;;type + +(def-oci-routine ("OCILogon" oci-logon) + :int + (envhp :pointer-void) ; env + (errhp :pointer-void) ; err + (svchpp (* :pointer-void)) ; svc + (username :cstring) ; username + (uname-len :unsigned-long) ; + (passwd :cstring) ; passwd + (password-len :unsigned-long) ; + (dsn :cstring) ; datasource + (dsn-len :unsigned-long)) ; + +(def-oci-routine ("OCILogoff" oci-logoff) + :int + (p0 :pointer-void) ; svc + (p1 :pointer-void)) ; err + +(uffi:def-function ("OCIErrorGet" oci-error-get) + ((handlp :pointer-void) + (recordno :unsigned-long) + (sqlstate :cstring) + (errcodep (* :long)) + (bufp (* :unsigned-char)) + (bufsize :unsigned-long) + (type :unsigned-long)) + :returning :void) + +(def-oci-routine ("OCIStmtPrepare" oci-stmt-prepare) + :int + (stmtp :pointer-void) + (errhp :pointer-void) + (stmt :cstring) + (stmt_len :unsigned-long) + (language :unsigned-long) + (mode :unsigned-long)) + +(def-oci-routine ("OCIStmtExecute" oci-stmt-execute) + :int + (svchp :pointer-void) + (stmtp1 :pointer-void) + (errhp :pointer-void) + (iters :unsigned-long) + (rowoff :unsigned-long) + (snap_in :pointer-void) + (snap_out :pointer-void) + (mode :unsigned-long)) + +(def-raw-oci-routine ("OCIParamGet" oci-param-get) + :int + (hndlp :pointer-void) + (htype :unsigned-long) + (errhp :pointer-void) + (parmdpp (* :pointer-void)) + (pos :unsigned-long)) + +(def-oci-routine ("OCIAttrGet" oci-attr-get) + :int + (trgthndlp :pointer-void) + (trghndltyp :unsigned-int) + (attributep :pointer-void) + (sizep (* :unsigned-int)) + (attrtype :unsigned-int) + (errhp :pointer-void)) + +(def-oci-routine ("OCIAttrSet" oci-attr-set) + :int + (trgthndlp :pointer-void) + (trgthndltyp :int :in) + (attributep :pointer-void) + (size :int) + (attrtype :int) + (errhp oci-error)) + +(def-oci-routine ("OCIDefineByPos" oci-define-by-pos) + :int + (stmtp :pointer-void) + (defnpp (* :pointer-void)) + (errhp :pointer-void) + (position :unsigned-long) + (valuep :pointer-void) + (value_sz :long) + (dty :unsigned-short) + (indp (* :short)) + (rlenp (* :unsigned-short)) + (rcodep (* :unsigned-short)) + (mode :unsigned-long)) + +(def-oci-routine ("OCIStmtFetch" oci-stmt-fetch) + :int + (stmthp :pointer-void) + (errhp :pointer-void) + (p2 :unsigned-long) + (p3 :unsigned-short) + (p4 :unsigned-long)) + + +(def-oci-routine ("OCITransStart" oci-trans-start) + :int + (svchp :pointer-void) + (errhp :pointer-void) + (p2 :unsigned-short) + (p3 :unsigned-short)) + +(def-oci-routine ("OCITransCommit" oci-trans-commit) + :int + (svchp :pointer-void) + (errhp :pointer-void) + (p2 :unsigned-short)) + +(def-oci-routine ("OCITransRollback" oci-trans-rollback) + :int + (svchp :pointer-void) + (errhp :pointer-void) + (p2 :unsigned-short)) + + +(def-oci-routine ("OCIServerVersion" oci-server-version) + :int + (handlp :pointer-void) + (errhp :pointer-void) + (bufp (* :unsigned-char)) + (bufsz :int) + (hndltype :short)) + + + +;;; Low-level routines that don't do error checking. They are used +;;; for setting up global environment. + +(uffi:def-function "OCIInitialize" + ((mode :unsigned-long) ; ub4 + (ctxp :pointer-void) ; dvoid * + (malocfp :pointer-void) ; dvoid *(*) + (ralocfp :pointer-void) ; dvoid *(*) + (mfreefp (* :pointer-void))) + :returning :int) + +(uffi:def-function "OCIEnvInit" + ((envpp :pointer-void) ; OCIEnv ** + (mode :unsigned-long) ; ub4 + (xtramem-sz :unsigned-long) ; size_t + (usermempp (* :pointer-void))) + :returning :int) + + +(uffi:def-function "OCIHandleAlloc" + ((parenth :pointer-void) ; const dvoid * + (hndlpp (* :pointer-void)) ; dvoid ** + (type :unsigned-long) ; ub4 + (xtramem_sz :unsigned-long) ; size_t + (usrmempp (* :pointer-void))) + :returning :int) + +(defstruct oci-handle + (type :unknown) + (pointer (uffi:allocate-foreign-object :pointer-void))) + +(defvar *oci-initialized* nil) +(defvar *oci-env* nil) + +(defvar *oci-handle-types* + '(:error ; error report handle (OCIError) + :service-context ; service context handle (OCISvcCtx) + :statement ; statement (application request) handle (OCIStmt) + :describe ; select list description handle (OCIDescribe) + :server ; server context handle (OCIServer) + :session ; user session handle (OCISession) + :transaction ; transaction context handle (OCITrans) + :complex-object ; complex object retrieval handle (OCIComplexObject) + :security)) ; security handle (OCISecurity) + + + +(defun oci-init (&key (mode +oci-default+)) + (let ((x (OCIInitialize mode +null-void-pointer+ +null-void-pointer+ + +null-void-pointer+ +null-void-pointer-pointer+))) + (if (= x 0) + (let ((env (uffi:allocate-foreign-object :pointer-void))) + (setq *oci-initialized* mode) + (let ((x (OCIEnvInit env +oci-default+ 0 +null-void-pointer+))) + (format t ";; OEI: returned ~d~%" x) + (setq *oci-env* env)))))) + +(defun oci-check-return (value) + (when (= value +oci-invalid-handle+) + (error 'sql-database-error :message "Invalid Handle"))) + +(defun oci-get-handle (&key type) + (if (null *oci-initialized*) + (oci-init)) + (case type + (:error + (let ((ptr (uffi:allocate-foreign-object :pointer-void))) + (let ((x (OCIHandleAlloc + (uffi:deref-pointer *oci-env* void-pointer) + ptr + +oci-default+ + 0 + +null-void-pointer-pointer+))) + (oci-check-return x) + ptr))) + (:service-context + "OCISvcCtx") + (:statement + "OCIStmt") + (:describe + "OCIDescribe") + (:server + "OCIServer") + (:session + "OCISession") + (:transaction + "OCITrans") + (:complex-object + "OCIComplexObject") + (:security + "OCISecurity") + (t + (error 'sql-database-error + :message + (format nil "'~s' is not a valid OCI handle type" type))))) + +(defun oci-environment () + (let ((envhp (oci-get-handle :type :env))) + (oci-env-init envhp 0 0 +null-void-pointer+) + envhp)) diff --git a/db-db2/db2-constants.lisp b/db-db2/db2-constants.lisp new file mode 100644 index 0000000..0b3f48e --- /dev/null +++ b/db-db2/db2-constants.lisp @@ -0,0 +1,17 @@ +;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- +;;;; ************************************************************************* +;;;; FILE IDENTIFICATION +;;;; +;;;; Name: db2-constants.lisp +;;;; Purpose: Constants for CLSQL Db2 interface +;;;; +;;;; $Id$ +;;;; +;;;; This file is part of CLSQL. +;;;; +;;;; CLSQL users are granted the rights to distribute and use this software +;;;; as governed by the terms of the Lisp Lesser GNU Public License +;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. +;;;; ************************************************************************* + +(in-package #:clsql-db2) diff --git a/db-db2/db2-loader.lisp b/db-db2/db2-loader.lisp new file mode 100644 index 0000000..0e53a31 --- /dev/null +++ b/db-db2/db2-loader.lisp @@ -0,0 +1,59 @@ +;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- +;;;; ************************************************************************* +;;;; FILE IDENTIFICATION +;;;; +;;;; Name: db2-loader.lisp +;;;; Purpose: Foreign library loader for CLSQL Db2 interface +;;;; +;;;; $Id$ +;;;; +;;;; This file is part of CLSQL. +;;;; +;;;; CLSQL users are granted the rights to distribute and use this software +;;;; as governed by the terms of the Lisp Lesser GNU Public License +;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. +;;;; ************************************************************************* + +(in-package #:clsql-db2) + +(defparameter *db2-lib-path* + (let ((db2-home (getenv "DB2_HOME"))) + (when db2-home + (make-pathname :directory + (append + (pathname-directory + (parse-namestring (concatenate 'string db2-home "/"))) + (list "lib")))))) + +(defparameter *db2-client-library-path* + (uffi:find-foreign-library + "libclntsh" + `(,@(when *load-truename* (list (make-pathname :directory (pathname-directory *load-truename*)))) + ,@(when *db2-lib-path* (list *db2-lib-path*)) + "/usr/lib/db2/10.1.0.2/client/lib/") + :drive-letters '("C"))) + +(defvar *db2-supporting-libraries* '("c") + "Used only by CMU. List of library flags needed to be passed to ld to +load the Db2 client library succesfully. If this differs at your site, +set to the right path before compiling or loading the system.") + +(defvar *db2-library-loaded* nil + "T if foreign library was able to be loaded successfully") + +(defmethod clsql-sys:database-type-library-loaded ((database-type (eql :db2))) + *db2-library-loaded*) + +(defmethod clsql-sys:database-type-load-foreign ((database-type (eql :db2))) + (if (pathnamep *db2-client-library-path*) + (progn + (uffi:load-foreign-library *db2-client-library-path* + :module "clsql-db2" + :supporting-libraries + *db2-supporting-libraries*) + (setq *db2-library-loaded* t)) + (warn "Unable to load db2 client library."))) + +(clsql-sys:database-type-load-foreign :db2) + + diff --git a/db-db2/db2-objects.lisp b/db-db2/db2-objects.lisp new file mode 100644 index 0000000..288e573 --- /dev/null +++ b/db-db2/db2-objects.lisp @@ -0,0 +1,119 @@ +;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- +;;;; ************************************************************************* +;;;; FILE IDENTIFICATION +;;;; +;;;; Name: db2-objects.lisp +;;;; +;;;; $Id$ +;;;; +;;;; This file is part of CLSQL. +;;;; +;;;; CLSQL users are granted the rights to distribute and use this software +;;;; as governed by the terms of the Lisp Lesser GNU Public License +;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. +;;;; ************************************************************************* + +(in-package #:clsql-db2) + +(defmethod database-get-type-specifier (type args database (db-type (eql :db2))) + (declare (ignore type args database)) + (format nil "VARCHAR2(~D)" *default-varchar-length*)) + +(defmethod database-get-type-specifier ((type (eql 'integer)) args + database (db-type (eql :db2))) + (declare (ignore database)) + (if args + (format nil "NUMBER(~A,~A)" + (or (first args) 38) (or (second args) 0)) + "INTEGER")) + +(defmethod database-get-type-specifier ((type (eql 'bigint)) args + database (db-type (eql :db2))) + (declare (ignore args database)) + "CHAR(20)") + +(defmethod database-get-type-specifier ((type (eql 'universal-time)) args + database (db-type (eql :db2))) + (declare (ignore args database)) + "CHAR(20)") + +(defmethod database-get-type-specifier ((type (eql 'string)) args + database (db-type (eql :db2))) + (declare (ignore database)) + (if args + (format nil "CHAR(~A)" (car args)) + (format nil "VARCHAR2(~D)" *default-varchar-length*))) + +(defmethod database-get-type-specifier ((type (eql 'varchar)) args + database (db-type (eql :db2))) + (declare (ignore database)) + (if args + (format nil "VARCHAR2(~A)" (car args)) + (format nil "VARCHAR2(~D)" *default-varchar-length*))) + +(defmethod database-get-type-specifier ((type (eql 'float)) args + database (db-type (eql :db2))) + (declare (ignore database)) + (if args + (format nil "NUMBER(~A,~A)" (or (first args) 38) (or (second args) 38)) + "DOUBLE PRECISION")) + +(defmethod database-get-type-specifier ((type (eql 'long-float)) args + database (db-type (eql :db2))) + (declare (ignore database)) + (if args + (format nil "NUMBER(~A,~A)" + (or (first args) 38) (or (second args) 38)) + "DOUBLE PRECISION")) + +(defmethod database-get-type-specifier ((type (eql 'boolean)) args + database (db-type (eql :db2))) + (declare (ignore args database)) + "CHAR(1)") + +(defmethod read-sql-value (val type + database (db-type (eql :db2))) + ;;(format t "value is \"~A\" of type ~A~%" val (type-of val)) + (declare (ignore type database)) + (etypecase val + (string + (read-from-string val)) + (symbol + nil))) + +(defmethod read-sql-value (val (type (eql 'integer)) + database (db-type (eql :db2))) + (declare (ignore database)) + val) + +(defmethod read-sql-value (val (type (eql 'float)) + database (db-type (eql :db2))) + (declare (ignore database)) + val) + +(defmethod read-sql-value (val (type (eql 'boolean)) + database (db-type (eql :db2))) + (declare (ignore database)) + (when (char-equal #\t (schar val 0)) + t)) + +(defmethod read-sql-value (val (type (eql 'bigint)) + database (db-type (eql :db2))) + (declare (ignore database)) + (parse-integer val)) + +(defmethod read-sql-value (val (type (eql 'universal-time)) + database (db-type (eql :db2))) + (declare (ignore database)) + (parse-integer val)) + + +(defmethod database-get-type-specifier ((type (eql 'wall-time)) args + database (db-type (eql :db2))) + (declare (ignore args database)) + "DATE") + +(defmethod database-get-type-specifier ((type (eql 'duration)) args + database (db-type (eql :db2))) + (declare (ignore args database)) + "NUMBER(38)") diff --git a/db-db2/db2-package.lisp b/db-db2/db2-package.lisp new file mode 100644 index 0000000..9ca0984 --- /dev/null +++ b/db-db2/db2-package.lisp @@ -0,0 +1,25 @@ +;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- +;;;; ************************************************************************* +;;;; FILE IDENTIFICATION +;;;; +;;;; Name: db2-package.cl +;;;; Purpose: Package definition for CLSQL Db2 interface +;;;; +;;;; $Id$ +;;;; +;;;; This file is part of CLSQL. +;;;; +;;;; CLSQL users are granted the rights to distribute and use this software +;;;; as governed by the terms of the Lisp Lesser GNU Public License +;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. +;;;; ************************************************************************* + +(in-package #:cl-user) + +(defpackage #:clsql-db2 + (:use #:common-lisp #:clsql-sys #:clsql-uffi) + (:export #:db2-database + #:*db2-server-version* + #:*db2-so-load-path* + #:*db2-so-libraries*) + (:documentation "This is the CLSQL interface to Db2.")) diff --git a/db-db2/db2-sql.lisp b/db-db2/db2-sql.lisp new file mode 100644 index 0000000..62333e3 --- /dev/null +++ b/db-db2/db2-sql.lisp @@ -0,0 +1,1001 @@ +;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- +;;;; ************************************************************************* +;;;; FILE IDENTIFICATION +;;;; +;;;; Name: db2-sql.lisp +;;;; +;;;; $Id$ +;;;; +;;;; This file is part of CLSQL. +;;;; +;;;; CLSQL users are granted the rights to distribute and use this software +;;;; as governed by the terms of the Lisp Lesser GNU Public License +;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. +;;;; ************************************************************************* + +(in-package #:clsql-db2) + +(defmethod database-initialize-database-type ((database-type (eql :db2))) + t) + +;;;; arbitrary parameters, tunable for performance or other reasons + +(eval-when (:compile-toplevel :load-toplevel :execute) + (defconstant +errbuf-len+ 512 + "the number of characters that we allocate for an error message buffer") + (defconstant +n-buf-rows+ 200 + "the number of table rows that we buffer at once when reading a table. +CMUCL has a compiled-in limit on how much C data can be allocated +(through malloc() and friends) at any given time, typically 8 Mb. +Setting this constant to a moderate value should make it less +likely that we'll have to worry about the CMUCL limit.")) + + +(uffi:def-type vp-type :pointer-void) +(uffi:def-type vpp-type (* :pointer-void)) + +(defmacro deref-vp (foreign-object) + `(the vp-type (uffi:deref-pointer (the vpp-type ,foreign-object) :pointer-void))) + +(defvar +unsigned-char-null-pointer+ + (uffi:make-null-pointer :unsigned-char)) +(defvar +unsigned-short-null-pointer+ + (uffi:make-null-pointer :unsigned-short)) +(defvar +unsigned-int-null-pointer+ + (uffi:make-null-pointer :unsigned-int)) + +;; constants - from OCI? + +(defconstant +var-not-in-list+ 1007) +(defconstant +no-data-found+ 1403) +(defconstant +null-value-returned+ 1405) +(defconstant +field-truncated+ 1406) + +(eval-when (:compile-toplevel :load-toplevel :execute) + (defconstant SQLT-NUMBER 2) + (defconstant SQLT-INT 3) + (defconstant SQLT-FLT 4) + (defconstant SQLT-STR 5) + (defconstant SQLT-DATE 12)) + +;;; Note that despite the suggestive class name (and the way that the +;;; *DEFAULT-DATABASE* variable holds an object of this class), a DB +;;; object is not actually a database but is instead a connection to a +;;; database. Thus, there's no obstacle to having any number of DB +;;; objects referring to the same database. + +(uffi:def-type pointer-pointer-void '(* :pointer-void)) + +(defclass db2-database (database) ; was struct db + ((envhp + :reader envhp + :initarg :envhp + :type pointer-pointer-void + :documentation + "OCI environment handle") + (errhp + :reader errhp + :initarg :errhp + :type pointer-pointer-void + :documentation + "OCI error handle") + (svchp + :reader svchp + :initarg :svchp + :type pointer-pointer-void + :documentation + "OCI service context handle") + (data-source-name + :initarg :dsn + :initform nil + :documentation + "optional data source name (used only for debugging/printing)") + (user + :initarg :user + :reader user + :type string + :documentation + "the \"user\" value given when data source connection was made") + (date-format + :initarg :date-format + :reader date-format + :initform "YYYY-MM-DD HH24:MI:SS\"+00\"") + (date-format-length + :type number + :documentation + "Each database connection can be configured with its own date +output format. In order to extract date strings from output buffers +holding multiple date strings in fixed-width fields, we need to know +the length of that format.") + (server-version + :type (or null string) + :initarg :server-version + :reader server-version + :documentation + "Version string of Db2 server.") + (major-server-version + :type (or null fixnum) + :initarg :major-server-version + :reader major-server-version + :documentation + "The major version number of the Db2 server, should be 8, 9, or 10"))) + + +;;; Handle the messy case of return code=+oci-error+, querying the +;;; system for subcodes and reporting them as appropriate. ERRHP and +;;; NULLS-OK are as in the OERR function. + +(defun handle-oci-error (&key database nulls-ok) + (cond (database + (with-slots (errhp) database + (uffi:with-foreign-objects ((errbuf '(:array :unsigned-char + #.+errbuf-len+)) + (errcode :long)) + ;; ensure errbuf empty string + (setf (uffi:deref-array errbuf '(:array :unsigned-char) 0) + (uffi:ensure-char-storable (code-char 0))) + (setf (uffi:deref-pointer errcode :long) 0) + + (uffi:with-cstring (sqlstate nil) + (oci-error-get (deref-vp errhp) 1 + sqlstate + errcode + (uffi:char-array-to-pointer errbuf) + +errbuf-len+ +oci-htype-error+)) + (let ((subcode (uffi:deref-pointer errcode :long))) + (unless (and nulls-ok (= subcode +null-value-returned+)) + (error 'sql-database-error + :database database + :error-id subcode + :message (uffi:convert-from-foreign-string errbuf))))))) + (nulls-ok + (error 'sql-database-error + :database database + :message "can't handle NULLS-OK without ERRHP")) + (t + (error 'sql-database-error + :database database + :message "OCI Error (and no ERRHP available to find subcode)")))) + +;;; Require an OCI success code. +;;; +;;; (The ordinary OCI error reporting mechanisms uses a fair amount of +;;; machinery (environments and other handles). In order to get to +;;; where we can use these mechanisms, we have to be able to allocate +;;; the machinery. The functions for allocating the machinery can +;;; return errors (e.g. out of memory) but shouldn't. Wrapping this function +;;; around function calls to such have-to-succeed functions enforces +;;; this condition.) + +(defun osucc (code) + (declare (type fixnum code)) + (unless (= code +oci-success+) + (error 'sql-database-error + :message (format nil "unexpected OCI failure, code=~S" code)))) + + +;;; Enabling this can be handy for low-level debugging. +#+nil +(progn + (trace #-oci7 oci-env-create oci-initialize oci-handle-alloc oci-logon + oci-error-get oci-stmt-prepare oci-stmt-execute + oci-param-get oci-logon oci-attr-get oci-define-by-pos oci-stmt-fetch) + (setf debug::*debug-print-length* nil)) + + +;; Return the INDEXth string of the OCI array, represented as Lisp +;; SIMPLE-STRING. SIZE is the size of the fixed-width fields used by +;; Db2 to store strings within the array. + +(uffi:def-type string-pointer (* :unsigned-char)) + +(defun deref-oci-string (arrayptr string-index size) + (declare (type string-pointer arrayptr)) + (declare (type (mod #.+n-buf-rows+) string-index)) + (declare (type (and unsigned-byte fixnum) size)) + (let ((str (uffi:convert-from-foreign-string + (uffi:make-pointer + (+ (uffi:pointer-address arrayptr) (* string-index size)) + :unsigned-char)))) + (if (string-equal str "NULL") nil str))) + +;; the OCI library, part Z: no-longer used logic to convert from +;; Db2's binary date representation to Common Lisp's native date +;; representation + +#+nil +(defvar +oci-date-bytes+ 7) + +;;; Return the INDEXth date in the OCI array, represented as +;;; a Common Lisp "universal time" (i.e. seconds since 1900). + +#+nil +(defun deref-oci-date (arrayptr index) + (oci-date->universal-time (uffi:pointer-address + (uffi:deref-array arrayptr + '(:array :unsigned-char) + (* index +oci-date-bytes+))))) +#+nil +(defun oci-date->universal-time (oci-date) + (declare (type (alien (* :unsigned-char)) oci-date)) + (flet (;; a character from OCI-DATE, interpreted as an unsigned byte + (ub (i) + (declare (type (mod #.+oci-date-bytes+) i)) + (mod (uffi:deref-array oci-date string-array i) 256))) + (let* ((century (* (- (ub 0) 100) 100)) + (year (+ century (- (ub 1) 100))) + (month (ub 2)) + (day (ub 3)) + (hour (1- (ub 4))) + (minute (1- (ub 5))) + (second (1- (ub 6)))) + (encode-universal-time second minute hour day month year)))) + + +(defmethod database-list-tables ((database db2-database) &key owner) + (let ((query + (if owner + (format nil + "select user_tables.table_name from user_tables,all_tables where user_tables.table_name=all_tables.table_name and all_tables.owner='~:@(~A~)'" + owner) + "select table_name from user_tables"))) + (mapcar #'car (database-query query database nil nil)))) + + +(defmethod database-list-views ((database db2-database) &key owner) + (let ((query + (if owner + (format nil + "select user_views.view_name from user_views,all_views where user_views.view_name=all_views.view_name and all_views.owner='~:@(~A~)'" + owner) + "select view_name from user_views"))) + (mapcar #'car + (database-query query database nil nil)))) + +(defmethod database-list-indexes ((database db2-database) + &key (owner nil)) + (let ((query + (if owner + (format nil + "select user_indexes.index_name from user_indexes,all_indexes where user_indexes.index_name=all_indexes.index_name and all_indexes.owner='~:@(~A~)'" + owner) + "select index_name from user_indexes"))) + (mapcar #'car (database-query query database nil nil)))) + +(defmethod database-list-table-indexes (table (database db2-database) + &key (owner nil)) + (let ((query + (if owner + (format nil + "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~)'" + table owner) + (format nil "select index_name from user_indexes where table_name='~A'" + table)))) + (mapcar #'car (database-query query database nil nil)))) + + +(defmethod database-list-attributes (table (database db2-database) &key owner) + (let ((query + (if owner + (format nil + "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~)'" + table owner) + (format nil + "select column_name from user_tab_columns where table_name='~A'" + table)))) + (mapcar #'car (database-query query database nil nil)))) + +(defmethod database-attribute-type (attribute (table string) + (database db2-database) + &key (owner nil)) + (let ((query + (if owner + (format nil + "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~)'" + table attribute owner) + (format nil + "select data_type,data_length,data_scale,nullable from user_tab_columns where table_name='~A' and column_name='~A'" + table attribute)))) + (destructuring-bind (type length scale nullable) (car (database-query query database :auto nil)) + (values (ensure-keyword type) length scale + (if (char-equal #\Y (schar nullable 0)) 1 0))))) + +;; Return one row of the table referred to by QC, represented as a +;; list; or if there are no more rows, signal an error if EOF-ERRORP, +;; or return EOF-VALUE otherwise. + +;; KLUDGE: This CASE statement is a strong sign that the code would be +;; cleaner if CD were made into an abstract class, we made variant +;; classes for CD-for-column-of-strings, CD-for-column-of-floats, +;; etc., and defined virtual functions to handle operations like +;; get-an-element-from-column. (For a small special purpose module +;; like this, would arguably be overkill, so I'm not going to do it +;; now, but if this code ends up getting more complicated in +;; maintenance, it would become a really good idea.) + +;; Arguably this would be a good place to signal END-OF-FILE, but +;; since the ANSI spec specifically says that END-OF-FILE means a +;; STREAM which has no more data, and QC is not a STREAM, we signal +;; DBI-ERROR instead. + +(uffi:def-type short-array '(:array :short)) +(uffi:def-type int-pointer '(* :int)) +(uffi:def-type double-pointer '(* :double)) + +;;; the result of a database query: a cursor through a table +(defstruct (db2-result-set (:print-function print-query-cursor) + (:conc-name qc-) + (:constructor %make-query-cursor)) + (db (error "missing DB") ; db conn. this table is associated with + :type db2-database + :read-only t) + (stmthp (error "missing STMTHP") ; the statement handle used to create +;; :type alien ; this table. owned by the QUERY-CURSOR + :read-only t) ; object, deallocated on CLOSE-QUERY + (cds) ; (error "missing CDS") ; column descriptors +; :type (simple-array cd 1) + ; :read-only t) + (n-from-oci + 0 ; buffered rows: number of rows recv'd + :type (integer 0 #.+n-buf-rows+)) ; from the database on the last read + (n-to-dbi + 0 ; number of buffered rows returned, i.e. + :type (integer 0 #.+n-buf-rows+)) ; the index, within the buffered rows, + ; of the next row which hasn't already + ; been returned + (total-n-from-oci + 0 ; total number of bytes recv'd from OCI + :type unsigned-byte) ; in all reads + (oci-end-seen-p nil)) ; Have we seen the end of OCI + ; data, i.e. OCI returning + ; less data than we requested? + ; OCI doesn't seem to like us + ; to try to read more data + ; from it after that.. + + +(defun fetch-row (qc &optional (eof-errorp t) eof-value) + ;;(declare (optimize (speed 3))) + (cond ((zerop (qc-n-from-oci qc)) + (if eof-errorp + (error 'sql-database-error :message + (format nil "no more rows available in ~S" qc)) + eof-value)) + ((>= (qc-n-to-dbi qc) + (qc-n-from-oci qc)) + (refill-qc-buffers qc) + (fetch-row qc nil eof-value)) + (t + (let ((cds (qc-cds qc)) + (reversed-result nil) + (irow (qc-n-to-dbi qc))) + (dotimes (icd (length cds)) + (let* ((cd (aref cds icd)) + (b (foreign-resource-buffer (cd-buffer cd))) + (value + (let* ((arb (foreign-resource-buffer (cd-indicators cd))) + (indicator (uffi:deref-array arb '(:array :short) irow))) + ;;(declare (type short-array arb)) + (unless (= indicator -1) + (ecase (cd-oci-data-type cd) + (#.SQLT-STR + (deref-oci-string b irow (cd-sizeof cd))) + (#.SQLT-FLT + (uffi:deref-array b '(:array :double) irow)) + (#.SQLT-INT + (ecase (cd-sizeof cd) + (4 + (uffi:deref-array b '(:array :int) irow)))) + (#.SQLT-DATE + (deref-oci-string b irow (cd-sizeof cd)))))))) + (when (and (eq :string (cd-result-type cd)) + value + (not (stringp value))) + (setq value (write-to-string value))) + (push value reversed-result))) + (incf (qc-n-to-dbi qc)) + (nreverse reversed-result))))) + +(defun refill-qc-buffers (qc) + (with-slots (errhp) (qc-db qc) + (setf (qc-n-to-dbi qc) 0) + (cond ((qc-oci-end-seen-p qc) + (setf (qc-n-from-oci qc) 0)) + (t + (let ((oci-code (%oci-stmt-fetch + (deref-vp (qc-stmthp qc)) + (deref-vp errhp) + +n-buf-rows+ + +oci-fetch-next+ +oci-default+))) + (ecase oci-code + (#.+oci-success+ (values)) + (#.+oci-no-data+ (setf (qc-oci-end-seen-p qc) t) + (values)) + (#.+oci-error+ (handle-oci-error :database (qc-db qc) + :nulls-ok t)))) + (uffi:with-foreign-object (rowcount :long) + (oci-attr-get (deref-vp (qc-stmthp qc)) + +oci-htype-stmt+ + rowcount + +unsigned-int-null-pointer+ + +oci-attr-row-count+ + (deref-vp errhp)) + (setf (qc-n-from-oci qc) + (- (uffi:deref-pointer rowcount :long) + (qc-total-n-from-oci qc))) + (when (< (qc-n-from-oci qc) +n-buf-rows+) + (setf (qc-oci-end-seen-p qc) t)) + (setf (qc-total-n-from-oci qc) + (uffi:deref-pointer rowcount :long))))) + (values))) + +;; the guts of the SQL function +;; +;; (like the SQL function, but with the QUERY argument hardwired to T, so +;; that the return value is always a cursor instead of a list) + +;; Is this a SELECT statement? SELECT statements are handled +;; specially by OCIStmtExecute(). (Non-SELECT statements absolutely +;; require a nonzero iteration count, while the ordinary choice for a +;; SELECT statement is a zero iteration count. + +;; SELECT statements are the only statements which return tables. We +;; don't free STMTHP in this case, but instead give it to the new +;; QUERY-CURSOR, and the new QUERY-CURSOR becomes responsible for +;; freeing the STMTHP when it is no longer needed. + +(defun sql-stmt-exec (sql-stmt-string db result-types field-names) + (with-slots (envhp svchp errhp) + db + (let ((stmthp (uffi:allocate-foreign-object :pointer-void))) + (uffi:with-foreign-object (stmttype :unsigned-short) + + (oci-handle-alloc (deref-vp envhp) + stmthp + +oci-htype-stmt+ 0 +null-void-pointer-pointer+) + (oci-stmt-prepare (deref-vp stmthp) + (deref-vp errhp) + (uffi:convert-to-cstring sql-stmt-string) + (length sql-stmt-string) + +oci-ntv-syntax+ +oci-default+ :database db) + (oci-attr-get (deref-vp stmthp) + +oci-htype-stmt+ + stmttype + +unsigned-int-null-pointer+ + +oci-attr-stmt-type+ + (deref-vp errhp) + :database db) + (let* ((select-p (= (uffi:deref-pointer stmttype :unsigned-short) 1)) + (iters (if select-p 0 1))) + + (oci-stmt-execute (deref-vp svchp) + (deref-vp stmthp) + (deref-vp errhp) + iters 0 +null-void-pointer+ +null-void-pointer+ +oci-default+ + :database db) + (cond (select-p + (make-query-cursor db stmthp result-types field-names)) + (t + (oci-handle-free (deref-vp stmthp) +oci-htype-stmt+) + nil))))))) + + +;; Return a QUERY-CURSOR representing the table returned from the OCI +;; operation done through STMTHP. TYPES is the argument of the same +;; name from the external SQL function, controlling type conversion +;; of the returned arguments. + +(defun make-query-cursor (db stmthp result-types field-names) + (let ((qc (%make-query-cursor :db db + :stmthp stmthp + :cds (make-query-cursor-cds db stmthp + result-types + field-names)))) + (refill-qc-buffers qc) + qc)) + + +;; the hairy part of MAKE-QUERY-CURSOR: Ask OCI for information +;; about table columns, translate the information into a Lisp +;; vector of column descriptors, and return it. + +;; Allegro defines several flavors of type conversion, but this +;; implementation only supports the :AUTO flavor. + +;; A note of explanation: OCI's internal number format uses 21 +;; bytes (42 decimal digits). 2 separate (?) one-byte fields, +;; scale and precision, are used to deduce the nature of these +;; 21 bytes. See pp. 3-10, 3-26, and 6-13 of OCI documentation +;; for more details. + +;; When calling OCI C code to handle the conversion, we have +;; only two numeric types available to pass the return value: +;; double-float and signed-long. It would be possible to +;; bypass the OCI conversion functions and write Lisp code +;; which reads the 21-byte field directly and decodes +;; it. However this is left as an exercise for the reader. :-) + +;; The following table describes the mapping, based on the implicit +;; assumption that C's "signed long" type is a 32-bit integer. +;; +;; Internal Values SQL Type C Return Type +;; =============== ======== ============= +;; Precision > 0 SCALE = -127 FLOAT --> double-float +;; Precision > 0 && <=9 SCALE = 0 INTEGER --> signed-long +;; Precision = 0 || > 9 SCALE = 0 BIG INTEGER --> double-float +;; Precision > 0 SCALE > 0 DECIMAL --> double-float + +;; (OCI uses 1-based indexing here.) + +;; KLUDGE: This should work for all other data types except those +;; which don't actually fit in their fixed-width field (BLOBs and the +;; like). As Winton says, we (Cadabra) don't need to worry much about +;; those, since we can't reason with them, so we don't use them. But +;; for a more general application it'd be good to have a more +;; selective and rigorously correct test here for whether we can +;; actually handle the given DEREF-DTYPE value. -- WHN 20000106 + +;; Note: The OCI documentation doesn't seem to say whether the COLNAME +;; value returned here is a newly-allocated copy which we're +;; responsible for freeing, or a pointer into some system copy which +;; will be freed when the system itself is shut down. But judging +;; from the way that the result is used in the cdemodsa.c example +;; program, it looks like the latter: we should make our own copy of +;; the value, but not try to free it. + +;; WORKAROUND: OCI seems to return ub2 values for the +;; +oci-attr-data-size+ attribute even though its documentation claims +;; that it returns a ub4, and even though the associated "sizep" value +;; is 4, not 2. In order to make the code here work reliably, without +;; having to patch it later if OCI is ever fixed to match its +;; documentation, we pre-zero COLSIZE before making the call into OCI. + +;; To exercise the weird OCI behavior (thereby blowing up the code +;; below, beware!) try setting this value into COLSIZE, calling OCI, +;; then looking at the value in COLSIZE. (setf colsize #x12345678) +;; debugging only + + +(uffi:def-type byte-pointer (* :byte)) +(uffi:def-type ulong-pointer (* :unsigned-long)) +(uffi:def-type void-pointer-pointer (* :void-pointer)) + +(defun make-query-cursor-cds (database stmthp result-types field-names) + (declare (optimize (safety 3) #+nil (speed 3)) + (type db2-database database) + (type pointer-pointer-void stmthp)) + (with-slots (errhp) database + (uffi:with-foreign-objects ((dtype-foreign :unsigned-short) + (parmdp :pointer-void) + (precision :byte) + (scale :byte) + (colname '(* :unsigned-char)) + (colnamelen :unsigned-long) + (colsize :unsigned-long) + (colsizesize :unsigned-long) + (defnp ':pointer-void)) + (let ((buffer nil) + (sizeof nil)) + (do ((icolumn 0 (1+ icolumn)) + (cds-as-reversed-list nil)) + ((not (eql (oci-param-get (deref-vp stmthp) + +oci-htype-stmt+ + (deref-vp errhp) + parmdp + (1+ icolumn) :database database) + +oci-success+)) + (coerce (reverse cds-as-reversed-list) 'simple-vector)) + ;; Decode type of ICOLUMNth column into a type we're prepared to + ;; handle in Lisp. + (oci-attr-get (deref-vp parmdp) + +oci-dtype-param+ + dtype-foreign + +unsigned-int-null-pointer+ + +oci-attr-data-type+ + (deref-vp errhp)) + (let ((dtype (uffi:deref-pointer dtype-foreign :unsigned-short))) + (declare (fixnum dtype)) + (case dtype + (#.SQLT-DATE + (setf buffer (acquire-foreign-resource :unsigned-char + (* 32 +n-buf-rows+))) + (setf sizeof 32 dtype #.SQLT-STR)) + (#.SQLT-NUMBER + (oci-attr-get (deref-vp parmdp) + +oci-dtype-param+ + precision + +unsigned-int-null-pointer+ + +oci-attr-precision+ + (deref-vp errhp)) + (oci-attr-get (deref-vp parmdp) + +oci-dtype-param+ + scale + +unsigned-int-null-pointer+ + +oci-attr-scale+ + (deref-vp errhp)) + (let ((*scale (uffi:deref-pointer scale :byte)) + (*precision (uffi:deref-pointer precision :byte))) + + ;;(format t "scale=~d, precision=~d~%" *scale *precision) + (cond + ((or (and (minusp *scale) (zerop *precision)) + (and (zerop *scale) (plusp *precision))) + (setf buffer (acquire-foreign-resource :int +n-buf-rows+) + sizeof 4 ;; sizeof(int) + dtype #.SQLT-INT)) + (t + (setf buffer (acquire-foreign-resource :double +n-buf-rows+) + sizeof 8 ;; sizeof(double) + dtype #.SQLT-FLT))))) + ;; Default to SQL-STR + (t + (setf (uffi:deref-pointer colsize :unsigned-long) 0) + (setf dtype #.SQLT-STR) + (oci-attr-get (deref-vp parmdp) + +oci-dtype-param+ + colsize + +unsigned-int-null-pointer+ + +oci-attr-data-size+ + (deref-vp errhp)) + (let ((colsize-including-null (1+ (uffi:deref-pointer colsize :unsigned-long)))) + (setf buffer (acquire-foreign-resource + :unsigned-char (* +n-buf-rows+ colsize-including-null))) + (setf sizeof colsize-including-null)))) + (let ((retcodes (acquire-foreign-resource :unsigned-short +n-buf-rows+)) + (indicators (acquire-foreign-resource :short +n-buf-rows+)) + (colname-string "")) + (when field-names + (oci-attr-get (deref-vp parmdp) + +oci-dtype-param+ + colname + colnamelen + +oci-attr-name+ + (deref-vp errhp)) + (setq colname-string (uffi:convert-from-foreign-string + (uffi:deref-pointer colname '(* :unsigned-char)) + :length (uffi:deref-pointer colnamelen :unsigned-long)))) + (push (make-cd :name colname-string + :sizeof sizeof + :buffer buffer + :oci-data-type dtype + :retcodes retcodes + :indicators indicators + :result-type (cond + ((consp result-types) + (nth icolumn result-types)) + ((null result-types) + :string) + (t + result-types))) + cds-as-reversed-list) + (oci-define-by-pos (deref-vp stmthp) + defnp + (deref-vp errhp) + (1+ icolumn) ; OCI 1-based indexing again + (foreign-resource-buffer buffer) + sizeof + dtype + (foreign-resource-buffer indicators) + +unsigned-short-null-pointer+ + (foreign-resource-buffer retcodes) + +oci-default+)))))))) + +;; Release the resources associated with a QUERY-CURSOR. + +(defun close-query (qc) + (oci-handle-free (deref-vp (qc-stmthp qc)) +oci-htype-stmt+) + (let ((cds (qc-cds qc))) + (dotimes (i (length cds)) + (release-cd-resources (aref cds i)))) + (values)) + + +;; Release the resources associated with a column description. + +(defun release-cd-resources (cd) + (free-foreign-resource (cd-buffer cd)) + (free-foreign-resource (cd-retcodes cd)) + (free-foreign-resource (cd-indicators cd)) + (values)) + + +(defmethod database-name-from-spec (connection-spec (database-type (eql :db2))) + (check-connection-spec connection-spec database-type (dsn user password)) + (destructuring-bind (dsn user password) connection-spec + (declare (ignore password)) + (concatenate 'string dsn "/" user))) + + +(defmethod database-connect (connection-spec (database-type (eql :db2))) + (check-connection-spec connection-spec database-type (dsn user password)) + (destructuring-bind (data-source-name user password) + connection-spec + (let ((envhp (uffi:allocate-foreign-object :pointer-void)) + (errhp (uffi:allocate-foreign-object :pointer-void)) + (svchp (uffi:allocate-foreign-object :pointer-void)) + (srvhp (uffi:allocate-foreign-object :pointer-void))) + ;; Requests to allocate environments and handles should never + ;; fail in normal operation, and they're done too early to + ;; handle errors very gracefully (since they're part of the + ;; error-handling mechanism themselves) so we just assert they + ;; work. + (setf (deref-vp envhp) +null-void-pointer+) + #-oci7 + (oci-env-create envhp +oci-default+ +null-void-pointer+ + +null-void-pointer+ +null-void-pointer+ + +null-void-pointer+ 0 +null-void-pointer-pointer+) + #+oci7 + (progn + (oci-initialize +oci-object+ +null-void-pointer+ +null-void-pointer+ + +null-void-pointer+ +null-void-pointer-pointer+) + (ignore-errors (oci-handle-alloc +null-void-pointer+ envhp + +oci-htype-env+ 0 + +null-void-pointer-pointer+)) ;no testing return + (oci-env-init envhp +oci-default+ 0 +null-void-pointer-pointer+)) + (oci-handle-alloc (deref-vp envhp) errhp + +oci-htype-error+ 0 +null-void-pointer-pointer+) + (oci-handle-alloc (deref-vp envhp) srvhp + +oci-htype-server+ 0 +null-void-pointer-pointer+) + + #+ignore ;; not used since CLSQL uses the OCILogon function instead + (uffi:with-cstring (dblink nil) + (oci-server-attach (deref-vp srvhp) + (deref-vp errhp) + dblink + 0 +oci-default+)) + + (oci-handle-alloc (deref-vp envhp) svchp + +oci-htype-svcctx+ 0 +null-void-pointer-pointer+) + (oci-attr-set (deref-vp svchp) + +oci-htype-svcctx+ + (deref-vp srvhp) 0 +oci-attr-server+ + (deref-vp errhp)) + ;; oci-handle-alloc((dvoid *)encvhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0); + ;;#+nil + + (let ((db (make-instance 'db2-database + :name (database-name-from-spec connection-spec + database-type) + :connection-spec connection-spec + :envhp envhp + :errhp errhp + :database-type :db2 + :svchp svchp + :dsn data-source-name + :user user))) + (oci-logon (deref-vp envhp) + (deref-vp errhp) + svchp + (uffi:convert-to-cstring user) (length user) + (uffi:convert-to-cstring password) (length password) + (uffi:convert-to-cstring data-source-name) (length data-source-name) + :database db) + ;; :date-format-length (1+ (length date-format))))) + (setf (slot-value db 'clsql-sys::state) :open) + (database-execute-command + (format nil "ALTER SESSION SET NLS_DATE_FORMAT='~A'" (date-format db)) db) + (let ((server-version + (caar (database-query + "SELECT BANNER FROM V$VERSION WHERE BANNER LIKE '%Db2%'" db nil nil)))) + (setf (slot-value db 'server-version) server-version + (slot-value db 'major-server-version) (major-client-version-from-string + server-version))) + db)))) + + +(defun major-client-version-from-string (str) + (cond + ((search " 10g " str) + 10) + ((search "Db29i " str) + 9) + ((search "Db28" str) + 8))) + +(defun major-server-version-from-string (str) + (when (> (length str) 2) + (cond + ((string= "10." (subseq str 0 3)) + 10) + ((string= "9." (subseq str 0 2)) + 9) + ((string= "8." (subseq str 0 2)) + 8)))) + + +;; Close a database connection. + +(defmethod database-disconnect ((database db2-database)) + (osucc (oci-logoff (deref-vp (svchp database)) + (deref-vp (errhp database)))) + (osucc (oci-handle-free (deref-vp (envhp database)) +oci-htype-env+)) + ;; Note: It's neither required nor allowed to explicitly deallocate the + ;; ERRHP handle here, since it's owned by the ENVHP deallocated above, + ;; and was therefore automatically deallocated at the same time. + t) + +;;; Do the database operation described in SQL-STMT-STRING on database +;;; DB and, if the command is a SELECT, return a representation of the +;;; resulting table. The representation of the table is controlled by the +;;; QUERY argument: +;;; * If QUERY is NIL, the table is returned as a list of rows, with +;;; each row represented by a list. +;;; * If QUERY is non-NIL, the result is returned as a QUERY-CURSOR +;;; suitable for FETCH-ROW and CLOSE-QUERY +;;; The TYPES argument controls the type conversion method used +;;; to construct the table. The Allegro version supports several possible +;;; values for this argument, but we only support :AUTO. + +(defmethod database-query (query-expression (database db2-database) result-types field-names) + (let ((cursor (sql-stmt-exec query-expression database result-types field-names))) + ;; (declare (type (or query-cursor null) cursor)) + (if (null cursor) ; No table was returned. + (values) + (do ((reversed-result nil)) + (nil) + (let* ((eof-value :eof) + (row (fetch-row cursor nil eof-value))) + (when (eq row eof-value) + (close-query cursor) + (if field-names + (return (values (nreverse reversed-result) + (loop for cd across (qc-cds cursor) + collect (cd-name cd)))) + (return (nreverse reversed-result)))) + (push row reversed-result)))))) + + +(defmethod database-create-sequence (sequence-name (database db2-database)) + (execute-command + (concatenate 'string "CREATE SEQUENCE " (sql-escape sequence-name)) + :database database)) + +(defmethod database-drop-sequence (sequence-name (database db2-database)) + (execute-command + (concatenate 'string "DROP SEQUENCE " (sql-escape sequence-name)) + :database database)) + +(defmethod database-sequence-next (sequence-name (database db2-database)) + (caar + (database-query + (concatenate 'string "SELECT " + (sql-escape sequence-name) + ".NEXTVAL FROM dual" + ) + database :auto nil))) + +(defmethod database-set-sequence-position (name position (database db2-database)) + (without-interrupts + (let* ((next (database-sequence-next name database)) + (incr (- position next))) + (database-execute-command + (format nil "ALTER SEQUENCE ~A INCREMENT BY ~D" name incr) + database) + (database-sequence-next name database) + (database-execute-command + (format nil "ALTER SEQUENCE ~A INCREMENT BY 1" name) + database)))) + +(defmethod database-list-sequences ((database db2-database) &key owner) + (let ((query + (if owner + (format nil + "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~)'" + owner) + "select sequence_name from user_sequences"))) + (mapcar #'car (database-query query database nil nil)))) + +(defmethod database-execute-command (sql-expression (database db2-database)) + (database-query sql-expression database nil nil) + (when (database-autocommit database) + (db2-commit database)) + t) + + +(defstruct (cd (:constructor make-cd) + (:print-function print-cd)) + "a column descriptor: metadata about the data in a table" + + ;; name of this column + (name (error "missing NAME") :type simple-string :read-only t) + ;; the size in bytes of a single element + (sizeof (error "missing SIZE") :type fixnum :read-only t) + ;; an array of +N-BUF-ROWS+ elements in C representation + (buffer (error "Missing BUFFER") + :type foreign-resource + :read-only t) + ;; an array of +N-BUF-ROWS+ OCI return codes in C representation. + ;; (There must be one return code for every element of every + ;; row in order to be able to represent nullness.) + (retcodes (error "Missing RETCODES") + :type foreign-resource + :read-only t) + (indicators (error "Missing INDICATORS") + :type foreign-resource + :read-only t) + ;; the OCI code for the data type of a single element + (oci-data-type (error "missing OCI-DATA-TYPE") + :type fixnum + :read-only t) + (result-type (error "missing RESULT-TYPE") + :read-only t)) + + +(defun print-cd (cd stream depth) + (declare (ignore depth)) + (print-unreadable-object (cd stream :type t) + (format stream + ":NAME ~S :OCI-DATA-TYPE ~S :OCI-DATA-SIZE ~S" + (cd-name cd) + (cd-oci-data-type cd) + (cd-sizeof cd)))) + +(defun print-query-cursor (qc stream depth) + (declare (ignore depth)) + (print-unreadable-object (qc stream :type t :identity t) + (prin1 (qc-db qc) stream))) + + +(defmethod database-query-result-set ((query-expression string) + (database db2-database) + &key full-set result-types) + (let ((cursor (sql-stmt-exec query-expression database result-types nil))) + (if full-set + (values cursor (length (qc-cds cursor)) nil) + (values cursor (length (qc-cds cursor)))))) + + +(defmethod database-dump-result-set (result-set (database db2-database)) + (close-query result-set)) + +(defmethod database-store-next-row (result-set (database db2-database) list) + (let* ((eof-value :eof) + (row (fetch-row result-set nil eof-value))) + (unless (eq eof-value row) + (loop for i from 0 below (length row) + do (setf (nth i list) (nth i row))) + list))) + +(defmethod database-start-transaction ((database db2-database)) + (call-next-method) + ;; Not needed with simple transaction + #+ignore + (with-slots (svchp errhp) database + (oci-trans-start (deref-vp svchp) + (deref-vp errhp) + 60 + +oci-trans-new+)) + t) + + +(defun db2-commit (database) + (with-slots (svchp errhp) database + (osucc (oci-trans-commit (deref-vp svchp) + (deref-vp errhp) + 0)))) + +(defmethod database-commit-transaction ((database db2-database)) + (call-next-method) + (db2-commit database) + t) + +(defmethod database-abort-transaction ((database db2-database)) + (call-next-method) + (osucc (oci-trans-rollback (deref-vp (svchp database)) + (deref-vp (errhp database)) + 0)) + t) + +;; Specifications + +(defmethod db-type-has-bigint? ((type (eql :db2))) + nil) + +(defmethod db-type-has-fancy-math? ((db-type (eql :db2))) + t) + +(defmethod db-type-has-boolean-where? ((db-type (eql :db2))) + nil) + +(when (clsql-sys:database-type-library-loaded :db2) + (clsql-sys:initialize-database-type :database-type :db2)) diff --git a/db-db2/foreign-resources.lisp b/db-db2/foreign-resources.lisp index badfedc..1a8d866 100644 --- a/db-db2/foreign-resources.lisp +++ b/db-db2/foreign-resources.lisp @@ -11,7 +11,7 @@ ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. ;;;; ************************************************************************* -(in-package #:clsql-oracle) +(in-package #:clsql-db2) (defparameter *foreign-resource-hash* (make-hash-table :test #'equal)) diff --git a/db-db2/oracle-api.lisp b/db-db2/oracle-api.lisp deleted file mode 100644 index eab4c6b..0000000 --- a/db-db2/oracle-api.lisp +++ /dev/null @@ -1,366 +0,0 @@ -;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- -;;;; ************************************************************************* -;;;; FILE IDENTIFICATION -;;;; -;;;; Name: oracle.lisp -;;;; Purpose: Package definition for CLSQL Oracle interface -;;;; -;;;; $Id$ -;;;; -;;;; This file is part of CLSQL. -;;;; -;;;; CLSQL users are granted the rights to distribute and use this software -;;;; as governed by the terms of the Lisp Lesser GNU Public License -;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. -;;;; ************************************************************************* - -(in-package #:clsql-oracle) - - -;; -;; Opaque pointer types -;; - -(uffi:def-foreign-type void-pointer :pointer-void) -(uffi:def-foreign-type oci-env :pointer-void) -(uffi:def-foreign-type oci-server :pointer-void) -(uffi:def-foreign-type oci-error :pointer-void) -(uffi:def-foreign-type oci-svc-ctx :pointer-void) -(uffi:def-foreign-type oci-stmt :pointer-void) - - -(defvar +null-void-pointer+ (uffi:make-null-pointer :void)) -(defvar +null-void-pointer-pointer+ (uffi:make-null-pointer :pointer-void)) - -;;; Check an OCI return code for erroricity and signal a reasonably -;;; informative condition if so. -;;; -;;; ERRHP provides an error handle which can be used to find -;;; subconditions; if it's not provided, subcodes won't be checked. -;;; -;;; NULLS-OK says that a NULL-VALUE-RETURNED subcondition condition is -;;; normal and needn't cause any signal. An error handle is required -;;; to detect this subcondition, so it doesn't make sense to set ERRHP -;;; unless NULLS-OK is set. - -(defmacro def-oci-routine ((c-oci-symbol lisp-oci-fn) c-return &rest c-parms) - (let ((ll (mapcar (lambda (x) (declare (ignore x)) (gensym)) c-parms))) - `(let ((%lisp-oci-fn (uffi:def-function - (,c-oci-symbol ,(intern (concatenate 'string "%" (symbol-name lisp-oci-fn)))) - ,c-parms - :returning ,c-return))) - (defun ,lisp-oci-fn (,@ll &key database nulls-ok) - (let ((result (funcall %lisp-oci-fn ,@ll))) - (case result - (#.+oci-success+ - +oci-success+) - (#.+oci-error+ - (handle-oci-error :database database :nulls-ok nulls-ok)) - (#.+oci-no-data+ - (error 'sql-database-error :message "OCI No Data Found")) - (#.+oci-success-with-info+ - (error 'sql-database-error :message "internal error: unexpected +oci-success-with-info")) - (#.+oci-no-data+ - (error 'sql-database-error :message "OCI No Data")) - (#.+oci-invalid-handle+ - (error 'sql-database-error :message "OCI Invalid Handle")) - (#.+oci-need-data+ - (error 'sql-database-error :message "OCI Need Data")) - (#.+oci-still-executing+ - (error 'sql-temporary-error :message "OCI Still Executing")) - (#.+oci-continue+ - (error 'sql-database-error :message "OCI Continue")) - (1804 - (error 'sql-database-error :message "Check ORACLE_HOME and NLS settings.")) - (t - (error 'sql-database-error - :message - (format nil "OCI unknown error, code=~A" result))))))))) - - -(defmacro def-raw-oci-routine - ((c-oci-symbol lisp-oci-fn) c-return &rest c-parms) - (let ((ll (mapcar (lambda (x) (declare (ignore x)) (gensym)) c-parms))) - `(let ((%lisp-oci-fn (uffi:def-function (,c-oci-symbol ,(intern (concatenate 'string "%" (symbol-name lisp-oci-fn)))) - ,c-parms - :returning ,c-return))) - (defun ,lisp-oci-fn (,@ll &key database nulls-ok) - (funcall %lisp-oci-fn ,@ll))))) - - -(def-oci-routine ("OCIInitialize" oci-initialize) - :int - (mode :unsigned-long) ; ub4 - (ctxp :pointer-void) ; dvoid * - (malocfp :pointer-void) ; dvoid *(*) - (ralocfp :pointer-void) ; dvoid *(*) - (mfreefp (* :pointer-void))) ; void *(*) - - -(def-oci-routine ("OCIEnvInit" oci-env-init) - :int - (envpp :pointer-void) ; OCIEnv ** - (mode :unsigned-long) ; ub4 - (xtramem-sz :unsigned-long) ; size_t - (usermempp (* :pointer-void))) ; dvoid ** - -#-oci7 -(def-oci-routine ("OCIEnvCreate" oci-env-create) - :int - (envhpp (* :pointer-void)) - (mode :unsigned-int) - (ctxp :pointer-void) - (malocfp :pointer-void) - (ralocfp :pointer-void) - (mfreefp :pointer-void) - (xtramemsz :unsigned-long) - (usrmempp (* :pointer-void))) - -(def-oci-routine ("OCIHandleAlloc" oci-handle-alloc) - :int - (parenth :pointer-void) ; const dvoid * - (hndlpp (* :pointer-void)) ; dvoid ** - (type :unsigned-long) ; ub4 - (xtramem_sz :unsigned-long) ; size_t - (usrmempp (* :pointer-void))) ; dvoid ** - -(def-oci-routine ("OCIServerAttach" oci-server-attach) - :int - (srvhp :pointer-void) ; oci-server - (errhp :pointer-void) ; oci-error - (dblink :cstring) ; :in - (dblink-len :unsigned-long) ; int - (mode :unsigned-long)) ; int - - -(def-oci-routine ("OCIHandleFree" oci-handle-free) - :int - (p0 :pointer-void) ;; handle - (p1 :unsigned-long)) ;;type - -(def-oci-routine ("OCILogon" oci-logon) - :int - (envhp :pointer-void) ; env - (errhp :pointer-void) ; err - (svchpp (* :pointer-void)) ; svc - (username :cstring) ; username - (uname-len :unsigned-long) ; - (passwd :cstring) ; passwd - (password-len :unsigned-long) ; - (dsn :cstring) ; datasource - (dsn-len :unsigned-long)) ; - -(def-oci-routine ("OCILogoff" oci-logoff) - :int - (p0 :pointer-void) ; svc - (p1 :pointer-void)) ; err - -(uffi:def-function ("OCIErrorGet" oci-error-get) - ((handlp :pointer-void) - (recordno :unsigned-long) - (sqlstate :cstring) - (errcodep (* :long)) - (bufp (* :unsigned-char)) - (bufsize :unsigned-long) - (type :unsigned-long)) - :returning :void) - -(def-oci-routine ("OCIStmtPrepare" oci-stmt-prepare) - :int - (stmtp :pointer-void) - (errhp :pointer-void) - (stmt :cstring) - (stmt_len :unsigned-long) - (language :unsigned-long) - (mode :unsigned-long)) - -(def-oci-routine ("OCIStmtExecute" oci-stmt-execute) - :int - (svchp :pointer-void) - (stmtp1 :pointer-void) - (errhp :pointer-void) - (iters :unsigned-long) - (rowoff :unsigned-long) - (snap_in :pointer-void) - (snap_out :pointer-void) - (mode :unsigned-long)) - -(def-raw-oci-routine ("OCIParamGet" oci-param-get) - :int - (hndlp :pointer-void) - (htype :unsigned-long) - (errhp :pointer-void) - (parmdpp (* :pointer-void)) - (pos :unsigned-long)) - -(def-oci-routine ("OCIAttrGet" oci-attr-get) - :int - (trgthndlp :pointer-void) - (trghndltyp :unsigned-int) - (attributep :pointer-void) - (sizep (* :unsigned-int)) - (attrtype :unsigned-int) - (errhp :pointer-void)) - -(def-oci-routine ("OCIAttrSet" oci-attr-set) - :int - (trgthndlp :pointer-void) - (trgthndltyp :int :in) - (attributep :pointer-void) - (size :int) - (attrtype :int) - (errhp oci-error)) - -(def-oci-routine ("OCIDefineByPos" oci-define-by-pos) - :int - (stmtp :pointer-void) - (defnpp (* :pointer-void)) - (errhp :pointer-void) - (position :unsigned-long) - (valuep :pointer-void) - (value_sz :long) - (dty :unsigned-short) - (indp (* :short)) - (rlenp (* :unsigned-short)) - (rcodep (* :unsigned-short)) - (mode :unsigned-long)) - -(def-oci-routine ("OCIStmtFetch" oci-stmt-fetch) - :int - (stmthp :pointer-void) - (errhp :pointer-void) - (p2 :unsigned-long) - (p3 :unsigned-short) - (p4 :unsigned-long)) - - -(def-oci-routine ("OCITransStart" oci-trans-start) - :int - (svchp :pointer-void) - (errhp :pointer-void) - (p2 :unsigned-short) - (p3 :unsigned-short)) - -(def-oci-routine ("OCITransCommit" oci-trans-commit) - :int - (svchp :pointer-void) - (errhp :pointer-void) - (p2 :unsigned-short)) - -(def-oci-routine ("OCITransRollback" oci-trans-rollback) - :int - (svchp :pointer-void) - (errhp :pointer-void) - (p2 :unsigned-short)) - - -(def-oci-routine ("OCIServerVersion" oci-server-version) - :int - (handlp :pointer-void) - (errhp :pointer-void) - (bufp (* :unsigned-char)) - (bufsz :int) - (hndltype :short)) - - - -;;; Low-level routines that don't do error checking. They are used -;;; for setting up global environment. - -(uffi:def-function "OCIInitialize" - ((mode :unsigned-long) ; ub4 - (ctxp :pointer-void) ; dvoid * - (malocfp :pointer-void) ; dvoid *(*) - (ralocfp :pointer-void) ; dvoid *(*) - (mfreefp (* :pointer-void))) - :returning :int) - -(uffi:def-function "OCIEnvInit" - ((envpp :pointer-void) ; OCIEnv ** - (mode :unsigned-long) ; ub4 - (xtramem-sz :unsigned-long) ; size_t - (usermempp (* :pointer-void))) - :returning :int) - - -(uffi:def-function "OCIHandleAlloc" - ((parenth :pointer-void) ; const dvoid * - (hndlpp (* :pointer-void)) ; dvoid ** - (type :unsigned-long) ; ub4 - (xtramem_sz :unsigned-long) ; size_t - (usrmempp (* :pointer-void))) - :returning :int) - -(defstruct oci-handle - (type :unknown) - (pointer (uffi:allocate-foreign-object :pointer-void))) - -(defvar *oci-initialized* nil) -(defvar *oci-env* nil) - -(defvar *oci-handle-types* - '(:error ; error report handle (OCIError) - :service-context ; service context handle (OCISvcCtx) - :statement ; statement (application request) handle (OCIStmt) - :describe ; select list description handle (OCIDescribe) - :server ; server context handle (OCIServer) - :session ; user session handle (OCISession) - :transaction ; transaction context handle (OCITrans) - :complex-object ; complex object retrieval handle (OCIComplexObject) - :security)) ; security handle (OCISecurity) - - - -(defun oci-init (&key (mode +oci-default+)) - (let ((x (OCIInitialize mode +null-void-pointer+ +null-void-pointer+ - +null-void-pointer+ +null-void-pointer-pointer+))) - (if (= x 0) - (let ((env (uffi:allocate-foreign-object :pointer-void))) - (setq *oci-initialized* mode) - (let ((x (OCIEnvInit env +oci-default+ 0 +null-void-pointer+))) - (format t ";; OEI: returned ~d~%" x) - (setq *oci-env* env)))))) - -(defun oci-check-return (value) - (when (= value +oci-invalid-handle+) - (error 'sql-database-error :message "Invalid Handle"))) - -(defun oci-get-handle (&key type) - (if (null *oci-initialized*) - (oci-init)) - (case type - (:error - (let ((ptr (uffi:allocate-foreign-object :pointer-void))) - (let ((x (OCIHandleAlloc - (uffi:deref-pointer *oci-env* void-pointer) - ptr - +oci-default+ - 0 - +null-void-pointer-pointer+))) - (oci-check-return x) - ptr))) - (:service-context - "OCISvcCtx") - (:statement - "OCIStmt") - (:describe - "OCIDescribe") - (:server - "OCIServer") - (:session - "OCISession") - (:transaction - "OCITrans") - (:complex-object - "OCIComplexObject") - (:security - "OCISecurity") - (t - (error 'sql-database-error - :message - (format nil "'~s' is not a valid OCI handle type" type))))) - -(defun oci-environment () - (let ((envhp (oci-get-handle :type :env))) - (oci-env-init envhp 0 0 +null-void-pointer+) - envhp)) diff --git a/db-db2/oracle-constants.lisp b/db-db2/oracle-constants.lisp deleted file mode 100644 index 692b55b..0000000 --- a/db-db2/oracle-constants.lisp +++ /dev/null @@ -1,543 +0,0 @@ -;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- -;;;; ************************************************************************* -;;;; FILE IDENTIFICATION -;;;; -;;;; Name: oracle-constants.lisp -;;;; Purpose: Constants for CLSQL Oracle interface -;;;; -;;;; $Id$ -;;;; -;;;; This file is part of CLSQL. -;;;; -;;;; CLSQL users are granted the rights to distribute and use this software -;;;; as governed by the terms of the Lisp Lesser GNU Public License -;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. -;;;; ************************************************************************* - -(in-package #:clsql-oracle) - -(defconstant +oci-default+ #x00) ; default value for parameters and attributes -(defconstant +oci-threaded+ #x01) ; application is in threaded environment -(defconstant +oci-object+ #x02) ; the application is in object environment -(defconstant +oci-non-blocking+ #x04) ; non blocking mode of operation -(defconstant +oci-env-no-mutex+ #x08) ; the environment handle will not be protected by a mutex internally - -;; Handle types - -(defconstant +oci-htype-env+ 1) ; environment handle -(defconstant +oci-htype-error+ 2) ; error handle -(defconstant +oci-htype-svcctx+ 3) ; service handle -(defconstant +oci-htype-stmt+ 4) ; statement handle -(defconstant +oci-htype-bind+ 5) ; bind handle -(defconstant +oci-htype-define+ 6) ; define handle -(defconstant +oci-htype-describe+ 7) ; describe handle -(defconstant +oci-htype-server+ 8) ; server handle -(defconstant +oci-htype-session+ 9) ; authentication handle -(defconstant +oci-htype-trans+ 10) ; transaction handle -(defconstant +oci-htype-complexobject+ 11) ; complex object retrieval handle -(defconstant +oci-htype-security+ 12) ; security handle - -;; Descriptor types - -(defconstant +oci-dtype-lob+ 50) ; lob locator -(defconstant +oci-dtype-snap+ 51) ; snapshot -(defconstant +oci-dtype-rset+ 52) ; result set -(defconstant +oci-dtype-param+ 53) ; parameter descriptor obtained from ocigparm -(defconstant +oci-dtype-rowid+ 54) ; rowid -(defconstant +oci-dtype-complexobjectcomp+ 55) ; complex object retrieval descriptor -(defconstant +oci-dtype-file+ 56) ; File Lob locator -(defconstant +oci-dtype-aqenq-options+ 57) ; enqueue options -(defconstant +oci-dtype-aqdeq-options+ 58) ; dequeue options -(defconstant +oci-dtype-aqmsg-properties+ 59) ; message properties -(defconstant +oci-dtype-aqagent+ 60) ; aq agent - -;; Objectr pointer types - -(defconstant +oci-otype-name+ 1) ; object name -(defconstant +oci-otype-ref+ 2) ; REF to TDO -(defconstant +oci-otype-ptr+ 3) ; PTR to TDO - -;; Attribute types - -(defconstant +oci-attr-fncode+ 1) ; the OCI function code -(defconstant +oci-attr-object+ 2) ; is the environment initialized in object mode -(defconstant +oci-attr-nonblocking-mode+ 3) ; non blocking mode -(defconstant +oci-attr-sqlcode+ 4) ; the SQL verb -(defconstant +oci-attr-env+ 5) ; the environment handle -(defconstant +oci-attr-server+ 6) ; the server handle -(defconstant +oci-attr-session+ 7) ; the user session handle -(defconstant +oci-attr-trans+ 8) ; the transaction handle -(defconstant +oci-attr-row-count+ 9) ; the rows processed so far -(defconstant +oci-attr-sqlfncode+ 10) ; the SQL verb of the statement -(defconstant +oci-attr-prefetch-rows+ 11) ; sets the number of rows to prefetch -(defconstant +oci-attr-nested-prefetch-rows+ 12) ; the prefetch rows of nested table -(defconstant +oci-attr-prefetch-memory+ 13) ; memory limit for rows fetched -(defconstant +oci-attr-nested-prefetch-memory+ 14) ; memory limit for nested rows -(defconstant +oci-attr-char-count+ 15) ; this specifies the bind and define size in characters -(defconstant +oci-attr-pdscl+ 16) ; packed decimal scale -(defconstant +oci-attr-pdfmt+ 17) ; packed decimal format -(defconstant +oci-attr-param-count+ 18) ; number of column in the select list -(defconstant +oci-attr-rowid+ 19) ; the rowid -(defconstant +oci-attr-charset+ 20) ; the character set value -(defconstant +oci-attr-nchar+ 21) ; NCHAR type -(defconstant +oci-attr-username+ 22) ; username attribute -(defconstant +oci-attr-password+ 23) ; password attribute -(defconstant +oci-attr-stmt-type+ 24) ; statement type -(defconstant +oci-attr-internal-name+ 25) ; user friendly global name -(defconstant +oci-attr-external-name+ 26) ; the internal name for global txn -(defconstant +oci-attr-xid+ 27) ; XOPEN defined global transaction id -(defconstant +oci-attr-trans-lock+ 28) ; -(defconstant +oci-attr-trans-name+ 29) ; string to identify a global transaction -(defconstant +oci-attr-heapalloc+ 30) ; memory allocated on the heap -(defconstant +oci-attr-charset-id+ 31) ; Character Set ID -(defconstant +oci-attr-charset-form+ 32) ; Character Set Form -(defconstant +oci-attr-maxdata-size+ 33) ; Maximumsize of data on the server -(defconstant +oci-attr-cache-opt-size+ 34) ; object cache optimal size -(defconstant +oci-attr-cache-max-size+ 35) ; object cache maximum size percentage -(defconstant +oci-attr-pinoption+ 36) ; object cache default pin option -(defconstant +oci-attr-alloc-duration+ 37) ; object cache default allocation duration -(defconstant +oci-attr-pin-duration+ 38) ; object cache default pin duration -(defconstant +oci-attr-fdo+ 39) ; Format Descriptor object attribute -(defconstant +oci-attr-postprocessing-callback+ 40) ; Callback to process outbind data -(defconstant +oci-attr-postprocessing-context+ 41) ; Callback context to process outbind data -(defconstant +oci-attr-rows-returned+ 42) ; Number of rows returned in current iter - for Bind handles -(defconstant +oci-attr-focbk+ 43) ; Failover Callback attribute -(defconstant +oci-attr-in-v8-mode+ 44) ; is the server/service context in V8 mode -(defconstant +oci-attr-lobempty+ 45) ; empty lob ? -(defconstant +oci-attr-sesslang+ 46) ; session language handle - -;; AQ Attribute Types -;; Enqueue Options - -(defconstant +oci-attr-visibility+ 47) ; visibility -(defconstant +oci-attr-relative-msgid+ 48) ; relative message id -(defconstant +oci-attr-sequence-deviation+ 49) ; sequence deviation - -; - Dequeue Options - - ; consumer name -;#define OCI-ATTR-DEQ-MODE 50 -;(defconstant +OCI-ATTR-CONSUMER-NAME 50 + 51) ; dequeue mode -;#define OCI-ATTR-NAVIGATION 52 ; navigation -;#define OCI-ATTR-WAIT 53 ; wait -;#define OCI-ATTR-DEQ-MSGID 54 ; dequeue message id - -; - Message Properties - -(defconstant +OCI-ATTR-PRIORITY+ 55) ; priority -(defconstant +OCI-ATTR-DELAY+ 56) ; delay -(defconstant +OCI-ATTR-EXPIRATION+ 57) ; expiration -(defconstant +OCI-ATTR-CORRELATION+ 58) ; correlation id -(defconstant +OCI-ATTR-ATTEMPTS+ 59) ; # of attempts -(defconstant +OCI-ATTR-RECIPIENT-LIST+ 60) ; recipient list -(defconstant +OCI-ATTR-EXCEPTION-QUEUE+ 61) ; exception queue name -(defconstant +OCI-ATTR-ENQ-TIME+ 62) ; enqueue time (only OCIAttrGet) -(defconstant +OCI-ATTR-MSG-STATE+ 63) ; message state (only OCIAttrGet) - -;; AQ Agent -(defconstant +OCI-ATTR-AGENT-NAME+ 64) ; agent name -(defconstant +OCI-ATTR-AGENT-ADDRESS+ 65) ; agent address -(defconstant +OCI-ATTR-AGENT-PROTOCOL+ 66) ; agent protocol - -;- Server handle - -(defconstant +OCI-ATTR-NATIVE-FDES+ 67) ; native cncxn file desc - -;-Parameter Attribute Types- - -(defconstant +OCI-ATTR-UNK+ 101) ; unknown attribute -(defconstant +OCI-ATTR-NUM-COLS+ 102) ; number of columns -(defconstant +OCI-ATTR-LIST-COLUMNS+ 103) ; parameter of the column list -(defconstant +OCI-ATTR-RDBA+ 104) ; DBA of the segment header -(defconstant +OCI-ATTR-CLUSTERED+ 105) ; whether the table is clustered -(defconstant +OCI-ATTR-PARTITIONED+ 106) ; whether the table is partitioned -(defconstant +OCI-ATTR-INDEX-ONLY+ 107) ; whether the table is index only -(defconstant +OCI-ATTR-LIST-ARGUMENTS+ 108) ; parameter of the argument list -(defconstant +OCI-ATTR-LIST-SUBPROGRAMS+ 109) ; parameter of the subprogram list -(defconstant +OCI-ATTR-REF-TDO+ 110) ; REF to the type descriptor -(defconstant +OCI-ATTR-LINK+ 111) ; the database link name -(defconstant +OCI-ATTR-MIN+ 112) ; minimum value -(defconstant +OCI-ATTR-MAX+ 113) ; maximum value -(defconstant +OCI-ATTR-INCR+ 114) ; increment value -(defconstant +OCI-ATTR-CACHE+ 115) ; number of sequence numbers cached -(defconstant +OCI-ATTR-ORDER+ 116) ; whether the sequence is ordered -(defconstant +OCI-ATTR-HW-MARK+ 117) ; high-water mark -(defconstant +OCI-ATTR-TYPE-SCHEMA+ 118) ; type's schema name -(defconstant +OCI-ATTR-TIMESTAMP+ 119) ; timestamp of the object -(defconstant +OCI-ATTR-NUM-ATTRS+ 120) ; number of sttributes -(defconstant +OCI-ATTR-NUM-PARAMS+ 121) ; number of parameters -(defconstant +OCI-ATTR-OBJID+ 122) ; object id for a table or view -(defconstant +OCI-ATTR-PTYPE+ 123) ; type of info described by -(defconstant +OCI-ATTR-PARAM+ 124) ; parameter descriptor -(defconstant +OCI-ATTR-OVERLOAD-ID+ 125) ; overload ID for funcs and procs -(defconstant +OCI-ATTR-TABLESPACE+ 126) ; table name space -(defconstant +OCI-ATTR-TDO+ 127) ; TDO of a type -(defconstant +OCI-ATTR-PARSE-ERROR-OFFSET+ 128) ; Parse Error offset -;-Credential Types- -(defconstant +OCI-CRED-RDBMS+ 1) ; database username/password -(defconstant +OCI-CRED-EXT+ 2) ; externally provided credentials - -;; Error Return Values- - -(defconstant +oci-continue+ -24200) ; Continue with the body of the OCI function -(defconstant +oci-still-executing+ -3123) ; OCI would block error -(defconstant +oci-invalid-handle+ -2) ; maps to SQL-INVALID-HANDLE -(defconstant +oci-error+ -1) ; maps to SQL-ERROR -(defconstant +oci-success+ 0) ; maps to SQL-SUCCESS of SAG CLI -(defconstant +oci-success-with-info+ 1) ; maps to SQL-SUCCESS-WITH-INFO -(defconstant +oci-need-data+ 99) ; maps to SQL-NEED-DATA -(defconstant +oci-no-data+ 100) ; maps to SQL-NO-DATA - -;; Parsing Syntax Types- - -(defconstant +oci-ntv-syntax+ 1) ; Use what so ever is the native lang of server -(defconstant +oci-v7-syntax+ 2) ; V7 language -(defconstant +oci-v8-syntax+ 3) ; V8 language - -;-Scrollable Cursor Options- - -(defconstant +oci-fetch-next+ #x02) ; next row -(defconstant +oci-fetch-first+ #x04) ; first row of the result set -(defconstant +oci-fetch-last+ #x08) ; the last row of the result set -(defconstant +oci-fetch-prior+ #x10) ; the previous row relative to current -(defconstant +oci-fetch-absolute+ #x20) ; absolute offset from first -(defconstant +oci-fetch-relative+ #x40) ; offset relative to current - -;-Bind and Define Options- - -(defconstant +OCI-SB2-IND-PTR+ #x01) ; unused -(defconstant +OCI-DATA-AT-EXEC+ #x02) ; data at execute time -(defconstant +OCI-DYNAMIC-FETCH+ #x02) ; fetch dynamically -(defconstant +OCI-PIECEWISE+ #x04) ; piecewise DMLs or fetch -;- - -;-Execution Modes- -(defconstant +OCI-BATCH-MODE+ #x01) ; batch the oci statement for execution -(defconstant +OCI-EXACT-FETCH+ #x02) ; fetch the exact rows specified -(defconstant +OCI-KEEP-FETCH-STATE+ #x04) ; unused -(defconstant +OCI-SCROLLABLE-CURSOR+ #x08) ; cursor scrollable -(defconstant +OCI-DESCRIBE-ONLY+ #x10) ; only describe the statement -(defconstant +OCI-COMMIT-ON-SUCCESS+ #x20) ; commit, if successful execution -;- - -;-Authentication Modes- -(defconstant +OCI-MIGRATE+ #x0001) ; migratable auth context -(defconstant +OCI-SYSDBA+ #x0002) ; for SYSDBA authorization -(defconstant +OCI-SYSOPER+ #x0004) ; for SYSOPER authorization -(defconstant +OCI-PRELIM-AUTH+ #x0008) ; for preliminary authorization -;- - -;-Piece Information- -(defconstant +OCI-PARAM-IN+ #x01) ; in parameter -(defconstant +OCI-PARAM-OUT+ #x02) ; out parameter -;- - -;- Transaction Start Flags - -; NOTE: OCI-TRANS-JOIN and OCI-TRANS-NOMIGRATE not supported in 8.0.X -(defconstant +OCI-TRANS-NEW+ #x00000001) ; starts a new transaction branch -(defconstant +OCI-TRANS-JOIN+ #x00000002) ; join an existing transaction -(defconstant +OCI-TRANS-RESUME+ #x00000004) ; resume this transaction -(defconstant +OCI-TRANS-STARTMASK+ #x000000ff) - - -(defconstant +OCI-TRANS-READONLY+ #x00000100) ; starts a readonly transaction -(defconstant +OCI-TRANS-READWRITE+ #x00000200) ; starts a read-write transaction -(defconstant +OCI-TRANS-SERIALIZABLE+ #x00000400) - ; starts a serializable transaction -(defconstant +OCI-TRANS-ISOLMASK+ #x0000ff00) - -(defconstant +OCI-TRANS-LOOSE+ #x00010000) ; a loosely coupled branch -(defconstant +OCI-TRANS-TIGHT+ #x00020000) ; a tightly coupled branch -(defconstant +OCI-TRANS-TYPEMASK+ #x000f0000) ; - -(defconstant +OCI-TRANS-NOMIGRATE+ #x00100000) ; non migratable transaction - -;- - -;- Transaction End Flags - -(defconstant +OCI-TRANS-TWOPHASE+ #x01000000) ; use two phase commit -;- - -;; AQ Constants -;; NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE -;; The following constants must match the PL/SQL dbms-aq constants -;; NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE - -; - Visibility flags - -(defconstant +OCI-ENQ-IMMEDIATE+ 1) ; enqueue is an independent transaction -(defconstant +OCI-ENQ-ON-COMMIT+ 2) ; enqueue is part of current transaction - -; - Dequeue mode flags - -(defconstant +OCI-DEQ-BROWSE+ 1) ; read message without acquiring a lock -(defconstant +OCI-DEQ-LOCKED+ 2) ; read and obtain write lock on message -(defconstant +OCI-DEQ-REMOVE+ 3) ; read the message and delete it - -; - Dequeue navigation flags - -(defconstant +OCI-DEQ-FIRST-MSG+ 1) ; get first message at head of queue -(defconstant +OCI-DEQ-NEXT-MSG+ 3) ; next message that is available -(defconstant +OCI-DEQ-NEXT-TRANSACTION+ 2) ; get first message of next txn group - -; - Message states - -(defconstant +OCI-MSG-WAITING+ 1) ; the message delay has not yet completed -(defconstant +OCI-MSG-READY+ 0) ; the message is ready to be processed -(defconstant +OCI-MSG-PROCESSED+ 2) ; the message has been processed -(defconstant +OCI-MSG-EXPIRED+ 3) ; message has moved to exception queue - -; - Sequence deviation - -(defconstant +OCI-ENQ-BEFORE+ 2) ; enqueue message before another message -(defconstant +OCI-ENQ-TOP+ 3) ; enqueue message before all messages - -; - Visibility flags - -(defconstant +OCI-DEQ-IMMEDIATE+ 1) ; dequeue is an independent transaction -(defconstant +OCI-DEQ-ON-COMMIT+ 2) ; dequeue is part of current transaction - -; - Wait - -(defconstant +OCI-DEQ-WAIT-FOREVER+ -1) ; wait forever if no message available -(defconstant +OCI-DEQ-NO-WAIT+ 0) ; do not wait if no message is available - -; - Delay - -(defconstant +OCI-MSG-NO-DELAY+ 0) ; message is available immediately - -;; Expiration -(defconstant +OCI-MSG-NO-EXPIRATION+ -1) ; message will never expire - -;; Describe Handle Parameter Attributes -;; Attributes common to Columns and Stored Procs - -(defconstant +oci-attr-data-size+ 1) ; maximum size of the data -(defconstant +oci-attr-data-type+ 2) ; the sql type of the column/argument -(defconstant +oci-attr-disp-size+ 3) ; the display size -(defconstant +oci-attr-name+ 4) ; the name of the column/argument -(defconstant +oci-attr-precision+ 5) ; precision if number type -(defconstant +oci-attr-scale+ 6) ; scale if number type -(defconstant +oci-attr-is-null+ 7) ; is it null ? -(defconstant +oci-attr-type-name+ 8) - -;; name of the named data type or a package name for package private types - -(defconstant +OCI-ATTR-SCHEMA-NAME+ 9) ; the schema name -(defconstant +OCI-ATTR-SUB-NAME+ 10) ; type name if package private type -(defconstant +OCI-ATTR-POSITION+ 11) ; relative position of col/arg in the list of cols/args - -; complex object retrieval parameter attributes -(defconstant +OCI-ATTR-COMPLEXOBJECTCOMP-TYPE+ 50) ; -(defconstant +OCI-ATTR-COMPLEXOBJECTCOMP-TYPE-LEVEL+ 51) ; -(defconstant +OCI-ATTR-COMPLEXOBJECT-LEVEL+ 52) ; -(defconstant +OCI-ATTR-COMPLEXOBJECT-COLL-OUTOFLINE+ 53) ; - -; Only Columns -(defconstant +OCI-ATTR-DISP-NAME+ 100) ; the display name - -;; stored procs - -(defconstant +OCI-ATTR-OVERLOAD+ 210) ; is this position overloaded -(defconstant +OCI-ATTR-LEVEL+ 211) ; level for structured types -(defconstant +OCI-ATTR-HAS-DEFAULT+ 212) ; has a default value -(defconstant +OCI-ATTR-IOMODE+ 213) ; in, out inout -(defconstant +OCI-ATTR-RADIX+ 214) ; returns a radix -(defconstant +OCI-ATTR-NUM-ARGS+ 215) ; total number of arguments - -;; named type attributes - -(defconstant +oci-attr-typecode+ 216) ; lobject or collection -(defconstant +oci-attr-collection-typecode+ 217) ; varray or nested table -(defconstant +oci-attr-version+ 218) ; user assigned version -(defconstant +oci-attr-is-incomplete-type+ 219) ; is this an incomplete type -(defconstant +oci-attr-is-system-type+ 220) ; a system type -(defconstant +oci-attr-is-predefined-type+ 221) ; a predefined type -(defconstant +oci-attr-is-transient-type+ 222) ; a transient type -(defconstant +oci-attr-is-system-generated-type+ 223) ; system generated type -(defconstant +oci-attr-has-nested-table+ 224) ; contains nested table attr -(defconstant +oci-attr-has-lob+ 225) ; has a lob attribute -(defconstant +oci-attr-has-file+ 226) ; has a file attribute -(defconstant +oci-attr-collection-element+ 227) ; has a collection attribute -(defconstant +oci-attr-num-type-attrs+ 228) ; number of attribute types -(defconstant +oci-attr-list-type-attrs+ 229) ; list of type attributes -(defconstant +oci-attr-num-type-methods+ 230) ; number of type methods -(defconstant +oci-attr-list-type-methods+ 231) ; list of type methods -(defconstant +oci-attr-map-method+ 232) ; map method of type -(defconstant +oci-attr-order-method+ 233) ; order method of type - -; only collection element -(defconstant +OCI-ATTR-NUM-ELEMS+ 234) ; number of elements - -; only type methods -(defconstant +OCI-ATTR-ENCAPSULATION+ 235) ; encapsulation level -(defconstant +OCI-ATTR-IS-SELFISH+ 236) ; method selfish -(defconstant +OCI-ATTR-IS-VIRTUAL+ 237) ; virtual -(defconstant +OCI-ATTR-IS-INLINE+ 238) ; inline -(defconstant +OCI-ATTR-IS-CONSTANT+ 239) ; constant -(defconstant +OCI-ATTR-HAS-RESULT+ 240) ; has result -(defconstant +OCI-ATTR-IS-CONSTRUCTOR+ 241) ; constructor -(defconstant +OCI-ATTR-IS-DESTRUCTOR+ 242) ; destructor -(defconstant +OCI-ATTR-IS-OPERATOR+ 243) ; operator -(defconstant +OCI-ATTR-IS-MAP+ 244) ; a map method -(defconstant +OCI-ATTR-IS-ORDER+ 245) ; order method -(defconstant +OCI-ATTR-IS-RNDS+ 246) ; read no data state method -(defconstant +OCI-ATTR-IS-RNPS+ 247) ; read no process state -(defconstant +OCI-ATTR-IS-WNDS+ 248) ; write no data state method -(defconstant +OCI-ATTR-IS-WNPS+ 249) ; write no process state - -; describing public objects -(defconstant +OCI-ATTR-DESC-PUBLIC+ 250) ; public object -;- - -;-OCIPasswordChange- -(defconstant +OCI-AUTH+ #x08) ; Change the password but do not login - - -;-Other Constants- -(defconstant +OCI-MAX-FNS+ 100) ; max number of OCI Functions -(defconstant +OCI-SQLSTATE-SIZE+ 5) ; -(defconstant +OCI-ERROR-MAXMSG-SIZE+ 1024) ; max size of an error message -;; (defconstant +OCI-LOBMAXSIZE+ 4MAXVAL) ; maximum lob data size -(defconstant +OCI-ROWID-LEN+ 23) ; -;- - -;- Fail Over Events - -(defconstant +OCI-FO-END+ #x00000001) ; -(defconstant +OCI-FO-ABORT+ #x00000002) ; -(defconstant +OCI-FO-REAUTH+ #x00000004) ; -(defconstant +OCI-FO-BEGIN+ #x00000008) ; -(defconstant +OCI-FO-ERROR+ #x00000010) ; -;- - -;- Fail Over Types - -(defconstant +OCI-FO-NONE+ #x00000001) ; -(defconstant +OCI-FO-SESSION+ #x00000002) ; -(defconstant +OCI-FO-SELECT+ #x00000004) ; -(defconstant +OCI-FO-TXNAL+ #x00000008) ; -;- - -;-Function Codes- -(defconstant +OCI-FNCODE-INITIALIZE+ 1) ; OCIInitialize -(defconstant +OCI-FNCODE-HANDLEALLOC+ 2) ; OCIHandleAlloc -(defconstant +OCI-FNCODE-HANDLEFREE+ 3) ; OCIHandleFree -(defconstant +OCI-FNCODE-DESCRIPTORALLOC+ 4) ; OCIDescriptorAlloc -(defconstant +OCI-FNCODE-DESCRIPTORFREE+ 5) ; OCIDescriptorFree -(defconstant +OCI-FNCODE-ENVINIT+ 6) ; OCIEnvInit -(defconstant +OCI-FNCODE-SERVERATTACH+ 7) ; OCIServerAttach -(defconstant +OCI-FNCODE-SERVERDETACH+ 8) ; OCIServerDetach -; unused 9 -(defconstant +OCI-FNCODE-SESSIONBEGIN+ 10) ; OCISessionBegin -(defconstant +OCI-FNCODE-SESSIONEND+ 11) ; OCISessionEnd -(defconstant +OCI-FNCODE-PASSWORDCHANGE+ 12) ; OCIPasswordChange -(defconstant +OCI-FNCODE-STMTPREPARE+ 13) ; OCIStmtPrepare - ; unused 14- 16 -(defconstant +OCI-FNCODE-BINDDYNAMIC+ 17) ; OCIBindDynamic -(defconstant +OCI-FNCODE-BINDOBJECT+ 18) ; OCIBindObject - ; 19 unused -(defconstant +OCI-FNCODE-BINDARRAYOFSTRUCT+ 20) ; OCIBindArrayOfStruct -(defconstant +OCI-FNCODE-STMTEXECUTE+ 21) ; OCIStmtExecute - ; unused 22-24 -(defconstant +OCI-FNCODE-DEFINEOBJECT+ 25) ; OCIDefineObject -(defconstant +OCI-FNCODE-DEFINEDYNAMIC+ 26) ; OCIDefineDynamic -(defconstant +OCI-FNCODE-DEFINEARRAYOFSTRUCT+ 27) ; OCIDefineArrayOfStruct -(defconstant +OCI-FNCODE-STMTFETCH+ 28) ; OCIStmtFetch -(defconstant +OCI-FNCODE-STMTGETBIND+ 29) ; OCIStmtGetBindInfo - ; 30, 31 unused -(defconstant +OCI-FNCODE-DESCRIBEANY+ 32) ; OCIDescribeAny -(defconstant +OCI-FNCODE-TRANSSTART+ 33) ; OCITransStart -(defconstant +OCI-FNCODE-TRANSDETACH+ 34) ; OCITransDetach -(defconstant +OCI-FNCODE-TRANSCOMMIT+ 35) ; OCITransCommit - ; 36 unused -(defconstant +OCI-FNCODE-ERRORGET+ 37) ; OCIErrorGet -(defconstant +OCI-FNCODE-LOBOPENFILE+ 38) ; OCILobFileOpen -(defconstant +OCI-FNCODE-LOBCLOSEFILE+ 39) ; OCILobFileClose - ; 40 was LOBCREATEFILE, unused - ; 41 was OCILobFileDelete, unused -(defconstant +OCI-FNCODE-LOBCOPY+ 42) ; OCILobCopy -(defconstant +OCI-FNCODE-LOBAPPEND+ 43) ; OCILobAppend -(defconstant +OCI-FNCODE-LOBERASE+ 44) ; OCILobErase -(defconstant +OCI-FNCODE-LOBLENGTH+ 45) ; OCILobGetLength -(defconstant +OCI-FNCODE-LOBTRIM+ 46) ; OCILobTrim -(defconstant +OCI-FNCODE-LOBREAD+ 47) ; OCILobRead -(defconstant +OCI-FNCODE-LOBWRITE+ 48) ; OCILobWrite - ; 49 unused -(defconstant +OCI-FNCODE-SVCCTXBREAK+ 50) ; OCIBreak -(defconstant +OCI-FNCODE-SERVERVERSION+ 51) ; OCIServerVersion -; unused 52, 53 -(defconstant +OCI-FNCODE-ATTRGET+ 54) ; OCIAttrGet -(defconstant +OCI-FNCODE-ATTRSET+ 55) ; OCIAttrSet -(defconstant +OCI-FNCODE-PARAMSET+ 56) ; OCIParamSet -(defconstant +OCI-FNCODE-PARAMGET+ 57) ; OCIParamGet -(defconstant +OCI-FNCODE-STMTGETPIECEINFO+ 58) ; OCIStmtGetPieceInfo -(defconstant +OCI-FNCODE-LDATOSVCCTX+ 59) ; OCILdaToSvcCtx - ; 60 unused -(defconstant +OCI-FNCODE-STMTSETPIECEINFO+ 61) ; OCIStmtSetPieceInfo -(defconstant +OCI-FNCODE-TRANSFORGET+ 62) ; OCITransForget -(defconstant +OCI-FNCODE-TRANSPREPARE+ 63) ; OCITransPrepare -(defconstant +OCI-FNCODE-TRANSROLLBACK+ 64) ; OCITransRollback -(defconstant +OCI-FNCODE-DEFINEBYPOS+ 65) ; OCIDefineByPos -(defconstant +OCI-FNCODE-BINDBYPOS+ 66) ; OCIBindByPos -(defconstant +OCI-FNCODE-BINDBYNAME+ 67) ; OCIBindByName -(defconstant +OCI-FNCODE-LOBASSIGN+ 68) ; OCILobAssign -(defconstant +OCI-FNCODE-LOBISEQUAL+ 69) ; OCILobIsEqual -(defconstant +OCI-FNCODE-LOBISINIT+ 70) ; OCILobLocatorIsInit -; 71 was lob locator size in beta2 -(defconstant +OCI-FNCODE-LOBENABLEBUFFERING+ 71) ; OCILobEnableBuffering -(defconstant +OCI-FNCODE-LOBCHARSETID+ 72) ; OCILobCharSetID -(defconstant +OCI-FNCODE-LOBCHARSETFORM+ 73) ; OCILobCharSetForm -(defconstant +OCI-FNCODE-LOBFILESETNAME+ 74) ; OCILobFileSetName -(defconstant +OCI-FNCODE-LOBFILEGETNAME+ 75) ; OCILobFileGetName -(defconstant +OCI-FNCODE-LOGON+ 76) ; OCILogon -(defconstant +OCI-FNCODE-LOGOFF+ 77) ; OCILogoff -(defconstant +OCI-FNCODE-LOBDISABLEBUFFERING+ 78) ; OCILobDisableBuffering -(defconstant +OCI-FNCODE-LOBFLUSHBUFFER+ 79) ; OCILobFlushBuffer -(defconstant +OCI-FNCODE-LOBLOADFROMFILE+ 80) ; OCILobLoadFromFile - - -;- - -;- FILE open modes - -(defconstant +OCI-FILE-READONLY+ 1) ; readonly mode open for FILE types -;- - -;- LOB Buffering Flush Flags - -(defconstant +OCI-LOB-BUFFER-FREE+ 1) ; -(defconstant +OCI-LOB-BUFFER-NOFREE+ 2) ; -;- - -;- OCI Statement Types - - -(defconstant +oci-stmt-select+ 1) ; select statement -(defconstant +oci-stmt-update+ 2) ; update statement -(defconstant +oci-stmt-delete+ 3) ; delete statement -(defconstant +oci-stmt-insert+ 4) ; insert statement -(defconstant +oci-stmt-create+ 5) ; create statement -(defconstant +oci-stmt-drop+ 6) ; drop statement -(defconstant +oci-stmt-alter+ 7) ; alter statement -(defconstant +oci-stmt-begin+ 8) ; begin ... (pl/sql statement) -(defconstant +oci-stmt-declare+ 9) ; declare .. (pl/sql statement ) -;- - -;- OCI Parameter Types - -(defconstant +OCI-PTYPE-UNK+ 0) ; unknown -(defconstant +OCI-PTYPE-TABLE+ 1) ; table -(defconstant +OCI-PTYPE-VIEW+ 2) ; view -(defconstant +OCI-PTYPE-PROC+ 3) ; procedure -(defconstant +OCI-PTYPE-FUNC+ 4) ; function -(defconstant +OCI-PTYPE-PKG+ 5) ; package -(defconstant +OCI-PTYPE-TYPE+ 6) ; user-defined type -(defconstant +OCI-PTYPE-SYN+ 7) ; synonym -(defconstant +OCI-PTYPE-SEQ+ 8) ; sequence -(defconstant +OCI-PTYPE-COL+ 9) ; column -(defconstant +OCI-PTYPE-ARG+ 10) ; argument -(defconstant +OCI-PTYPE-LIST+ 11) ; list -(defconstant +OCI-PTYPE-TYPE-ATTR+ 12) ; user-defined type's attribute -(defconstant +OCI-PTYPE-TYPE-COLL+ 13) ; collection type's element -(defconstant +OCI-PTYPE-TYPE-METHOD+ 14) ; user-defined type's method -(defconstant +OCI-PTYPE-TYPE-ARG+ 15) ; user-defined type method's argument -(defconstant +OCI-PTYPE-TYPE-RESULT+ 16) ; user-defined type method's result -;- - -;- OCI List Types - -(defconstant +OCI-LTYPE-UNK+ 0) ; unknown -(defconstant +OCI-LTYPE-COLUMN+ 1) ; column list -(defconstant +OCI-LTYPE-ARG-PROC+ 2) ; procedure argument list -(defconstant +OCI-LTYPE-ARG-FUNC+ 3) ; function argument list -(defconstant +OCI-LTYPE-SUBPRG+ 4) ; subprogram list -(defconstant +OCI-LTYPE-TYPE-ATTR+ 5) ; type attribute -(defconstant +OCI-LTYPE-TYPE-METHOD+ 6) ; type method -(defconstant +OCI-LTYPE-TYPE-ARG-PROC+ 7) ; type method w/o result argument list -(defconstant +OCI-LTYPE-TYPE-ARG-FUNC+ 8) ; type method w/result argument list - -;; typecodes - diff --git a/db-db2/oracle-loader.lisp b/db-db2/oracle-loader.lisp deleted file mode 100644 index 6d91e8d..0000000 --- a/db-db2/oracle-loader.lisp +++ /dev/null @@ -1,59 +0,0 @@ -;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- -;;;; ************************************************************************* -;;;; FILE IDENTIFICATION -;;;; -;;;; Name: oracle-loader.lisp -;;;; Purpose: Foreign library loader for CLSQL Oracle interface -;;;; -;;;; $Id$ -;;;; -;;;; This file is part of CLSQL. -;;;; -;;;; CLSQL users are granted the rights to distribute and use this software -;;;; as governed by the terms of the Lisp Lesser GNU Public License -;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. -;;;; ************************************************************************* - -(in-package #:clsql-oracle) - -(defparameter *oracle-lib-path* - (let ((oracle-home (getenv "ORACLE_HOME"))) - (when oracle-home - (make-pathname :directory - (append - (pathname-directory - (parse-namestring (concatenate 'string oracle-home "/"))) - (list "lib")))))) - -(defparameter *oracle-client-library-path* - (uffi:find-foreign-library - "libclntsh" - `(,@(when *load-truename* (list (make-pathname :directory (pathname-directory *load-truename*)))) - ,@(when *oracle-lib-path* (list *oracle-lib-path*)) - "/usr/lib/oracle/10.1.0.2/client/lib/") - :drive-letters '("C"))) - -(defvar *oracle-supporting-libraries* '("c") - "Used only by CMU. List of library flags needed to be passed to ld to -load the Oracle client library succesfully. If this differs at your site, -set to the right path before compiling or loading the system.") - -(defvar *oracle-library-loaded* nil - "T if foreign library was able to be loaded successfully") - -(defmethod clsql-sys:database-type-library-loaded ((database-type (eql :oracle))) - *oracle-library-loaded*) - -(defmethod clsql-sys:database-type-load-foreign ((database-type (eql :oracle))) - (if (pathnamep *oracle-client-library-path*) - (progn - (uffi:load-foreign-library *oracle-client-library-path* - :module "clsql-oracle" - :supporting-libraries - *oracle-supporting-libraries*) - (setq *oracle-library-loaded* t)) - (warn "Unable to load oracle client library."))) - -(clsql-sys:database-type-load-foreign :oracle) - - diff --git a/db-db2/oracle-objects.lisp b/db-db2/oracle-objects.lisp deleted file mode 100644 index a5583ee..0000000 --- a/db-db2/oracle-objects.lisp +++ /dev/null @@ -1,119 +0,0 @@ -;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- -;;;; ************************************************************************* -;;;; FILE IDENTIFICATION -;;;; -;;;; Name: oracle-objects.lisp -;;;; -;;;; $Id$ -;;;; -;;;; This file is part of CLSQL. -;;;; -;;;; CLSQL users are granted the rights to distribute and use this software -;;;; as governed by the terms of the Lisp Lesser GNU Public License -;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. -;;;; ************************************************************************* - -(in-package #:clsql-oracle) - -(defmethod database-get-type-specifier (type args database (db-type (eql :oracle))) - (declare (ignore type args database)) - (format nil "VARCHAR2(~D)" *default-varchar-length*)) - -(defmethod database-get-type-specifier ((type (eql 'integer)) args - database (db-type (eql :oracle))) - (declare (ignore database)) - (if args - (format nil "NUMBER(~A,~A)" - (or (first args) 38) (or (second args) 0)) - "INTEGER")) - -(defmethod database-get-type-specifier ((type (eql 'bigint)) args - database (db-type (eql :oracle))) - (declare (ignore args database)) - "CHAR(20)") - -(defmethod database-get-type-specifier ((type (eql 'universal-time)) args - database (db-type (eql :oracle))) - (declare (ignore args database)) - "CHAR(20)") - -(defmethod database-get-type-specifier ((type (eql 'string)) args - database (db-type (eql :oracle))) - (declare (ignore database)) - (if args - (format nil "CHAR(~A)" (car args)) - (format nil "VARCHAR2(~D)" *default-varchar-length*))) - -(defmethod database-get-type-specifier ((type (eql 'varchar)) args - database (db-type (eql :oracle))) - (declare (ignore database)) - (if args - (format nil "VARCHAR2(~A)" (car args)) - (format nil "VARCHAR2(~D)" *default-varchar-length*))) - -(defmethod database-get-type-specifier ((type (eql 'float)) args - database (db-type (eql :oracle))) - (declare (ignore database)) - (if args - (format nil "NUMBER(~A,~A)" (or (first args) 38) (or (second args) 38)) - "DOUBLE PRECISION")) - -(defmethod database-get-type-specifier ((type (eql 'long-float)) args - database (db-type (eql :oracle))) - (declare (ignore database)) - (if args - (format nil "NUMBER(~A,~A)" - (or (first args) 38) (or (second args) 38)) - "DOUBLE PRECISION")) - -(defmethod database-get-type-specifier ((type (eql 'boolean)) args - database (db-type (eql :oracle))) - (declare (ignore args database)) - "CHAR(1)") - -(defmethod read-sql-value (val type - database (db-type (eql :oracle))) - ;;(format t "value is \"~A\" of type ~A~%" val (type-of val)) - (declare (ignore type database)) - (etypecase val - (string - (read-from-string val)) - (symbol - nil))) - -(defmethod read-sql-value (val (type (eql 'integer)) - database (db-type (eql :oracle))) - (declare (ignore database)) - val) - -(defmethod read-sql-value (val (type (eql 'float)) - database (db-type (eql :oracle))) - (declare (ignore database)) - val) - -(defmethod read-sql-value (val (type (eql 'boolean)) - database (db-type (eql :oracle))) - (declare (ignore database)) - (when (char-equal #\t (schar val 0)) - t)) - -(defmethod read-sql-value (val (type (eql 'bigint)) - database (db-type (eql :oracle))) - (declare (ignore database)) - (parse-integer val)) - -(defmethod read-sql-value (val (type (eql 'universal-time)) - database (db-type (eql :oracle))) - (declare (ignore database)) - (parse-integer val)) - - -(defmethod database-get-type-specifier ((type (eql 'wall-time)) args - database (db-type (eql :oracle))) - (declare (ignore args database)) - "DATE") - -(defmethod database-get-type-specifier ((type (eql 'duration)) args - database (db-type (eql :oracle))) - (declare (ignore args database)) - "NUMBER(38)") diff --git a/db-db2/oracle-package.lisp b/db-db2/oracle-package.lisp deleted file mode 100644 index 07f0a55..0000000 --- a/db-db2/oracle-package.lisp +++ /dev/null @@ -1,25 +0,0 @@ -;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- -;;;; ************************************************************************* -;;;; FILE IDENTIFICATION -;;;; -;;;; Name: oracle-package.cl -;;;; Purpose: Package definition for CLSQL Oracle interface -;;;; -;;;; $Id$ -;;;; -;;;; This file is part of CLSQL. -;;;; -;;;; CLSQL users are granted the rights to distribute and use this software -;;;; as governed by the terms of the Lisp Lesser GNU Public License -;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. -;;;; ************************************************************************* - -(in-package #:cl-user) - -(defpackage #:clsql-oracle - (:use #:common-lisp #:clsql-sys #:clsql-uffi) - (:export #:oracle-database - #:*oracle-server-version* - #:*oracle-so-load-path* - #:*oracle-so-libraries*) - (:documentation "This is the CLSQL interface to Oracle.")) diff --git a/db-db2/oracle-sql.lisp b/db-db2/oracle-sql.lisp deleted file mode 100644 index 0250a34..0000000 --- a/db-db2/oracle-sql.lisp +++ /dev/null @@ -1,1001 +0,0 @@ -;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- -;;;; ************************************************************************* -;;;; FILE IDENTIFICATION -;;;; -;;;; Name: oracle-sql.lisp -;;;; -;;;; $Id$ -;;;; -;;;; This file is part of CLSQL. -;;;; -;;;; CLSQL users are granted the rights to distribute and use this software -;;;; as governed by the terms of the Lisp Lesser GNU Public License -;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. -;;;; ************************************************************************* - -(in-package #:clsql-oracle) - -(defmethod database-initialize-database-type ((database-type (eql :oracle))) - t) - -;;;; arbitrary parameters, tunable for performance or other reasons - -(eval-when (:compile-toplevel :load-toplevel :execute) - (defconstant +errbuf-len+ 512 - "the number of characters that we allocate for an error message buffer") - (defconstant +n-buf-rows+ 200 - "the number of table rows that we buffer at once when reading a table. -CMUCL has a compiled-in limit on how much C data can be allocated -(through malloc() and friends) at any given time, typically 8 Mb. -Setting this constant to a moderate value should make it less -likely that we'll have to worry about the CMUCL limit.")) - - -(uffi:def-type vp-type :pointer-void) -(uffi:def-type vpp-type (* :pointer-void)) - -(defmacro deref-vp (foreign-object) - `(the vp-type (uffi:deref-pointer (the vpp-type ,foreign-object) :pointer-void))) - -(defvar +unsigned-char-null-pointer+ - (uffi:make-null-pointer :unsigned-char)) -(defvar +unsigned-short-null-pointer+ - (uffi:make-null-pointer :unsigned-short)) -(defvar +unsigned-int-null-pointer+ - (uffi:make-null-pointer :unsigned-int)) - -;; constants - from OCI? - -(defconstant +var-not-in-list+ 1007) -(defconstant +no-data-found+ 1403) -(defconstant +null-value-returned+ 1405) -(defconstant +field-truncated+ 1406) - -(eval-when (:compile-toplevel :load-toplevel :execute) - (defconstant SQLT-NUMBER 2) - (defconstant SQLT-INT 3) - (defconstant SQLT-FLT 4) - (defconstant SQLT-STR 5) - (defconstant SQLT-DATE 12)) - -;;; Note that despite the suggestive class name (and the way that the -;;; *DEFAULT-DATABASE* variable holds an object of this class), a DB -;;; object is not actually a database but is instead a connection to a -;;; database. Thus, there's no obstacle to having any number of DB -;;; objects referring to the same database. - -(uffi:def-type pointer-pointer-void '(* :pointer-void)) - -(defclass oracle-database (database) ; was struct db - ((envhp - :reader envhp - :initarg :envhp - :type pointer-pointer-void - :documentation - "OCI environment handle") - (errhp - :reader errhp - :initarg :errhp - :type pointer-pointer-void - :documentation - "OCI error handle") - (svchp - :reader svchp - :initarg :svchp - :type pointer-pointer-void - :documentation - "OCI service context handle") - (data-source-name - :initarg :dsn - :initform nil - :documentation - "optional data source name (used only for debugging/printing)") - (user - :initarg :user - :reader user - :type string - :documentation - "the \"user\" value given when data source connection was made") - (date-format - :initarg :date-format - :reader date-format - :initform "YYYY-MM-DD HH24:MI:SS\"+00\"") - (date-format-length - :type number - :documentation - "Each database connection can be configured with its own date -output format. In order to extract date strings from output buffers -holding multiple date strings in fixed-width fields, we need to know -the length of that format.") - (server-version - :type (or null string) - :initarg :server-version - :reader server-version - :documentation - "Version string of Oracle server.") - (major-server-version - :type (or null fixnum) - :initarg :major-server-version - :reader major-server-version - :documentation - "The major version number of the Oracle server, should be 8, 9, or 10"))) - - -;;; Handle the messy case of return code=+oci-error+, querying the -;;; system for subcodes and reporting them as appropriate. ERRHP and -;;; NULLS-OK are as in the OERR function. - -(defun handle-oci-error (&key database nulls-ok) - (cond (database - (with-slots (errhp) database - (uffi:with-foreign-objects ((errbuf '(:array :unsigned-char - #.+errbuf-len+)) - (errcode :long)) - ;; ensure errbuf empty string - (setf (uffi:deref-array errbuf '(:array :unsigned-char) 0) - (uffi:ensure-char-storable (code-char 0))) - (setf (uffi:deref-pointer errcode :long) 0) - - (uffi:with-cstring (sqlstate nil) - (oci-error-get (deref-vp errhp) 1 - sqlstate - errcode - (uffi:char-array-to-pointer errbuf) - +errbuf-len+ +oci-htype-error+)) - (let ((subcode (uffi:deref-pointer errcode :long))) - (unless (and nulls-ok (= subcode +null-value-returned+)) - (error 'sql-database-error - :database database - :error-id subcode - :message (uffi:convert-from-foreign-string errbuf))))))) - (nulls-ok - (error 'sql-database-error - :database database - :message "can't handle NULLS-OK without ERRHP")) - (t - (error 'sql-database-error - :database database - :message "OCI Error (and no ERRHP available to find subcode)")))) - -;;; Require an OCI success code. -;;; -;;; (The ordinary OCI error reporting mechanisms uses a fair amount of -;;; machinery (environments and other handles). In order to get to -;;; where we can use these mechanisms, we have to be able to allocate -;;; the machinery. The functions for allocating the machinery can -;;; return errors (e.g. out of memory) but shouldn't. Wrapping this function -;;; around function calls to such have-to-succeed functions enforces -;;; this condition.) - -(defun osucc (code) - (declare (type fixnum code)) - (unless (= code +oci-success+) - (error 'sql-database-error - :message (format nil "unexpected OCI failure, code=~S" code)))) - - -;;; Enabling this can be handy for low-level debugging. -#+nil -(progn - (trace #-oci7 oci-env-create oci-initialize oci-handle-alloc oci-logon - oci-error-get oci-stmt-prepare oci-stmt-execute - oci-param-get oci-logon oci-attr-get oci-define-by-pos oci-stmt-fetch) - (setf debug::*debug-print-length* nil)) - - -;; Return the INDEXth string of the OCI array, represented as Lisp -;; SIMPLE-STRING. SIZE is the size of the fixed-width fields used by -;; Oracle to store strings within the array. - -(uffi:def-type string-pointer (* :unsigned-char)) - -(defun deref-oci-string (arrayptr string-index size) - (declare (type string-pointer arrayptr)) - (declare (type (mod #.+n-buf-rows+) string-index)) - (declare (type (and unsigned-byte fixnum) size)) - (let ((str (uffi:convert-from-foreign-string - (uffi:make-pointer - (+ (uffi:pointer-address arrayptr) (* string-index size)) - :unsigned-char)))) - (if (string-equal str "NULL") nil str))) - -;; the OCI library, part Z: no-longer used logic to convert from -;; Oracle's binary date representation to Common Lisp's native date -;; representation - -#+nil -(defvar +oci-date-bytes+ 7) - -;;; Return the INDEXth date in the OCI array, represented as -;;; a Common Lisp "universal time" (i.e. seconds since 1900). - -#+nil -(defun deref-oci-date (arrayptr index) - (oci-date->universal-time (uffi:pointer-address - (uffi:deref-array arrayptr - '(:array :unsigned-char) - (* index +oci-date-bytes+))))) -#+nil -(defun oci-date->universal-time (oci-date) - (declare (type (alien (* :unsigned-char)) oci-date)) - (flet (;; a character from OCI-DATE, interpreted as an unsigned byte - (ub (i) - (declare (type (mod #.+oci-date-bytes+) i)) - (mod (uffi:deref-array oci-date string-array i) 256))) - (let* ((century (* (- (ub 0) 100) 100)) - (year (+ century (- (ub 1) 100))) - (month (ub 2)) - (day (ub 3)) - (hour (1- (ub 4))) - (minute (1- (ub 5))) - (second (1- (ub 6)))) - (encode-universal-time second minute hour day month year)))) - - -(defmethod database-list-tables ((database oracle-database) &key owner) - (let ((query - (if owner - (format nil - "select user_tables.table_name from user_tables,all_tables where user_tables.table_name=all_tables.table_name and all_tables.owner='~:@(~A~)'" - owner) - "select table_name from user_tables"))) - (mapcar #'car (database-query query database nil nil)))) - - -(defmethod database-list-views ((database oracle-database) &key owner) - (let ((query - (if owner - (format nil - "select user_views.view_name from user_views,all_views where user_views.view_name=all_views.view_name and all_views.owner='~:@(~A~)'" - owner) - "select view_name from user_views"))) - (mapcar #'car - (database-query query database nil nil)))) - -(defmethod database-list-indexes ((database oracle-database) - &key (owner nil)) - (let ((query - (if owner - (format nil - "select user_indexes.index_name from user_indexes,all_indexes where user_indexes.index_name=all_indexes.index_name and all_indexes.owner='~:@(~A~)'" - owner) - "select index_name from user_indexes"))) - (mapcar #'car (database-query query database nil nil)))) - -(defmethod database-list-table-indexes (table (database oracle-database) - &key (owner nil)) - (let ((query - (if owner - (format nil - "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~)'" - table owner) - (format nil "select index_name from user_indexes where table_name='~A'" - table)))) - (mapcar #'car (database-query query database nil nil)))) - - -(defmethod database-list-attributes (table (database oracle-database) &key owner) - (let ((query - (if owner - (format nil - "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~)'" - table owner) - (format nil - "select column_name from user_tab_columns where table_name='~A'" - table)))) - (mapcar #'car (database-query query database nil nil)))) - -(defmethod database-attribute-type (attribute (table string) - (database oracle-database) - &key (owner nil)) - (let ((query - (if owner - (format nil - "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~)'" - table attribute owner) - (format nil - "select data_type,data_length,data_scale,nullable from user_tab_columns where table_name='~A' and column_name='~A'" - table attribute)))) - (destructuring-bind (type length scale nullable) (car (database-query query database :auto nil)) - (values (ensure-keyword type) length scale - (if (char-equal #\Y (schar nullable 0)) 1 0))))) - -;; Return one row of the table referred to by QC, represented as a -;; list; or if there are no more rows, signal an error if EOF-ERRORP, -;; or return EOF-VALUE otherwise. - -;; KLUDGE: This CASE statement is a strong sign that the code would be -;; cleaner if CD were made into an abstract class, we made variant -;; classes for CD-for-column-of-strings, CD-for-column-of-floats, -;; etc., and defined virtual functions to handle operations like -;; get-an-element-from-column. (For a small special purpose module -;; like this, would arguably be overkill, so I'm not going to do it -;; now, but if this code ends up getting more complicated in -;; maintenance, it would become a really good idea.) - -;; Arguably this would be a good place to signal END-OF-FILE, but -;; since the ANSI spec specifically says that END-OF-FILE means a -;; STREAM which has no more data, and QC is not a STREAM, we signal -;; DBI-ERROR instead. - -(uffi:def-type short-array '(:array :short)) -(uffi:def-type int-pointer '(* :int)) -(uffi:def-type double-pointer '(* :double)) - -;;; the result of a database query: a cursor through a table -(defstruct (oracle-result-set (:print-function print-query-cursor) - (:conc-name qc-) - (:constructor %make-query-cursor)) - (db (error "missing DB") ; db conn. this table is associated with - :type oracle-database - :read-only t) - (stmthp (error "missing STMTHP") ; the statement handle used to create -;; :type alien ; this table. owned by the QUERY-CURSOR - :read-only t) ; object, deallocated on CLOSE-QUERY - (cds) ; (error "missing CDS") ; column descriptors -; :type (simple-array cd 1) - ; :read-only t) - (n-from-oci - 0 ; buffered rows: number of rows recv'd - :type (integer 0 #.+n-buf-rows+)) ; from the database on the last read - (n-to-dbi - 0 ; number of buffered rows returned, i.e. - :type (integer 0 #.+n-buf-rows+)) ; the index, within the buffered rows, - ; of the next row which hasn't already - ; been returned - (total-n-from-oci - 0 ; total number of bytes recv'd from OCI - :type unsigned-byte) ; in all reads - (oci-end-seen-p nil)) ; Have we seen the end of OCI - ; data, i.e. OCI returning - ; less data than we requested? - ; OCI doesn't seem to like us - ; to try to read more data - ; from it after that.. - - -(defun fetch-row (qc &optional (eof-errorp t) eof-value) - ;;(declare (optimize (speed 3))) - (cond ((zerop (qc-n-from-oci qc)) - (if eof-errorp - (error 'sql-database-error :message - (format nil "no more rows available in ~S" qc)) - eof-value)) - ((>= (qc-n-to-dbi qc) - (qc-n-from-oci qc)) - (refill-qc-buffers qc) - (fetch-row qc nil eof-value)) - (t - (let ((cds (qc-cds qc)) - (reversed-result nil) - (irow (qc-n-to-dbi qc))) - (dotimes (icd (length cds)) - (let* ((cd (aref cds icd)) - (b (foreign-resource-buffer (cd-buffer cd))) - (value - (let* ((arb (foreign-resource-buffer (cd-indicators cd))) - (indicator (uffi:deref-array arb '(:array :short) irow))) - ;;(declare (type short-array arb)) - (unless (= indicator -1) - (ecase (cd-oci-data-type cd) - (#.SQLT-STR - (deref-oci-string b irow (cd-sizeof cd))) - (#.SQLT-FLT - (uffi:deref-array b '(:array :double) irow)) - (#.SQLT-INT - (ecase (cd-sizeof cd) - (4 - (uffi:deref-array b '(:array :int) irow)))) - (#.SQLT-DATE - (deref-oci-string b irow (cd-sizeof cd)))))))) - (when (and (eq :string (cd-result-type cd)) - value - (not (stringp value))) - (setq value (write-to-string value))) - (push value reversed-result))) - (incf (qc-n-to-dbi qc)) - (nreverse reversed-result))))) - -(defun refill-qc-buffers (qc) - (with-slots (errhp) (qc-db qc) - (setf (qc-n-to-dbi qc) 0) - (cond ((qc-oci-end-seen-p qc) - (setf (qc-n-from-oci qc) 0)) - (t - (let ((oci-code (%oci-stmt-fetch - (deref-vp (qc-stmthp qc)) - (deref-vp errhp) - +n-buf-rows+ - +oci-fetch-next+ +oci-default+))) - (ecase oci-code - (#.+oci-success+ (values)) - (#.+oci-no-data+ (setf (qc-oci-end-seen-p qc) t) - (values)) - (#.+oci-error+ (handle-oci-error :database (qc-db qc) - :nulls-ok t)))) - (uffi:with-foreign-object (rowcount :long) - (oci-attr-get (deref-vp (qc-stmthp qc)) - +oci-htype-stmt+ - rowcount - +unsigned-int-null-pointer+ - +oci-attr-row-count+ - (deref-vp errhp)) - (setf (qc-n-from-oci qc) - (- (uffi:deref-pointer rowcount :long) - (qc-total-n-from-oci qc))) - (when (< (qc-n-from-oci qc) +n-buf-rows+) - (setf (qc-oci-end-seen-p qc) t)) - (setf (qc-total-n-from-oci qc) - (uffi:deref-pointer rowcount :long))))) - (values))) - -;; the guts of the SQL function -;; -;; (like the SQL function, but with the QUERY argument hardwired to T, so -;; that the return value is always a cursor instead of a list) - -;; Is this a SELECT statement? SELECT statements are handled -;; specially by OCIStmtExecute(). (Non-SELECT statements absolutely -;; require a nonzero iteration count, while the ordinary choice for a -;; SELECT statement is a zero iteration count. - -;; SELECT statements are the only statements which return tables. We -;; don't free STMTHP in this case, but instead give it to the new -;; QUERY-CURSOR, and the new QUERY-CURSOR becomes responsible for -;; freeing the STMTHP when it is no longer needed. - -(defun sql-stmt-exec (sql-stmt-string db result-types field-names) - (with-slots (envhp svchp errhp) - db - (let ((stmthp (uffi:allocate-foreign-object :pointer-void))) - (uffi:with-foreign-object (stmttype :unsigned-short) - - (oci-handle-alloc (deref-vp envhp) - stmthp - +oci-htype-stmt+ 0 +null-void-pointer-pointer+) - (oci-stmt-prepare (deref-vp stmthp) - (deref-vp errhp) - (uffi:convert-to-cstring sql-stmt-string) - (length sql-stmt-string) - +oci-ntv-syntax+ +oci-default+ :database db) - (oci-attr-get (deref-vp stmthp) - +oci-htype-stmt+ - stmttype - +unsigned-int-null-pointer+ - +oci-attr-stmt-type+ - (deref-vp errhp) - :database db) - (let* ((select-p (= (uffi:deref-pointer stmttype :unsigned-short) 1)) - (iters (if select-p 0 1))) - - (oci-stmt-execute (deref-vp svchp) - (deref-vp stmthp) - (deref-vp errhp) - iters 0 +null-void-pointer+ +null-void-pointer+ +oci-default+ - :database db) - (cond (select-p - (make-query-cursor db stmthp result-types field-names)) - (t - (oci-handle-free (deref-vp stmthp) +oci-htype-stmt+) - nil))))))) - - -;; Return a QUERY-CURSOR representing the table returned from the OCI -;; operation done through STMTHP. TYPES is the argument of the same -;; name from the external SQL function, controlling type conversion -;; of the returned arguments. - -(defun make-query-cursor (db stmthp result-types field-names) - (let ((qc (%make-query-cursor :db db - :stmthp stmthp - :cds (make-query-cursor-cds db stmthp - result-types - field-names)))) - (refill-qc-buffers qc) - qc)) - - -;; the hairy part of MAKE-QUERY-CURSOR: Ask OCI for information -;; about table columns, translate the information into a Lisp -;; vector of column descriptors, and return it. - -;; Allegro defines several flavors of type conversion, but this -;; implementation only supports the :AUTO flavor. - -;; A note of explanation: OCI's internal number format uses 21 -;; bytes (42 decimal digits). 2 separate (?) one-byte fields, -;; scale and precision, are used to deduce the nature of these -;; 21 bytes. See pp. 3-10, 3-26, and 6-13 of OCI documentation -;; for more details. - -;; When calling OCI C code to handle the conversion, we have -;; only two numeric types available to pass the return value: -;; double-float and signed-long. It would be possible to -;; bypass the OCI conversion functions and write Lisp code -;; which reads the 21-byte field directly and decodes -;; it. However this is left as an exercise for the reader. :-) - -;; The following table describes the mapping, based on the implicit -;; assumption that C's "signed long" type is a 32-bit integer. -;; -;; Internal Values SQL Type C Return Type -;; =============== ======== ============= -;; Precision > 0 SCALE = -127 FLOAT --> double-float -;; Precision > 0 && <=9 SCALE = 0 INTEGER --> signed-long -;; Precision = 0 || > 9 SCALE = 0 BIG INTEGER --> double-float -;; Precision > 0 SCALE > 0 DECIMAL --> double-float - -;; (OCI uses 1-based indexing here.) - -;; KLUDGE: This should work for all other data types except those -;; which don't actually fit in their fixed-width field (BLOBs and the -;; like). As Winton says, we (Cadabra) don't need to worry much about -;; those, since we can't reason with them, so we don't use them. But -;; for a more general application it'd be good to have a more -;; selective and rigorously correct test here for whether we can -;; actually handle the given DEREF-DTYPE value. -- WHN 20000106 - -;; Note: The OCI documentation doesn't seem to say whether the COLNAME -;; value returned here is a newly-allocated copy which we're -;; responsible for freeing, or a pointer into some system copy which -;; will be freed when the system itself is shut down. But judging -;; from the way that the result is used in the cdemodsa.c example -;; program, it looks like the latter: we should make our own copy of -;; the value, but not try to free it. - -;; WORKAROUND: OCI seems to return ub2 values for the -;; +oci-attr-data-size+ attribute even though its documentation claims -;; that it returns a ub4, and even though the associated "sizep" value -;; is 4, not 2. In order to make the code here work reliably, without -;; having to patch it later if OCI is ever fixed to match its -;; documentation, we pre-zero COLSIZE before making the call into OCI. - -;; To exercise the weird OCI behavior (thereby blowing up the code -;; below, beware!) try setting this value into COLSIZE, calling OCI, -;; then looking at the value in COLSIZE. (setf colsize #x12345678) -;; debugging only - - -(uffi:def-type byte-pointer (* :byte)) -(uffi:def-type ulong-pointer (* :unsigned-long)) -(uffi:def-type void-pointer-pointer (* :void-pointer)) - -(defun make-query-cursor-cds (database stmthp result-types field-names) - (declare (optimize (safety 3) #+nil (speed 3)) - (type oracle-database database) - (type pointer-pointer-void stmthp)) - (with-slots (errhp) database - (uffi:with-foreign-objects ((dtype-foreign :unsigned-short) - (parmdp :pointer-void) - (precision :byte) - (scale :byte) - (colname '(* :unsigned-char)) - (colnamelen :unsigned-long) - (colsize :unsigned-long) - (colsizesize :unsigned-long) - (defnp ':pointer-void)) - (let ((buffer nil) - (sizeof nil)) - (do ((icolumn 0 (1+ icolumn)) - (cds-as-reversed-list nil)) - ((not (eql (oci-param-get (deref-vp stmthp) - +oci-htype-stmt+ - (deref-vp errhp) - parmdp - (1+ icolumn) :database database) - +oci-success+)) - (coerce (reverse cds-as-reversed-list) 'simple-vector)) - ;; Decode type of ICOLUMNth column into a type we're prepared to - ;; handle in Lisp. - (oci-attr-get (deref-vp parmdp) - +oci-dtype-param+ - dtype-foreign - +unsigned-int-null-pointer+ - +oci-attr-data-type+ - (deref-vp errhp)) - (let ((dtype (uffi:deref-pointer dtype-foreign :unsigned-short))) - (declare (fixnum dtype)) - (case dtype - (#.SQLT-DATE - (setf buffer (acquire-foreign-resource :unsigned-char - (* 32 +n-buf-rows+))) - (setf sizeof 32 dtype #.SQLT-STR)) - (#.SQLT-NUMBER - (oci-attr-get (deref-vp parmdp) - +oci-dtype-param+ - precision - +unsigned-int-null-pointer+ - +oci-attr-precision+ - (deref-vp errhp)) - (oci-attr-get (deref-vp parmdp) - +oci-dtype-param+ - scale - +unsigned-int-null-pointer+ - +oci-attr-scale+ - (deref-vp errhp)) - (let ((*scale (uffi:deref-pointer scale :byte)) - (*precision (uffi:deref-pointer precision :byte))) - - ;;(format t "scale=~d, precision=~d~%" *scale *precision) - (cond - ((or (and (minusp *scale) (zerop *precision)) - (and (zerop *scale) (plusp *precision))) - (setf buffer (acquire-foreign-resource :int +n-buf-rows+) - sizeof 4 ;; sizeof(int) - dtype #.SQLT-INT)) - (t - (setf buffer (acquire-foreign-resource :double +n-buf-rows+) - sizeof 8 ;; sizeof(double) - dtype #.SQLT-FLT))))) - ;; Default to SQL-STR - (t - (setf (uffi:deref-pointer colsize :unsigned-long) 0) - (setf dtype #.SQLT-STR) - (oci-attr-get (deref-vp parmdp) - +oci-dtype-param+ - colsize - +unsigned-int-null-pointer+ - +oci-attr-data-size+ - (deref-vp errhp)) - (let ((colsize-including-null (1+ (uffi:deref-pointer colsize :unsigned-long)))) - (setf buffer (acquire-foreign-resource - :unsigned-char (* +n-buf-rows+ colsize-including-null))) - (setf sizeof colsize-including-null)))) - (let ((retcodes (acquire-foreign-resource :unsigned-short +n-buf-rows+)) - (indicators (acquire-foreign-resource :short +n-buf-rows+)) - (colname-string "")) - (when field-names - (oci-attr-get (deref-vp parmdp) - +oci-dtype-param+ - colname - colnamelen - +oci-attr-name+ - (deref-vp errhp)) - (setq colname-string (uffi:convert-from-foreign-string - (uffi:deref-pointer colname '(* :unsigned-char)) - :length (uffi:deref-pointer colnamelen :unsigned-long)))) - (push (make-cd :name colname-string - :sizeof sizeof - :buffer buffer - :oci-data-type dtype - :retcodes retcodes - :indicators indicators - :result-type (cond - ((consp result-types) - (nth icolumn result-types)) - ((null result-types) - :string) - (t - result-types))) - cds-as-reversed-list) - (oci-define-by-pos (deref-vp stmthp) - defnp - (deref-vp errhp) - (1+ icolumn) ; OCI 1-based indexing again - (foreign-resource-buffer buffer) - sizeof - dtype - (foreign-resource-buffer indicators) - +unsigned-short-null-pointer+ - (foreign-resource-buffer retcodes) - +oci-default+)))))))) - -;; Release the resources associated with a QUERY-CURSOR. - -(defun close-query (qc) - (oci-handle-free (deref-vp (qc-stmthp qc)) +oci-htype-stmt+) - (let ((cds (qc-cds qc))) - (dotimes (i (length cds)) - (release-cd-resources (aref cds i)))) - (values)) - - -;; Release the resources associated with a column description. - -(defun release-cd-resources (cd) - (free-foreign-resource (cd-buffer cd)) - (free-foreign-resource (cd-retcodes cd)) - (free-foreign-resource (cd-indicators cd)) - (values)) - - -(defmethod database-name-from-spec (connection-spec (database-type (eql :oracle))) - (check-connection-spec connection-spec database-type (dsn user password)) - (destructuring-bind (dsn user password) connection-spec - (declare (ignore password)) - (concatenate 'string dsn "/" user))) - - -(defmethod database-connect (connection-spec (database-type (eql :oracle))) - (check-connection-spec connection-spec database-type (dsn user password)) - (destructuring-bind (data-source-name user password) - connection-spec - (let ((envhp (uffi:allocate-foreign-object :pointer-void)) - (errhp (uffi:allocate-foreign-object :pointer-void)) - (svchp (uffi:allocate-foreign-object :pointer-void)) - (srvhp (uffi:allocate-foreign-object :pointer-void))) - ;; Requests to allocate environments and handles should never - ;; fail in normal operation, and they're done too early to - ;; handle errors very gracefully (since they're part of the - ;; error-handling mechanism themselves) so we just assert they - ;; work. - (setf (deref-vp envhp) +null-void-pointer+) - #-oci7 - (oci-env-create envhp +oci-default+ +null-void-pointer+ - +null-void-pointer+ +null-void-pointer+ - +null-void-pointer+ 0 +null-void-pointer-pointer+) - #+oci7 - (progn - (oci-initialize +oci-object+ +null-void-pointer+ +null-void-pointer+ - +null-void-pointer+ +null-void-pointer-pointer+) - (ignore-errors (oci-handle-alloc +null-void-pointer+ envhp - +oci-htype-env+ 0 - +null-void-pointer-pointer+)) ;no testing return - (oci-env-init envhp +oci-default+ 0 +null-void-pointer-pointer+)) - (oci-handle-alloc (deref-vp envhp) errhp - +oci-htype-error+ 0 +null-void-pointer-pointer+) - (oci-handle-alloc (deref-vp envhp) srvhp - +oci-htype-server+ 0 +null-void-pointer-pointer+) - - #+ignore ;; not used since CLSQL uses the OCILogon function instead - (uffi:with-cstring (dblink nil) - (oci-server-attach (deref-vp srvhp) - (deref-vp errhp) - dblink - 0 +oci-default+)) - - (oci-handle-alloc (deref-vp envhp) svchp - +oci-htype-svcctx+ 0 +null-void-pointer-pointer+) - (oci-attr-set (deref-vp svchp) - +oci-htype-svcctx+ - (deref-vp srvhp) 0 +oci-attr-server+ - (deref-vp errhp)) - ;; oci-handle-alloc((dvoid *)encvhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0); - ;;#+nil - - (let ((db (make-instance 'oracle-database - :name (database-name-from-spec connection-spec - database-type) - :connection-spec connection-spec - :envhp envhp - :errhp errhp - :database-type :oracle - :svchp svchp - :dsn data-source-name - :user user))) - (oci-logon (deref-vp envhp) - (deref-vp errhp) - svchp - (uffi:convert-to-cstring user) (length user) - (uffi:convert-to-cstring password) (length password) - (uffi:convert-to-cstring data-source-name) (length data-source-name) - :database db) - ;; :date-format-length (1+ (length date-format))))) - (setf (slot-value db 'clsql-sys::state) :open) - (database-execute-command - (format nil "ALTER SESSION SET NLS_DATE_FORMAT='~A'" (date-format db)) db) - (let ((server-version - (caar (database-query - "SELECT BANNER FROM V$VERSION WHERE BANNER LIKE '%Oracle%'" db nil nil)))) - (setf (slot-value db 'server-version) server-version - (slot-value db 'major-server-version) (major-client-version-from-string - server-version))) - db)))) - - -(defun major-client-version-from-string (str) - (cond - ((search " 10g " str) - 10) - ((search "Oracle9i " str) - 9) - ((search "Oracle8" str) - 8))) - -(defun major-server-version-from-string (str) - (when (> (length str) 2) - (cond - ((string= "10." (subseq str 0 3)) - 10) - ((string= "9." (subseq str 0 2)) - 9) - ((string= "8." (subseq str 0 2)) - 8)))) - - -;; Close a database connection. - -(defmethod database-disconnect ((database oracle-database)) - (osucc (oci-logoff (deref-vp (svchp database)) - (deref-vp (errhp database)))) - (osucc (oci-handle-free (deref-vp (envhp database)) +oci-htype-env+)) - ;; Note: It's neither required nor allowed to explicitly deallocate the - ;; ERRHP handle here, since it's owned by the ENVHP deallocated above, - ;; and was therefore automatically deallocated at the same time. - t) - -;;; Do the database operation described in SQL-STMT-STRING on database -;;; DB and, if the command is a SELECT, return a representation of the -;;; resulting table. The representation of the table is controlled by the -;;; QUERY argument: -;;; * If QUERY is NIL, the table is returned as a list of rows, with -;;; each row represented by a list. -;;; * If QUERY is non-NIL, the result is returned as a QUERY-CURSOR -;;; suitable for FETCH-ROW and CLOSE-QUERY -;;; The TYPES argument controls the type conversion method used -;;; to construct the table. The Allegro version supports several possible -;;; values for this argument, but we only support :AUTO. - -(defmethod database-query (query-expression (database oracle-database) result-types field-names) - (let ((cursor (sql-stmt-exec query-expression database result-types field-names))) - ;; (declare (type (or query-cursor null) cursor)) - (if (null cursor) ; No table was returned. - (values) - (do ((reversed-result nil)) - (nil) - (let* ((eof-value :eof) - (row (fetch-row cursor nil eof-value))) - (when (eq row eof-value) - (close-query cursor) - (if field-names - (return (values (nreverse reversed-result) - (loop for cd across (qc-cds cursor) - collect (cd-name cd)))) - (return (nreverse reversed-result)))) - (push row reversed-result)))))) - - -(defmethod database-create-sequence (sequence-name (database oracle-database)) - (execute-command - (concatenate 'string "CREATE SEQUENCE " (sql-escape sequence-name)) - :database database)) - -(defmethod database-drop-sequence (sequence-name (database oracle-database)) - (execute-command - (concatenate 'string "DROP SEQUENCE " (sql-escape sequence-name)) - :database database)) - -(defmethod database-sequence-next (sequence-name (database oracle-database)) - (caar - (database-query - (concatenate 'string "SELECT " - (sql-escape sequence-name) - ".NEXTVAL FROM dual" - ) - database :auto nil))) - -(defmethod database-set-sequence-position (name position (database oracle-database)) - (without-interrupts - (let* ((next (database-sequence-next name database)) - (incr (- position next))) - (database-execute-command - (format nil "ALTER SEQUENCE ~A INCREMENT BY ~D" name incr) - database) - (database-sequence-next name database) - (database-execute-command - (format nil "ALTER SEQUENCE ~A INCREMENT BY 1" name) - database)))) - -(defmethod database-list-sequences ((database oracle-database) &key owner) - (let ((query - (if owner - (format nil - "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~)'" - owner) - "select sequence_name from user_sequences"))) - (mapcar #'car (database-query query database nil nil)))) - -(defmethod database-execute-command (sql-expression (database oracle-database)) - (database-query sql-expression database nil nil) - (when (database-autocommit database) - (oracle-commit database)) - t) - - -(defstruct (cd (:constructor make-cd) - (:print-function print-cd)) - "a column descriptor: metadata about the data in a table" - - ;; name of this column - (name (error "missing NAME") :type simple-string :read-only t) - ;; the size in bytes of a single element - (sizeof (error "missing SIZE") :type fixnum :read-only t) - ;; an array of +N-BUF-ROWS+ elements in C representation - (buffer (error "Missing BUFFER") - :type foreign-resource - :read-only t) - ;; an array of +N-BUF-ROWS+ OCI return codes in C representation. - ;; (There must be one return code for every element of every - ;; row in order to be able to represent nullness.) - (retcodes (error "Missing RETCODES") - :type foreign-resource - :read-only t) - (indicators (error "Missing INDICATORS") - :type foreign-resource - :read-only t) - ;; the OCI code for the data type of a single element - (oci-data-type (error "missing OCI-DATA-TYPE") - :type fixnum - :read-only t) - (result-type (error "missing RESULT-TYPE") - :read-only t)) - - -(defun print-cd (cd stream depth) - (declare (ignore depth)) - (print-unreadable-object (cd stream :type t) - (format stream - ":NAME ~S :OCI-DATA-TYPE ~S :OCI-DATA-SIZE ~S" - (cd-name cd) - (cd-oci-data-type cd) - (cd-sizeof cd)))) - -(defun print-query-cursor (qc stream depth) - (declare (ignore depth)) - (print-unreadable-object (qc stream :type t :identity t) - (prin1 (qc-db qc) stream))) - - -(defmethod database-query-result-set ((query-expression string) - (database oracle-database) - &key full-set result-types) - (let ((cursor (sql-stmt-exec query-expression database result-types nil))) - (if full-set - (values cursor (length (qc-cds cursor)) nil) - (values cursor (length (qc-cds cursor)))))) - - -(defmethod database-dump-result-set (result-set (database oracle-database)) - (close-query result-set)) - -(defmethod database-store-next-row (result-set (database oracle-database) list) - (let* ((eof-value :eof) - (row (fetch-row result-set nil eof-value))) - (unless (eq eof-value row) - (loop for i from 0 below (length row) - do (setf (nth i list) (nth i row))) - list))) - -(defmethod database-start-transaction ((database oracle-database)) - (call-next-method) - ;; Not needed with simple transaction - #+ignore - (with-slots (svchp errhp) database - (oci-trans-start (deref-vp svchp) - (deref-vp errhp) - 60 - +oci-trans-new+)) - t) - - -(defun oracle-commit (database) - (with-slots (svchp errhp) database - (osucc (oci-trans-commit (deref-vp svchp) - (deref-vp errhp) - 0)))) - -(defmethod database-commit-transaction ((database oracle-database)) - (call-next-method) - (oracle-commit database) - t) - -(defmethod database-abort-transaction ((database oracle-database)) - (call-next-method) - (osucc (oci-trans-rollback (deref-vp (svchp database)) - (deref-vp (errhp database)) - 0)) - t) - -;; Specifications - -(defmethod db-type-has-bigint? ((type (eql :oracle))) - nil) - -(defmethod db-type-has-fancy-math? ((db-type (eql :oracle))) - t) - -(defmethod db-type-has-boolean-where? ((db-type (eql :oracle))) - nil) - -(when (clsql-sys:database-type-library-loaded :oracle) - (clsql-sys:initialize-database-type :database-type :oracle))