correct way of extracting image (BLOB) from ORACLE

Question

I have a project in which i need to extract some images in BLOB format as string, from an ORACLE database to send it through a JSON. I'm using Eclipse Java EE IDE.

Version: Mars Release (4.5.0)
Build id: 20150621-1200

Would this be the proper way to extract the BLOB data as string?

        String query = "SELECT operation, c_book, x_book, x_text1, x_text2, x_text3, x_text4,"
                           + "UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(img_logo,32670,1))FROM "
                           + dataBaseConnectionData.getDB_SHCHEMA() + "."+ dataBaseConnectionData.getDB_TABLE_COLA()
                           + " WHERE status = 'P' OR status = 'N' OR status = 'E'" 
                           + " ORDER BY c_book";

Andreas Andreas · Accepted Answer · 2015-09-25T16:07:09

There are 3 ways to get BLOB data using JDBC:

Blob blob = rs.getBlob("img_logo")
InputStream stream = rs.getBinaryStream("img_logo")
byte[] bytes = rs.getBytes("img_logo")

If the blob is of limited size, which a logo would be, the third version is the easiest to use.

You will then need to convert to a string, which means you need to know which encoding was used to convert the original text to binary in the first place. The most conservative choice would be US-ASCII, so:

String text = new String(bytes, StandardCharsets.US_ASCII)

Of course, this assumes the binary data is text, but it's a logo, and that doesn't sound like a text value, so you might have meant that you want to encode the binary data for embedding in a JSON structure, to be decoded back to binary at the other end.

For that, you'd need to decide on an encoding. The two top choices are HEX and BASE64, where HEX will double the size (two hex digits per byte), and BASE64 will add 33% (4 chars per 3 bytes).

For HEX, see How to convert a byte array to a hex string in Java?
For BASE64, see How do I convert a byte array to Base64 in Java?, or use the new Base64 class (Java 8).

correct way of extracting image (BLOB) from ORACLE

1 Answers