[Zope-DB] DCO2 and CLOBS
Matthew T. Kromer
Mon, 26 Nov 2001 10:20:10 -0500
Geir B=E6kholt wrote:
>Has anyone succeeded in adding data to an oracle CLOB through
>ZSQL-methods , or do i need to go directly for external
>methods/stored procedures to accomplish this ?
You might need to fiddle around a bit with a combination of SQL method=20
and python script.
You can insert an EMPTY_CLOB() into a table row, then select it for=20
update; but DCO2 isn't smart enough to let you instantiate a LOB outside=20
of Oracle and then insert it automatically.
So, you might
INSERT INTO MY_TABLE (MY_CLOB, LOBID) VALUES ( EMPTY_CLOB(), MY_LOBID=
SELECT MY_CLOB FROM MY_TABLE WHERE LOBID =3D MY_LOBID FOR UPDATE
then you'll get a writable LOB object back from the database, so you can=20
call its read() write() trim() and length() methods. You'll notice the=20
problem that unless you use a stored procedure, you've got two SQL=20
statments being executed. Fortunately, DCO2's ZOracleDA provides stored=20
procedure wrappers, to make invoking Oracle stored procedures from Zope=20
a bit easier (although the default permisison excludes execute=20
permission so you have to enable it explicitly -- just to 'help' people=20
accidentally not expose their stored procedures to the world).
You have to usually do an extra gymnastic step to get the LOB object,=20
since the result from a SQL method is a result set, so you have to pull=20
out the first cell (iterate over "all" of 1 rows then use the named colum=