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