Hi Lori I tried what you suggested and still no luck. If one of the inserts in the loop violates a db constraint, the previous insert are still not removed from the db.
Anyone else have any ideas? Stone, Mary L CTR NAWCAD, Bldg 447 wrote: > try this: > > <cfcatch type="any"> > <cftransaction action="rollback" /> > <cfset returnObj["message"] = cfcatch.Message & ": " & > cfcatch.Detail /> > <cfset blnOk = false> //of course set this to true outside > cfcatch first > </cfcatch> > > <cfif blnOk> > <cftransaction action="commit" /> > </cfif> > </cftransaction> > > </cftry> > Lori > > > -----Original Message----- > From: Steve Milburn [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 26, 2006 14:50 > To: CF-Talk > Subject: cftry and transactions > > Hi folks > > Can anyone tell me what is wrong with the following code? It is a > snippet of code from a cfc I am working on. The problem I am having if > that if one of the queries inside the loop fails for whatever reason > (ie, db constraint error), the previous inserts are not removed from the > db. Shouldn't the rollback in the cfcatch take care of that for me? > > Thanks > > <cftry> > <cftransaction action="begin" /> > > <cfstoredproc procedure="addSequence" datasource="sis"> > <cfprocparam value="#arguments.seqName#" > cfsqltype="cf_sql_varchar" type="in" /> > <cfprocparam type="out" cfsqltype="cf_sql_bigint" > variable="sequenceID" /> > </cfstoredproc> > > <cfloop from="1" to=#arrayLen(seqArray)# index="index"> > <cfquery name="addSequence" datasource="sis"> > insert into sequenceLookup (sequenceID, examID, > placeInOrder) values ( > <cfqueryparam value="#sequenceID#" > cfsqltype="cf_sql_bigint" />, > <cfqueryparam value="#seqArray[index][1]#" > cfsqltype="cf_sql_bigint" />, > <cfqueryparam value="#seqArray[index][2]#" > cfsqltype="cf_sql_bigint" /> > ) > </cfquery> > </cfloop> > > <cfset returnObj["status"] = true /> > <cfset returnObj["message"] = "Sequence Added." /> > > <cftransaction action="commit" /> > <cfcatch type="any"> > <cftransaction action="rollback" /> > <cfset returnObj["message"] = cfcatch.Message & ": " & > cfcatch.Detail /> > </cfcatch> > > </cftry> > --- > --- > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:258419 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
