Christian HagerSoftware Engineer // Technical Director // Photographer

Streaming from Oracle bfiles. The right way?

Today I faced the task to stream a PDF-Document from a bfile stored in an Oracle database. So I wrote the following code to get a stream supplying the file from bfile and writing it to an outputstream.

final String sql = ...;
BFILE bfile = null;

Connection con = null;
Statement stmt = null;
ResultSet rs = null;
OracleResultSet oracleRs = null;

try {
    con = dataSource.getConnection();
    stmt = con.createStatement();
    rs = stmt.executeQuery(sql);
    
    oracleRs = ((Wrapper) rs).unwrap(OracleResultSet.class);
    
    if (oracleRs != null) {
        if (oracleRs.next()) {
            bfile = oracleRs.getBFILE(1);
            
            if (bfile.fileExists()) {
                bfile.open(BFILE.MODE_READONLY);
                
                final InputStream is = bfile.getBinaryStream();
                
                int read = 0;
                final byte[] bytes = new byte[BUFFER_SIZE];
                
                while ((read = is.read(bytes)) != -1) {
                    os.write(bytes, 0, read);
                }
                
                is.close();
                bfile.close();
                } else {
                log.error("Das Dokument mit der Nummer " + id + " konnte leider nicht gefunden werden!");
            }
        }
        } else {
        log.error("Konnte kein OracleResultSet finden! Dokument kann nicht geladen werden!");
    }
    } catch (final SQLException e) {
    log.error("Beim Laden eines PDF-Dokuments ist ein Fehler aufgetreten.", e);
    } finally {
    try {
        if (bfile != null) {
            bfile = null;
        }
        if (oracleRs != null) {
            oracleRs.close();
        }
        if (rs != null) {
            rs.close();
        }
        if (stmt != null) {
            stmt.close();
        }
        if (con != null) {
            con.close();
        }
        } catch (final SQLException e1) {
        log.error("Beim Laden eines PDF-Dokuments ist ein Fehler aufgetreten.", e1);
    }
}

I directed the outputstream to a file and after running the code I could find the file, in my case a PDF-file, in the expected location on my harddrive. I opened it and everything seemed fine.

Next I went ahead and implemented a hash check to proof that the document had been correctly streamed to the destination folder. To prove this I used the sha-1 hashes we generated before uploading the file and which we store next to the file in our database. I generated the same hash for the file I just streamed to my harddrive and SURPRISE it didn’t match.

So what happend here? Upon inspection of the file using a hex-editor I found out that the original file and the streamed file were equal exept that the streamed file had some 0-bytes appended after the EOF-marker.

I finally found out that the read-method of oracles inputstream always returns the full size of the buffer instead of the bytes that were really read into the buffer which it should return as stated in the javadoc of java.io.InputStream.read(byte[] b). This in fact caused the outputstream to always write full buffers using os.write(bytes, 0, read). For most of the file this is no problem but in case of the last buffer of the stream it is wrong except if the buffer is completely filled.

The following code shows how I fixed the problem and made it work for my use-case.

final String sql = ...;
BFILE bfile = null;

Connection con = null;
Statement stmt = null;
ResultSet rs = null;
OracleResultSet oracleRs = null;

try {
    con = dataSource.getConnection();
    stmt = con.createStatement();
    rs = stmt.executeQuery(sql);
    
    oracleRs = ((Wrapper) rs).unwrap(OracleResultSet.class);
    
    if (oracleRs != null) {
        if (oracleRs.next()) {
            bfile = oracleRs.getBFILE(1);
            
            if (bfile.fileExists()) {
                bfile.open(BFILE.MODE_READONLY);
                
                Long fileSize = bfile.length();
                final InputStream is = bfile.getBinaryStream();
                final byte[] bytes = new byte[BUFFER_SIZE];
                
                while (is.read(bytes) != -1) {
                    os.write(bytes, 0, fileSize > BUFFER_SIZE ? BUFFER_SIZE : fileSize.intValue());
                    fileSize = fileSize - BUFFER_SIZE;
                }
                
                is.close();
                bfile.close();
                } else {
                log.error("Das Dokument mit der Nummer " + id + " konnte leider nicht gefunden werden!");
            }
        }
        } else {
        log.error("Konnte kein OracleResultSet finden! Dokument kann nicht geladen werden!");
    }
    } catch (final SQLException e) {
    log.error("Beim Laden eines PDF-Dokuments ist ein Fehler aufgetreten.", e);
    } finally {
    try {
        if (bfile != null) {
            bfile = null;
        }
        if (oracleRs != null) {
            oracleRs.close();
        }
        if (rs != null) {
            rs.close();
        }
        if (stmt != null) {
            stmt.close();
        }
        if (con != null) {
            con.close();
        }
        } catch (final SQLException e1) {
        log.error("Beim Laden eines PDF-Dokuments ist ein Fehler aufgetreten.", e1);
    }
}

Maybe this is not the most elegant solution but it worked for me. I would love to hear if anyone knows of a better or more elegant solution. Please feel free to post in the comments.

Tags: , ,

Leave a Reply

Your email address will not be published. Required fields are marked *