Solved

CFtransaction with Oracle

Posted on 2002-03-27
2
350 Views
Last Modified: 2013-12-24

The application is inserting/updating data into several Oracle tables.  The insert statements are surrounded by the CFTRANSACTION tag.  

Sometimes it works on all the tables. In some cases, however, there is one table in which the insert fails.  That table is the last one in the list (pzrrapp@jobapp).

1. Shouldn't it roll back when it fails?  
2. Is cftransaction being used correctly?
3. Do you have to specify commit?
Thanks, Paul

Here is the code.

<cftransaction>

<cfquery name="get_seq" datasource="jobapp">
      Select max(pzrappl_appl_seq) as sequence_num
      From pzrappl@jobapp
      Where 0=0
</cfquery>
<cfset sequence_number = #get_seq.sequence_num# + 1>

<cfquery name="update_time" datasource="jobapp">
      Update pzrappl@jobapp
      Set pzrappl_appl_date = '#set_time.sysdate#',
      pzrappl_appl_seq = #sequence_number#
      Where PZRAPPL_APPL_DATE = (Select max(PZRAPPL_APPL_DATE)
            From PZRAPPL@jobapp
            Where pzrappl_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
      Update pzrrelt@jobapp
      Set pzrrelt_appl_date = '#set_time.sysdate#',
      pzrrelt_appl_seq = #sequence_number#
      Where pzrrelt_APPL_DATE = (Select max(pzrrelt_APPL_DATE)
            From pzrrelt@jobapp
            Where pzrrelt_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
      Update pzrschl@jobapp
      Set pzrschl_appl_date = '#set_time.sysdate#',
      pzrschl_appl_seq = #sequence_number#
      Where pzrschl_APPL_DATE = (Select max(pzrschl_APPL_DATE)
            From pzrschl@jobapp
            Where pzrschl_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
      Update pzrlicn@jobapp
      Set pzrlicn_appl_date = '#set_time.sysdate#',
      pzrlicn_appl_seq = #sequence_number#
      Where pzrlicn_APPL_DATE = (Select max(pzrlicn_APPL_DATE)
            From pzrlicn@jobapp
            Where pzrlicn_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
      Update pzrskil@jobapp
      Set pzrskil_appl_date = '#set_time.sysdate#',
      pzrskil_appl_seq = #sequence_number#
      Where pzrskil_APPL_DATE = (Select max(pzrskil_APPL_DATE)
            From pzrskil@jobapp
            Where pzrskil_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
      Update pzrexam@jobapp
      Set pzrexam_appl_date = '#set_time.sysdate#',
      pzrexam_appl_seq = #sequence_number#
      Where pzrexam_APPL_DATE = (Select max(pzrexam_APPL_DATE)
            From pzrexam@jobapp
            Where pzrexam_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
      Update pzrexpe@jobapp
      Set pzrexpe_appl_date = '#set_time.sysdate#',
      pzrexpe_appl_seq = #sequence_number#
      Where pzrexpe_APPL_DATE = (Select max(pzrexpe_APPL_DATE)
            From pzrexpe@jobapp
            Where pzrexpe_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
      Update pzrresu@jobapp
      Set pzrresu_appl_date = '#set_time.sysdate#',
      pzrresu_appl_seq = #sequence_number#
      Where pzrresu_APPL_DATE = (Select max(pzrresu_APPL_DATE)
            From pzrresu@jobapp
            Where pzrresu_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
      Update pzrcrim@jobapp
      Set pzrcrim_appl_date = '#set_time.sysdate#',
      pzrcrim_appl_seq = #sequence_number#
      Where pzrcrim_APPL_DATE = (Select max(pzrcrim_APPL_DATE)
            From pzrcrim@jobapp
            Where pzrcrim_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
      Update pzraeeo@jobapp
      Set pzraeeo_appl_date = '#set_time.sysdate#',
      pzraeeo_appl_seq = #sequence_number#
      Where pzraeeo_APPL_DATE = (Select max(pzraeeo_APPL_DATE)
            From pzraeeo@jobapp
            Where pzraeeo_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
      Update pzrsign@jobapp
      Set pzrsign_appl_date = '#set_time.sysdate#',
      pzrsign_appl_seq = #sequence_number#
      Where pzrsign_APPL_DATE = (Select max(pzrsign_APPL_DATE)
            From pzrsign@jobapp
            Where pzrsign_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
      Update pzrrapp@jobapp
      Set pzrrapp_appl_date = '#set_time.sysdate#',
      pzrrapp_appl_seq = #sequence_number#
      Where pzrrapp_APPL_DATE = (Select max(pzrrapp_APPL_DATE)
            From pzrrapp@jobapp
            Where pzrrapp_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="get_jobs" datasource="jobapp">
      Select pzrrapp_req_no
      From pzrrapp@jobapp
      Where pzrrapp_appl_no = '#cookie.appl_no#'
      AND pzrrapp_applied = 'N'
</cfquery>

<cfquery name="apply_for_jobs" datasource="jobapp">
      Update pzrrapp@jobapp
      Set pzrrapp_applied = 'Y',
      pzrrapp_appl_seq = #sequence_number#,
      pzrrapp_appl_date = '#set_time.sysdate#',
      pzrrapp_activity_date = '#set_time.sysdate#'
      Where pzrrapp_appl_no = '#cookie.appl_no#'
      AND pzrrapp_applied = 'N'
</cfquery>

</cftransaction>




0
Comment
Question by:hop702
2 Comments
 
LVL 2

Accepted Solution

by:
Cyril_H earned 200 total points
ID: 6901915
You can try to catch DB Error and then Rollback all insertions. Here's the code :

<CFTRY>

<CFSET commitIt = "Yes">

<CFTRANSACTION ACTION="BEGIN">

<cfquery name="get_seq" datasource="jobapp">
     Select max(pzrappl_appl_seq) as sequence_num
     From pzrappl@jobapp
     Where 0=0
</cfquery>
<cfset sequence_number = #get_seq.sequence_num# + 1>

<cfquery name="update_time" datasource="jobapp">
     Update pzrappl@jobapp
     Set pzrappl_appl_date = '#set_time.sysdate#',
     pzrappl_appl_seq = #sequence_number#
     Where PZRAPPL_APPL_DATE = (Select max(PZRAPPL_APPL_DATE)
          From PZRAPPL@jobapp
          Where pzrappl_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
     Update pzrrelt@jobapp
     Set pzrrelt_appl_date = '#set_time.sysdate#',
     pzrrelt_appl_seq = #sequence_number#
     Where pzrrelt_APPL_DATE = (Select max(pzrrelt_APPL_DATE)
          From pzrrelt@jobapp
          Where pzrrelt_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
     Update pzrschl@jobapp
     Set pzrschl_appl_date = '#set_time.sysdate#',
     pzrschl_appl_seq = #sequence_number#
     Where pzrschl_APPL_DATE = (Select max(pzrschl_APPL_DATE)
          From pzrschl@jobapp
          Where pzrschl_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
     Update pzrlicn@jobapp
     Set pzrlicn_appl_date = '#set_time.sysdate#',
     pzrlicn_appl_seq = #sequence_number#
     Where pzrlicn_APPL_DATE = (Select max(pzrlicn_APPL_DATE)
          From pzrlicn@jobapp
          Where pzrlicn_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
     Update pzrskil@jobapp
     Set pzrskil_appl_date = '#set_time.sysdate#',
     pzrskil_appl_seq = #sequence_number#
     Where pzrskil_APPL_DATE = (Select max(pzrskil_APPL_DATE)
          From pzrskil@jobapp
          Where pzrskil_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
     Update pzrexam@jobapp
     Set pzrexam_appl_date = '#set_time.sysdate#',
     pzrexam_appl_seq = #sequence_number#
     Where pzrexam_APPL_DATE = (Select max(pzrexam_APPL_DATE)
          From pzrexam@jobapp
          Where pzrexam_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
     Update pzrexpe@jobapp
     Set pzrexpe_appl_date = '#set_time.sysdate#',
     pzrexpe_appl_seq = #sequence_number#
     Where pzrexpe_APPL_DATE = (Select max(pzrexpe_APPL_DATE)
          From pzrexpe@jobapp
          Where pzrexpe_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
     Update pzrresu@jobapp
     Set pzrresu_appl_date = '#set_time.sysdate#',
     pzrresu_appl_seq = #sequence_number#
     Where pzrresu_APPL_DATE = (Select max(pzrresu_APPL_DATE)
          From pzrresu@jobapp
          Where pzrresu_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
     Update pzrcrim@jobapp
     Set pzrcrim_appl_date = '#set_time.sysdate#',
     pzrcrim_appl_seq = #sequence_number#
     Where pzrcrim_APPL_DATE = (Select max(pzrcrim_APPL_DATE)
          From pzrcrim@jobapp
          Where pzrcrim_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
     Update pzraeeo@jobapp
     Set pzraeeo_appl_date = '#set_time.sysdate#',
     pzraeeo_appl_seq = #sequence_number#
     Where pzraeeo_APPL_DATE = (Select max(pzraeeo_APPL_DATE)
          From pzraeeo@jobapp
          Where pzraeeo_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
     Update pzrsign@jobapp
     Set pzrsign_appl_date = '#set_time.sysdate#',
     pzrsign_appl_seq = #sequence_number#
     Where pzrsign_APPL_DATE = (Select max(pzrsign_APPL_DATE)
          From pzrsign@jobapp
          Where pzrsign_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="update_time" datasource="jobapp">
     Update pzrrapp@jobapp
     Set pzrrapp_appl_date = '#set_time.sysdate#',
     pzrrapp_appl_seq = #sequence_number#
     Where pzrrapp_APPL_DATE = (Select max(pzrrapp_APPL_DATE)
          From pzrrapp@jobapp
          Where pzrrapp_appl_no = '#cookie.appl_no#')
</cfquery>

<cfquery name="get_jobs" datasource="jobapp">
     Select pzrrapp_req_no
     From pzrrapp@jobapp
     Where pzrrapp_appl_no = '#cookie.appl_no#'
     AND pzrrapp_applied = 'N'
</cfquery>

<cfquery name="apply_for_jobs" datasource="jobapp">
     Update pzrrapp@jobapp
     Set pzrrapp_applied = 'Y',
     pzrrapp_appl_seq = #sequence_number#,
     pzrrapp_appl_date = '#set_time.sysdate#',
     pzrrapp_activity_date = '#set_time.sysdate#'
     Where pzrrapp_appl_no = '#cookie.appl_no#'
     AND pzrrapp_applied = 'N'
</cfquery>

<!--- Rolls back the pending insertion if database exception is caught. --->

    <CFCATCH TYPE="DATABASE">
        <CFTRANSACTION ACTION="ROLLBACK"/>
        <CFSET commitIt = "No">
    </CFCATCH>


<!--- Commits the pending insertion. --->

    <CFIF commitIt>
        <CFTRANSACTION ACTION="COMMIT"/>
    <CFELSE>
        <CFSET commitIt="Yes">
    </CFIF>

</CFTRANSACTION>

</CFTRY>
0
 

Author Comment

by:hop702
ID: 6902803
Thanks for your quick response.
0

Featured Post

Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

Join & Write a Comment

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now