dcsimg

How do I upload SQL3 BLOB & CLOB data to a database?

Created May 4, 2012

Lennart Jorelid

Although one may simply extract BLOB & CLOB data from the database using the methods of the java.sql.CLOB and java.sql.BLOB, one must upload the data as normal java datatypes. The example below inserts a BLOB in the form of a byte[] and a CLOB in the form of a String into the database

Inserting SQL3 type data [BLOB & CLOB] 
private void runInsert() {
  try {
      // Log
      this.log("Inserting values ... ");
      
      // Open a new Statement
      PreparedStatement stmnt = conn.prepareStatement(
        "insert Lobtest (image, name) values (?, ?)");

      // Create a timestamp to measure the insert time
      Date before = new java.util.Date();

      for(int i = 0; i < 500; i++) {
          // Set parameters
          stmnt.setBytes(1, blobData);
          stmnt.setString(2, "i: " + i + ";" + clobData);

          // Perform insert
          int rowsAffected = stmnt.executeUpdate();
      }

      // Get another timestamp to complete the time measurement
      Date after = new java.util.Date();
      this.log(" ... Done!");
      log("Total run time: " + (
        after.getTime() - before.getTime()));

      // Close database resources
      stmnt.close();
  } catch(SQLException ex) {
      this.log("Hmm... " + ex);
  }
}