Re: Mismatch in Statement and PreparedStatement execution in Oracle
DB.
Alex Kizub wrote:
ps.setString(1,"A "); // A+space
brings, of course, the same 'A ' // A+space
ps.setString(1,"A"); // A without space
brings nothing
Any suggestions except make text match length of the database field
which makes application schema dependent?
markspace wrote:
This strikes me as a classic Garbage In Garbage Out problem. You put
garbage in your database that you don't want to match, and then you
complain when it doesn't match. Gee, really?
You'll probably have to use some sort of substring function to get rid
of the junk in your DB.
select text from table1 where LTRIM(RTRIM(text))=LTRIM(RTRIM(?))
Normally one trims off white space before doing an insert or update, imo.
You seem to be ignoring the fact that the SQL CHAR column type space-pads its
entries. Even when you trim the input. By law. And that the OP mentioned
that using a non-prepared statement successfully matched on the trimmed string
(the part you didn't quote).
<http://en.wikipedia.org/wiki/SQL#Character_strings>
Seems to me that if one knows one is dealing with a CHAR type column, that
it's worth the effort to make sure that values used to compare or insert into
that column should be padded to that column's length in the Java code anyway.
I am curious whether regular database prepared statements would have the same
problem, that is, independently of whether JDBC is involved. Or is this
something that JDBC drivers get wrong?
As for the use of oracle.sql.CHAR, be aware of the advice in
<http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/oraint.htm#i1064692>
--
Lew