Oracle JDBC and Oracle CHAR data type

Question

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 this question from the point of view of the developer of jOOQ, a database abstraction library. So my requirements are to provide a very generic solution that doesn't break anything in jOOQ's client code. That is why I'm not really a big fan of the workaround. And that's why I don't have access to that CHAR column's declaration. But still, I want to be able to handle this case.

What would you do instead? What's a good practice for handling CHAR data types when I want to ignore trailing whitespace?

Answer 1

Gary's solution works well. Here's an alternative.

If you are using an Oracle JDBC driver, the call to prepareStatement() will actually return an OraclePreparedStatement, which has a setFixedCHAR() method that automatically pads your inputs with whitespace.

String sql = "select * from x where c = ?";
OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement(sql);
stmt.setFixedCHAR(1, "a");
...

Obviously, the cast is only safe if you are using the Oracle driver.

The only reason I would suggest that you use this over Gary's answer is that you can change your column sizes without having to modify your JDBC code. The driver pads the correct number of spaces without the developer needing to know/manage the column size.

Answer 2

If you want

stmt.setString(1, "a");    // This won't return any records

to return a record, try

conn.prepareStatement("select * from x where c = cast(? as char(4))")

Answer 3

I have nice fix for this. You have to add one property while getting connection from database.

NLS_LANG=american_america.AL32UTF8

or in Java connection you can use below code:

java.util.Properties info = new java.util.Properties();
info.put ("user", user);
info.put ("password",password);
info.put("fixedString","TRUE");
info.put("NLS_LANG","american_america.AL32UTF8");
info.put("SetBigStringTryClob","TRUE");
String url="jdbc:oracle:thin:@"+serverName;
log.debug("url="+url);
log.debug("info="+info);
Class.forName("oracle.jdbc.OracleDriver");
conn  = DriverManager.getConnection(url,info);

Answer 4

the other way is modify your sql as

select * from x where NVL(TRIM(c),' ') = NVL(TRIM('a'),' ')

Answer 5

I don't see any reason to use CHAR datatype even if it is char(1) in Oracle. Can you change the datatype instead?