[thelist] tip owed

[Raymond Camden]

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 !