Java Database Connectivity (JDBC)

I am wrestling with a subtle difference between PreparedStatement and Statement with respect to issuing a select against a table with a CHAR(30) column.

Originally, our application used Statements and inserted values into a SQL template and then fired off the query. For example, a template may have stated:

SELECT * FROM MYTAB WHERE COLA = $colA

The value $colA was replaced with a String like 'ABC', and even though COLA was a CHAR(30) and the value was actually 'ABC ', the query would return a match. The same applies when issuing a query in SQL*Plus like SELECT * FROM MYTAB WHERE COLA = 'ABC'.

However, for efficiency reasons, we replaced the Statements with PreparedStatements as fillows:

SELECT * FROM MYTAB WHERE COLA = ?
then...
ps.setString(1, 'ABC');

The query no longer returns any matches.

We then modified the table definition, made COLA a VARCHAR2(30) and trimed all the white space in the values. Then the query worked again.

We do not want to change the database definitions since this may introduce unpredictable behaviour, but padding the bind variables is an uncofortable option as well.

Can anyone explain this behaviour or have any suggestions ?

Thanks !

//Nicholas