Solved

CFtransaction with Oracle

Posted on 2002-03-27
2
355 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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 …
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

778 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