如何将空字符串更新到oracle Clob

发布于 2024-12-17 02:13:39 字数 805 浏览 1 评论 0原文

我知道它可以通过使用 SQL 来工作，

update activity set REFERENCE = EMPTY_CLOB() where id = ?

但我不能这样做，我不能在 SQL 中硬编码“EMPTY_CLOB()”。我使用了如下方法：

String empty_string = "";
conn = getConnection();

pStmt = conn.prepareStatement("SELECT REFERENCE FROM activity WHERE ID = ? FOR UPDATE");
pStmt.setLong(1, 1);
rset = pStmt.executeQuery();
Clob clob = null;
while (rset.next()) {
    clob = rset.getClob(1);
    Writer writer = adapter.getCharacterOutputStream(clob);
    writer.write(empty_string);         
    writer.flush();
    writer.close();
}

pStmt = conn.prepareStatement("update activity set REFERENCE = ? WHERE ID = ?");
pStmt.setClob(1, clob);
pStmt.setLong(2, 1);
pStmt.executeUpdate();

但是没有用。 clob 没有更新为空字符串，它仍然存储为以前的值。

任何机构都可以帮助我吗？

原文

I know it works by using SQL

update activity set REFERENCE = EMPTY_CLOB() where id = ?

But I cannot do like this, I cannot hard coded 'EMPTY_CLOB()' in SQL.
I used the way like the following:

String empty_string = "";
conn = getConnection();

pStmt = conn.prepareStatement("SELECT REFERENCE FROM activity WHERE ID = ? FOR UPDATE");
pStmt.setLong(1, 1);
rset = pStmt.executeQuery();
Clob clob = null;
while (rset.next()) {
    clob = rset.getClob(1);
    Writer writer = adapter.getCharacterOutputStream(clob);
    writer.write(empty_string);         
    writer.flush();
    writer.close();
}

pStmt = conn.prepareStatement("update activity set REFERENCE = ? WHERE ID = ?");
pStmt.setClob(1, clob);
pStmt.setLong(2, 1);
pStmt.executeUpdate();

But It didn't work. the clob didn't be updated to empty string, it still stored as previous value.

Any body can help me on this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

醉殇 2024-12-24 02:13:39

正如我在你的其他问题中已经提到的：在我的遇到 getClob() 和 setClob() 不能正常工作的情况。

请改用 setCharacterStream()：

StringReader clob = new StringReader("");
pStmt = conn.prepareStatement("update activity set REFERENCE = ? WHERE ID = ?");
pStmt.setCharacterStream(1, clob, 0);
pStmt.setLong(2, 1);
pStmt.executeUpdate();

这样您还可以在更新之前删除不必要的 SELECT，这也会提高性能。

另一种选择是简单地将该列设置为 NULL

编辑：

对于较新的驱动程序 (11.x)，您可能还想尝试使用 setString()< CLOB 列上的 /code> 和 getString()。

仅当您使用打算在跨越多个语句的事务期间保留的 LOB 定位器时，才需要对行进行锁定（至少这是我对手册链接参考的理解）。

As I have already mentionued in your other question: in my experience getClob() and setClob() don't work properly.

Use setCharacterStream() instead:

StringReader clob = new StringReader("");
pStmt = conn.prepareStatement("update activity set REFERENCE = ? WHERE ID = ?");
pStmt.setCharacterStream(1, clob, 0);
pStmt.setLong(2, 1);
pStmt.executeUpdate();

That way you can also remove the unnecessary SELECT before updating, which will improve performance as well.

Another option would be to simply set that column to NULL

Edit:

With newer drivers (11.x) you might also want to try to use setString() and getString() on the CLOB column.

The locking of the row should only be necessary when you use a LOB locator that you intend to keep during a transaction that spans more than one statement (at least that's my understanding of the linked reference to the manual).

回复收藏 0 原文

~没有更多了~