asked on

CFTRY w CFTRANSACTION - code not working

the below code ois directly from cf help files, when i tried to implement it i get an error about teh last block of a cftry must bre a cfcatch, but thsi code sample has a cftransaction, is there a different way to implement this?

<cftry>
<cfset commitIt = "Yes">
<cftransaction action = "begin">
  <cfquery name = 'makeNewCourse' dataSource = 'cfsnippets'>
  INSERT INTO Courses
    (Course_Num, Descript)
  VALUES
    ('#myNumber#', '#myDescription#')
  </cfquery>
  
  <cfcatch type = "DATABASE">
    <cftransaction action = "rollback"/>
    <cfset commitIt = "No">
  </cfcatch>
  
  <cfif commitIt>
    <cftransaction action = "commit"/>
  <cfelse>
    <cfset commitIt = "Yes">
  </cfif>
  <cfquery name = 'insertNewCourseToList' dataSource = 'cfsnippets'>
  INSERT INTO CourseList
    (CorNumber, CorDesc, Dept_ID,
    CorName, CorLevel, LastUpdate)
  VALUES
    ('#myNumber#', '#myDescription#', '#myDepartment#',
    '#myDescription#', '#myCorLevel#', #Now()#)
  </cfquery>
  
  <cfcatch type = "DATABASE">
    <cftransaction action = "rollback"/>
    <cfset commitIt = "No">
  </cfcatch>
  
  <cfif commitIt>
    <cftransaction action = "commit"/>
  <cfelse>
    <cfset commitIt = "Yes">
  </cfif>
</cftransaction>
</cftry>

Open in new window

Scott Bennett

The example is not the right way to do it. You should put your ctry's around the queries and then use cfcatch's to determine if the queries failed. if a query fails then you should roll back the transaction. Also I am not sure if you would want that first commit or not, In this situation I would only use one commit after both queries have run successfully but to keep the example as close to the original as possible, here it is:

<cfset commitIt = "Yes">
<cftransaction action = "begin">
  <cftry>
  <cfquery name = 'makeNewCourse' dataSource = 'cfsnippets'>
  INSERT INTO Courses
    (Course_Num, Descript)
  VALUES
    ('#myNumber#', '#myDescription#')
  </cfquery>
  
  <cfcatch type = "DATABASE">
    <cftransaction action = "rollback"/>
    <cfset commitIt = "No">
  </cfcatch>
  </cftry>
  
  <cfif commitIt>
    <cftransaction action = "commit"/>
  <cfelse>
    <cfset commitIt = "Yes">
  </cfif>
  
  <cftry>
  <cfquery name = 'insertNewCourseToList' dataSource = 'cfsnippets'>
  INSERT INTO CourseList
    (CorNumber, CorDesc, Dept_ID,
    CorName, CorLevel, LastUpdate)
  VALUES
    ('#myNumber#', '#myDescription#', '#myDepartment#',
    '#myDescription#', '#myCorLevel#', #Now()#)
  </cfquery>
  
  <cfcatch type = "DATABASE">
    <cftransaction action = "rollback"/>
    <cfset commitIt = "No">
  </cfcatch>
  </cftry>
  
  <cfif commitIt>
    <cftransaction action = "commit"/>
  <cfelse>
    <cfset commitIt = "Yes">
  </cfif>
</cftransaction>

Open in new window

ASKER CERTIFIED SOLUTION

Scott Bennett

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

James Rodgers

ASKER

that code makes more sense...

thanks