cx-oracle-users

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

[Bradley L. <bra...@ra...>]

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

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

[Anthony T. <ant...@gm...>]

Its a BLOB so yes, you have a means of streaming this data. What you
need to do is something like the following:

bigFileVar =3D cursor.var(cx_Oracle.BLOB)
cursor.execute("""
        insert into blobtable (bigfile) values (empty_blob())
        returning bigfile into :1""", [bigFileVar])
f =3D file('gigantic.bin', 'rb')
offset =3D 1
while True:
    data =3D f.read(SOME_SIZE)
    if not data:
        break
    bigFileVar.write(data, offset)
    offset +=3D len(data)

Something like that should do the trick.

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 =3D file('gigantic.bin','rb')
> cur =3D db.cursor()
> sql =3D """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 fi=
les
> 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=3Dlnk&kid=3D103432&bid=3D230486&dat=
=3D121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

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

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

[Bradley L. <bra...@ra...>]

Thank you, Anthony!

This worked very well, although I needed to change 
"bigFileVar.write(data, offset)" to "bigFileVar.getvalue().write(data, 
offset)" to make it work.


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.
>
>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=k&kid3432&bid#0486&dat1642
>_______________________________________________
>cx-oracle-users mailing list
>cx-...@li...
>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>  
>


-- 

Bradley Lawrence
Application Developer, RapiData
Rapid Technology Corporation
(403) 209-3000 ext. 237

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

[Anthony T. <ant...@gm...>]

Glad to hear it. You might want to, then, do the following __after__
the execution of the insert statement.

blob =3D bigFileVar.getvalue()

and then use blob.write(data, offset)

just to avoid the overhead of acquiring the LOB variable over and over
again -- its not much but just to be complete.... :-)

On 2/7/06, Bradley Lawrence <bra...@ra...> wrote:
> Thank you, Anthony!
>
> This worked very well, although I needed to change
> "bigFileVar.write(data, offset)" to "bigFileVar.getvalue().write(data,
> offset)" to make it work.
>
>
> 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 =3D cursor.var(cx_Oracle.BLOB)
> >cursor.execute("""
> >        insert into blobtable (bigfile) values (empty_blob())
> >        returning bigfile into :1""", [bigFileVar])
> >f =3D file('gigantic.bin', 'rb')
> >offset =3D 1
> >while True:
> >    data =3D f.read(SOME_SIZE)
> >    if not data:
> >        break
> >    bigFileVar.write(data, offset)
> >    offset +=3D len(data)
> >
> >Something like that should do the trick.
> >
> >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 glu=
e
> >>code is as follows:
> >>
> >>...
> >>f =3D file('gigantic.bin','rb')
> >>cur =3D db.cursor()
> >>sql =3D """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=3Dlnk&kid=3D103432&bid=3D230486&dat=
=3D121642
> >>_______________________________________________
> >>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 f=
iles
> >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=3Dk&kid=103432&bid#0486&dat=121642
> >_______________________________________________
> >cx-oracle-users mailing list
> >cx-...@li...
> >https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
> >
>
>
> --
>
> 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 fi=
les
> 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=3Dlnk&kid=3D103432&bid=3D230486&dat=
=3D121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>