SELECT DELETE DELETE DELETE DELETE DELETE DELETE DELETE DELETE [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => RayBees [ip] => rbakker@thunder [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> CFTRANSACTION not rolling back - Coldfusion - Advanced Techniques

CFTRANSACTION not rolling back - Coldfusion - Advanced Techniques

Greetings I am performing a series of deletes from several tables and one selection. It does catch the an error however, it commits all the deletes exception the one that threw the error. Why? Here is the mock up of my code: <CFSET Commit = "Yes"> <CFSET errCount = 0> <CFSET arrErrorMessage = ArrayNew(1)> <CFTRANSACTION action="begin"> <!--- Get FSubFeatureID form CS_SubServices table used to detetmine if a section has contact information ---> <CFTRY> <CFQUERY name="GetAllSubServiceID" DATASOURCE="DSN"> SELECT </CFQUERY> <CFCATCH type="database"> <CFSET Commit = "No"> <CFSET errCount = errCount + 1> <CFSET arrErrorMessage[errCount] ="Unable to retrieve SubServiceID from table1. Please contact ...

  1. #1

    Default CFTRANSACTION not rolling back

    Greetings

    I am performing a series of deletes from several tables and one selection. It
    does catch the an error however, it commits all the deletes exception the one
    that threw the error. Why?

    Here is the mock up of my code:


    <CFSET Commit = "Yes">
    <CFSET errCount = 0>
    <CFSET arrErrorMessage = ArrayNew(1)>


    <CFTRANSACTION action="begin">

    <!--- Get FSubFeatureID form CS_SubServices table used to detetmine if a
    section has contact information --->
    <CFTRY>
    <CFQUERY name="GetAllSubServiceID" DATASOURCE="DSN">
    SELECT
    </CFQUERY>

    <CFCATCH type="database">
    <CFSET Commit = "No">
    <CFSET errCount = errCount + 1>
    <CFSET arrErrorMessage[errCount] ="Unable to retrieve SubServiceID from
    table1. Please contact webmaster.">
    </CFCATCH>
    </CFTRY>

    <!---
    ************************************************** ******************************
    ******* --->
    <!--- ************************** DELETE FROM ADMIN TABLES
    **************************** --->
    <!---
    ************************************************** ******************************
    ******* --->

    <CFIF GetAllSubServiceID.recordcount GT 0>
    <CFOUTPUT query="GetAllSubServiceID">
    <CFTRY>
    <CFQUERY name="DeleteFeatures" Datasource="DSN">
    DELETE
    </CFQUERY>

    <CFCATCH type="database">
    <CFSET Commit = "No">
    <CFSET errCount = errCount + 1>
    <CFSET arrErrorMessage[errCount] ="Unable to delete from table2. Please
    contact webmaster.">
    </CFCATCH>
    </CFTRY>
    </CFOUTPUT>
    </CFIF>


    <CFTRY>
    <CFQUERY NAME="DeleteSections" DATASOURCE="DSN">
    DELETE
    </CFQUERY>

    <CFCATCH type="database">
    <CFSET Commit = "No">
    <CFSET errCount = errCount + 1>
    <CFSET arrErrorMessage[errCount] ="Unable to delete from table3. Please
    contact webmaster.">
    </CFCATCH>
    </CFTRY>



    <CFTRY>
    <CFQUERY name="DeleteLinks" Datasource="DSN">
    DELETE
    </CFQUERY>

    <CFCATCH type="database">
    <CFSET Commit = "No">
    <CFSET errCount = errCount + 1>
    <CFSET arrErrorMessage[errCount] ="Unable to delete from table4. Please
    contact webmaster.">
    </CFCATCH>
    </CFTRY>



    <CFTRY>
    <CFQUERY name="DeleteService" Datasource="DSN">
    DELETE
    </CFQUERY>

    <CFCATCH type="database">
    <CFSET Commit = "No">
    <CFSET errCount = errCount + 1>
    <CFSET arrErrorMessage[errCount] ="Unable to delete from table5. Please
    contact webmaster.">
    </CFCATCH>
    </CFTRY>


    <!---
    ************************************************** ******************************
    ******* --->
    <!--- ************************* DELETE FROM PUBLIC TABLES
    **************************** --->
    <!---
    ************************************************** ******************************
    ******* --->

    <CFIF GetAllSubServiceID.recordcount GT 0>
    <CFOUTPUT query="GetAllSubServiceID">
    <CFTRY>
    <CFQUERY name="DeletePROD_Features" Datasource="DSN">
    DELETE
    </CFQUERY>

    <CFCATCH type="database">
    <CFSET Commit = "No">
    <CFSET errCount = errCount + 1>
    <CFSET arrErrorMessage[errCount] ="Unable to delete from table5. Please
    contact ">
    </CFCATCH>
    </CFTRY>
    </CFOUTPUT>
    </CFIF>



    <CFTRY>
    <CFQUERY NAME="DeletePROD_Sections" DATASOURCE="DSN">
    DELETE
    </CFQUERY>

    <CFCATCH type="database">
    <CFSET Commit = "No">
    <CFSET errCount = errCount + 1>
    <CFSET arrErrorMessage[errCount] ="Unable to delete from table6. Please
    contact webmaster.">
    </CFCATCH>
    </CFTRY>



    <CFTRY>
    <CFQUERY name="DeletePROD_Links" Datasource="DSN">
    DELETE
    </CFQUERY>

    <CFCATCH type="database">
    <CFSET Commit = "No">
    <CFSET errCount = errCount + 1>
    <CFSET arrErrorMessage[errCount] ="Unable to delete from table7. Please
    contact webmaster.">
    </CFCATCH>
    </CFTRY>



    <CFTRY>
    <CFQUERY name="DeletePROD_Service" Datasource="DSN">
    DELETE
    </CFQUERY>

    <CFCATCH type="database">
    <CFSET Commit = "No">
    <CFSET errCount = errCount + 1>
    <CFSET arrErrorMessage[errCount] ="Unable to delete from table8. Please
    contact webmaster.">
    </CFCATCH>
    </CFTRY>

    </CFTRANSACTION>



    <CFIF Commit EQ "Yes">
    <CFTRANSACTION action="commit" />
    <CFLOCATION url="index.cfm?fuseaction=ServicesGuide">
    <CFELSE>
    <CFTRANSACTION action="rollback" />
    <CFINCLUDE template="dsp_ErrorMessage.cfm">
    </CFIF>

    RayBees Guest

  2. #2

    Default Re: CFTRANSACTION not rolling back

    The trouble is you are error handling all problems then continuing to do
    deletes. You should
    structure your cftransaction something like this. It's much simpler:

    <cfset progressMsg = "">
    <cftransaction action="begin">
    <cftry>
    Run Select query
    <cfset progressmsg = "Select OK">
    Run Delete 1
    <cfset progressmsg = "Delete 1 OK">
    Run Delete 2
    ...........
    <cfcatch type="database">
    <cftransaction action="rollback"/>
    #Progressmsg#
    </cfcatch>
    </cftransaction>

    Now the first time a DB error occurs, it jumps to the catch block and rolls
    everything back.
    BTW the
    <CFIF Commit EQ "Yes">
    <CFTRANSACTION action="commit" />
    in your code doesn't do anything. SInce it follows the closing
    </cftransaction> commits
    or rollbacks have already been done. You can't do anything at this point.



    OldCFer Guest

Similar Threads

  1. Coldfusion MX 5 log rolling
    By Dineker in forum Coldfusion Server Administration
    Replies: 0
    Last Post: October 8th, 09:10 AM
  2. Rolling out Flash Player
    By thedogplace in forum Macromedia Flash Player
    Replies: 0
    Last Post: August 25th, 02:24 PM
  3. Rolling back Hot Fix 2/CFMXE7
    By LL@Work in forum Coldfusion Server Administration
    Replies: 2
    Last Post: May 31st, 05:56 PM
  4. underline when rolling over link
    By JJohnstone67 in forum Macromedia Dynamic HTML
    Replies: 1
    Last Post: May 16th, 10:59 AM
  5. rolling your own AOP
    By Volkmann, Mark in forum Ruby
    Replies: 0
    Last Post: August 6th, 07:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139