Prev: Best Way to Process Large Text Files
Next: finite state machine with enum
From: Alex Kizub on 10 Feb 2010 10:59
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?
From: John B. Matthews on 10 Feb 2010 21:59
In article
<f0dae4c2-4b01-46ee-ab7a-f99bcad9b71e(a)c4g2000yqa.googlegroups.com>,
Alex Kizub <akizub(a)gmail.com> wrote:

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

--
John B. Matthews
trashgod at gmail dot com
<http://sites.google.com/site/drjohnbmatthews>
From: Alex Kizub on 11 Feb 2010 10:42
On Feb 11, 12:30 am, markspace <nos...(a)nowhere.com> wrote:
> 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.

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...
From: Lew on 11 Feb 2010 11:27
Alex Kizub wrote:
> 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.

--
Lew
From: markspace on 11 Feb 2010 11:30
Alex Kizub wrote:

> "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.
 |  Next  |  Last
Pages: 1 2
Prev: Best Way to Process Large Text Files
Next: finite state machine with enum