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