Notes

driver-connect

Using driver-connect one does not have to create an ODBC datasource for every database one wants to use. The necessary parameters for the driver manager/driver are supplied via connection string. Some connection strings examples for using driver-connect One way to get a connection string is to create a file dsn and have a look at its contants, note that userid and password are encrypted in this file,

Problems using the Jet driver on windows

It seems that the jet driver needs to have write access to the registry directory HKEY_LOCAL_MACHINE\SOFTWARE\ODBC, see PRB: Error Message: 0x80004005: General Error Unable to Open Registry Key . In the mentioned document these steps are recommended

driver-connect, another approach

It seems that windows creates a temporary dsn if you try to connect via driver connect and if use the key DRIVER=????. If security is high on the computer this will fail. Another option is to create for each kind of datasource a default datasource with the correct driver.

performances

It seems that with clisp storing and retrieving C datatypes from memory take a lot time. This should be possible without consing, maybe I have to change my clisp FFC module.

access

For character parameters Access requires that the columnsize argument is set to a value smaller than 255. Oracle and sql server do not care. It sems that the columns size parameter must depend on the driver.

lobs and oracle

There is a problem with stored procedures with lob parameters. If the actual parameter is longer than 32513, then an error occurs: 
[Oracle][ODBC][Ora]ORA-01460: Nicht implementierte oder sinnlose Umwandlung gefordert
, error code 1460, State: 37000.
When doing an insert,update there is no problem. I have found he following in V$SQL:
insert into test (id,text) values(23* :1,:2) RETURNING "TEXT" INTO :TEXT

line end and oracle

Oracle has problems with ascii(13) (or ^M) in create procedure statements (I supect this is the holds for all PL/SQL statements). They are accpeted in SQL statements. It seems that CLISP replaces this character on Windows with chr(10). Allegro does not do this. Therefore some code that runs fine with CLISP causes an error on with Allegro. 

[24]> (exec-command  *con* (concatenate 'string "create or replace procedure b99 (x integer) as begin null;" (string (code-char 32)) " end;"))

NIL
[25]> (exec-command  *con* (concatenate 'string "create or replace procedure b99 (x integer) as begin null;" (string (code-char 13)) " end;"))

WARNING:
[Oracle][ODBC][Ora]Trigger, procedure or function created with PL/SQL compilation error(s)., error code 24344, State: S1000.
NIL
[26]> (exec-command  *con* (concatenate 'string "create or replace procedure b99 (x integer) as begin null;" (string (code-char 10)) " end;"))

NIL
[27]> (exec-query *con* (concatenate 'string "select " (string (code-char 13)) " * from dual"))

(("X")) ;
("DUMMY")

MYODBC and unicode

MYODBC (3.51?) does not return unicode character data as datatype SQL_WVARCHAR. If for SQL_VARCHAR data the return datatype is set to SQLWCHAR, the length in the indicator is 0. It seems that the buffer is also empty (= #x000000000 ...). There is some talk about better support for unicode in another version (3.52?).

Oracle 9.2 and unicode

I can not make Oracle return unicode. I am using a 9.2 DB I have created a nchar column which is a unicode datatype for 9.2.

I am using the oracle driver. There is a workaround tab where one can force the driver to return string data as SQL_WCHAR. Storing unicode strings with parameters of type :unicode-string inserts some dummy character (a '?' standing on its head). Selecting nchr(1000) returns the same charcater.
Only selecting from NLS_SESSION_PARAMETERS returns a 16bit charcater. Or do I have this problem since NLS_CHARACTERSET=WE8ISO8859P15?

Microsoft ODBC driver for Oracle

Currently it does not work on my computer. I get the error message: 
*** - [Microsoft][ODBC driver for Oracle][Oracle], error code 0, State: NA000.
Visual Studio 6.0 has the same problem. Is 9.2 supported by Microsoft ODBC-Driver?

Oracle and ref cursors

This is easy with the oracle odbc driver and an 9.2 database. Example: 
create or replace package test99_pkg as        
  type refcursor is ref cursor;       
  procedure test_cursor(v varchar2,c in out refcursor);      
end;

create or replace package body test99_pkg as       
  procedure test_cursor(v varchar2,c in out refcursor) is
  begin         
    open c for select v as a,'1234567890' as b from dual; 
  end;      
end;
The cursor can be retrieved with following lisp code: 
(with-prepared-statement (stm con 
                                "{call test99_pkg.test_cursor(?,?)}" 
                                '((:string :in )))
    (let ((str "just a string"))
      (exec-prepared-query stm (list str))))
Note that the cursor parameter must be declared in/out. If a parameter ist supplied for the cursor parameter, it still works. This needs more investigations.

Oracle TIMESTAMP datatype in 9.2, 10g

Windows: For the Oracle-Driver, queries on TIMESTAMP columns return SQL_NULL_TYPE. Normally this would be retrieved as String. But the program coredumps at the first fetch. The Microsoft driver coredumps earlier, namely when the sqlexec is executed.
Columns of type SQL_NULL_TYPE cause an error, the offending column has to be removed from the query. At least we prevent the coredump of the Oracle driver.
I am pretty sure that this is a driver bug, Microsoft Access coredumps as well in similar situations.