var q = new com.adobe.coldfusion.query();
q.setDatasource("cfartgallery");
var sql = "select mediatype from media";
if(structKeyExists(arguments, "something")) {
sql &= " where media like :search";
q.addParam(name="search",value="%#arguments.str#%",cfsqltype="cf_sql_varchar");
}
q.setSQL(sql);
var results = q.execute().getResult();
Does that make sense?
On Tue, Apr 26, 2011 at 1:32 PM, Kyle McLean <kmclea...@gmail.com> wrote:
>
> I've been working on a CF9 project, and I'm trying to take full advantage of
> the cfscript syntax for my components. I have many cases where I'm using
> dynamic SQL based on the existence of an optional argument in a function. In
> CF8 and below it'd look something like this:
>
> <cffunction name="testFunc" returntype="query">
> <cfargument name="testArg" type="string" required="false" />
>
> <cfquery name="testQuery" datasource="testDSN">
> SELECT
> *
> FROM
> TBL
> <cfif structKeyExists(arguments.testArg)>
> WHERE
> COL = '#arguments.testArg#'
> </cfif>
> </cfquery>
>
> <cfreturn testQuery />
> </cffunction>
>
> Is there an elegant way to do the same thing using cfscript? I haven't had
> any luck with string concatenation in the setSQL method of the query object
> (and it's kind of clunky to boot). I'd also hate to have to write two
> almost-identical blocks of code one with the where clause and one without and
> then apply the correct string to setSQL at runtime, although I assume that
> would solve my problem.
>
> I'd also like to use query binding, just to make things more complicated. Is
> there some sort of cleaner approach that I'm missing, or is this just one of
> the downsides to using cfscript?
>
> Thanks in advance!
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344004
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
