CODEWITHSUNDEEP
pythonblobcx-oracle
Leo
Leo

Reputation: 1809

cx_oracle how to update blob column

could anyone help with how to update blob data in oracle

so, i'm trying like:

file = open ('picture.jpg','rb') 
ext = 'jpg'
content = file.read ()
file.close ()
db = cx_Oracle.connect('user', 'pwd', dsn_tns)
db=db.cursor()
sqlStr = "update table_name set column1=:blobData, column2=" + str(ext) + " where id = 1"
db.setinputsizes (blobData = cx_Oracle.BLOB)
db.execute (sqlStr, {'blobData': content})
db.execute ('commit')
db.close()

finally, I got such error:

cx_Oracle.DatabaseError: ORA-00904: "JPG": invalid identifier

Upvotes: 4

Views: 5239

Answers (2)

Gareth Boyes
Gareth Boyes

Reputation: 53

cx_Oracle 6.0 does not allow you to close a connection whilst a BLOB is open leading to a DPI-1054 error.

cx_Oracle.DatabaseError: DPI-1054: connection cannot be closed when open statements or LOBs exist

Adding to Leo's answer, this can be resolved by deleting the BLOB variable.

file = open ('picture.jpg','rb') 
ext = 'jpg'
content = file.read ()
file.close ()
con = cx_Oracle.connect('user', 'pwd', dsn_tns)
db = con.cursor()
blobvar = db.var(cx_Oracle.BLOB)
blobvar.setvalue(0,content)
sqlStr = "update table_name set column1=:blobData, column2="jpg" where id = 1"
db.setinputsizes (blobData = cx_Oracle.BLOB)
db.execute (sqlStr, {'blobData': blobvar})

del blobvar  #  <-------- 

db.execute ('commit')
db.close()
con.close()

Upvotes: 2

Leo
Leo

Reputation: 1809

file = open ('picture.jpg','rb') 
ext = 'jpg'
content = file.read ()
file.close ()
db = cx_Oracle.connect('user', 'pwd', dsn_tns)
db=db.cursor()
blobvar = db.var(cx_Oracle.BLOB)
blobvar.setvalue(0,content)
sqlStr = "update table_name set column1=:blobData, column2="jpg" where id = 1"
db.setinputsizes (blobData = cx_Oracle.BLOB)
db.execute (sqlStr, {'blobData': blobvar})
db.execute ('commit')
db.close()

Upvotes: 5

Related Questions