How to retrieve actual blob data rather than "oracle.sql.blob@" from an Oracle database over JDBC?

Question

We are retrieving data from an oracle database using Jython and JDBC but a BLOB field isn't returning the blob data. Instead of the actual blob data, we are seeing fields that look like this:

oracle.sql.BLOB@10e8881

If we do the same query in Razor SQL using a JDBC connection, we can see the blobs in the result text and they look fine. (We do not see the "oracle.sql.Blob" in the fields at all.)

This is the Python code and the SQL doing the query:

cur = connect_to_database()
query = "SELECT * FROM {} WHERE ROWNUM = 1".format(table_name)
cur.execute(query)
results = cur.fetchall()
print results

When we print the results, one of the columns has the value we show above. We aren't seeing the binary blob.

Here's how we are executing our Python script:

C:\jython2.7.0\bin\jython.exe C:\path_to_our_script.py

How do we get the actual binary data in the blob returned rather than the "oracle.sql.blob@" text we are seeing?

That isn't the value of the Blob, it is the default Object.toString() of Java. A blob object is just a handle to get data out of a database blob, it is not the value of the blob itself. You would need to call the java.sql.Blob methods to get the actual data. — Mark Rotteveel
– Mark Rotteveel, Commented Dec 20, 2019 at 8:20
@MarkRotteveel We seem unable to find that method in the Python SQL library. That appears to be a java library. How is that useable within the Python codebase we are using above? — Praxiteles
– Praxiteles, Commented Jan 7, 2020 at 22:21
You are using JDBC, which is Java-based, but behind some Python wrapper. Check the documentation of that wrapper or contact the author. — Mark Rotteveel
– Mark Rotteveel, Commented Jan 8, 2020 at 8:05

Dmitry.M · Accepted Answer · 2020-01-10 15:39:31Z

If you have an option to upgrade to zxJDBC instead of pure JDBC then you would get support for blob to array conversion out of the box.

create table T_BLOB (b BLOB,c INTEGER);
insert into t_blob(b,c) values ( utl_raw.cast_to_raw('test1'),1);
insert into t_blob(b,c) values ( utl_raw.cast_to_raw('test2'),1);
commit;

Get blob and int values:

from com.ziclix.python.sql import zxJDBC

def connect():
    p = {}
    p['URL'] = 'jdbc:oracle:thin:user/[email protected]:1521:dev'
    db = apply(zxJDBC.connectx, ("oracle.jdbc.pool.OracleDataSource",), p)    
    return db.cursor()

cur = connect()
query = "SELECT * FROM t_blob"
cur.execute(query)
print cur.fetchall()

It prints:

[(array('b', [116, 101, 115, 116, 49]), 1.0), (array('b', [116, 101, 115, 116, 50]), 1.0)]

It works using following versions:

oracle: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production on Sun Solaris
java: [Java HotSpot(TM) 64-Bit Server VM (Oracle Corporation)] on java1.8.0_231
jython: Jython 2.7.0 (default:9987c746f838, Apr 29 2015, 02:25:11)
jdbc: jdbc7.jar (Implementation-Version: 12.1.0.2.0)

Bilesh Ganguly · Accepted Answer · 2019-12-21 00:28:48Z

0

You have to read the binary data to get the text from the blob.

The following code snippet is roughly what you need.

import java.io

# Some code

bs = theBlob.getBinaryStream()
ir = java.io.InputStreamReader(bs)
br = java.io.BufferedReader(ir)

total = ''
st = br.readLine()
while st != None:
   total = total + st
   st = br.readLine()

In the above code snippet, the blob gets dumped to the string total.

Following is a similar working example of reading from a file.

import java.io

f = java.io.File('test.txt')
fr = java.io.FileReader(f)
br = java.io.BufferedReader(fr)

st = br.readLine()
while st != None:
    print st
    st = br.readLine()

edited Dec 21, 2019 at 0:28

answered Dec 20, 2019 at 23:16

Bilesh Ganguly

4,1814 gold badges42 silver badges65 bronze badges

2 Comments

Praxiteles Over a year ago

It is unclear to us how we integrate your code into what we are doing above. We don't have a binary stream or a file. We are trying to get the binary stream over SQL. What are the hooks that bridge the variables and methods we are using to the code you are suggesting?

Bilesh Ganguly Over a year ago

@Praxiteles - The first sample should work. Unfortunately, I didn't have a working blob in hand to test the code. That is why I shared an equivalent file read example to demonstrate what roughly needs to be done. bs = theBlob.getBinaryStream() should work since it is a oracle.sql.BLOB object.

Collectives™ on Stack Overflow

How to retrieve actual blob data rather than "oracle.sql.blob@" from an Oracle database over JDBC?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related