Problem on Oracle 9.i with char(?) attribute
I've a table 'MYTABLE' on Oracle 9.i with an attribute 'PHONENUMBER' char(8).
The table has a record with this value equal '834'.
I've different behaviour if I execute a select when I add spaces at this value.
If I execute a Statement object :
SELECT * FROM MYTABLE WHERE PHONENUMBER='834 '
I've one record.
If I execute a PreparedStatement object :
SELECT * FROM MYTABLE WHERE PHONENUMBER='834 '
I've no record.
Please..... Any one help me.
Pierluigi
[535 byte] By [
Pierluigia] at [2007-10-2 10:29:06]
Hi,
I had a very similiar problem today with Oracle 8.1.5. using the Oracle111.zip JDBC-Driver. Though I don't have a definite answer, I have something to add.
I also have a CHAR(8) Field filled with the value '123'. The statement
UPDATE foo SET bar='321' WHERE bar='123'
results in one row being updated using PL/SQL, a Statement Object or anything else but a prepared statement.
The same using a prepared statement fails to update any rows.
I tried
UPDATE foo set bar='321' WHERE bar like '123%'
and it worked with a prepared statement.
Further experimenting showed that using
UPDATE foo set bar='321' WHERE bar like '123'
worked also. The amount of blanks after the '123' is five. I have noticed that values in CHAR(n) Fields seem to be stored or at least returned in queries with return-values of exactly n length, regardless of length used, with blanks filling in the rest. Can you verify this ?
Could you please verify, whether your query would work if you fed '834<5 blanks> to your prepared statement ?
Rudi
