473,320 Members | 1,794 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Python Oracle 10g odbc blob insertion problem

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...

Mar 22 '07 #1
8 3837
Godzilla wrote:
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
--
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

Mar 22 '07 #2
On 22 Mar, 10:21, "Godzilla" <godzillais...@gmail.comwrote:
>
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

Mar 22 '07 #3
Godzilla wrote:
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
--
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

Mar 22 '07 #4
On Mar 22, 10:56 pm, Steve Holden <s...@holdenweb.comwrote:
Godzilla wrote:
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
--
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.

Mar 22 '07 #5
On Mar 23, 4:38 am, Dennis Lee Bieber <wlfr...@ix.netcom.comwrote:
On 22 Mar 2007 05:36:46 -0700, "Godzilla" <godzillais...@gmail.com>
declaimed the following in comp.lang.python:
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
wlfr...@ix.netcom.com wulfr...@bestiaria.com
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: web-a...@bestiaria.com)
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...

Mar 22 '07 #6
On Mar 23, 4:38 am, Dennis Lee Bieber <wlfr...@ix.netcom.comwrote:
On 22 Mar 2007 05:36:46 -0700, "Godzilla" <godzillais...@gmail.com>
declaimed the following in comp.lang.python:
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
wlfr...@ix.netcom.com wulfr...@bestiaria.com
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: web-a...@bestiaria.com)
HTTP://www.bestiaria.com/
Everything seems to work fine right now... thanks all of you for
helping... Have a great day..

Mar 22 '07 #7
Godzilla wrote:
On Mar 23, 4:38 am, Dennis Lee Bieber <wlfr...@ix.netcom.comwrote:
>On 22 Mar 2007 05:36:46 -0700, "Godzilla" <godzillais...@gmail.com>
declaimed the following in comp.lang.python:
>>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
wlfr...@ix.netcom.com wulfr...@bestiaria.com
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: web-a...@bestiaria.com)
HTTP://www.bestiaria.com/

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

Mar 22 '07 #8
On Mar 23, 9:50 am, Steve Holden <s...@holdenweb.comwrote:
Godzilla wrote:
On Mar 23, 4:38 am, Dennis Lee Bieber <wlfr...@ix.netcom.comwrote:
On 22 Mar 2007 05:36:46 -0700, "Godzilla" <godzillais...@gmail.com>
declaimed the following in comp.lang.python:
>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
wlfr...@ix.netcom.com wulfr...@bestiaria.com
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: web-a...@bestiaria.com)
HTTP://www.bestiaria.com/
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
--
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.

Mar 23 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: ATK | last post by:
Hi, I'm trying to upload a image file to a oracle DB and i need to only use ODBC functions. In db i have a LONG RAW column (if this is not correct, please tell me). I'm getting the error from...
6
by: Dan Ellis | last post by:
Hi, I've been down so many dead ends trying to get something working, so I'm really hoping someone can help out. I need to access an Oracle database running on a Windows server from Python...
4
by: Morten Goodwin Olsen | last post by:
Python, PostgreSQL and bytea Hi I am having a bit of trouble inserting images into a postgres database using python. The images should be stored in a bytea field. I have tried both with an odbc...
1
by: X | last post by:
Howdy: To follow up on a problem with MS Access (2000) and Oracle 9i (9.2.0.x) - History: I am trying to import tables from my Oracle 9i database on RedHat Linux 7.2 to MS Access (2000) on...
11
by: Chris Fink | last post by:
I have setup an Oracle table which contains a blob field. How do I insert data into this field using C# and ADO.net?
2
by: Chenna Sudarsan | last post by:
Hello, I am connecting to Oracle9.2 database from VB6.0 using MSDAORA provider. I am using ADO2.5. When I try to access a blob column of oracle I am getting the below mentioned error. ...
17
by: dananrg | last post by:
I'm a little confused about what's out there for database modules at: http://python.org/topics/database/modules.html What I'd like to do is use Python to access an Oracle 9.X database for...
3
by: Gox | last post by:
Hi. This is my situation: Backend: Oracle 8i Frontend: MS Access 2k (ODBC driver support LOBs) Q: How insert (upload) a CLOB (pdf or doc file) to an Oracle using MS access (form)?? Q: How...
2
by: Vinod Sadanandan | last post by:
All, Below listed are the new features in Oracle 11g ,please join me in this discussion to generate a testcase and analyze each of the listed features . Precompilers:...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.