Re: Getting DB2 Connectivity

Thanks for the feedback. I started playing with the ODBC code, and it  
seems to work fine with DB2. I added the following to the list of  
ODBC driver libraries so that DB2 would load:


$ diff -ru odbc-loader.orig.lisp odbc-loader.lisp
--- odbc-loader.orig.lisp       2006-03-01 01:41:46.000000000 -0500
+++ odbc-loader.lisp    2006-03-01 03:04:55.000000000 -0500
@@ -19,7 +19,7 @@
(in-package #:odbc)
(defparameter *odbc-library-filenames*
-  '("odbc32" "libodbc" "libiodbc"))
+  '("odbc32" "libodbc" "libiodbc" "libdb2"))

(defvar *odbc-supporting-libraries* '("c")
    "Used only by CMU. List of library flags needed to be passed to  
ld to


Also, DB2 does not need a user/password to connect in some cases (for  
example, when your Lisp is running on the DB2 server under a userid  
allowed to connect to DB2). This seems really useful to me because  
you don't need to keep a copy of your password in your Lisp  
application for connecting to DB2. To do this, I made these changes  
to the connection spec code:


$ diff -ru odbc-sql.orig.lisp odbc-sql.lisp
--- odbc-sql.orig.lisp  2006-03-01 02:02:13.000000000 -0500
+++ odbc-sql.lisp       2006-03-01 03:08:00.000000000 -0500
@@ -30,26 +30,32 @@
(defmethod database-name-from-spec (connection-spec
                                     (database-type (eql :odbc)))
-  (check-connection-spec connection-spec database-type (dsn user  
password &key connection-string completion window-handle))
-  (destructuring-bind (dsn user password &key connection-string  
completion window-handle) connection-spec
+  (check-connection-spec connection-spec database-type (dsn  
&optional user password &key connection-string completion window- 
handle))
+  (destructuring-bind (dsn &optional user password &key connection- 
string completion window-handle) connection-spec
      (declare (ignore password connection-string completion window- 
handle))
      (concatenate 'string dsn "/" user)))
(defmethod database-connect (connection-spec (database-type  
(eql :odbc)))
-  (check-connection-spec connection-spec database-type (dsn user  
password &key connection-string completion window-handle))
-  (destructuring-bind (dsn user password &key connection-string  
(completion :no-prompt) window-handle) connection-spec
+  (check-connection-spec connection-spec database-type (dsn  
&optional user password &key connection-string completion window- 
handle))
+  (destructuring-bind (dsn &optional user password &key connection- 
string (completion :no-prompt) window-handle)
+                      connection-spec
      (handler-case
         (let ((db (make-instance 'odbc-database
                                  :name (database-name-from-spec  
connection-spec :odbc)
                                  :database-type :odbc
                                  :dbi-package (find-package '#:odbc- 
dbi)
                                  :odbc-conn
-                                (odbc-dbi:connect :user user
-                                                  :password password
-                                                  :data-source-name dsn
-                                                   :connection- 
string connection-string
-                                                   :completion  
completion
-                                                   :window-handle  
window-handle))))
+                                 (if user
+                                     (odbc-dbi:connect :user user
+                                                       :password  
password
+                                                       :data-source- 
name dsn
+                                                       :connection- 
string connection-string
+                                                       :completion  
completion
+                                                       :window- 
handle window-handle)
+                                     (odbc-dbi:connect :data-source- 
name dsn
+                                                       :connection- 
string connection-string
+                                                       :completion  
completion
+                                                       :window- 
handle window-handle)))))
           (store-type-of-connected-database db)
           ;; Ensure this database type is initialized so can check  
capabilities of
           ;; underlying database



Harold Lee
[email protected]


On Feb 28, 2006, at 8:00 AM, Kevin Rosenberg wrote:

> Harold Lee wrote:
>> To begin with, let me say that I know DB2 is not supported. I'm
>> curious if the work on DB2 is ongoing, or stalled (maybe waiting for
>> a developer to help?).
>
> I started work on the DB2 backend a few years ago. But stopped working
> on it when I saw the semantics for DB2 were very close to ODBC. Based
> on that, I thought that there wouldn't be a big overhead using ODBC to
> access DB2 since the ODBC library for DB2 is likely a thin wrapper.
>
>> I found that I could connect if I change the code in db2-sql.lisp for
>> DB2-CONNECT to use nil instead of SQL_NULL_HANDLE (which slime says
>> evaluates to 0). The problem is that DB2 defines SQL_NULL_HANDLE as 0
>> in the C header file sqlcli.h but DB2 is expecting a NULL pointer as
>> the input handle to sql-alloc-handle. Since 0 and nil are different
>> in Lisp, maybe you want to change the definition of SQL_NULL_HANDLE
>> in db2-constants.lisp to be nil instead.
>
> Yes, that's a reasonable change.
>
>> Here's the output from DB2 verifying that I got connected:
>
> Very good.
>
>> From there it looks like most of the functionality is missing, such
>> as methods for the generic functions database-query and database-
>> execute-command. Also, there is a lot less code in the DB2
>> subdirectory than in the MySQL directory.
>
> Yes, other functionality has not been written given my thoughts that
> the potential benefit was likely low.
>
>> Should I provide more feedback as I make progress?
>
> If you'd like to finish the DB2 interface, I'd be glad to add your
> code to CLSQL. Currently, I don't have a DB2 system for testing,
> though. So, I'd end up accepting your DB2 patches as untested.
>
> -- 
> Kevin Rosenberg
> [email protected]
>