[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

CFtransaction with Oracle

Posted on 2002-03-27
2
Medium Priority
?
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Accepted Solution

by:
Cyril_H earned 800 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
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…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

650 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