Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    26

    Unanswered: direct sql exec right,but paramed sql passed by JDBC exec wrong

    Hi,I have a table which has a field of type char(5),field name is 'cust_area',and I use JDBC to pass my querys.
    The Driver class for oracle is: oracle.jdbc.driver.OracleDriver

    then I used this sql to query customers:
    select * from customers where cust_area='371'
    it returned right records.

    and I used this sql:
    select * from customers where cust_area=?
    and pass it with '371',it returned nothing!

    I had thought that the problem may be cause of the field's type.And if I change sql to 'select * from customers where trim(cust_area)=?',it works! But it run so slowly because it can't use the index of cust_area

    anybody can tell me why?
    and is there a way to use JDBC's param mechanism to access oracle without modify the field type(cause I have no right to change the field type)
    ......

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Never use CHAR for columns in Oracle - always use VARCHAR2. CHAR values are always padded out to their full length, which causes issues like the one you just hit.

  3. #3
    Join Date
    Jan 2004
    Posts
    26
    yes,char values really brought me problems,but unfortunately i cannt make any changes to it......that mean i had to make any way to do my work with char values even i dont like it

    could u give me any idea about why the 'cust_are='371'' can get results while using param cannt? I may think it is oracle's different explanation to each way,but i dont know exactly.
    ......

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    First, if you cannot change it then presumably someone else can, right? Someone owns this database! You should advise them to change it, because using CHAR to store variable length data (as you are) is flat-out wrong! They really, really don't want to be doing this!

    Second, you can avoid trimming the column (and so disabling the index) by padding the argument instead:

    select * from customers where cust_area=rpad(?,5)

    See docs for why Oracle treats the bind variable differently to the literal. I guess the literal is treated as a CHAR and so blank-padded semantics are used, whereas the bind variable is treated as VARCHAR2,so non-padded semantics are used.

    You cannot change Oracle's behaviour, so you must either stop using CHAR or work around its unpleasant ways.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •