Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > Mismatch in Statement and PreparedStatement execution in Oracle DB.

Reply
Thread Tools

Mismatch in Statement and PreparedStatement execution in Oracle DB.

 
 
Alex Kizub
Guest
Posts: n/a
 
      02-10-2010
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?
 
Reply With Quote
 
 
 
 
John B. Matthews
Guest
Posts: n/a
 
      02-11-2010
In article
<(E-Mail Removed)>,
Alex Kizub <(E-Mail Removed)> 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>
 
Reply With Quote
 
 
 
 
markspace
Guest
Posts: n/a
 
      02-11-2010
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.
 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      02-11-2010
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
 
Reply With Quote
 
markspace
Guest
Posts: n/a
 
      02-11-2010
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.



 
Reply With Quote
 
Alex Kizub
Guest
Posts: n/a
 
      02-11-2010
On Feb 11, 12:30*am, markspace <(E-Mail Removed)> 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...
 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      02-11-2010
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
 
Reply With Quote
 
markspace
Guest
Posts: n/a
 
      02-11-2010
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.
 
Reply With Quote
 
John B. Matthews
Guest
Posts: n/a
 
      02-11-2010
In article
<(E-Mail Removed)>,
Alex Kizub <(E-Mail Removed)> wrote:

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

--
John B. Matthews
trashgod at gmail dot com
<http://sites.google.com/site/drjohnbmatthews>
 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      02-11-2010
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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Difference between Statement and preparedStatement (for SQL databases) ? Ken Philips Java 4 11-05-2008 01:04 AM
PreparedStatement Insert statement MS SQL Server problem ddog Java 1 02-07-2007 05:37 PM
get SQL statement from PreparedStatement JY Java 11 02-24-2006 05:23 PM
install_driver(Oracle) failed: Can't load 'C:/Perl/site/lib/auto/DBD/Oracle/Oracle.dll' for module DBD::Oracle: load_file:The specified procedure could not be found at C:/Perl/lib/DynaLoader.pm line 230. Feyruz Perl Misc 4 10-14-2005 06:47 PM
Execution time problem with Insert Query using PreparedStatement Manoj S. P. Java 2 07-02-2003 03:52 PM



Advertisments