I'd bet your SP is performing a commit. DK
On 10/30/06, Steve Milburn <[EMAIL PROTECTED]> wrote: > > 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:258422 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
