Coldfusion: executing dynamic query containing cfqueryparam

Question

First, the code. I'm making a struct of queries:

<cfset myQueryStruct = { 
qone = "select * from t1 where column = <cfqueryparam cfsqltype='cf_sql_varchar' value='#arguments.a1#'>",
qtwo = "select * from t2 where column = <cfqueryparam cfsqltype='cf_sql_varchar' value='#arguments.a1#'>"
}>

I need to execute the queries dynamically like below:

<cfquery name="qName" datasource="#dsn#">
    #myQueryStruct[arguments.type]#
</cfquery>

But I am getting an error:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '<'.

It seems the error is related with "<cfqueryparam cfsqltype='cf_sql_varchar' value='#arguments.a1#'>" in the query("<")

I tried using evaluate() and is getting this error:

Invalid CFML construct found on line 1 at column 15.
ColdFusion was looking at the following text:

t1

I know we can do this by using <cfif> ladder inside <cfquery> but my question is, can we do this like the way I mentioned(I must be missing something) or is it not possible in CFML?

You may want to look into the `cfscript` version of `cfqueryparam`. Can't say for sure, but it looks like it might apply to what you are attempting to do. https://cfdocs.org/cfqueryparam — snackboy, Oct 11 '17 at 14:30
Using CFQUERYPARAM is laudable, but using them with dynamic queries is diffidult. See Andreas answer below for a solution. Meanwhile, consider simply outputting your code in between the cfquery tags. That's what cfquery is designed for and one of the places I actually prefer tags to script - makes it easy to simply copy from SQL manager and add your bindings. — Mark A Kruger, Oct 11 '17 at 15:20

score 6 · Answer 1 · edited Oct 11 '17 at 18:35

The problem is, that you are passing a string with a tag that must be processed by ColdFusion. But you are too late. CF has already parsed the template, so the string remains untranslated and therefore throws an SQL error.

I'm not sure, what exactly you would like to accomplish here, maybe something like this code would work:

<cfscript>
dsn = "myDatabase";
criteria = "something";
myQueryStruct = {
    qone = "select * from t1 where column = ( :a1 ) ",
    qtwo = "select * from t2 where column = ( :a1 ) "
};

result1 = getQueryResult( queryString = myQueryStruct[ "qone" ], filterValue = criteria, dsn = dsn );
result2 = getQueryResult( queryString = myQueryStruct[ "qtwo" ], filterValue = criteria, dsn = dsn );

public function getQueryResult( queryString, filterValue, dsn ){
    var qry = new Query();
    var qryString = arguments.queryString;
    var queryResult = "";
    qry.setDatasource( arguments.dsn );
    qry.setSQl( qryString );
    qry.addParam( name="a1", value= arguments.filterValue, cfsqltype="cf_sql_varchar" ); 
    queryResult = qry.execute().getResult();
    return queryResult;
}
</cfscript>

Thanks, Andreas. Do you think this is possible using CFML tags? — Binod Kalathil, Oct 12 '17 at 06:43
The project is not using script. That is why. I am new to CF ecosystem.. What do you think about the 2 variants - tag and script? Each have its own advantages or one is superior? — Binod Kalathil, Oct 16 '17 at 09:17

score 0 · Answer 2 · answered Oct 17 '17 at 18:44

0

You could use cfparam to establish the arguments.a1 and type of data first and then just reference the variables as '#arguments.a1#' within the query. It should help you at least figure out if it is the cfqueryparam tag that is causing the problem.

answered Oct 17 '17 at 18:44

Jason Peterman

85
6

Coldfusion: executing dynamic query containing cfqueryparam

2 Answers2

Linked