Why can't I use the same cfqueryparam in two places in the same query when using CFScript?

Question

I'm not sure that I am doing something wrong, but it seems that you cannot use cfqueryparam more than once in a single request if you write it in CFScript.

This behavior is not consistent with CFML. I just rewrote a similar request from CFML to CFScript and I get the following error: cfsqlparam 'id' is not defined

 local.query = new Query(); local.query.setSql(" SELECT id FROM myTable WHERE myTable.id = :id OR myTable.parentId = :id "); local.query.addParam(name="id", cfsqltype="CF_SQL_INTEGER", value=arguments.id, maxlength=10); local.query.execute().getResult(); 

If I print OR myTable.parentId = :id , it works fine. Should I create a parameter for every place that I intend to use?

Answer 1

If it were CFQuery, you would have one cfqueryparam for each value:

 where myTable.id = <cfqueryparam... value="#arguments.id#" /> or myTable.parentid = <cfqueryparam ... value="#arguments.id#" /> 

so I assume that you need to do the same in the script:

 local.query.setSql(" SELECT id FROM myTable WHERE myTable.id = :id OR myTable.parentId = :pid "); local.query.addParam(name="id", cfsqltype="CF_SQL_INTEGER",value=arguments.id,maxlength=10); local.query.addParam(name="pid",cfsqltype="CF_SQL_INTEGER",value=arguments.id,maxlength=10); 

Regarding the question “why cannot one placeholder address both AddParam addresses”, I assume that it comes down to how the request is parsed by ColdFusion, because you specify two placeholders for the parameters, which, apparently, the parser expects to find two parameters defined ,