Problems with string parameter insertion into prepared statement

Question

Hi,

I have a database running on an MS SQL Server. My application communicates via JDBC and ODBC with it. Now I try to use prepared statements.

When I insert a numeric (Long) parameter everything works fine. When I insert a string parameter it does not work. There is no error message, but an empty result set.

WHERE column LIKE ('%' + ? + '%') --inserted "test" -> empty result set
WHERE column LIKE ? --inserted "%test%" -> empty result set
WHERE column = ? --inserted "test" -> works

But I need the LIKE functionality. When I insert the same string directly into the query string (not as a prepared statement parameter) it runs fine.

WHERE column LIKE '%test%'

It looks a little bit like double quoting for me, but I never used quotes inside a string. I use preparedStatement.setString(int index, String x) for insertion.

What is causing this problem? How can I fix it?

Thanks in advance.

Answer 1

What are you inserting at '?'

If you are inserting

test

Then this will result in

WHERE column LIKE ('%' + test + '%')

which will fail. If you are inserting

"test"

Then this will result in

WHERE column LIKE ('%' + "test" + '%')

Which will fail. You need to insert

'test'

Then this will result in

WHERE column LIKE ('%' + 'test' + '%')

And this should work.

I don't know why = "test" works, it should not unless you have a column called test.

Answer 2

I am using SUN's JdbcOdbcBridge. As far as I read yet, you should avoid to use it. Maybe there is a better implementation out there.

For now, I wrote the folling method. It inserts string-type parameters into the statement with string operations before the statement is compiled. You should build a map of the parameters with the parameter index as the key and the value as the parameter itself.

private static String insertStringParameters(String statement, Map<Integer, Object> parameters) {
    for (Integer parameterIndex : parameters.keySet()) {
        Object parameter = parameters.get(parameterIndex);
        if (parameter instanceof String) {
            String parameterString = "'" + (String) parameter + "'";
            int occurence = 0;
            int stringIndex = 0;
            while(occurence < parameterIndex){
                stringIndex = statement.indexOf("?", stringIndex) + 1;
                occurence++;
            }
            statement = statement.substring(0, stringIndex - 1) + parameterString + statement.substring(stringIndex);
        }
    }
    return statement;
}