Jdbc数组绑定：字符集编码 [英] Jdbc array binding: character set encoding
问题描述
我试图将SQL字符串数组绑定到预先准备的语句，对于某些数据库字符集，数组的值变为null。如果我绑定简单的字符串（不在数组），它的工作原理。
I'm trying to bind a SQL string array to a prepared statement, and for some database charset, the values of the array become null. If I bind simple strings (not in array), it works.
如果字符集（NLS_CHARACTERSET在v $ nls_parameters）是AL32UTF8，如果是WE8ISO8859P15，那么我能够绑定字符串，但不能绑定字符串数组。差异似乎是Oracle JDBC具有特定
If the charset (NLS_CHARACTERSET in v$nls_parameters) is AL32UTF8, it works fine. If it is WE8ISO8859P15, then I'm able to bind strings, but not arrays of strings. The difference seems to be that Oracle JDBC has a specific list of character set for which conversion is supported, and ISO-8859-15 is not part of them.
这解释了问题的一部分，因为这是一个支持转换的字符集列表，它发现在DB中，它将所有字符串转换为null。但是当字符串不在数组中时，转换会起作用...所以我很困惑。
That explains part of the problem, as when it finds that in the DB, it converts all the string to null. But the conversion does work when the string is not in an array... So I'm confused.
我的整个测试如下。我使用的表类型定义为 create type t_v4000_table as table of varchar2（4000）;
My whole test is below. The table type I'm using is defined as create type t_v4000_table as table of varchar2(4000);
Connection connection;
@Before
public void setup() throws SQLException {
OracleDataSource ds = new OracleDataSource();
ds.setUser("aaa");
ds.setPassword("a");
ds.setURL("jdbc:oracle:thin:@server:1521:orcl");
connection = ds.getConnection();
}
@Test
// works with both AL32UTF8 and WE8ISO8859P15
public void testScalar() throws SQLException {
CallableStatement stmt = connection.prepareCall("declare a varchar2(4000) := ?; "
+ "begin if a is null then raise_application_error(-20000,'null'); end if; end;");
stmt.setString(1, "a");
stmt.execute();
}
@Test
// works only with AL32UTF8
public void testArray() throws SQLException {
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("T_V4000_TABLE", connection);
String[] array = new String[] {"a"};
Array sqlArray = new ARRAY(descriptor, connection, array);
CallableStatement stmt = connection.prepareCall("declare a t_v4000_table := ?; " +
"begin if a(1) is null then raise_application_error(-20000,'null'); end if; end;");
stmt.setArray(1, sqlArray);
stmt.execute();
}
我怀疑我在声明和绑定时做错了我的数组，但我找不出什么。任何想法？
I suspect that I'm doing something wrong in the way I declare and bind my array, but I can't find out what. Any idea?
推荐答案
解决方案以及对象/集合中的字符串之间的区别，已记录在案中，实际上：
The solution, as well as the distinction between string inside objects/collections or not, is well documented, actually:
基本Java Archive（JAR）文件ojdbc5.jar和ojdbc6.jar包含所有必要的类，以提供完整的全球化支持：
The basic Java Archive (JAR) files, ojdbc5.jar and ojdbc6.jar, contain all the necessary classes to provide complete globalization support for:
- 未检索或作为Oracle对象或集合类型的数据成员插入的CHAR，VARCHAR，LONGVARCHAR或CLOB数据的Oracle字符集。
- 字符集US7ASCII，WE8DEC，WE8ISO8859P1，WE8MSWIN1252和UTF8的对象和集合的CHAR或VARCHAR数据成员。
要在对象或集合的CHAR或VARCHAR数据成员中使用任何其他字符集，您必须在应用程序的CLASSPATH环境变量中包含orai18n.jar。
To use any other character sets in CHAR or VARCHAR data members of objects or collections, you must include orai18n.jar in the CLASSPATH environment variable of your application.
在CLASSPATH中添加orai18n.jar之后，它就像一个charm
After adding orai18n.jar in the CLASSPATH, it works like a charm
这篇关于Jdbc数组绑定：字符集编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
