Solved

CFtransaction with Oracle

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

687 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