>- see footer for list info -<
If You have something along the lines of this:
<cfquery ...>
<cfif condition>
...some sql
</cfif>
<cfif condition>
...some sql
</cfif>
</cfquery>
Not really as the above is clear enough however when doing this...
<cfset mysql = "start of some sql..." />
<cfif condition ..>
<cfset mysql = mysql & "some more sql" />
</cfif>
<cfif condition ..>
<cfset mysql = mysql & "yet some more sql with single quotes,
etc..." />
</cfif>
I'd argue that using cfsavecontent is more readable...
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:dev-
> [EMAIL PROTECTED] On Behalf Of RichL
> Sent: 21 December 2006 11:59
> To: Coldfusion Development
> Subject: Re: [CF-Dev] Dynamic Where Syntax
>
> >- see footer for list info -<
> No worries... appreciate any input/advice for best practices
>
> Is there any advantage to using cfsavecontent when you can loop and
> build the where clauses up directly in the cfquery statement?
>
> On 12/21/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]>
wrote:
> > >- see footer for list info -<
> > You know what far be it from me to tell people how to write code - but
that
> > is one of my pet peeves - I cringe when I review code where people build
up
> > dynamic queries in variables using cfset... not picking on you at all
Rich
> > and I realize you wanted to use cfqueryparam but using savecontent for
> > building up dynamic queries is just so much more readable....
> >
> > Just my 2p
> > Kola
> >
> > > -----Original Message-----
> > > From: [EMAIL PROTECTED] [mailto:dev-
> > > [EMAIL PROTECTED] On Behalf Of Duncan Cumming
> > > Sent: 21 December 2006 11:30
> > > To: [email protected]
> > > Subject: Re: [CF-Dev] Dynamic Where Syntax
> > >
> > > >- see footer for list info -<
> > > have you tried using cfsavecontent? because of the loop you could
perhaps
> > setup a
> > > 1D array, store each individual 'whereclause' line in a separate cell
in
> > that array, using
> > > cfsavecontent to save it, then loop through that array in your query.
> > >
> > > Duncan Cumming
> > > New Media Developer
> > > Customer Relations Management / Education
> > > Fife Council
> > > 700 4105 / 01592 414105
> > >
> > > >>> [EMAIL PROTECTED] 21/12/2006 10:54 >>>
> > > >- see footer for list info -<
> > > Guys
> > >
> > > I am trying to build up a where clause dynamically as follows:
> > >
> > > <cfset whereClause = 'Where 1 = 0 or '>
> > >
> > > loop with i as a numeric index value...
> > >
> > > <cfset whereClause = whereClause & '(centreid = <cfqueryparam
> > > cfsqltype="cf_sql_numeric" value="#i#"> and cplname = <cfqueryparam
> > > cfsqltype="cf_sql_varchar" value="#attributes["cplname_#i#"]#">)'>
> > >
> > > end loop
> > >
> > > I am running a query as follows: select * from x #whereclause#
> > >
> > > The SQL is falling over with 'Line 1: Incorrect syntax near '<'.' but
> > > on the same error message it outputs the SQL being run as:
> > >
> > > select * from rik_viewCPLReferenceData Where 1 = 0 or (centreid =
> > > <cfqueryparam cfsqltype="cf_sql_numeric" value="78"> and cplname =
> > > <cfqueryparam cfsqltype="cf_sql_varchar" value="BSc Accounting for
> > > Management">)
> > >
> > > which is exactly what I want and if I copy and paste this and run it
> > > directly, all is well.
> > >
> > > my suspicion is that this SQL being shown on the screen is correct
> > > after it has been rendered to screen but not at the time CF picks it
> > > up and passes it to SQL Server?
> > >
> > > I have messed about with htmleditformat, htmlcodeformat, urldecode,
> > > urlencodedformat, de, evaluate, using '<' '>' both within the
> > > initial where clause creation and the insertion of the #whereClause#
> > > variable in the query and can't get anything to work.
> > >
> > > Can anybody shed some light on this or suggest a better way to do it
> > please?
> > >
> > > Many thanks
> > >
> > > --
> > > Rich
> > > _______________________________________________
> > >
> > > For details on ALL mailing lists and for joining or leaving lists, go
to
> > > http://list.cfdeveloper.co.uk/mailman/listinfo
> > >
> > > --
> > > CFDeveloper Sponsors:-
> > > >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
> > > >- Lists hosted by www.Gradwell.com -<
> > > >- CFdeveloper is run by Russ Michaels, feel free to volunteer your
help
> > -<
> > >
> > >
> > >
> ********************************************************************
> > > **
> > > This email and any files transmitted with it are confidential and
intended
> > solely for the
> > > use of the individual or entity to whom they are addressed and should
not
> > be
> > > disclosed to any other party.
> > > If you have received this email in error please notify your system
manager
> > and the
> > > sender of this message.
> > >
> > > This email message has been swept for the presence of computer viruses
but
> > no
> > > guarantee is given that this e-mail message and any attachments are
free
> > from
> > > viruses.
> > >
> > > Fife Council
> > > Tel: 08451 55 00 00
> > > ************************************************
> > >
> > > _______________________________________________
> > >
> > > For details on ALL mailing lists and for joining or leaving lists, go
to
> > > http://list.cfdeveloper.co.uk/mailman/listinfo
> > >
> > > --
> > > CFDeveloper Sponsors:-
> > > >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
> > > >- Lists hosted by www.Gradwell.com -<
> > > >- CFdeveloper is run by Russ Michaels, feel free to volunteer your
help
> > -<
> >
> >
> >
> > _______________________________________________
> >
> > For details on ALL mailing lists and for joining or leaving lists, go to
> http://list.cfdeveloper.co.uk/mailman/listinfo
> >
> > --
> > CFDeveloper Sponsors:-
> > >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
> > >- Lists hosted by www.Gradwell.com -<
> > >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help
-<
> >
>
>
> --
> Rich
> _______________________________________________
>
> For details on ALL mailing lists and for joining or leaving lists, go to
> http://list.cfdeveloper.co.uk/mailman/listinfo
>
> --
> CFDeveloper Sponsors:-
> >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
> >- Lists hosted by www.Gradwell.com -<
> >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help
-<
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
>- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
>- Lists hosted by www.Gradwell.com -<
>- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
