cftransaction with multiple data sources

I have a script that basically pulls data from one dsn and inserts it using another dsn.

I am currently wrapping each insert in DSN 1 with a cftry and then at the end

  <cfcatch type = "DATABASE">
    <cfset commitIt = "No">
    <cftransaction action = "rollback"/>
  </cfcatch>
</CFTRY>


at the end of the entire transaction i have the following:

  <cfif commitIt>
    <cftransaction action = "commit"/>
    <!--------------------------------------------------------------
    Commits the pending insertion.
    ---------------------------------------------------------------->
  <cfelse>
    <cfset commitIt = "Yes">
  </cfif>


</cftransaction>



I'm getting an error  of:

 Data source eventreg verification failed.
The root cause was that: java.sql.SQLException: Datasource names for all the database tags within CFTRANSACTION must be the same.

This is correct because i have nested queries inside the transaction that lookup records and verify them.
 

What solution can i do to safely complete this transaction even when i have queries like this.
polobruceAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PluckaConnect With a Mentor Commented:
polobruce,

You simply can't use cftransaction across datasources.

Put the two different tasks within two seperate <cftransaction blocks.

If one fails, you may need to code manually a rollback.

Regards
Plucka
0
All Courses

From novice to tech pro — start learning today.