CODEWITHSUNDEEP
javaoracle-databasejdbcblob
ptpdlc
ptpdlc

Reputation: 2621

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.

Upvotes: 1

Views: 2203

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1503479

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.

Upvotes: 2

Mark Rotteveel
Mark Rotteveel

Reputation: 109257

You are closing the FileInputStream before the database has used it. The JDBC driver is allowed to defer consumption of the stream until the actual execute.

Also note that your test comparison with a fixed string isn't entirely fair: it isn't the same method overload so it might be that one works and the other one doesn't (although that isn't the case here).

Upvotes: 0

Related Questions