CFTRY/CFCATCH/CFTRANSACTION Question

Hi Experts,
I have a scheduled program that does transaction processing.  I think I am OK with CFTRANSACTION to commit or rollback based on "logical errors".  I'd like to integrate CFTRY/CFCATCH to be able to continue processing on "hard errors" that end the processing.

To avoid the nitty gritty, here is the pseudo code of my process:

<cfquery name="getTransactions" datasource="#application.dsn#">
Select * from MyTransactions where RunDate = '#toDay#'
</cfquery>

<cftransaction>
 <cfoutput query="getTransactions">      
       
<cfinvoke component="transaction-methods" method="process Transactions" returnvariable="ans">
                    <cfinvokeargument name="runDate" value="#toDay#">
 </cfinvoke

<cfif ans eq "OK">
  Log sucess details to a log file
  <CFTRANSACTION ACTION="COMMIT" />

<cfelse>
   log error to log file
   <CFTRANSACTION ACTION="ROLLBACK" />
</cfif>

</cfoutput>
</cftransaction>  


So, I am not sure where to put the try/catch to be able to rollback the transaction in error, log it and continue to the next.

Thanks in advance,
hefterr
LVL 1
hefterrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mr_NilCommented:
Hefterr,

It is probably as simple as wrapping the cfoutput loop inside your transaction with a try/catch.

<cftransaction>
   <cftry>
      <cfoutput query="getTransactions">
      <!--- SP calls and other normal logical transaction processing here --->
      </cfoutput>
   </cftry>
   <cfcatch type="any">
   <!--- rollback here --->
   </cfcatch>
</cftransaction>

Open in new window


A point to note, you need to be using cfqueryparam in your cfquery that gets your "getTransactions" query object.
0
dgrafxCommented:
so you said you want to commit or rollback then continue on to the next

so do this:

<cfoutput query="...">
<cftransaction>
<cftry>
your code here ...
<cfcatch>
rollback if error
</cfcatch>
</cftry>
</cftransaction>
</cfoutput>

Open in new window

0
Gurpreet Singh RandhawaCEOCommented:
a little update to your above code:

   <cftry>
      <cftransaction action="begin"/>
      <cfoutput query="getTransactions">
      <!--- SP calls and other normal logical transaction processing here --->
      </cfoutput>
    <cftransaction action="commit"/>
   <cfcatch type="any">
          <cftransaction action="rollback"/>
   </cfcatch>
   </cftry>                                         

Open in new window

0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

gdemariaCommented:
The other posts didn't account for your testing for an "OK" response from the cfinvoke.    I would set it up one of these two ways... I prefer the 2nd method..

<cftransaction action="BEGIN">
  <cfoutput query="getTransactions">       
        
     <cftry>
     <cfinvoke component="transaction-methods" method="process Transactions" returnvariable="ans">
         <cfinvokeargument name="runDate" value="#toDay#">
     </cfinvoke

     <cfif ans eq "OK">
         Log sucess details to a log file
        <CFTRANSACTION ACTION="COMMIT" /> 
     <cfelse>
        log error to log file
       <CFTRANSACTION ACTION="ROLLBACK" />
     </cfif>

    <cfcatch type="Any">
       <CFTRANSACTION ACTION="ROLLBACK" />
    </cfcatch>
    </cftry>
  </cfoutput>
</cftransaction>  

Open in new window




But I think this is cleaner as you consolodate your error handling

<cftransaction action="BEGIN">
  <cfoutput query="getTransactions">       
        
     <cftry>
         <cfinvoke component="transaction-methods" method="process Transactions" returnvariable="ans">
         <cfinvokeargument name="runDate" value="#toDay#">
         </cfinvoke

         <cfif ans neq "OK">
            <cfthrow message="#ans#">
         </cfif>

         <CFTRANSACTION ACTION="COMMIT" /> 

    <cfcatch type="Any">
       <CFTRANSACTION ACTION="ROLLBACK" />
       <!---- error handling here ----->
    </cfcatch>
    </cftry>
  </cfoutput>
</cftransaction>  

Open in new window



As a tip, I would look into using createObject over CFINVOKE.  You're calling your method in a loop which means you have to reinvoke it every time.   It's much better to create the object (invoke once) and then just call it again and again.   createObject, IMHO, is much cleaner as it allows you to call methods just like any other function.   You can even set it into an application scoped variable and invoke it once for all users!

 <cfset ans = transactionMethods.process(today)>

Isn't that much cleaner ???
0
Gurpreet Singh RandhawaCEOCommented:
good @gd, The Code is much cleaner now

Good Work
0
hefterrAuthor Commented:
LOL!!

I think there are only 6 possibilities to the order of CFTRY, CFTRANSACTION and CFOUTPUT and I received 4 of them in  4 replies!  Either it doesn't matter or we really have a disagreement here!

I think I will start with gdemaria approach first as he had backing by myselfrandhawa and I've had good luck with gdemaria before.

@gdemaria
Does:
<cfset ans = transactionMethods.process(today)> use createObject or did you leave that out?  If you create an object, do you have to release it also?

hefterr
0
_agx_Commented:
If you just need to commit each cfinvoke result individually, you don't need to wrap the whole loop in a transaction. The problem w/doing that is you're applying the rollback to the "log" action as well. I think dgrafx's approach is the one you want. Just be sure the log action is *outside* the rollback - or it will be undone along with everything else.

Just my $0.02
0
gdemariaCommented:
This version centralizes error handling, if your log is in your database, then just rollback your changes and your logs and save.   If you use dgrafx's version, it doesn't take into account that an error message could be retuned from from the invoke via "ans" variable; so that would have to be added.


<cftransaction action="BEGIN">
  <cfoutput query="getTransactions">       
        
     <cftry>
         <cfinvoke component="transaction-methods" method="process Transactions" returnvariable="ans">
         <cfinvokeargument name="runDate" value="#toDay#">
         </cfinvoke

         <cfif ans neq "OK">
            <cfthrow message="#ans#">
         </cfif>

         <CFTRANSACTION ACTION="COMMIT" /> 

    <cfcatch type="Any">
       
        <!---- rollback updates ---->
        <CFTRANSACTION ACTION="ROLLBACK" />

        <!---- error handling here ----->
         ... Log Action....
         <CFTRANSACTION ACTION="COMMIT" /> 

    </cfcatch>
    </cftry>
  </cfoutput>
</cftransaction>  

Open in new window

0
_agx_Commented:
> so that would have to be added.

That's true, but the other examples would rollback the log actions too. Plus I'm not sure I see gain of wrapping the whole loop in a transaction if each set is committed individually anyway.
0
hefterrAuthor Commented:
@agx and gdemaria

FYI.  My "logging" is to a flat file (logfile.log) on my server using CFFILE.  So any rollback will not affect the logging.  I did this in case there was a DB problem.

Soooooo, which is the best solution with that piece of information?

Sorry for the confusion!
0
gdemariaCommented:
> That's true, but the other examples would rollback the log actions too.

Any example that has a rollback would rollback database logs if those logs were written before the rollback.   So, in all cases,  the log just has to be written after the rollback and followed by a commit.

That makes the only difference between the examples whether you start the transaction inside in the loop and restart it again and again; or if you just start int once and save it again and again.   I'm not sure what the technical differences may be between these.

<cfoutput query="...">
   <cftransaction>

or..

<cfoutput query="...">
   <cftransaction>
0
_agx_Commented:
> I did this in case there was a DB problem.

Because your cfinvoke calls multiple statements or just to prevent the loop from stopping if one call fails?

EDIT:   If it's the first, I *think* both methods should work (haven't tested either).  Though personally I prefer dgrafx's version, more intuitive IMO and doesn't keep the transaction open for prolonged periods of time. Generally they should short and sweet, and non-db code should be done outside the transaction.  Just be sure to implement gdemaria's comments about handling according to status if needed.

BUT if it's the latter (keep loop from stopping) you don't need a transaction at all.
0
_agx_Commented:
> Any example that has a rollback would rollback database logs

I didn't mean the database logs. From his initial description it sounded like he was logging to a db table, and that's why he was concerned about the transactions. But he's since cleared up the confusion :) and now we know it's just a flat file.

Sorry heferr - should have asked for more details up front!
0
gdemariaCommented:
> I didn't mean the database logs

Sorry, I know you didn't.   I was referring to a message/log that is written to the datatbase;



> and doesn't keep the transaction open for prolonged periods of time

Ok, that's a good reason to put the cftransaction INSIDE the cfouptu


Given that you have multiple SQL statements (insert/updates) and these examples are simplified; you would want cftranaction to roll them all back.   There's no problem writing to the databaes, provided you do it in order - rollback changes, write log to database, save changes, end transaction.

Based on the conversation, I believe this code satisfies the requirements.   But if you choose to keep writing to the file, you can remove the last commit after writing the log.

<cfoutput query="getTransactions">       
  <cftransaction action="BEGIN">
        
     <cftry>
         <cfinvoke component="transaction-methods" method="process Transactions" returnvariable="ans">
         <cfinvokeargument name="runDate" value="#toDay#">
         </cfinvoke

         <cfif ans neq "OK">
            <cfthrow message="#ans#">
         </cfif>

         <CFTRANSACTION ACTION="COMMIT" /> 

    <cfcatch type="Any">
       
        <!---- rollback updates ---->
        <CFTRANSACTION ACTION="ROLLBACK" />

        <!---- error handling here ----->
         ... Log Action....
         <CFTRANSACTION ACTION="COMMIT" /> 

    </cfcatch>
    </cftry>
    </cftransaction>  
</cfoutput>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hefterrAuthor Commented:
FYI.  This particular process is only processing 12 "transactions" per day.  It is filling in missing slides in 12 slides shows where admins have entered some slides on each (but not all slides).  There are algorithms for each slides.  And a few tables are updated as a result on each CFINVOKE.

There will be "soft errors" - where the algorithm fails to find a missing slide.  In this case no updates take place and the errors are logged for that slide show.

If there is a DB problem (duplicate index issue), I'd like to roll back any updates, log the error and continue to the next Slideshow.

I think gdemaria solution is very elegant and cover's this nicely.
0
hefterrAuthor Commented:
Thanks for your help (again).  And thanks to all for participating!
0
_agx_Commented:
> I think gdemaria solution is very elegant and cover's this nicely.

Yep, I like the final solution as well since it combines both dgrafx's approach and gdemaria's improvements.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.