Mismatch in Statement and PreparedStatement execution in Oracle DB.

[Alex Kizub]

sqls:

CREATE TABLE table1
(
text CHAR(2)
);

insert into table1 values('A');
select text from table1 where text='A'; // A without space
select text from table1 where text='A '; // A+space

both selects bring 'A ' // A+space

Java:
ResultSet rs = stmt.executeQuery("select text from table1 where
text='A'");
brings the same 'A ' // A+space

PerparedStatement ps=con.prepareStatement("select text from table1
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

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

 

[John B. Matthews]

sqls:

CREATE TABLE table1
(
text CHAR(2)
);

insert into table1 values('A');
select text from table1 where text='A'; // A without space
select text from table1 where text='A '; // A+space

both selects bring 'A ' // A+space

Java:
ResultSet rs = stmt.executeQuery("select text from table1 where
text='A'");
brings the same 'A ' // A+space

PerparedStatement ps=con.prepareStatement("select text from table1
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

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

"To remedy this, Oracle has added the setFixedCHAR method to the
OraclePreparedStatement class." It's Oracle dependent, but it might
help.

<http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/datacc.htm#BABCHGCH>

 

[markspace]

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.

 

[Lew]

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>

 

[markspace]

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.

 

[Alex Kizub]

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


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


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.

You also missed this:

"Any suggestions __except make text match length of the database field
which makes application schema dependent___?"

So, based on this thread, solution should be schema dependent...

 

[Lew]

You also missed this:

"Any suggestions __except make text match length of the database field
which makes application schema dependent___?"

So, based on this thread, solution should be schema dependent...

If the "requirement" is unrealistic or cannot be met, and actually provides no
value, it shouldn't be a requirement. The OP shouldn't have that requirement.

You might as well as the doctor to cure a broken bone but demand that they not
set it.

One could make the lengths match without hard-coding the length, by using
metadata to determine the length. Still schema dependent, but not hard coded.
That might have been a reasonable requirement.

Whether the OP's requirement as originally stated is reasonable depends on the
answer to my question upthread:

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?

If the problem is inherent to prepared statements, then nothing can be done at
the Java level except to space-pad the arguments to the correct length.

 

[markspace]

"Any suggestions __except make text match length of the database field
which makes application schema dependent___?"

So, based on this thread, solution should be schema dependent...

I think he could check the schema, programmatically, and adjust the
range checking of the variable himself. That's not "schema dependent"
imo because it adjusts to the schema. There's only CHAR, VARCHAR, and
LONGVARCHAR that JDBC maps to String, so those should be the only types
he has to check, and LONGVARCHAR and VARCHAR I think would map to
String. For CHAR, he may have to come up with his own type based on
char[] to get the fixed length semantics he wants.

My guess is: this is what the sql compiler does when it compiles the
entire statement. Checks the type of the column, and pads out the
literal string provided. With a pre-compiled statement, it can't do
that because both parameters are not available at the same time, since
the input is not a literal. So it assumes the user will provide a
String (char[]) of the correct type (length), and just goes on its merry
way.

 

[John B. Matthews]

So, based on this thread, solution should be schema dependent...

Or vendor dependent:

<http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/datacc.htm#BABCHGCH>

 

[Lew]

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

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.

 

[Alex Kizub]

Or vendor dependent:

<http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/datacc.h...>

Thanks for all.
There was no problem at all.
Point was to pay attention that PreparedStetment works differently
from Statement. That's all.
Thanks again.