This documents contains some observations and pitfalls encountered when using different drivers and databases. Of course this list is not complete. Also some of these problems might have vanished in the mean time.


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.


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.


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;"))

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

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

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

(("X")) ;

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?).

More Information about Oracle ODBC Driver

There is documentation for the Oracle ODBC driver, it is at

OracleŽ Database Administrator's Reference
10g Release 2 (10.2) for UNIX-Based Operating Systems
Part Number B15658-05
G Using Oracle ODBC Driver
or better under the link G Using Oracle ODBC Driver, but this can change any time.

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?

Oracle 10gR2 and unicode

It works now (with Oracle driver!):
[13]> (schar (caar (exec-query *con* "select nCHR(232) from dual")) 0)


[16]> (char-code (schar (caar (exec-query *con* "select ? from dual"  (list (string (code-char 1234)) :unicode-string))  ) 0))


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 works with 9.2 and 10g and 10gR2. Make sure that the ODBC datasource is configured correctly. On the "Oracle ODBC Driver Configuration" window check the checkbox with label "Enable Result Sets".
create or replace package test99_pkg as        
  type refcursor is ref cursor;       
  procedure test_cursor(v varchar2,c in out refcursor);      

create or replace package body test99_pkg as       
  procedure test_cursor(v varchar2,c in out refcursor) is
    open c for select v as a,'1234567890' as b from dual; 
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 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

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.
10g R2
It works now, a timestamp column is retrieved as SQL_TIMESTAMP. Of course the sub second precision is not supported.


This is not one would expect!
MySQL> create view bla as select  date_add('2007-8-1',interval 1 day) as a;
Query OK, 0 rows affected (0.01 sec)

MySQL> desc bla
    -> ;
| Field | Type          | Null | Key | Default | Extra |
| a     | varbinary(29) | YES  |     | NULL    |       |
1 row in set (0.00 sec)
how to do it right:
MySQL> create view bla2 as select  date_add(cast('2007-8-1' as datetime),interval 1 day) as a;
Query OK, 0 rows affected (0.00 sec)

MySQL> desc bla2;
| Field | Type     | Null | Key | Default | Extra |
| a     | datetime | YES  |     | NULL    |       |
1 row in set (0.00 sec)

Be carefull with variables, declared with something like set @a =1. The types are automatically determined, and a type for dates does not exist.

Of course error handling in MySQL is not existent, maybe I should change the server settings:

MySQL> select cast(cast('12345678901' as decimal) as char) as a, cast(cast('1234567890' as decimal) as char) as b;
| a          | b          |
| 9999999999 | 1234567890 |
1 row in set, 1 warning (0.00 sec)

So there is no overflow error.

Division by zero is handled, but only for an insert:

MySQL> set @a=1;
Query OK, 0 rows affected (0.00 sec)

MySQL> set @b=0;
Query OK, 0 rows affected (0.00 sec)

MySQL> select @a/@b;
| @a/@b |
|  NULL |
1 row in set, 1 warning (0.00 sec)

MySQL> insert into blu select @a/@b;
ERROR 1365 (22012): Division by 0
Handling of numbers
[130]> (exec-query *con* "select cast(1.5e0/3.9e0 as char(200)) as a")

(("0.38461538461538")) ;
[131]> (exec-query *con* "select cast(cast(1.5e0/3.9e0 as decimal(40,20)) as char(200)) as a")

(("0.38461538461538460000")) ;
In clisp on x86 I get:
[132]> (/ 1.5d0 3.9d0)

With sql server I get the same value, as with clisp.

And another example :

MySQL> select 1e14/7e0,1e14/7e0-14285714285714;
| 1e14/7e0       | 1e14/7e0-14285714285714 |
| 14285714285714 |              0.28515625 |
1 row in set (0.00 sec)
The decimal expansion of 1/7 is periodic, so the digits 0.285 are correct. Thus MySQL uses the precision of double, but does not return all digits.

The SQL Parser

The /* ..*/ comments do not have to be closed:
19]> (exec-query *con3* "select 1 as a /*bla")

((1)) ;
Maybe this depends on the version of MySQL or some system parameter.


As mentioned in the documentation, MySQL does not support out and in/out parameters for sql statements. As the documentation of MySQL suggest one should declare variables, pass them as parameters to the stored procedures. After the procedure call one get there contents with a select statement, example: select @param1 as param1,@param2 as param2.

The problem with variables on MySQL is that one can only set them. It is not possible to declare a datatype for them. Another option is to select the parameters directly in the stored procedure. Thus instead of having out or in/out parameters, the procedure returns return values as a result set. Note that exec-query accepts parameters and can return more than one result set. One example, assume *con* is a MySQL connection.

[33]>  (exec-command *con* "
    create procedure test99(a1 varchar(200),b1 int,c1 date) 
    declare a2 varchar(200);
    declare b2 int;
    declare c2 date;
     set a2=concat(a1,'x');
      set b2=b1+3;
      set c2=c1+ interval 1 day;
      select a2 as a2,b2 as b2, c2 as c2;

[34]> (exec-query *con* "call test99(?,?,?)" "abc" 
                          (list 12 :integer) (list  (encode-universal-time 12 3 5 12 11 2007) :date))

(("abcx" 15 3403897200)) ;
("a2" "b2" "c2")

It seems that lobs are not supported. When executing an update with a BLOB I get:
*** - 
      ERROR: type "lo" does not exist;
      Error while executing the query, error code 7, State: 42704.
According to the documentation one has to run lo.sql from the contrib section. Blobs Columns have to be declared as of type lo.
SQL Parser
The parser is buggy.
 (plain-odbc:exec-query *con* "select 'a' as a,? as b " 1213)

(("a" 1213)) ;
("a" "b")
[77]> (plain-odbc:exec-query *con* "select 'a' as a,/* comment */ ? as b " 1213)

(("a" 1213)) ;
("a" "b")
[78]> (plain-odbc:exec-query *con* "select 'a' as a,/* comment ? */ ? as b " 1213)

*** - The # of binded parameters < the # of parameter markers, error code 32, State: 07002.
The following restarts are available:
ABORT          :R1      Abort main loop
Break 1 [79]> 

[80]> (plain-odbc:exec-query *con* "select 'a' as a,/* comment '?' */ ? as b " 1213)

(("a" 1213)) ;
("a" "b")

Maybe this the ODBC driver and not the database itself.
PostgreSQL, 8.3
Creating a function like
create function test99 ( a in integer ) returns void as $$
    insert into testtab99 (a) values(a+1);
   $$ language plpgsql;
gives an error. PG Sql does not like that the name of the column is the same as the name of the parameter. This is a well known feature.
Out Parameters
Out parameter will be returned as out parameters. But a function with out parameters must have as a return type record and the return values are returned as a result set with just one row.


The following depends as usual very much on the ODBC driver. This driver is not written by the people who wrote Sqlite.

Computed String columns are returnd as $SQL_VARCHAR of size 255. Of course this is not enough in many cases and data is truncated. And the indicated size for the value in a specific row can be larger than 255.

 (trace plain-odbc::column-info-to-class-and-args)
[61]> (plain-odbc:exec-command *con* "create table bla(a varchar(254),b varchar(255),c varchar(256))")

[62]> (plain-odbc:exec-query *con* "select * from bla")

("a" "b" "c")

So string columns which are larger than 255 are treated as SQL_LONGVARCHAR. It is possible to insert strings larger than column size into a column. It is possible to retrieve the value via ODBC's sql_get_data_raw???. So for Sqlite one should always retrieve variable length values with this method. Mismatch between raw and text!

It seems blob columns are reported as SQL_BINARY.