Oracle RBMS+PreparedStatment+CHAR(n) Column=Problem Retrieving Data

Noticed the following inconsistency with making a WHERE clause call while using a PreparedStatement against and OracleRDBMS CHAR(n) data-type column. A call to a WHERE col1 = ? was failing to make a match and return any data against a column in the DB when I was using prepStmt.setString(col1, "INPUT DATA STRING"); when I knew that string existed in the DB. The following is some quick notes I wrote up while resolving why:

Given an Oracle DB table with the following definition of columns:

  COLUMN1 CHAR(4),

  COLUMN2 VARCHAR(4)

1. prepStmt.setString(parameterIndex, "420");
2. ((oracle.jdbc.OraclePreparedStatement) prepStmt).setFixedCHAR(parameterIndex, "420");

The first statement will fail to return results matching for a value of 420 stored in the CHAR column as its padded out to the length specified (4 characters in this case). Using "420 " padded to the correct number of spaces will work but this means that you have to pad the value yourself (in much the same way as you would do for a plain Statement rather than a PreparedStatement, ie: WHERE COLUMN1 = '420 ' where the 420 3 character string has been padded out with blanks to the required 4 character length).

The second statement, the oracle specific method - setFixedCHAR(), can also be used and in this case when we pass in the string 420 not padded Oracle will do a comparison in much the same way as if the column had been of the type VARCHAR(4) not worrying about the padding requirements of the first case and in this case comparing and matching whether you pass in "420" or "420 " etc.

Furthermore using something like the following which I found suggested on various websites: setObject(obj,java.sql.Types.CHAR) still does NOT do what setFixedCHAR does.

Under the covers I haven't looked at how setFixedCHAR works (as its closed code) but the JavaDocs can be found in my link below and also provide some further examples of cases when matches do and dont work for various cases using the PreparedStatement.

Javadoc For OraclePreparedStatement - http://download.oracle.com/docs/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OraclePreparedStatement.html#setFixedCHAR_int__java_lang_String_

Whether the driver gets the column length using MetaData and pads it itself internally or whether it uses some form of ltrim(rtrim()) SQL call Im unsure of but essentially you can treat the setFixedCHAR() in the same way as a setString() against a VARCHAR column datatype. (Anyone know the answer and can provide a cool link with this info yell out). For performance reasons it may be more useful to pad out the data yourself, or even if there are no performance differences in the SQL generated by the driver for a setFixedCHAR(not-padded-input) and setString(padded-input) you may still prefer to use the more standard setString() method and pad out the data yourself rather than use the setFixedCHAR which is limited only to OraclePreparedStatement class. I'm not going to even try to get involved in which method is the best way to go here...primarily cos I really dont have a leaning myself strongly in any one direction. I can see cases where any one of the several ways that this can be resolved correctly are useful. I think the main point here is to understand the issue that CHAR(n) data type, combined with Oracle and PreparedStatement can bring about in terms of matching data and retrieving results and based on that knowledge making an appropriate choice of one of the several possible ways around this:

1. - use Statement and manually pad the data yourself. ie: WHERE column1 = '420 '
2. - use PreparedStatement and setString() and manually pad the data yourself, ie: prepStmt.setString(x, "420 ");
3. - use PreparedStatement and OraclePreparedStatement.setFixedCHAR(x, "420");

Note: the JDBC spec in section 8.3.1 (http://download.oracle.com/javase/1.3/docs/guide/jdbc/getstart/mapping.html#1055140) does actually talk about how it is the driver/databases responsibilities to actually pad out the data correctly for CHAR columns when doing comparisons or INSERT/UPDATE-ing the data. However the link below gives some further Oracle specific information:

http://download.oracle.com/docs/cd/B14117_01/java.101/b10979/datacc.htm#sthref910

The main thing to keep in mind is that this is only an issue for the case where we have the combination of Oracle RBDMS and PreparedStatement and CHAR(n) column.