Notes

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.

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

2. Select the following key in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC

3. On the Security menu, click Permissions.

4. Type the required permissions for the account that is accessing the Web page.

5. Quit Registry Editor

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.

performance

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:

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.

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?

Microsoft ODBC driver for Oracle

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

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.

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

Maybe this depends on the version of MySQL or some system parameter.

Parameters

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.

PostgreSQL, 8.3

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.

Sqlite

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.

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!