[Zope-DB] DCO2 and CLOBS

[Matthew T. Kromer]

Geir B=E6kholt wrote:

>zope-db,
>
>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 ?
>
>

Hi Geir,

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=
n).

=20