通过具有CLOB绑定的JDBC执行SQL语句 [英] Execute sql statement via JDBC with CLOB binding
本文介绍了通过具有CLOB绑定的JDBC执行SQL语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下查询(列日志的类型为CLOB):
I have the following query (column log is of type CLOB):
UPDATE table SET log=? where id=?
使用setAsciiStream方法将一个长度超过4000个字符的值放入日志列时,上面的查询工作正常.
The query above works fine when using the setAsciiStream method to put a value longer than 4000 characters into the log column.
但是我不想替换该值,而是要附加它,因此我的查询看起来像这样:
But instead of replacing the value, I want to append it, hence my query looks like this:
UPDATE table SET log=log||?||chr(10) where id=?
以上查询不再起作用,并且出现以下错误:
The above query DOES NOT work any more and I get the following error:
java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column
推荐答案
在我看来,您必须使用PL/SQL块来执行所需的操作.假设有一个id
1:
It looks to me like you have to use a PL/SQL block to do what you want. The following works for me, assuming there's an entry with id
1:
import oracle.jdbc.OracleDriver;
import java.sql.*;
import java.io.ByteArrayInputStream;
public class JDBCTest {
// How much test data to generate.
public static final int SIZE = 8192;
public static void main(String[] args) throws Exception {
// Generate some test data.
byte[] data = new byte[SIZE];
for (int i = 0; i < SIZE; ++i) {
data[i] = (byte) (64 + (i % 32));
}
ByteArrayInputStream stream = new ByteArrayInputStream(data);
DriverManager.registerDriver(new OracleDriver());
Connection c = DriverManager.getConnection(
"jdbc:oracle:thin:@some_database", "user", "password");
String sql =
"DECLARE\n" +
" l_line CLOB;\n" +
"BEGIN\n" +
" l_line := ?;\n" +
" UPDATE table SET log = log || l_line || CHR(10) WHERE id = ?;\n" +
"END;\n";
PreparedStatement stmt = c.prepareStatement(sql);
stmt.setAsciiStream(1, stream, SIZE);
stmt.setInt(2, 1);
stmt.execute();
stmt.close();
c.commit();
c.close();
}
}
这篇关于通过具有CLOB绑定的JDBC执行SQL语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文