Barry
Remember that as soon as you output a variable inside CFQUERY, it will
escape all your single quotes
so
SELECT *
FROM TABLE
WHERE Column = 'My Value'
will turn into
SELECT *
FROM TABLE
WHERE Column = ''My Value''
This will error big time
If your going to do it this way, you may need to use the
PreserveSingleQuotes() function
so
<cfquery name="qArticle" datasource="weblog">
#PreserveSingleQuotes(strSQL)#
</cfquery>
But I dont see why you would bother. You can use your CF condiftion logic
within the CFQUERY tags anyway, so why would you need to build the query
first, then pass it in as a whole string? The only reason I Caould see you
needing this is if you were passing the query into a custom tag or CFC for
processing.
Another thing. How are you compiling the SQL string? I hope your using
CFSAVECONTENT and not using CFSET strSQL = strSQL & "........."
Steve
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Beattie,
Barry
Sent: Sunday, 22 June 2003 10:21 PM
To: CFAussie Mailing List
Subject: [cfaussie] <CFQUERY > SQL as a string command
Hi all.
I'm using some of my ASP tricks and trying to build a SQL statement as a
string (eg: with conditional WHERE clauses) and executed with <CFQUERY>
I'm getting "Syntax error in query expression" although the SQL statement
itself looks - and works - fine.
To make the code (below) work, I've had to move the WHERE condition building
to within the CFQUERY tag with the rest of the SQL
This works (sort of) in this simple example but it's not the way I like to
work when things get more complicated.
is doing it this way (a string for a SQL command) impossible in CFMX?
eg:
<cfquery name="qArticle" datasource="weblog">
#strSQL#
</cfquery>
any ideas, thoughts, etc appreciated
cheers
barry.b
(PS: yeah I know about SP's)
<!--- the code I'm using --->
<cfparam name="sqlWhereClause" default="" type="string">
<cfparam name="FORM.selCategory" default="2">
<cfparam name="FORM.txtTitle" default="q">
<cfif FORM.selCategory GREATER THAN 0><!--- category IS selected --->
<cfif trim(FORM.txtTitle) eq ""><!--- title clue NOT selected --->
<cfset sqlWhereClause = " WHERE tArticle.idCategory =
#form.selCategory#">
<cfelse> <!--- title clue IS selected --->
<cfset sqlWhereClause = " WHERE tArticle.idCategory = #form.selCategory#
AND tArticle.titleArticle LIKE
'%#PreserveSingleQuotes(FORM.txtTitle)#%'">
</cfif>
<cfelse> <!--- category NOT selected --->
<cfif trim(FORM.txtTitle) neq ""><!--- title clue IS selected --->
<cfset sqlWhereClause = " WHERE tArticle.titleArticle
LIKE '%#PreserveSingleQuotes(FORM.txtTitle)#%'">
</cfif>
</cfif>
<cfset strSQL = "SELECT * FROM tArticle " & sqlWhereClause>
<!--- check how it looks --->
<cfoutput>#strSQL#</cfoutput>
<!--- run it --->
<cfquery name="qArticle" datasource="weblog">
#strSQL#
</cfquery>
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
