Query param not behaving as expected

Question

I'm using an :order query param to pass an order argument to my function. Unfortunately, it seems not to have an effect on the output.

The request debugging output shows the order argument is parsed correctly:

Parameter #2(cf_sql_varchar) = posts.createdAt ASC

Yet it still makes no difference to output. If I hard code the argument (ORDER BY ..., #arguments.order#), it works fine.

Any ideas?

public any function getPost(required numeric postId, string order)
{
    switch(arguments.order)
    {
        case "new":
            arguments.order = "posts.createdAt DESC";
            break;
        case "old": 
            arguments.order = "posts.createdAt ASC";
            break;
        default:
            arguments.order = "posts.score DESC";
    }

    local.post = new Query(dataSource=variables.wheels.class.connection.datasource);
    local.post.setSql("
        SELECT *
        FROM
        WHERE posts.id = :postId OR posts.parentId = :postId
        ORDER BY posts.postTypeId ASC, :order"
    );

    local.post.addParam(name="postId", cfsqltype="cf_sql_integer", value=arguments.postId, maxlength=10);
    local.post.addParam(name="order", cfsqltype="cf_sql_varchar", value=arguments.order, maxlength=20);
    local.post = local.post.execute().getResult();

    return local.post;

}

just a guess, try using a name other than "order", wondering if that's a keyword that the parser chokes on. — orangepips, Jun 02 '11 at 15:10
@orangepips @Al Everett, that would be a no! It appears Al is right that you can't bind params to the ORDER BY clause. — Mohamad, Jun 02 '11 at 15:50

score 3 · Accepted Answer · answered Jun 02 '11 at 15:29

3

If I remember correctly, queryparams won't work anywhere except in the where clause. So you're not dealing with a bug, but a limitation.

answered Jun 02 '11 at 15:29

CreativeNotice

236
2
6

1

You can use queryparams in join statements or in the select list as well. They are limited to where you would be able to put a SQL variable in the query (since that is what ColdFusion is doing with queryparams behind the scenes). – Mike Oliver Jun 06 '11 at 22:32
I think that queryParam is only used to insert dynamics values in a where clause. Personnally, for this kind of situation I do a concatenation like setQuery(" ... " & orderyBy) – LarZuK Jun 02 '11 at 19:49

Query param not behaving as expected

1 Answers1