John Beynon

Here’s one that had me stumpted for a little while.

We have all our CFCs implemented using CFSCRIPT as CF9 has provided us the ability to do and we had a situation where we needed to perform a SQL query with an IN clause. In tags that’s a pretty routine query but in script we kept getting errors.

queryservice.setName("users");
queryservice.setdatasource(get('datasourcename'));
queryservice.addParam(name="usergroups",value=params.usergroups,list="yes");
result = queryservice.execute(sql="select email from users where user_group_id in (:usergroups) ");
writeDump(result.getResult());

Seems pretty straightforward right? We knew that params.usergroups may be a list so we’d accounted for that in the addparam – it’s a series of checkboxes for the user to select using this syntax we got the error message returned;

Named Sql parameter ‘usergroups)’ used in the query not found in the queryparams

Well, after some more head scratch we found the problem – when you’re using named parameters using a notation similar to symbols in Ruby with brackets in the SQL statement they need to have a space AFTER the name of the parameter! So (:usergroups) becomes (:usergroups ) – notice the extra space.

Hope that helps someone!

No related posts.