2

In a DB2 database, I have the following table:

CREATE TABLE MyTestTable
( 
    MYPATH VARCHAR(512) NOT NULL, 
    MYDATA BLOB, 
    CONSTRAINT MYTESTTABLE_PK PRIMARY KEY (MYPATH)
);

Using Java, I wish to update an existing row in this table with new blob data. My preferred way is to obtain an OutputStream to the BLOB column & write my data to the OutputStream.

Here is the test code I am using:

Connection connection = null;
PreparedStatement pStmnt = null;
ResultSet rSet = null;

try {
    connection =  ... // get db connection
    String id = ... // set the MYPATH value 

    String sql = "SELECT MYDATA FROM MyTestTable WHERE MYPATH='"+id+"' FOR UPDATE";

    pStmnt = connection.prepareStatement(sql);
    rSet = pStmnt.executeQuery();
    while (rSet.next()) {
        Blob blobData = rSet.getBlob("MYDATA");  // this is a java.sql.Blob

        OutputStream blobOutputStream = blobData.setBinaryStream(1);
        blobOutputStream.write(data);
        blobOutputStream.close();
        connection.commit();
    }
}
// close ResultSet/PreparedStatement/etc in the finally block

The above code works for the Oracle DB.

However, in DB2, calling setBinaryStream to get the OutputStream does not seem to work. The data does not get updated, and I do not get any error messages.

Qs: How can I get an OutputStream to the BLOB column of a DB2 table? What might need to be changed in the above code?

Perihelion
  • 33
  • 1
  • 6
  • Without seeing your code I'd venture to guess that you may not be committing the transaction properly. The Oracle JDBC driver on `Connection.close()` implicitly commits an in-flight transaction, while the DB2 driver by default would roll it back. – mustaccio Oct 02 '14 at 11:37
  • Have you looked at the IBM-provided sample code on how to do this? http://www-01.ibm.com/support/knowledgecenter/#!/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.sample.doc/doc/java_jdbc/s-DtLob-java.html – Ian Bjorhovde Oct 02 '14 at 15:52
  • @mustaccio ... I have added my test code. I am doing a commit. But it does not seem to update the data. – Perihelion Oct 03 '14 at 05:05
  • @IanBjorhovde ... thanks for the link. However the link does not provide any examples on using OutputStream, which is what I am looking for – Perihelion Oct 03 '14 at 05:07
  • The standard way of updating table rows is using the `UPDATE` statements. If for some reason you prefer the abstraction of an updatable result set you need to specify appropriate parameters in the `prepareStatement()` call, as well as use the necessary methods to update the column. [The manual explains how to do that.](http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_tjvrsush.html?lang=en) – mustaccio Oct 03 '14 at 13:47

1 Answers1

2

You are probably getting the data written to the Blob object successfully, but you need to do more with the PreparedStatement and ResultSet in order to actually update the value in the database.

First, your PreparedStatement must be instantiated using a version of Connection.prepareStatement() that takes a resultSetConcurrency parameter, which you must set to the value ResultSet.CONCUR_UPDATABLE. (I don't know that the SQL SELECT actually needs to specify the FOR UPDATE clause - see the tutorial at the link at the end of this answer.)

Second, after you close blobOutputStream, you need to update the value in the ResultSet using updateBlob(int columnIndex, Blob x) or updateBlob(String columnLabel, Blob x), then invoke ResultSet.updateRow() before doing a Connection.commit().

I haven't updated Blob values this way myself, but it should work. If you run into any issues trying to reuse the Blob originally read from the ResultSet (which you probably don't need to do if you're not actually using the original data), you can use Connect.createBlob() to make an empty one to start with. You can learn more about updating ResultSets from this tutorial.

Kevin Rahe
  • 1,609
  • 3
  • 19
  • 27