Tuesday, January 16, 2007

Handling Oracle Large Objects with JDBC

LOBs (Large OBjects) are are designed to support large unstructured data such as text, images, video etc. Oracle supports the following two types of LOBs:
  • Character Large Object (CLOB) and Binary Large Object(BLOB) are stored in the database either in-line in the table or in a separate segment or tablespace.
  • BFILEs are large binary data objects stored in operating system files outside of database tablespaces.
Oracle extension classes are provided to support these types objects in JDBC like oracle.sql.CLOB, oracle.sql.BLOB. While you can use java.sql.Blob and java.sql.Clob, oracle extensions provide added functionalities, such as adding bytes specific positions (getBytes(int pos, byte[] data) etc.

Working with LOB Data

CLOB and the BLOB objects are not created and managed in the same way as the ordinary types such as VARCHAR. To work with LOB data, you must first obtain a LOB locator. Then you can read or write LOB data and perform data manipulation. Use the ResultSet's getBlob method to obtain the LOB locator, and then you can obtain the a Stream of the blob to read/write to the Blob
Blob blob = rs.getBlob(1);
InputStream is = blob.getBinaryStream();
OutputStream os = blob.setBinaryStream(1);
The following example shows how to insert, read and write Blobs to Oracle from Java. The table here has only two columns (IMAGE_ID and IMAGE) IMAGE is a BLOB.
package data;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class BlobTest {

public void insertBlob(String imageId, String fileName) {
Connection conn = null;
try {
conn = getConnection();
if (!fileName.equals("")) {
PreparedStatement ps = conn.prepareStatement("INSERT INTO IMAGES VALUES(?, ?)");
ps.setString(1, imageId);
FileInputStream fis = new FileInputStream(fileName);
ps.setBinaryStream(2, fis, fis.available());
ps.execute();
ps.close();
} else {
PreparedStatement ps = conn.prepareStatement("INSERT INTO IMAGES VALUES (?, empty_blob())");
ps.setString(1, imageId);
ps.execute();
ps.close();

}
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}

public void readBlob(String fileName) {
Connection conn = null;
try {
conn = getConnection();
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT IMAGE FROM IMAGES");
while (rs.next()) {
// The following two lines can be replaced by
// InputStream is = rs.getBinaryStream(1);
Blob blob = rs.getBlob(1);
InputStream is = blob.getBinaryStream();
FileOutputStream fos = null;

fos = new FileOutputStream("c:/TEMP/" + fileName);
byte[] data = new byte[1024];
int i = 0;
while ((i = is.read(data)) != -1) {
fos.write(data, 0, i);
}
}
conn.close();

} catch (Exception e) {
e.printStackTrace();
}
}

public void writeBlob(String fileName) {
Connection conn = null;
try {
conn = getConnection();
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT IMAGE FROM IMAGES FOR UPDATE");
while (rs.next()) {
Blob blob = rs.getBlob(1);
System.out.println(blob);
OutputStream os = blob.setBinaryStream(1);
FileInputStream fis = null;
fis = new FileInputStream("c:/TEMP/" + fileName);
byte[] data = new byte[1];
int i;
while ((i = fis.read(data)) != -1) {
os.write(data, 0, i);
}
os.close();
break;
}
conn.close();

} catch (Exception e) {
e.printStackTrace();
}
}

private Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");
return conn;
}

public static void main(String[] args) {
BlobTest blobTest = new BlobTest();
blobTest.insertBlob("img1", "");
blobTest.writeBlob("2.gif");

}
}
BlobTest.java

Insert Blob into Oracle

The insertBlob method takes the image id and the image file name as arguments. If the image file is an empty string, then an empty blob is inserted into the table. The empty_blob() function returns an empty locator of type BLOB, this is used in the INSERT.

Read from a Blob

The getBinaryStream of java.sql.Blob class returns an InputStream, which can be used to read from the blob.

Write to a Blob

The writeBlob method writes to the first row retrieved from the table. The SQL statement uses FOR UPDATE. In the absence of FOR UPDATE, you will get an IOException
java.io.IOException: ORA-22920: row containing the LOB value is not locked
at oracle.jdbc.driver.DatabaseError.SQLToIOException(DatabaseError.java:517)
at oracle.jdbc.driver.OracleBlobOutputStream.flushBuffer(OracleBlobOutputStream.java:214)
at oracle.jdbc.driver.OracleBlobOutputStream.close(OracleBlobOutputStream.java:179)
at data.BlobTest.writeBlob(BlobTest.java:90)
at data.BlobTest.main(BlobTest.java:111)
The following article describes how to handle CLOB using JDBC

13 comments:

  1. Hi I tested your code and it gives me a unimplemented or unreasonable conversion error....

    ReplyDelete
  2. I use Scriptella ETL open source tool for SQL scripts execution. With Scriptella uploading a blob from an external file is extremely easy:
    INSERT INTO TABLE VALUES (?{file 'image.gif'});

    ReplyDelete
  3. "unimplemented or unreasonable conversion error"

    This example was run using Oracle DB 10g Release 2, on Java 5. Please check the versions you are using. The error could be due to version conflicts.

    ReplyDelete
  4. Hi Ahbi,

    Good example. I tested it on 10gR1: upload works fine with small files (< 4kb) , but fails with larger files (IOException: socket write error).

    For files > 4 kb, using oracle.sql.BLOB fixes it. Haven't tryied on 10gR2.

    ReplyDelete
  5. even I am getting following exception using the code you have givem-
    java.sql.SQLException: ORA-01460: unimplemented or unreasonable conversion requested

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
    --------------
    DB Version is ::Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
    --------------
    can you please help?

    ReplyDelete
  6. Does anybody found the solution for this problem? Neither me can insert a blob file with dimension more than 4k :-(

    ReplyDelete
  7. Thanks for that. Personally I would have done

    if (!"".equals(fileName))

    then

    a. You avoid null pointer errors
    b. You can use a null String too as a parameter

    ReplyDelete

Popular Posts