Oracle10和JDBC:如何使CHAR在比较时忽略尾随空格? [英] Oracle10 and JDBC: how to make CHAR ignore trailing spaces at comparision?
问题描述
我有一个查询
... WHERE PRT_STATUS ='ONT'...
... WHERE PRT_STATUS='ONT' ...
尽管prt_status字段定义为CHAR(5).因此，它总是被空格填充.查询不匹配任何结果.要使此查询有效，我必须要做
The prt_status field is defined as CHAR(5) though. So it's always padded with spaces. The query matches nothing as the result. To make this query work I have to do
... WHERE rtrim(PRT_STATUS)='ONT'
... WHERE rtrim(PRT_STATUS)='ONT'
有效.
这很烦人.
同时，我有几个纯Java DBMS客户端(Oracle SQLDeveloper和AquaStudio)对第一个查询没有问题，它们返回了正确的结果. TOAD也没有问题.
At the same time, a couple of pure-java DBMS clients (Oracle SQLDeveloper and AquaStudio) I have do NOT have a problem with the first query, they return the correct result. TOAD has no problem either.
我想他们只是将连接置于某种兼容模式(例如ANSI)，因此Oracle知道希望比较CHAR(5)而不考虑尾随字符.
I presume they simply put the connection into some compatibility mode (e.g. ANSI), so the Oracle knows that CHAR(5) expected to be compared with no respect to trailing characters.
如何处理我在应用程序中获得的Connection对象?
更新，我无法更改数据库架构.
UPDATE I cannot change the database schema.
解决方案，这确实是Oracle比较字段和传入参数的方式.
SOLUTION It was indeed the way Oracle compares fields with passed in parameters.
绑定完成后，该字符串通过PreparedStatement.setString()传递，该字符串将类型设置为VARCHAR，因此Oracle使用未填充的比较-失败.
When bind is done, the string is passed via PreparedStatement.setString(), which sets type to VARCHAR, and thus Oracle uses unpadded comparision -- and fails.
我尝试使用setObject(n，str，Types.CHAR).失败反编译表明Oracle忽略了CHAR并再次将其作为VARCHAR传递.
I tried to use setObject(n,str,Types.CHAR). Fails. Decompilation shows that Oracle ignores CHAR and passes it in as a VARCHAR again.
最终可行的变体是
setObject(n,str,OracleTypes.FIXED_CHAR);
这使代码无法移植.
UI客户端成功的原因不同-它们使用字符文字，而不是绑定.当我输入PRT_STATUS ='ONT'时，'ONT'是一个文字，因此使用填充方式进行比较.
The UI clients succeed for a different reason -- they use character literals, not binding. When I type PRT_STATUS='ONT', 'ONT' is a literal, and as such compared using padded way.
推荐答案
Note that Oracle compares CHAR values using blank-padded comparison semantics.
来自数据类型比较规则，
Oracle使用空白填充的比较 仅当两个值都在 比较是 数据类型CHAR，NCHAR，文本文字， 或USER返回的值 功能.
Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of datatype CHAR, NCHAR, text literals, or values returned by the USER function.
在您的示例中，如您所说明的，'ONT'是作为绑定参数传递的，还是它以文本形式内置到查询中?如果为绑定参数，请确保将其绑定为类型CHAR.否则，请验证所使用的客户端库版本，因为真正的Oracle旧版本(例如v6)对于CHAR具有不同的比较语义.
In your example, is 'ONT' passed as a bind parameter, or is it built into the query textually, as you illustrated? If a bind parameter, then make sure that it is bound as type CHAR. Otherwise, verify the client library version used, as really old versions of Oracle (e.g. v6) will have different comparison semantics for CHAR.
这篇关于Oracle10和JDBC:如何使CHAR在比较时忽略尾随空格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
