Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel