Prev: JSF + XLS ?
Next: Preventing Memory Leak when using HashMap
From: markspace on 10 Feb 2010 22:59
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?



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.
From: Lew on 10 Feb 2010 23:41
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
From: markspace on 11 Feb 2010 00:30
Lew wrote:

>
> 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

Oh, no I was totally ignorant of that fact. I'm used to varchar, I suppose.


> that the OP mentioned that using a non-prepared statement successfully
> matched on the trimmed string (the part you didn't quote).

Yeah I saw that but I assumed it was just some idiosyncrasy of the sql
compiler.


> 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.


Yeah, I'd assume that he's going to have to pad his input strings then.
Shouldn't be too hard if he knows the length. He asked his database
to use fixed length strings so I guess he should too.



From: Lew on 11 Feb 2010 13:24
Alex Kizub wrote:
> PerparedStatement ps=con.prepareStatement("select text from table1

You should copy and paste your code into the post.

> where text='A'"); // A without space
> brings the same 'A ' // A+space
>
> ps=con.prepareStatement("select text from table1 where text=?"); //
> parameter
> ps.setString(1,"A "); // A+space

> brings, of course, the same 'A ' // A+space
>
> ps.setString(1,"A"); // A without space
> brings nothing

Wrong 'setX()' method. According to the docs at
<http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html#setString(int,%20java.lang.String)>
> The driver converts this to an SQL VARCHAR or LONGVARCHAR value
> (depending on the argument's size relative to the driver's limits
> on VARCHAR values) when it sends it to the database.

So it's not a bug in 'setString()' ut your attempt to use it contrary to the
documented purpose.

I don't see a method specific to SQL CHAR types.

This tells me that providing a value to 'setString()' of the correct length
for the CHAR column is the only portable approach.

> Any suggestions except make text match length of the database field
> which makes application schema dependent?

There is no such animal, other than non-portable approaches.

I reached this conclusion by a combination of reading the Javadocs and
googling around. I presume you tried both of those things, too. I may well
have missed something obvious there; it's happened to me before.

You can, however, use
<http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html#getMetaData()>
to dynamically retrieve the length for padding purposes.

--
Lew
 | 
Pages: 1
Prev: JSF + XLS ?
Next: Preventing Memory Leak when using HashMap