Oracle BLOBs with Common SQL

Hi,

Recently I have been working a bit with Common SQL (+ LispWorks 4.3.7
on Windows 2000, ODBC driver) and unfortunately I have to use Oracle
BLOBs for one of our applications.

BLOBs don't seem to be officially supported, but they paritally work.
So if I define the following table:
 
    create table test (
      id number(10) primary key,
      data blob
    );

and insert some data with another tool I can next read the inserted BLOBs:

    (setf rows (with-connection *db-conn-spec*
                 (select [id] [data] :from [test])))

The BLOB column is read as:

    (SIMPLE-ARRAY (UNSIGNED-BYTE 8) (105814))

I can also insert some strings into the column:

    (with-connection *db-conn-spec*
      (insert-records :into [test]
                      :attributes '(id data)
                      :values '(1 "simple string")))

but if the string contains null character (of code 0) then it is truncated :-(
If I try to insert a vector of unsigned-bytes:

    (setf v (make-array 5 :element-type '(unsigned-byte 8)))
    ...
    ; v --> #(72 101 108 108 111 33)

I get the following error coming from sql::execute-with-sqlbindparameter:

    CL-USER 46 > (with-connection *db-conn-spec*
                  (insert-records :into [test]
                                  :attributes '(id data)
                                  :values '(1 v)))

    Error: Failed to allocate foreign object of size 2147483648 [allocator NIL]
      1 (abort) Return to level 0.
      2 Return to top loop level 0.

And that's all I can get.  Do you have any further ideas / experiences with
registration of BLOB?

Kind regards,

Jaroslaw

--
Jarosław Tomczak, Ph.D.
Aventis Pharma Deutschland GmbH
DI&A Lead Generation Chemoinformatics
Industriepark Hoechst, H840, room 114
D-65926 Frankfurt / Main
phone: +49 (69) 305-14710
fax: +49 (69) 305-13068