[thelist] tip owed

Bruce Heerssen bheerssen at visualbridge.tv
Wed Dec 13 16:12:53 CST 2000


So, you're saying that the cfquery tag is processed as a block and then the
request is sent to the database? That makes sense. Damn I hate being wrong. But
I like learning things! Thanks for the clarification.

-- Bruce


> -----Original Message-----
> From: thelist-admin at lists.evolt.org
> [mailto:thelist-admin at lists.evolt.org]On Behalf Of Raymond Camden
> Sent: Wednesday, December 13, 2000 12:45 PM
> To: thelist at lists.evolt.org
> Subject: RE: [thelist] tip owed
>
>
> FYI, this is not true. ColdFusion will not open the connection to the
> database until the SQL is ready to go. This is straight from the guy who
> designed CF. (Not me, I mean I just got done speaking with him.)
>
> =======================================================================
> Raymond Camden, Principal Spectra Compliance Engineer for Allaire
>
> Email   : jedimaster at allaire.com
> ICQ UIN : 3679482
>
> "My ally is the Force, and a powerful ally it is." - Yoda
>
> > -----Original Message-----
> > From: thelist-admin at lists.evolt.org
> > [mailto:thelist-admin at lists.evolt.org]On Behalf Of Bruce Heerssen
> > Sent: Tuesday, December 12, 2000 3:51 PM
> > To: thelist at lists.evolt.org
> > Subject: [thelist] tip owed
> >
> >
> > I'm sure I owe a tip or two. Here's one.
> >
> > <tip type="database" author="Bruce Heerssen">
> > Help minimize the time database connections are held open by building your
> > dynamic queries as a string before opening a database connection.
> > Then after
> > opening the db connection, pass that string as the query. For Instance, in
> > ColdFusion, it is common practice to build a query like so:
> >
> > <cfquery name="getArticles" datasource="myDSN">
> > 	select * from articles
> > 	<cfif isDefined('form.keywords')>
> > 		where keywords like '#form.keywords#%'
> > 	<cfelseif isDefined('url.catid')>
> > 		where catid = #url.catid#
> > 	</cfif>
> > </cfquery>
> >
> > The problem with building queries like this is that the
> > connection is held open
> > while the conditional processing takes place. With a very complex
> > query (the
> > example is kept simple for brevity), that could cause database
> > locking errors
> > and timeouts under high load.
> >
> > Here is a more database-friendly solution:
> >
> > <cfset sql="select * from articles ">
> > <cfif isDefined('form.keywords')>
> > 	<cfset sql = sql & "where keywords like '#form.keywords#%'">
> > <cfelseif isDefined('url.catid')>
> > 	<cfset sql = sql & "where catid = #url.catid#">
> > </cfif>
> > <cfquery name="getArticles" datasource="myDSN">
> > #preserveSingleQuotes(sql)#
> > </cfquery>
> >
> > * Note: preserveSingleQuotes() is required to properly format the query in
> > ColdFusion.
> > </tip>
> >
> > Bruce Heerssen
> > Software Engineer
> > Visual Bridge, Inc.
> > http://www.iecommerce.net
> > (713) 350-8321 ext. 8358
> >
> >
> > ---------------------------------------
> > For unsubscribe and other options, including
> > the Tip Harvester and archive of TheList go to:
> > http://lists.evolt.org Workers of the Web, evolt !
>
>
> ---------------------------------------
> For unsubscribe and other options, including
> the Tip Harvester and archive of TheList go to:
> http://lists.evolt.org Workers of the Web, evolt !





More information about the thelist mailing list