OutputStream到DB2数据库表的BLOB列 [英] OutputStream to the BLOB column of a DB2 database table

问题描述

在DB2数据库中，我有以下表格：

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)
);

使用Java，我希望使用新的blob数据更新此表中的现有行。我的首选方式是获得一个到BLOB列&

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

上面的代码适用于Oracle DB。

The above code works for the Oracle DB.

然而，在DB2中，调用setBinaryStream来获取OutputStream似乎不工作。数据未更新，我没有收到任何错误消息。

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.

问：如何获得一个OutputStream到DB2表的BLOB列？

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?

推荐答案

您可能已经成功地将数据写入Blob对象，但是，你需要使用PreparedStatement和ResultSet做更多的事情，以便实际更新数据库中的值。

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.

首先，你的PreparedStatement必须使用版本 Connection.prepareStatement（）需要一个resultSetConcurrency参数，必须设置为 ResultSet.CONCUR_UPDATABLE 。 （我不知道SQL SELECT实际上需要指定 FOR UPDATE 子句 - 请参阅本答案末尾链接的教程。）

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.)

其次，关闭blobOutputStream后，您需要使用 updateBlob（int columnIndex，Blob x） 或 updateBlob（String columnLabel，Blob x） ，然后调用 ResultSet.updateRow（）之前执行 Connection.commit（）。

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().

我自己，但它应该工作。如果你遇到任何问题，试图重用从ResultSet（你可能不需要做，如果你没有实际使用原始数据）最初读取的Blob，你可以使用 Connect.createBlob（） 使一个空的开始。您可以从本教程中了解有关更新ResultSets的详细信息。

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.

这篇关于OutputStream到DB2数据库表的BLOB列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！