1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
2 ;;;; *************************************************************************
3 ;;;; FILE IDENTIFICATION
6 ;;;; Purpose: Low-level interface for CLSQL ODBC backend
7 ;;;; Programmer: Kevin M. Rosenberg
8 ;;;; Date Started: Feb 2002
10 ;;;; $Id: odbc-sql.lisp 8983 2004-04-12 21:16:48Z kevin $
12 ;;;; This file, part of CLSQL, is Copyright (c) 2002 by Kevin M. Rosenberg
14 ;;;; CLSQL users are granted the rights to distribute and use this software
15 ;;;; as governed by the terms of the Lisp Lesser GNU Public License
16 ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
17 ;;;; *************************************************************************
19 (defpackage #:clsql-odbc
20 (:use #:common-lisp #:clsql-base-sys)
21 (:export #:odbc-database)
22 (:documentation "This is the CLSQL interface to ODBC."))
24 (in-package #:clsql-odbc)
28 (defclass odbc-database (database)
29 ((odbc-conn :accessor database-odbc-conn :initarg :odbc-conn)))
31 (defmethod database-name-from-spec (connection-spec
32 (database-type (eql :odbc)))
33 (check-connection-spec connection-spec database-type (dsn user password))
34 (destructuring-bind (dsn user password) connection-spec
35 (declare (ignore password))
36 (concatenate 'string dsn "/" user)))
38 (defmethod database-connect (connection-spec (database-type (eql :odbc)))
39 (check-connection-spec connection-spec database-type (dsn user password))
40 (destructuring-bind (dsn user password) connection-spec
42 (make-instance 'odbc-database
43 :name (database-name-from-spec connection-spec :odbc)
45 (odbc-dbi:connect :user user
47 :data-source-name dsn))
48 (error () ;; Init or Connect failed
49 (error 'clsql-connect-error
50 :database-type database-type
51 :connection-spec connection-spec
53 :error "Connection failed")))))
56 (defun store-type-of-connected-database (db)
57 (let* ((odbc-db (odbc-db db))
58 (server-name (get-odbc-info odbc-db odbc::$SQL_SERVER_NAME))
59 (dbms-name (get-odbc-info odbc-db odbc::$SQL_DBMS_NAME))
61 ;; need SERVER-NAME and DBMS-NAME because many drivers mix this up
63 ((or (search "postgresql" server-name :test #'char-equal)
64 (search "postgresql" dbms-name :test #'char-equal))
66 ((or (search "mysql" server-name :test #'char-equal)
67 (search "mysql" dbms-name :test #'char-equal))
69 ((or (search "oracle" server-name :test #'char-equal)
70 (search "oracle" dbms-name :test #'char-equal))
72 (setf (database-type db) type)))
75 (defmethod database-disconnect ((database odbc-database))
76 (odbc-dbi:disconnect (database-odbc-conn database))
77 (setf (database-odbc-conn database) nil)
80 (defmethod database-query (query-expression (database odbc-database) result-types)
82 (odbc-dbi:sql query-expression :db (database-odbc-conn database)
85 (error 'clsql-sql-error
87 :expression query-expression
89 :error "Query failed"))))
91 (defmethod database-execute-command (sql-expression
92 (database odbc-database))
94 (odbc-dbi:sql sql-expression (database-odbc-conn database))
96 (error 'clsql-sql-error
98 :expression sql-expression
100 :error "Execute command failed"))))
102 (defstruct odbc-result-set
104 (types nil :type cons)
105 (full-set nil :type boolean))
107 (defmethod database-query-result-set ((query-expression string)
108 (database odbc-database)
109 &key full-set result-types)
111 (multiple-value-bind (query column-names)
112 (odbc-dbi:sql query-expression
113 :db (database-odbc-conn database)
117 :result-types result-types
120 (make-odbc-result-set :query query :full-set full-set
122 (length column-names)
123 nil ;; not able to return number of rows with odbc
126 (error 'clsql-sql-error
128 :expression query-expression
130 :error "Query result set failed"))))
132 (defmethod database-dump-result-set (result-set (database odbc-database))
133 (odbc-dbi:close-query (odbc-result-set-query result-set))
136 (defmethod database-store-next-row (result-set
137 (database odbc-database)
139 (let ((row (odbc-dbi:fetch-row (odbc-result-set-query result-set) nil 'eof)))
143 (loop for elem in row
146 (setf (car rest) elem))
149 ;;; Sequence functions
151 (defun %sequence-name-to-table (sequence-name)
152 (concatenate 'string "_clsql_seq_" (sql-escape sequence-name)))
154 (defun %table-name-to-sequence-name (table-name)
155 (and (>= (length table-name) 11)
156 (string= (subseq table-name 0 11) "_clsql_seq_")
157 (subseq table-name 11)))
159 (defmethod database-create-sequence (sequence-name
160 (database odbc-database))
161 (let ((table-name (%sequence-name-to-table sequence-name)))
162 (database-execute-command
163 (concatenate 'string "CREATE TABLE " table-name
164 " (id int NOT NULL PRIMARY KEY AUTO_INCREMENT)")
166 (database-execute-command
167 (concatenate 'string "INSERT INTO " table-name
171 (defmethod database-drop-sequence (sequence-name
172 (database odbc-database))
173 (database-execute-command
174 (concatenate 'string "DROP TABLE " (%sequence-name-to-table sequence-name))
177 (defmethod database-list-sequences ((database odbc-database)
179 (declare (ignore owner))
180 (mapcar #'(lambda (s) (%table-name-to-sequence-name (car s)))
181 (database-query "SHOW TABLES LIKE '%clsql_seq%'"
184 (defmethod database-set-sequence-position (sequence-name
186 (database odbc-database))
187 (database-execute-command
188 (format nil "UPDATE ~A SET id=~A" (%sequence-name-to-table sequence-name)
193 (defmethod database-sequence-next (sequence-name (database odbc-database))
194 (warn "Not implemented."))
196 (defmethod database-sequence-last (sequence-name (database odbc-database))
197 (declare (ignore sequence-name)))
199 (defmethod database-create (connection-spec (type (eql :odbc)))
200 (warn "Not implemented."))
202 (defmethod database-destroy (connection-spec (type (eql :odbc)))
203 (warn "Not implemented."))
205 (defmethod database-probe (connection-spec (type (eql :odbc)))
206 (warn "Not implemented."))
209 (when (clsql-base-sys:database-type-library-loaded :odbc)
210 (clsql-base-sys:initialize-database-type :database-type :odbc))