Ah yes. Thanx Taco
I did have to resort to putting the conditions within the <CFQUERY> tag to
get to work last night (great minds think alike, eh?) and yes, it is a
solution. But can you imagine what it would look like if this wasn't such a
simple SQL statement? It'd be almost unreadable - spaghetti code!
as for SP's, below is the same thing using <cfstoredproc> as well as a
similar ASP version.
Is there any way of rewriting the <CFSTOREDPROC> version so it's as neat and
simple as the ASP version?
thanx
barry.b
++++++++++++++++++++++ similar ASP version
++++++++++++++++++++++++++++++++++++
dim title, langid
title = replace(request.Form("title"),"'","''")
langid = request.Form("select2")
if not len(title) > 0 and not len(langid) > 0 then
sql = "exec qry_search_all"
elseif len(title) > 0 and not len(langid) > 0 then
sql = "exec qry_search_by_title '%" & title & "%' "
elseif not len(title) > 0 and len(langid) > 0 then
sql = "exec qry_search_by_langid " & langid
else
sql = "exec qry_search_by_title_langid '%" & title & "%', " & langid
end if
conn.Open dsn
set rs = conn.execute(sql)
++++++++++++++++++++++ CF using CFSTOREDPROC
++++++++++++++++++++++++++++++++++
<cfparam name="FORM.selCategory" default="">
<cfparam name="FORM.txtTitle" default="a">
<cfif FORM.selCategory GREATER THAN 0><!--- category IS selected --->
<cfif trim(FORM.txtTitle) eq ""><!--- title clue NOT selected --->
<cfstoredproc procedure="qsp_articles_by_idCategory"
datasource="weblog">
<cfprocparam type="In" cfsqltype="cf_sql_integer"
null="no" value="#form.selCategory#">
<cfprocresult name="qArticle">
</cfstoredproc>
<cfelse> <!--- title clue IS
selected --->
<cfstoredproc
procedure="qsp_articles_by_titleArticle_and_idCategory" datasource="weblog">
<cfprocparam type="In" cfsqltype="cf_sql_varchar"
null="no" value="*#PreserveSingleQuotes(FORM.txtTitle)#*">
<cfprocparam type="In" cfsqltype="cf_sql_integer"
null="no" value="#form.selCategory#">
<cfprocresult name="qArticle">
</cfstoredproc>
</cfif>
<cfelse> <!--- category NOT
selected --->
<cfif trim(FORM.txtTitle) neq ""><!--- title clue IS selected --->
<cfstoredproc procedure="qsp_articles_by_titleArticle"
datasource="weblog">
<cfprocparam type="In" cfsqltype="cf_sql_varchar"
null="no" value="*#PreserveSingleQuotes(FORM.txtTitle)#*">
<cfprocresult name="qArticle">
</cfstoredproc>
<cfelse> <!--- nothing selected:
search all --->
<cfstoredproc procedure="qsp_articles_all"
datasource="weblog">
<cfprocresult name="qArticle">
</cfstoredproc>
</cfif>
</cfif>
-----Original Message-----
From: Taco Fleur [mailto:[EMAIL PROTECTED]
Sent: Monday, 23 June 2003 11:26 AM
To: CFAussie Mailing List
Subject: [cfaussie] RE: <CFQUERY > SQL as a string command
Barry,
How about
<cfparam name="FORM.selCategory" default="2">
<cfparam name="FORM.txtTitle" default="q">
<cfquery name="qArticle" datasource="weblog">
SELECT *
FROM tArticle
<cfif FORM.selCategory GT 0>
<cfif len(FORM.txtTitle)>
WHERE tArticle.idCategory = <cfqueryparam
value="#form.selCategory#" cfsqltype="CF_SQL_INTEGER">
<cfelse>
WHERE tArticle.idCategory = <cfqueryparam
value="#form.selCategory#" cfsqltype="CF_SQL_INTEGER">
AND tArticle.titleArticle
LIKE '%<cfqueryparam value="#form.txtTitle#"
cfsqltype="CF_SQL_VARCHAR">%'
</cfif>
<cfelse>
<cfif len(FORM.txtTitle)>
WHERE tArticle.titleArticle
LIKE '%<cfqueryparam value="#form.txtTitle#"
cfsqltype="CF_SQL_VARCHAR">%'">
</cfif>
</cfif>
</cfquery>
I have not tested it, but it should work...
And since you know about Stored Procedures, why not use them? They are
fast, more secure and the above is easy to archieve with them.
Taco
PS. Let me know if it works...
-----Original Message-----
From: Beattie, Barry [mailto:[EMAIL PROTECTED]
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/
----------------------------------------------------------------------------
This email, together with any attachments, is intended for the named
recipient(s) only and may contain privileged and confidential information.
If
received in error, please inform the sender as quickly as possible and
delete
this email and any copies from your computer system network.
If not an intended recipient of this email, you must not copy, distribute or
rely upon it and any form of disclosure, modification, distribution and/or
publication of this email is prohibited.
Unless stated otherwise, this email represents only the views of the sender
and
not the views of the Queensland Government.
----------------------------------------------------------------------------
---
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/
