Mismatch in Statement and PreparedStatement execution in Oracle DB.

Discussion in 'Java' started by Alex Kizub, Feb 10, 2010.

  1. Alex Kizub

    Alex Kizub Guest

    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?
    Alex Kizub, Feb 10, 2010
    #1
    1. Advertising

  2. In article
    <>,
    Alex Kizub <> 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>
    John B. Matthews, Feb 11, 2010
    #2
    1. Advertising

  3. Alex Kizub

    markspace Guest

    Re: Mismatch in Statement and PreparedStatement execution in OracleDB.

    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.
    markspace, Feb 11, 2010
    #3
  4. Alex Kizub

    Lew Guest

    Re: Mismatch in Statement and PreparedStatement execution in OracleDB.

    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
    Lew, Feb 11, 2010
    #4
  5. Alex Kizub

    markspace Guest

    Re: Mismatch in Statement and PreparedStatement execution in OracleDB.

    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.
    markspace, Feb 11, 2010
    #5
  6. Alex Kizub

    Alex Kizub Guest

    Re: Mismatch in Statement and PreparedStatement execution in OracleDB.

    On Feb 11, 12:30 am, markspace <> 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...
    Alex Kizub, Feb 11, 2010
    #6
  7. Alex Kizub

    Lew Guest

    Re: Mismatch in Statement and PreparedStatement execution in OracleDB.

    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
    Lew, Feb 11, 2010
    #7
  8. Alex Kizub

    markspace Guest

    Re: Mismatch in Statement and PreparedStatement execution in OracleDB.

    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.
    markspace, Feb 11, 2010
    #8
  9. In article
    <>,
    Alex Kizub <> 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>
    John B. Matthews, Feb 11, 2010
    #9
  10. Alex Kizub

    Lew Guest

    Re: Mismatch in Statement and PreparedStatement execution in OracleDB.

    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
    Lew, Feb 11, 2010
    #10
  11. Alex Kizub

    Alex Kizub Guest

    Re: Mismatch in Statement and PreparedStatement execution in OracleDB.

    On Feb 11, 12:32 pm, "John B. Matthews" <> wrote:
    > In article
    > <>,
    >  Alex Kizub <> 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.h...>
    >
    > --
    > John B. Matthews
    > trashgod at gmail dot com
    > <http://sites.google.com/site/drjohnbmatthews>


    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.
    Alex Kizub, Feb 11, 2010
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Manoj S. P.
    Replies:
    2
    Views:
    4,398
    Manoj S. P.
    Jul 2, 2003
  2. Ken Philips
    Replies:
    4
    Views:
    48,930
    venkat.sadasivam
    Nov 5, 2008
  3. JY
    Replies:
    11
    Views:
    25,052
    Andrea Desole
    Feb 24, 2006
  4. ddog
    Replies:
    1
    Views:
    2,031
    Thomas Kellerer
    Feb 7, 2007
  5. Feyruz
    Replies:
    4
    Views:
    2,161
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page