Oh my goodness me, still wrong! Should be:
SELECT email
FROM user u
WHERE country = <cfqueryparam cfsqltype="cf_sql_varchar"
value=#somecountry#>
AND ( Len('<cfqueryparam cfsqltype="cf_sql_varchar" value=#somestate#>') = 0
OR state = <cfqueryparam cfsqltype="cf_sql_varchar" value=#somestate#>)
Phew!
Dom
On 24/10/2007, Dominic Watson <[EMAIL PROTECTED]> wrote:
>
> Woops, I made a mistake in that SQL, should have been:
>
> <cfquery name="myname" datasource="myDB">
>
> SELECT email
> FROM user u
> WHERE country = <cfqueryparam cfsqltype="cf_sql_varchar"
> value=#somecountry#>
> AND ( Len(<cfqueryparam cfsqltype="cf_sql_varchar" value=#somestate#>) =
> 0
> OR state = <cfqueryparam cfsqltype="cf_sql_varchar" value=#somestate#>)
> </cfif>
> </cfquery>
>
> On 24/10/2007, Dominic Watson <[EMAIL PROTECTED]> wrote:
> >
> > If you are talking about whether, in the case of your multiple IF
> > statement DSQL, to use or not use CFQP then I think there is only one
> > answer; use CFQP! As I said before, if there is a performance boost in using
> > CFQP over not using it, then that performance boost should still be valid.
> > Also, all the arguments of security probably outweigh any performance issues
> > there may be.
> >
> > If you are talking about finding an alternative to the multiple IF DSQL
> > then I would say that if it is at all possible, then do it! I.e., in the
> > example you gave, you could replace the CFIF with this:
> >
> > <cfquery name="myname" datasource="myDB">
> >
> > SELECT email
> > FROM user u
> > WHERE country = <cfqueryparam cfsqltype="cf_sql_varchar"
> > value=#somecountry#>
> > AND ( Len(<cfqueryparam cfsqltype="cf_sql_varchar" value=#somestate#>)
> > > 0
> > OR state = <cfqueryparam cfsqltype="cf_sql_varchar"
> > value=#somestate#>)
> > </cfif>
> > </cfquery>
> >
> > Dom
> >
> > On 24/10/2007, Ben Mueller <[EMAIL PROTECTED] > wrote:
> > >
> > > Thanks, all, for your replies. So, a follow-up question: will SQL
> > > create multiple prepared statements for each condition? If so, that's
> > > great. My fear is that it will only create 1 prepared statement for each
> > > cfquery block, so if the current statement doesn't match the previous
> > > statement, it would discard the previous one and create a new one.
> > >
> > > I have a bunch of queries that have 8 or more <cfif> conditions,
> > > almost all in the WHERE clause. Obviously, the more <cfif> conditions
> > > there
> > > are, the greater the likelihood that any one individual call won't match
> > > the
> > > previous one.
> > >
> > > I know cfqp is really good and all that, but this would be pretty
> > > annoying if I got no real performance benefit.
> > >
> > >
> > >
> > > >
> > > >Yes, to the extent that you are less likely to have another query
> > > with the
> > > >same prepared statement (and the same execution plan, of course) that
> > > can be
> > > >run again. Frankly, though, I wouldn't worry too much about that,
> > > since
> > > >coding around that causes all kinds of problems.
> > > >
> > > >Dave Watts, CTO, Fig Leaf Software
> > > >http://www.figleaf.com/
> > >
> > >
> > >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion 8 - Build next generation apps
today, with easy PDF and Ajax features - download now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291985
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
