I have a tricky issue with the Oracle JDBC driver's handling of CHAR data types. Let's take this simple table:

create table x (c char(4));
insert into x (c) values ('a');  -- inserts 'a   '

So when I insert something into CHAR(4), the string is always filled with whitespace. This is also done when I execute queries like this:

select * from x where c = 'a';    -- selects 1 record
select * from x where c = 'a ';   -- selects 1 record
select * from x where c = 'a   '; -- selects 1 record

Here, the constant 'a' is filled with whitespace as well. That's why the record is always returned. This holds true when these queries are executed using a JDBC PreparedStatement as well. Now the tricky thing is when I want to use a bind variable:

PreparedStatement stmt = 
  conn.prepareStatement("select * from x where c = ?");
stmt.setString(1, "a");    // This won't return any records
stmt.setString(1, "a   "); // This will return a record
stmt.executeQuery();

This is a workaround:

PreparedStatement stmt = 
  conn.prepareStatement("select * from x where trim(c) = trim(?)");
stmt.setString(1, "a");    // This will return a record
stmt.setString(1, "a   "); // This will return a record
stmt.executeQuery();

EDIT: Now these are the constraints:

  • The above workaround is not desireable as it modifies both the contents of c and ?, AND it makes using indexes on c quite hard.
  • Moving the column from CHAR to VARCHAR (which it should be, of course) is not possible

EDIT: The reasons for these constraints is because I ask thi

Edit
Report