JDBCTEMPLATE performs non padded comparison for CHAR sql Type. [DATAJDBC-44]

[spring-projects-issues]

Ashish Bilochi opened DATAJDBC-44 and commented

JDBCTEMPLATE performs non padded comparison for CHAR sql Type. If a field has CHAR type in database table and the data in that table has length lesser then the size of field, select query will not return any row.
For eg :
Table : Account
Field ACCT_CODE CHAR(4)
Value in database = 'CON '
sql Select * from ACCOUNT a where a.ACCT_CODE = ?
jdbctemplate.query(sql, new Object[]{"CON"}, new RowCallbackHandler());

This will not return any row since "CON" does not matches "CON ".

---

Affects: Ext 1.0 M1, Ext 1.0 M2, Ext 1.0 RC1, Ext 1.0 RC2, Ext 1.0 RC3, Ext 1.0 RC4, Ext 1.0 RELEASE

[spring-projects-issues]

Thomas Risberg commented

You would have to pad the argument you pass in. We can't do that since we have no idea what your use case requirements are. The comparison behavior also seems to be database specific.

-Thomas

[spring-projects-issues]

Ashish Bilochi commented

My Application is using Oracle data base. It has more then 1000 tables. There are cases where fields are of CHAR data type in database, while contains data of lesser length.

When I user jdbctemplate.query spring performs non padded comparison.

I have verified in your org.springframework.jdbc.core.StatementCreatorUtils.java.

public static void setParameterValue()

THis method currently uses

ps.SetString(index,param);

If we use

ps.setObject(index, param, OracleTypes.FIXEDCHAR)

this will compare the java string with the trimed value of database field and will retrieve the result.

This issue should be there for other databases also.

[spring-projects-issues]

Thomas Risberg commented

You can get this behavior by wrapping your value in an SqlParameterValue instance.

Here is a example:

new SqlParameterValue(OracleTypes.FIXEDCHAR, "CON")

The StatementCreatorUtils will use the type when setting the value.

-Thomas

[spring-projects-issues]

Thomas Risberg commented

Closing very old issue, please re-open if necessary