Blob into Oracle: about 15% are filled with \00

Question

Under Weblogic 10, I am using Hibernate to store data into several tables with BLOBs. It's always worked fine but the customer found specific circumstances where 15% of the BLOBs have the correct size but only contain null characters. I can't figure out what makes it good or full of emptiness.

The BLOB type I am using does a:

public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
    if (value == null) {
        st.setNull(index, sqlTypes()[0]);
        return;
    }

    try {
        Connection conn = st.getConnection();
        if (conn instanceof org.apache.commons.dbcp.DelegatingConnection) {
            log.debug("Delegating connection, digging for actual driver");
            conn = ((org.apache.commons.dbcp.DelegatingConnection)st.getConnection()).getInnermostDelegate();
        }
        OutputStream tempBlobWriter = null;
        BLOB tempBlob = BLOB.createTemporary(conn, true, BLOB.DURATION_SESSION);
        try {
            tempBlob.open(BLOB.MODE_READWRITE);
            tempBlobWriter = tempBlob.setBinaryStream(1L);
            tempBlobWriter.write((byte[])value);
            tempBlobWriter.flush();
        } finally {
            if (tempBlobWriter != null)
             tempBlobWriter.close();
            tempBlob.close();
        }

        st.setBlob(index, (Blob) tempBlob);
    } catch (IOException e) {
        throw new HibernateException(e);
    }
}

I put a log in there and can confirm that the value (byte[]) is good. I tried to change the createTemporary parameters, no success.

I am running this under Weblogic 10.0 (can't upgrade that) with the bundled Oracle Thin driver.

A clue is that the working calls come from the standard web service deployed and managed by WLS. But the problematic calls are done from a thread started along with the component that interfaces with some legacy system with JNI. This thread works like a charm for everything except these BLOBs. I am getting a new Session just before inserting the data and closing it a bit after. (The Session does NOT remain open for the lifetime of the thread)

I have set the Hibernate log level to DEBUG but it does not give me any clue. I'm starting to run out of ideas...

Answer 1

Problem solved.

In fact, I was doing:

• open session
  • open transaction
    • get first item from legacy system
    • write first item to database (blob)
  • close transaction
  • open transaction
    • get second item from legacy system
    • write second item to database (blob)
  • close transaction
  • ... until the legacy system has nothing more to process
• close session

This would typically process between 1 and 5 items per round.

But because the Oracle driver does not use the standard way of handling blobs in JDBC, our custom type has to create a temporary blob that is stored in the session. And apparently when you're inserting blobs in differents transactions within the same session, they tend to interfere and cause my problem.

I solved it by closing the session after each commit. I do not like it but I consider it being the Oracle driver's fault.