[Baypiggies] Reading file and inserting BLOB in oracle database.

[Prasanna Rajagopal]

Hi
Thank you for your reply.  Is there something similar to "ingresdbi" for
Oracle database, in other words, which python oracle driver I should
use?  I tried ADODB for python, but I felt it does not offer much
support for BLOB handling.  I am new to Python, so I might be wrong.  

I tried the following:

file_object = open('C:\insertUpdateSelectBLOB\CoffeeBean.bmp','rb')

data = file_object.read()
 
sql = "insert into blob_test (blobtestid, blob_field) values
(101,empty_blob())"

oraCur = oraConn.Execute(sql)

oraConn.UpdateBlob('BLOB_TEST','BLOB_FIELD', data, 'BLOBTESTID = 101')

This does not work.

-Prasanna

-----Original Message-----
From: Chris Clark [mailto:Chris.Clark at ingres.com] 
Sent: Wednesday, July 26, 2006 10:26 AM
To: Prasanna Rajagopal
Cc: baypiggies at python.org
Subject: Re: [Baypiggies] Reading file and inserting BLOB in oracle
database.

Prasanna Rajagopal wrote:
>
> I am trying to read a file, create a BLOB object and insert that BLOB 
> object into an Oracle column.  Then retrieve the BLOB from Oracle and 
> write it to a file again.  I was wondering if I could get any help on 
> this.  If there is any code snippet, that would be very useful. 
>

I don't have any code for Oracle but I do for Ingres ;-)

In theory the code below should be enough to get you going with Oracle, 
and if not (prepare for shameless plug) Ingres is under the GPL so give 
it a download http://www.ingres.com/products/Prod_Download_Portal.html 
(the DBMS and driver are near the bottom of the page). Please don't use 
this as a model of good style, it is is short as possible to demo DBI 
concepts.

# simple lob demo
import ingresdbi, sys

# Trivial command line args, no real checks performed
if len(sys.argv) != 3:
    print("Usage: blob.py dsn file_name")
    sys.exit()
dsn_str = sys.argv[1]
filename = sys.argv[2]
out_filename = filename+"_out"

# lob read
f = open(filename, "r")
blob = f.read()
f.close()

conn = ingresdbi.connect(dsn=dsn_str)
curs = conn.cursor()

#curs.execute("create table myBlob(blobCol long varchar)")
curs.execute("create table myBlob(blobCol long byte)")
curs.execute("insert into myBlob values(?)",(blob,))
curs.execute("select * from myBlob")
all_rows = curs.fetchall()
#print "all_rows", all_rows

# lob write
f = open(out_filename, "w")
f.write(all_rows[0][0])
f.close()
print "created out lob file", out_filename

curs.execute("drop myBlob")
curs.close()
conn.close()