[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1261
  • Last Modified:

cfqueryparam dynamic sql

Suppose I have a
<cfoutput>
<cfsavecontent variable="sqlWhere"
and x = <cfqueryparam value=#x# ...>
and y = <cfqueryparam value=#y# ...>
and z = <cfqueryparam value=#z# ...>
</cfsavecontent>
</cfoutput>

can I include this in a query

<cfquery name='testme" datasource="MM2005">
select * from myschema.mytable
where
1=1
and .... = ....
#preservesinglequotes(sqlWhere)#
</cfquery>

Is this possible...
(seems an app I am working on has several nested loops which are running basically 4 similar queries for a total 13 times each).

In the itermim
I would like to seperate the where clause into 2 where clauses and add the same where clause portion to each query appending to whatever is different in the where clauses.

sorry if it doesn't make sense, I just need to see how I can add <cfqueryparam's to a query when they are part of a variable (sqlWHERE).
0
astro26
Asked:
astro26
  • 4
  • 3
1 Solution
 
mrichmonCommented:
It should work, but

Why do you need to do it that way?

Why not have the dynamic portion of the where in the query like so:

<cfquery name='testme" datasource="MM2005">
select * from myschema.mytable
where
1=1
and
<cfif condition is true>
  x = <cfqueryparam value=#x# ...>
and y = <cfqueryparam value=#y# ...>
and z = <cfqueryparam value=#z# ...>
<cfelse>
a = <cfqueryparam value=#a# ...>
and b = <cfqueryparam value=#b# ...>
and c = <cfqueryparam value=#c# ...>
and d = <cfqueryparam value=#d# ...>
</cfif>
</cfquery>

0
 
astro26Author Commented:
There are about 5 if statements different conditions which determine what displays in query.  I made it one to keep it simple.

Rather than have all of these if statements being evaluated 52 times, I wanted to evaluate them one time, store the results in a sqlWhere variable and append that veriable to the cfquery tag that gets run 52 times.

if not compare(x,0)
do this cfparam
/cfif

if not compare(y,'red')
do this cfparam
/cfif

if not compare(z,'babelfish')
do this cfparam
/cfif

if not compare(zz,'abracadabra')
do this cfparam
/cfif
0
 
astro26Author Commented:
Suppose I have a
<cfoutput>
<cfsavecontent variable="sqlWhere"
and x = <cfqueryparam value=#x# ...>
and y = <cfqueryparam value=#y# ...>
and z = <cfqueryparam value=#z# ...>
</cfsavecontent>
</cfoutput>

can I include this in a query

<cfquery name='testme" datasource="MM2005">
select * from myschema.mytable
where
1=1
and .... = ....
#preservesinglequotes(sqlWhere)#
</cfquery>

this does not work.  I get a <cfqueryparam must be placed inside a cfquery tag error.

Therefore, I converted all the < to #chr(60)# and the > to #chr(62)#, but I get another error
Macromedia][Oracle JDBC Driver][Oracle]ORA-00936: missing expression
I am assuming that the cfquery tag has started to be evaluated and instead of seeing (param1) it sees <cfparam , but this should have already been parsed by the coldfusion tag (parser) preprocessor or whatever it is called.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
astro26Author Commented:
<cfoutput>
<cfsavecontent variable="sqlWhere"
and x = #chr(60)#cfqueryparam value=#x# ...#chr(62)#
and y = #chr(60)#cfqueryparam value=#y# ...#chr(62)#
and z = #chr(60)#cfqueryparam value=#z# ...#chr(62)#
</cfsavecontent>
</cfoutput>

but this returns the error listed in the post above.
<cfquery name='testme" datasource="MM2005">
select * from myschema.mytable
where
1=1
and .... = ....
#preservesinglequotes(sqlWhere)#
</cfquery>
0
 
mrichmonCommented:
The problem is that You cannot use cfqueryparam outside of a cfquery tag.

exactly as the error says.  WHen you convert it to chr(60) it is not going to convert it back when evaluating.

I do not see how it would be evaluated any more times in an if than in in the cfsaveconent method.

0
 
astro26Author Commented:
Just so I understand correctly, there is no way to dynamically add cfqueryparams to a cfquery tag unless they are already in a cfquery tag, thus must be manually entered and have switch/if statements around them?
0
 
mrichmonCommented:
As far as I understand....
0
 
mmc98dl1Commented:
I second that to mrichmon, you would need to put cfswitch/cfcase inside the query, then put in your cfqueryparams within the cfcase.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now