[Zope-dev] DCOracle and BLOBs Handling

[Matthew T. Kromer]

Cheuk Cheng wrote:

> Hi Everyone:
>
> In the past couple weeks, I started looking at Zope for my future
> development work.  One of the requirements is to be able to upload and
> download Oracle blob via Zope.  In the last few days, I spent most of my
> time searching through the mailing list for help from installing DCOracle to
> inserting blob from an external method.  So far I've successfully used
> DCOracle to create an Oracle connection in an external method and performed
> some simple db tasks.  Now here're my questions for all the zope gurus:
> 1.      Can I use ZSQL method to insert blob into Oracle?  It works for
> querying blob out of the database.  Why doesn't it work in insertion?
> 2.      In an external method can I get a hold of the connection object from
> zope by doing these:
>
> import  Shared.DCOracle
> def     sql_exec(self, param)
> .....
> conn = self.oracle_connection
> cur = conn.cursor()
> cur.execute(sqlstat)
> .....
>
>                 When I execute the function from Zope, it keeps giving me an
> AttributeError for the cursor.  Can someone please explain?  And if I create
> the db connection inside using
>                 conn = Shared.DCOracle.Connect('scott/tiger@oracledb'), I'll
> be getting the cursor successfully.
>
> 3.      With the connection created in the external method, I thought I
> could make the blob insertion work.  Obviously, I'm wrong!  I keep getting
> an error about invalid hex number "oci.error: (1465, 'ORA-01465: invalid hex
> number\012')".  The way I'm testing the insertion is to select a blob from
> one table and try to insert it in another as follow:
>
> cur.execute('select  no, blob_data from res_info where no=1000')
> data = cur.fetchall()
> a,b = data[0]
>         image = (100, b)
>         cur.execute('insert into test_info (no, data) values(:p1, :p2)',
> image)
>
>                 I found that the returned "data" is a list of tuple.  So I
> get the first tuple and unpack it. Then I created a new tuple and tried to
> insert it into the test_info table.  And I keep getting the invalid hex
> number  error.  When I took this and converted it to a standalone program
> with a print statement for 'b', it printed out the correct binary data.  So
> I suspect that I may have to convert the binary stream into a string before
> I can do the insertion?  I also tried to set b = 'ff00ff00ff'  (a text
> string of hex numbers), and it didn't complain about it.  Can someone please
> tell me how blob shall be handled?
>
> Thanks in advance.
>
> - Cheuk

Can you provide what rev of Oracle you're using, the platform, OS, maintenance
level, etc?  Oracle 8.0.5 runs a lot differently then 8.1.5 (e.g. Oracle 8i).
Sadly, code that runs correctly on one release/platform may not function
properly on another.

If you can provide enough for me to replicate the problem I can try to take a
look at it, particularly if you can generate some test sql etc. that reproduces
only that problem (such that I can spend as little time as possible trying to
recreate what you are seeing.)