RE: Oracle BLOBs with Common SQL

Hi,

first of all, thanks for your quick responses.  I summarize
comment on them below.

> > 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 :-(
>
> [John DeSoi]
>
> I'm not familiar with Oracle, but in PostgreSQL you need to escape 
> non-printables (including nulls) as octal values:
>
> "mystring\\000"
>
> Since inserting strings works as long as you don't have nulls, you 
> might want to check the Oracle documentation to see if there is a 
> similar convention.

Unfortunately, Oracle is not as clever as PostgreSQL and generally
it doesn't allow escaping (except for single quotes and the SELECT
clause).

> > 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. 
> 
> [Dave Fox]
> 
> You appear to have passed the symbol v rather than its value.

You are right.  But even if it is fixed [ :values (list 1 v) ]
I get the same error message.  It seems that the only data type
allowed for values corresponding to BLOBs is string.

> But anyway, CLOB/BLOB types are not supported in Common SQL. The SQL
> types that we support directly for Oracle are CHAR, VARCHAR2, NUMBER,
> LONG, ROWID and DATE.

Yes, I know that, but for our applications we need BLOBs and,
to tell the truth, I prefer to invest some time to try to find
a way of using BLOBs with Common SQL rather than spend it on
boring J2EE-based development.

> In general it is helpful if, when you think you have found a bug, you
> supply a bug report including the stack backtrace. Instructions are at
> http://www.lispworks.com/support/bug-report.html. 

Actually, I don't think that the behavior with arrays described above
is a bug.  I simply used an unsupported data type (and its conversion)
and I failed.  But I hope that it might be possible to "unofficially"
fix sql::execute-with-sqlbindparameter, so that it accepts data types
other than string and sends it through ODBC to Oracle, which will
take responsability for possible errors.
 
> > And that's all I can get.  Do you have any further ideas / experiences with 
> > registration of BLOB? 
> 
> Other users (not on this list) did try using the Oracle LONG type to
> access CLOB data. I don't actually know whether they were successful.

CLOBs work without problems in my case (after upgrade to the newest
Oracle 9i ODBC driver).

Anyway, I will try further and inform you if I succeed.

Best regards,

Jaroslaw Tomczak