Python Oracle 10g odbc blob insertion problem

[Godzilla]

Dear all,

I cannot find a solution for my problem with inserting a blob object
(>4000 in length) into an ORACLE database via ODBC.


I have tried the two ways of inserting the blob object (a zip file):


1)
fp = open("c:/test/test.zip", "r+b")
data = fp.read()
s = odbc.odbc(cs)
qry = s.cursor()
qry.execute("Insert into tBlob (data) values ('%s')" %
data.encode('hex'))


return the error: Input String Too Long Limit: 4096


2)
qry.execute("Insert into tBlob (data) values (?)",
data.encode('hex'))


does not return error, but it does not insert the record correctly.


Any help will be fully appreciated...

 

[Steve Holden]

Dear all,

I cannot find a solution for my problem with inserting a blob object
(>4000 in length) into an ORACLE database via ODBC.


I have tried the two ways of inserting the blob object (a zip file):


1)
fp = open("c:/test/test.zip", "r+b")
data = fp.read()
s = odbc.odbc(cs)
qry = s.cursor()
qry.execute("Insert into tBlob (data) values ('%s')" %
data.encode('hex'))


return the error: Input String Too Long Limit: 4096


2)
qry.execute("Insert into tBlob (data) values (?)",
data.encode('hex'))


does not return error, but it does not insert the record correctly.


Any help will be fully appreciated...

Try, just on the off chance:

qry.execute("Insert into tBlob (data) values (?)",
(data.encode('hex'), ))


The second argument to execute should always be a tuple. And be aware
that the odbc module hasn't had much maintenance lately.

regards
Steve

 

[Paul Boddie]

I cannot find a solution for my problem with inserting a blob object
(>4000 in length) into an ORACLE database via ODBC.

This brings back "happy" memories with Oracle 9i and JDBC.

I have tried the two ways of inserting the blob object (a zip file):

1)
fp = open("c:/test/test.zip", "r+b")
data = fp.read()
s = odbc.odbc(cs)
qry = s.cursor()
qry.execute("Insert into tBlob (data) values ('%s')" %
data.encode('hex'))

return the error: Input String Too Long Limit: 4096

2)
qry.execute("Insert into tBlob (data) values (?)",
data.encode('hex'))

does not return error, but it does not insert the record correctly.

Any help will be fully appreciated...

If I recall correctly, JDBC provided an API to stream the contents
into the column. That is, you needed to provide an instance of one of
the mutually incompatible Java stream classes (I don't remember which
one) when setting the value, and then the database would suck up the
contents. It's quite possible that a similar mechanism involving file-
like objects exists in the different Python modules for Oracle,
although I haven't checked.

Paul

 

[Steve Holden]

Dear all,

I cannot find a solution for my problem with inserting a blob object
(>4000 in length) into an ORACLE database via ODBC.


I have tried the two ways of inserting the blob object (a zip file):


1)
fp = open("c:/test/test.zip", "r+b")
data = fp.read()
s = odbc.odbc(cs)
qry = s.cursor()
qry.execute("Insert into tBlob (data) values ('%s')" %
data.encode('hex'))


return the error: Input String Too Long Limit: 4096


2)
qry.execute("Insert into tBlob (data) values (?)",
data.encode('hex'))


does not return error, but it does not insert the record correctly.


Any help will be fully appreciated...

I would certainly recommend that you think about using the cxOracle
package rather than relying on odbc. Most Orcale users do, with evident
satisfaction.

regards
Steve

 

[Godzilla]

I would certainly recommend that you think about using the cxOracle
package rather than relying on odbc. Most Orcale users do, with evident
satisfaction.

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://del.icio.us/steve.holden
Recent Ramblings http://holdenweb.blogspot.com- Hide quoted text -

- Show quoted text -

Thanks guys for all your help.

Steve, I think I've tried what you have suggested without any luck as
well... The statement works fine, but what inserted is not correct...
it seems like only the symbol '?' was inserted into the blob field...

I will try the suggested cxOracle library in place of odbc as a
trial... but I think since 95% of the code is using odbc, it would be
hard to convince the team to chance to the new library... anyway, I
will let you know how it goes.

 

[Dennis Lee Bieber]

Steve, I think I've tried what you have suggested without any luck as
well... The statement works fine, but what inserted is not correct...
it seems like only the symbol '?' was inserted into the blob field...

You didn't have a set of 's around the ?, did you? Parameter
substitution will add needed quotes on its own rather than you having to
put in quotes.

Also, though I find no documentation on it, odbc module cursors have
setinputsizes() and setoutputsizes() methods -- perhaps that could
change things... OTOH: the db-api 1.0 PEP (which is, it seems, what odbc
module follows) says they may be do-nothing methods.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Godzilla]

You didn't have a set of 's around the ?, did you? Parameter
substitution will add needed quotes on its own rather than you having to
put in quotes.

Also, though I find no documentation on it, odbc module cursors have
setinputsizes() and setoutputsizes() methods -- perhaps that could
change things... OTOH: the db-api 1.0 PEP (which is, it seems, what odbc
module follows) says they may be do-nothing methods.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

Gidday Dennis,

Thank you for your suggestion. I have found those two functions you
mentioned, and there were not much or no documentation for those
functions... I used both function and they pretty much do nothing for
me...

 

[Godzilla]

You didn't have a set of 's around the ?, did you? Parameter
substitution will add needed quotes on its own rather than you having to
put in quotes.

Also, though I find no documentation on it, odbc module cursors have
setinputsizes() and setoutputsizes() methods -- perhaps that could
change things... OTOH: the db-api 1.0 PEP (which is, it seems, what odbc
module follows) says they may be do-nothing methods.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

Everything seems to work fine right now... thanks all of you for
helping... Have a great day..

 

[Steve Holden]

Everything seems to work fine right now... thanks all of you for
helping... Have a great day..

Have you any idea what fixed the problem?

regards
Steve

 

[Godzilla]

Have you any idea what fixed the problem?

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://del.icio.us/steve.holden
Recent Ramblings http://holdenweb.blogspot.com- Hide quoted text -

- Show quoted text -

Hi Steve,

I guess Dennis and yourself pointed me to the right direction; no need
the 's around ? and also the parameter substitution requires tuple.
I.e.:

qry.execute("Insert into tBlob (data) values (?)",
(data.encode('hex'),))

Thanks again guys... it's been greatly appreciated by the team here as
well.