Insertion of BLOB fails with the file being NULL

In Java, with JDBC I am trying to insert a file in a BLOB column in a table of an Oracle database.

Here is how I proceed:

private Statement getStatement(File f, String fid, Long dex, String uid, int id)
{
  FileInputStream fis = null;
  PreparedStatement statement;
  try
  {
    statement = connection.prepareStatement("INSERT INTO BLOBTABLE (FID, FDEX, SFILE, UID, ID) VALUES (?, ?, ?, ?, ?)");
    statement.setString(1, fid);
    statement.setLong(2, dex);
    fis = new FileInputStream(file);
    statement.setBinaryStream(3, fis, file.length());
    statement.setString(4, uid);
    statement.setInt(5, id);
  }
  finally
  {
    if (fis != null)
      fis.close();
  }
  return statement;
}

private insertStuff()
{
  File f = new File("/home/user/thisFileExists");
  PreparedStatement statement = getStatement(f, "XYZ", 18L, "ABC", 78);
  statement.execute();
}

When the .execute is run, I get an Oracle error:

java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("ORACLEUSER"."BLOBTABLE"."SFILE")

SFILE is the BLOB column. So this means the database at the end of the chain receives NULL in the query.

How come?

If I replace:

statement.setBinaryStream(3, fis, file.length());

With: 

statement.setBinaryStream(3, new ByteArrayInputStream(("RANDOMSTRING".getBytes())));

It works so it somehow does not like my file stream.

Is it a problem that I close the stream? that is how they do it on all samples I saw.

Jon Skeet
people
quotationmark

You're closing the FileInputStream before you execute the statement, so there's no way for the statement to get the data when it actually needs it. It would better to pass an InputStream into your method, so you can close it externally after the statement has executed:

private insertStuff() {
    File file = new File("/home/user/thisFileExists");
    try (InputStream stream = new FileInputStream(file)) {
        PreparedStatement statement = getStatement(stream, "XYZ", 18L, "ABC", 78);
        statement.execute();
    }
}

... where getStatement would accept an InputStream instead of the File, and use the overload of setBinaryStream which doesn't take a data length. Alternatively, you could pass in the File and it could open the stream, create the statement, execute the statement, then close the stream.

As a side note, you should be closing the statement using a try-with-resource or try/finally statement, too.

people

See more on this question at Stackoverflow