Dynamically create Blob in DB2 Java stored procedure

Question

I would like to return some data as a Blob from a DB2 stored procedure written in Java.

This is the code to generate the procedure on the DB2 server:

CREATE PROCEDURE CLUB.P_CLUB_GET_BACKUP ( IN CLUBID INTEGER,
                                          IN BNR INTEGER,
                                          OUT BACKUP BLOB(50000000) )
 NO SQL
 NOT DETERMINISTIC
 LANGUAGE Java
 EXTERNAL NAME 'P_CLUB_GET_BACKUP:ch.swissasp.vvv.procedures.P_GET_BACKUP1.p_GET_BACKUP1'
 FENCED
 THREADSAFE
 PARAMETER STYLE JAVA

And the corresponding Java Code is this:

    /**
 * SQLJ Stored Procedure P_GET_BACKUP
 * @param clubID 
 * @param backupID 
 * @param Backup 
 */
package ch.swissasp.vvv.procedures;

import java.io.File;
import java.io.FileInputStream;
import java.sql.SQLException;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

    public class P_GET_BACKUP1
    {
     public static void p_GET_BACKUP1(int clubID, int backupNr, java.sql.Blob[] backup)
      throws SQLException,
       Exception
     {
        // create blob and return as output parameter 'backup'
     }
    }

What I would need now is some way to generate a Blob from some binary data that is generated in the stored procedure (so the blob does not come from a query but reads data that resides on the database server) and then return this blob as the output parameter 'backup'. The problem is, that I have no clue how to dynamically create a blob, as java.sql.Blob itself is an interface. I can create a blob using a dummy SQL as such:

// create a blob the complicated way...
Connection con = DriverManager.getConnection("jdbc:default:connection");
con.setReadOnly(true);
PreparedStatement stmt = con.prepareStatement("VALUES (Cast(? AS Blob))");
stmt.setBytes(1, new byte[0]);
ResultSet rs = stmt.executeQuery();
rs.next();
Blob blob = rs.getBlob(1);
rs.close();

And then use it like this:

OutputStream out = blob.setBinaryStream(1L); // get output stream to blob
// code to write the data to output stream
out.close(); // close blob
backup[0] = blob; // set output param

But I would preferably be able to replace all that SQL code with something like this:

java.sql.Blob blob = new DB2Blob(); // dynamically allocate blob

Any ideas if there exists such facility to dynamically create a blob in DB2?

Answer 1

Here's what IBM suggests. Rather than using a DB2 stored procedure, you can just do the query from your Java code.

PreparedStatement preparedStatement = 
    connection.prepareStatement(
    "SELECT BOOKCOVER FROM BOOKCOVERS WHERE BOOKISBN=?"); 
preparedStatement.setString(1, "0738425826"); 
ResultSet resultSet = preparedStatement.executeQuery(); 
while (resultSet.next()) { 
    // materialization of the Blob 
    Blob blob = resultSet.getBlob(1); 
    InputStream inputStream = blob.getBinaryStream(); 
    File fileOutput = new 
        File("C:\\clonedredbookcover.jpg"); 
    FileOutputStream fo = new
        FileOutputStream(fileOutput); 
    int c; 
    while ((c = inputStream.read()) != -1) 
        fo.write(c); 
    fo.close(); 
    System.out.println("Blob retrieved");