6

I am currently validating an application developed on Oracle for DB2. Since we don't want to maintain two separate sources, I need some query to insert blob into a field, that works in both oracle and db2. I don't have any identifier to distinguish under which DB the application is running.

I used utl_raw.cast_to_raw in oracle and CAST() as BLOB in DB2 which are mutually incompatible.

Saju
  • 402
  • 3
  • 11
  • 30

1 Answers1

7

You won't be able to find a common SQL that uses some kind of casting. But you can do this with "plain" SQL using JDBC's setBinaryStream() 

PreparedStatement pstmt = connection.prepareStatement(
   "insert into blob_table (id, blob_data) values (?, ?)";

File blobFile = new File("your_document.pdf");
InputStream in = new FileInputStream(blobFile);

pstmt.setInt(1, 42);
pstmt.setBinaryStream(2, in, (int)blobFile.length());
pstmt.executeUpdate();
connection.commit();

You can use setBinaryStream() the same way with an UPDATE statement.

  • I have the data inside a variable of type String. Is it fine to use the above method by converting the String to BinaryStream?? – Saju May 09 '13 at 13:01
  • @Saju: how can you have a *binary* large object as a String? What is the actual (e.g. Oracle) datatype for that column? If that is a `CLOB` (rather than a `BLOB`) you should use `setCharacterStream()` instead of `setBinaryStream()` –  May 09 '13 at 13:27
  • Perhaps @Saju means NCLOB in Oracle, DBCLOB in DB2. – WarrenT May 09 '13 at 16:05