RE: [CFCDev] Loop DB transaction in CFC

Sat, 06 Mar 2004 16:41:27 -0800

I would let the DataBase take care of what it does best than
running a query loop in CF. You could pass all the values to
an SP and have the SP take care of the DB Processing.
 
With the below type of code, you are making multiple calls
to the DB which does not perform well.. Ideally... there
are techniques to create the rowset and activate the rowset
with an update.
 
HTH
Joe Eugene
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Sana Ullah
Sent: Saturday, March 06, 2004 3:06 PM
To: [EMAIL PROTECTED]
Subject: [CFCDev] Loop DB transaction in CFC

Hi Guys,

 

I am interested to find out below loop query would only one DB transaction if that is compiled in CFMX  

OR

 below code would be separate hit to database each time of loop

                                   

                                    <cfset transaction_action = "COMMIT">

                                    <cftransaction action="">

                                    <cftry>

                                    <cfloop list="#temp_formFieldsList#" index="key">

                                   

                                    <cfset elementValue = safeText(arguments.formElementData[elementName],true) />

                                   

                                    <cfif listFindNoCase(formElementList,elementId , ",")>

                                          <cfif elementValue NEQ "not_updated_it">

                                                <cfquery name="updateFormElementData" datasource="#variables.dsn#">

                                                update site_user_element_data

                                                 set

                                                            data_value  = <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#elementValue#">,

                                                            updated_id   = <cfqueryparam cfsqltype="cf_sql_integer" value="#updated_id#">,

                                                            updated_date= <cfqueryparam cfsqltype="cf_sql_date" value="#updated_date#">

                                                  where element_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#elementId#">

                                                  and site_user_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#formElementData.siteUserId#">

                                                  and form_uid = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.formUUID#">

                                                 </cfquery>

                                         </cfif>

                                    <cfelse>

                                                                        <cfquery datasource="#variables.dsn#" name="addFormElementData">

                                                                        insert into site_user_element_data (

                                                                                    site_user_id,

                                                                                    element_id,

                                                                                    data_value,

                                                                                    form_uid,

                                                                                    expiry_date,

                                                                                    created_id,

                                                                                    created_date,

                                                                                    updated_id,

                                                                                    updated_date

                                                                        ) values (

                                                                                    #formElementData.siteUserId#,

                                                                                    #elementId#,

                                                                                    <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#elementValue#">,

                                                                                    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form_uuid#">,

                                                                                    <cfqueryparam cfsqltype="cf_sql_date" value="#expiryDate#">,

                                                                                    <cfqueryparam cfsqltype="cf_sql_integer" value="#created_id#">,

                                                                                    <cfqueryparam cfsqltype="cf_sql_date" value="#created_date#">,

                                                                                    <cfqueryparam cfsqltype="cf_sql_integer" value="#updated_id#">,

                                                                                    <cfqueryparam cfsqltype="cf_sql_date" value="#updated_date#">

                                                                        )

                                                            </cfquery>

                                                </cfif>

 

                                    <cfset elementName = "" />

                                    <cfset elementValue = "" />

                                    <cfset elementId = "" />

                        </cfloop>

Reply via email to