Prepared statement. Can I miss out parameter?

Question

I use queries like

"UPDATE MAILSCH.MESSAGE "
            + "SET IDFOLDER=?, SUBJECT=?, CONTENT=?, CREATIONTIME=?, AD_FROM=?, AD_TO=?, STATUS=? "
            + "WHERE IDMESSAGE=?";

May I miss out IDFOLDER without changing query?

Answer 1

No you can't. You need to conditionally insert that part of the SQL into the SQL string when needed.

Answer 2

No, you'll have to write a second query that doesn't include the IDFOLDER column. All parameters have to be bound.

Answer 3

The javadoc doesn't explicitly say that you can't, but when I tried it, I got an exception like this:

java.sql.SQLException: Parameter not set

Answer 4

Unfortunately not. Positional parameters ('?') are exactly that, identified by their position or order of appearance in the query. If you remove the 'IDFOLDER=?', then you will assign the wrong parameters to the remainder of the query, and possibly get an exception since the number of assigned parameters do not match the number expected in the query.

I'm assuming you can't change the source code, as that's the simplest route - change the SQL and then the JDBC parameters to match. If you need to use the same number of parameters, you can write the query in a way that doesn't change the value of IDFOLDER, yet uses the first parameter.

SET IDFOLDER=CASE ISNULL(?) WHEN 0 THEN IDFOLDER ELSE IDFOLDER END

If your JDBC driver supports named parameters, that may give you a cleaner alternative.