无法将 CLOB 数据存储到 DB2 中的 CLOB 定义列中 [英] Unable to store CLOB data in to CLOB defined column in DB2

问题描述

我猜这是一个重复的问题，但还没有找到合适的解决方案.基本上，我正在尝试通过 DB2 过程将位巨大的 XML (即 32000+ 个字符)插入到 CLOB 列中.插入失败并出现以下错误，看起来 DB2 将输入视为字符串而不是 CLOB 数据类型.你能建议需要做什么吗?

It's a repeated issue i guess, but couldn't find a proper solution yet. Basically I am trying to insert bit huge XML i.e. 32000+ characters in to a CLOB column through DB2 procedure. Insertion is failing with the below error looks DB2 is considering the input as String rather than CLOB datatype. Can you please suggest what needs to be done?

SP

CREATE OR REPLACE PROCEDURE logging (IN HEADERDATA CLOB(10M))
LANGUAGE SQL
BEGIN
    INSERT INTO Logging(Header) VALUES (HEADERDATA);
    COMMIT;
END

错误

The string constant beginning with 
"'<?xml version="1.0" encoding="UTF-8"?><XXXXXXXX xmlns:xsi="http:" is too long.. 
SQLCODE=-102, SQLSTATE=54002, DRIVER=XXXXXX

推荐答案

DB2 中的字符文字被限制为大约 32K 字节.要处理较大的 LOB，您需要避免使用 SQL 文字值.

Character literals in DB2 are limited to about 32K bytes. To handle larger LOBs you need to avoid having to use SQL literal values.

无需额外编程即可做到这一点的一种方法是将 [future] CLOB 内容写入文件并使用 IMPORT 或 LOAD 将其内容插入列中.

One way to do this without extra programming is write your [future] CLOB contents to a file and use IMPORT or LOAD to insert its contents into a column.

或者，您可以在过程调用周围包装一个简单的 Java 程序，您可以在其中使用 PreparedStatement.setClob() 来处理大型 XML 文档.

Alternatively, you could wrap a simple Java program around your procedure call where you would use PreparedStatement.setClob() to handle your large XML document.

这篇关于无法将 CLOB 数据存储到 DB2 中的 CLOB 定义列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！