[Zope] DCOracle data types

[Jeffrey Shell]

> From: paulabrams@yahoo.com
> I need to store a large amount of textual data in oracle 8 
> using a zsql
> method. I used type BLOB for the column, but when I try to save to I
> get the error message "oci.error ... Invalid type code, 113, 
> for column 7"
>
> Column 7 is type BLOB in the oracle database, but in my SZQL method I
> try to update that field with "<!--#sqlvar body type=string-->". 
> 
>  - Does Zope know how to handle BLOBs?

Not automatically, to the best of my knowledge.

>  - Should I be using a different SZQL type than "string"? 

Quite likely.

>  - Is there is a different data type I should be using in the 
> database?

Very likely.

I haven't dealt with BLOB's or other large-objects in Oracle 8, but I
have done it (a little bit) in Sybase.  Well, a text BLOB (field of type
'text').  If it's textual data, you might not need a BLOB since the
first B stands for Binary.  See first if Oracle has a long-text data
type (not VARCHAR2(6000) :-) and try using that instead.  I have used
this with sqlvar's string and nb type before to stuff Pickled versions
of data input from humungous HTML forms, and it worked fine.

Then, I'd use the 'Test' view (tab) of the SQL method since that will
show you the SQL rendered by Zope and sent to the DA.  This way you can
see how it's formatting your data.  I'm pretty sure that a SQLVAR of
type String or NB will not work with BLOB's because part of SQLVAR's job
is to type-check (ie, making sure that the character 'a' isn't being
sent t o a type 'float') and quote (in the case of strings).

So if BODY's value is "bill's bike shop", <!--#sqlvar body
type=string--> will render::

'bill''s bike shop'

The surrounding single-quotes are automatically inserted, and any
single-quotes inside the string are properly escaped (from ' to '').
I'd bet that Oracle is seeing the quotes and percieving the insertion of
a text value where it's expecting Binary.

So my suggestions are (and again, I'm nothing close to an Oracle guru):

 - See if there's a textual equivalent of BLOB for storing text of large
and unknown sizes in Oracle.  (In Sybase, this a type 'text').  If there
is and you can change the column type to that, I would recommend this
first since sqlvar will work (again, it does with Sybase.  Not sure
about Oracle.  It's a good thing that the S in SQL doesn't stand for
'standard':)

 - Or, look at the SQL being generated by the SQL Method when you try to
do the insert and compare that with how you would do it by hand in
Oracle.  If it doesn't look right, try using a normal #var tag instead.
Keep in mind that Binary objects in databases may have to be formatted
specially and Zope doesn't automatically handle this.

Just a couple of options you might try.

.jPS