RE: cftry and transactions

[Stone, Mary L CTR NAWCAD, Bldg 447]

 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:258157
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4