問題描述

在 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 列的 OutputStream &將我的數據寫入 OutputStream.

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.

問:我怎樣才能得到一個輸出流到 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(StringcolumnLabel, Blob x)，然后在執行 Connection.commit() 之前調用 ResultSet.updateRow().

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

我自己沒有以這種方式更新 Blob 值，但它應該可以工作.如果您在嘗試重用最初從 ResultSet 讀取的 Blob 時遇到任何問題(如果您實際上并未使用原始數據，則可能不需要這樣做)，您可以使用 Connect.createBlob()做一個空的開始.您可以從本教程了解有關更新結果集的更多信息.

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 列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！