In Scripted Coldfusion Query - why would queryParams not get added?

Question

I have a dynamically generated query, all but one params are optional with one required field. I thought I would be smart but something very odd happens.

-cfscript
// Standard new query(), set name, datasource etc

query.addParam(value=requiredParam, type=cf_sql_varchar);
sqlStatement = "select ....";

if (optional field 2){
    sqlStatement &= "and field like '%?%' ";
    query.addParam(value=optionalValue, type=cf_sql_varchar);
}
... optional fields 2,3,4,5 repeats ...
query.execute(sql=sqlStatement);

So when I execute passing only the required field, the query is correct as are the results. dump shows just the required entry in the sqlParameter (with 2 results)

When I add calls with the optional arguments, only the first (required argument is shown). The optional arguments are NOT shown (in the result dump) and the result set goes to 0 rows. (?? very odd)

I traced it with the debugger, (from cf8 extenstions, cfeclipse, not builder, on CF9 standalone)

The optional field logic was correct, the code was tested and executed. In the variables pane the params seemed to be added properly but don't show up in the result query, (dump on the result page) only the required one.

I am guessing something odd is happening, but this does not make sense. Is it possible that all the addParams have to be added together? Is the like (syntax) the problem?

Why would working with ifs/conditional prevent additional addParams to not work/showup? Some odd display issue from dump?

Sorry about not providing real code, in a sensitive area.

Answer 1

PEBKAC ...

Wrote

if (optional field 2){
    sqlStatement &= "and field like '%?%' ";
    query.addParam(value=optionalValue, type=cf_sql_varchar);
}

Should have been:

if (optional field 2){
    sqlStatement &= "and field like ? ";
    query.addParam(value='%#optionalValue#%', type=cf_sql_varchar);
}

Query works properly now, returns correct rows and the parameters used show up in the dump ...

Answer 2

If you have multiple fields I think it would be best to use the name parameter:

if (optional field 1) {
    sqlStatement &= "AND Field LIKE :field1 ";
    query.addParam( name="field2", value="%#optionalvalue#%", type=cf_sql_varchar);

}
if (optional field 2) {
    sqlStatement &= "AND Field LIKE :field2 ";
    query.addParam( name="field2", value="%#optionalvalue#%", type=cf_sql_varchar);
}

Good reading: http://www.bennadel.com/blog/1678-Learning-ColdFusion-9-Using-CFQuery-And-Other-Service-Tags-In-CFScript.htm

Note the bug about putting a space behind the :name in the statement in some cases.