?
Solved

CFtransaction with Oracle

Posted on 2002-03-27
2
Medium Priority
?
363 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

800 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