Re: [cx-oracle-users] Streaming to and from BLOBs?

[Mihai I. <mi...@re...>]

On Tue, Feb 07, 2006 at 09:15:22AM -0700, Anthony Tuininga wrote:
> Its a BLOB so yes, you have a means of streaming this data. What you
> need to do is something like the following:
> 
> bigFileVar = cursor.var(cx_Oracle.BLOB)
> cursor.execute("""
>         insert into blobtable (bigfile) values (empty_blob())
>         returning bigfile into :1""", [bigFileVar])
> f = file('gigantic.bin', 'rb')
> offset = 1
> while True:
>     data = f.read(SOME_SIZE)
>     if not data:
>         break
>     bigFileVar.write(data, offset)
>     offset += len(data)
> 
> Something like that should do the trick.

This is definitely more elegant than the "classic" two-step approach:

insert into blobtable (bigfile) values (empty_blob());
select bigfile from blobtables where some_condition_to_identify_row for update
of bigfile;

Note the "for update" clause of select - this will lock the blob.

But you have to keep in mind, the "returning foo into blah" syntax is Oracle
specific, whereas the two-step approach should be more portable across various
RDBMSes.

HTH,
Mihai

> On 2/6/06, Bradley Lawrence <bra...@ra...> wrote:
> > Hi folks. I'm having a problem with inserting some particularly large
> > files in a BLOB field. The particular file in question is 1.5GB though
> > there are others which are similar in size. (I believe it is an awful
> > idea to have these files in the database, but it is unfortunately
> > non-negotiable at this point) An example snippet of my file-to-blob glue
> > code is as follows:
> >
> > ...
> > f = file('gigantic.bin','rb')
> > cur = db.cursor()
> > sql = """INSERT INTO BLOBTABLE (BIGFILE) VALUES (:1)"""
> > cur.execute(sql, f.read())
> > ...
> >
> > Basically I'm reading the whole file into memory before passing it into
> > the cursor, which naturally causes an out of memory exception.
> >
> > Does cx_Oracle have any way of dealing with this problem? What's a
> > better way of inserting this file into the database?
> >
> > --
> >
> > Bradley Lawrence
> > Application Developer, RapiData
> > Rapid Technology Corporation
> > (403) 209-3000 ext. 237
> >
> >
> >
> >
> >
> > -------------------------------------------------------
> > This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
> > for problems?  Stop!  Download the new AJAX search engine that makes
> > searching your log files as easy as surfing the  web.  DOWNLOAD SPLUNK!
> > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
> for problems?  Stop!  Download the new AJAX search engine that makes
> searching your log files as easy as surfing the  web.  DOWNLOAD SPLUNK!
> http://sel.as-us.falkag.net/sel?cmd_______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users