Oracle BLOB Writes Twice the Number of Bytes to the OutputStream

Hi,

I have a very strange problem when working with oracle.sql.BLOB; I cannot figure out what is causing my BLOB's output stream doubling the amount of data inserted into the Oracle database. I have a table that contains two BLOB objects(image files) and the goal is to insert two images into each row by a BLOB stream.

For example, if the image_bin data size is 820k and image_thumbnail size is 105k, this code actually writes 1640k (double) and 210k (double) the amount of bytes to each BLOB column, respectively. However, the print method at the end of insertBlob() indicates a correct number of bytes being written to the output stream (820k for the first image file and 105k for the second image file).

I know for the fact the retrieval method (not mentioned here) doesn't duplicate the bytes when it reads the data from the BLOB column because I have written another test program that does not utilize oracle.sql.BLOB but instead uses PreparedStatement's setBinaryStream(index, InputStream, size_of_file) to insert a binary image into BLOB column and it accurately writes the exact image size (no double sizing) to the database -- but not with BLOB. Here's a snippet of my code, note that the actual writing occurs in insertBlob():

Any help would be appreciated
private void insertBlob(java.sql.Blob lobImage, String imgName) 
                           throws SQLException, IOException {
 
    File imgFile                   = null;
    FileInputStream imgOnDisk  = null;
    OutputStream imgToDB           = null;
    int bufferSize                 = 0;
    oracle.sql.BLOB blobImage      = (oracle.sql.BLOB) lobImage;
 
    try {
      
      int bytesRead    = 0;
      long bytesWritten = 0L;
      byte[] byteBuffer = null; 
 
      bufferSize = blobImage.getBufferSize();
      byteBuffer = new byte[bufferSize];
 
      imgFile = new File(imgName);
      // Stream to read the file from the local disk
      imgOnDisk = new FileInputStream(imgFile);
      // Stream to write to the Oracle database
      imgToDB = blobImage.setBinaryStream(imgFile.length());
 
      while ((bytesRead = imgOnDisk.read(byteBuffer)) != -1 )  {
        imgToDB.write(byteBuffer, 0, bytesRead);
 
        bytesWritten += bytesRead;
 
      } // end of while
 
      System.out.print("Done. " + bytesWritten + "-bytes inserted, buffer size: " +
                       bufferSize + "-bytes, chunk size: " + 
                       blobImage.getChunkSize() + ".\n");
 
 
    } catch (SQLException sqlEx) {
      System.out.println("SQLException caught: JDBCOracleLOBBinaryStream.insertBlob()");
      connRollback();
      throw sqlEx;
 
    } catch (IOException ioe) {
      System.out.println("IOException caught: JDBCOracleLOBBinaryStream.insertBlob()");
      throw ioe;
 
    } finally {
      try {
        if (imgOnDisk != null ) {
          imgOnDisk.close();
        }
 
        if (imgToDB != null ) {
          imgToDB.close();
        }
 
      } catch (IOException ioeClosing) {
        System.out.println("IOException caught: JDBCOracleLOBBinaryStream.insertBlob() " +
                           "on closing stream.");
        ioeClosing.printStackTrace();
      }
 
    } // end of finally
 
  }
 
 
  public void insertImageIntoOracleDB() throws SQLException, IOException {
 
    PreparedStatement pstmt = null;
    Statement         stmt  = null;
    ResultSet rset          = null;
 
    try {
 
      this.getConnection(_driver, _host, _port, _database, _user, _pass);
 
      pstmt = conn.prepareStatement("INSERT INTO " + 
               " gallery_v (picture_id, picture_title, image_bin, image_thumbnail) " +
               " VALUES (?, ?, EMPTY_BLOB(), EMPTY_BLOB())");
 
      pstmt.setInt(1, picID);
      pstmt.setString(2, picTitle);
      pstmt.executeUpdate();
 
      stmt = conn.createStatement();
      rset = stmt.executeQuery("SELECT image_bin, image_thumbnail FROM gallery_v " +
                               " WHERE picture_id = " + picID + "  FOR UPDATE");
 
      int rsetCount                = 0;
      oracle.sql.BLOB imgBlob      = null;
      oracle.sql.BLOB imgThumbBlob = null;
 
      while (rset.next()) {
 
        imgBlob = ((OracleResultSet) rset).getBLOB("image_bin");
        System.out.print("Inserting " + img + "... ");
        insertBlob(imgBlob, img);
 
        imgThumbBlob = ((OracleResultSet) rset).getBLOB("image_thumbnail");
        System.out.print("Inserting " + imgThumb + "... ");
        insertBlob(imgThumbBlob, imgThumb);
 
        rsetCount++;
 
      }
 
      System.out.println("\nNumber of rows updated: " + rsetCount);
      conn.commit();
 
    } catch (SQLException sqlEx) {
      System.out.println("SQLException caught: JDBCOracleLOBBinaryStream.insertImageIntoOracleDB()");
      connRollback();
      throw sqlEx;
 
    } catch (IOException ioe) {
      throw ioe; 
 
    } finally {
      try {
        if (rset != null) {
          rset.close();
        }
 
        if (pstmt != null) { 
          pstmt.close();
        }
 
        if (stmt != null) { 
          stmt.close();
        }
 
        closeConnection();
 
      } catch (SQLException closingSqlEx) {
        System.out.println("SQLException caught: JDBCOracleLOBBinaryStream.insertImageIntoOracleDB() " +
                           "on closing ResultSet or PreparedStatement.");
        closingSqlEx.printStackTrace();
 
      } 
 
    } // end of finally
 
  }

Open in new window

oxbloodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
Sounds to me like you've got the column set in double-byte (Unicode) mode somehow
0
oxbloodAuthor Commented:
CEHJ,

Thanks for your reply. This is a raw transmission of binary bytes (not an encoded bytes) so  there is no conversion performed by the driver to and from the database. If the LOB object was a CLOB type, I would consider that.

But in either case, I found the culprit and I feel like a moron. The new BLOB#setBinaryStream() method takes a position of where the data is read from in the stream given to it. So the following code:

imgToDB = blobImage.setBinaryStream(imgFile.length());

Starts off from the end of the file. Now I don't understand how this position would result in the duplicated amount of bytes read from the binary file (an image here) to the output stream! The correct line should be:

imgToDB = blobImage.setBinaryStream(0L);

ARGH!!! Now everything works as it expected. I gotta read the API's more carefully as I was expecting the same semantic parameter as PreparedStatement#setBinaryStream() which takes the length of the stream as one of its parameters.
0
CEHJCommented:
Glad you got it sorted out
0
Computer101Commented:
PAQed with points refunded (100)

Computer101
EE Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.