Monday, February 18, 2008

Oracle JDBC PreparedStatement & setString Issue

I've noticed some interesting behavior with the Oracle JDBC driver (version 10g, ojdbc14.jar).

This interesting "feature" surrounds the behavior of the PreparedStatement. The easiest way to describe the issue is with an example.

Table A has one column, b CHAR(3).

Query, SELECT * FROM A WHERE b = ?

My intended query was SELECT * FROM A WHERE b = ' ', but the Oracle JDBC driver actually was building this query, SELECT * FROM A WHERE b = ''.

The problem is that when calling setString() on the PreparedStatement to populate the ?, the PreparedStatement strips any leading or trailing spaces.

Normally this wouldn't be an issue and the driver would actually be doing me a favor. However, in my case where I was trying to substitute the ' ' space character it was a big problem.

The easiest way I found to get around this behavior was to simply not use a PreparedStatement. By using a normal Statement, I could control how the substitution was processed and allow the proper query to be generated.

No comments: