Oracle BLOBs with Common SQL (this time plain text)

I have send the email below this morning with my default Outlook
settings, so I do it correctly as plain text.

Sorry for inconvenience and duplicate mail.

***

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 



-- 
Jaroslaw 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