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

[Chris Clark]

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()