Notes on Plain-Odbc

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

using the excel driver "DRIVER={Microsoft Excel Driver (*.xls)}; DBQ=C:/TEMP/Book1.xls"
using the access driver: "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:/temp/Copy of OMR TT Reports.mdb"
using an Oracle Driver "DRIVER=Oracle in oracl92;UID=SCOTT;PWD=tiger;server=ltrav1"

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

1. Start Registry Editor (Regedt32.exe).
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:

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

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)

#\LATIN_SMALL_LETTER_E_WITH_GRAVE
[14]> 

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

1234

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

MySQL

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")) ;
("a")
[131]> (exec-query *con* "select cast(cast(1.5e0/3.9e0 as decimal(40,20)) as char(200)) as a")

(("0.38461538461538460000")) ;
("a")

In clisp on x86 I get:

[132]> (/ 1.5d0 3.9d0)

0.38461538461538464d0

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

[33]>  (exec-command *con* "
    create procedure test99(a1 varchar(200),b1 int,c1 date) 
    begin
    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;
    end;")


[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")
[35]>

PostgreSQL

http://sql-info.de/postgresql/postgres-gotchas.html

BLOBS

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 $$
    begin 
    insert into testtab99 (a) values(a+1);
    end; 
   $$ 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.

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.

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

1. Trace: (PLAIN-ODBC::COLUMN-INFO-TO-CLASS-AND-ARGS '12 '254 '0)
1. Trace: PLAIN-ODBC::COLUMN-INFO-TO-CLASS-AND-ARGS ==> PLAIN-ODBC::STRING-COLUMN, NIL
1. Trace: (PLAIN-ODBC::COLUMN-INFO-TO-CLASS-AND-ARGS '12 '255 '0)
1. Trace: PLAIN-ODBC::COLUMN-INFO-TO-CLASS-AND-ARGS ==> PLAIN-ODBC::STRING-COLUMN, NIL
1. Trace: (PLAIN-ODBC::COLUMN-INFO-TO-CLASS-AND-ARGS '-1 '256 '0)
1. Trace: PLAIN-ODBC::COLUMN-INFO-TO-CLASS-AND-ARGS ==> PLAIN-ODBC::CLOB-COLUMN, NIL
NIL ;
("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.