Multiple Data Sources with one cftransaction tag

[MsPepperMintUSA]

I am having difficulties creating code to manage a series of transactions which reference 2 different data sources within one cftransaction tag.

The error message reads: More than one data source used in a CFTRANSACTION.

Question: Is it possible to have multiple database transactions for multiple data sources managed within one cftransaction tag?


Other Important Information

Database: Oracle
CF Version: 5.0
Using Fusebox coding methodology

Also: if any errors occur with any of the delete statements, none of the transactions should be committed.


Any suggestions would be greatly appreciated.

Below is sample code:

<cfset request.dbErr = "N">
<cfset request.checkcount = 0>
<cftransaction action="BEGIN">
	
<cfloop list="#attributes.ids#" index="thisid">
	<cfset qry="check">
	<cfquery name="check"  datasource="datasource2">
	 SELECT column FROM table2
	 WHERE column =  #thisid#
	</cfquery>
		
	<!--- Cannot delete data from the datasource1,
             if the datasource2 record above exists --->	
<cfif Check.recordcount EQ 0> 		
<cftry>
<cfset qry="query1">
<cfquery name="query1" datasource="#request.datasource1#">
 delete from table_A
 where id = #thisid#
</cfquery>

<cfset qry="query2">
<cfquery name="query2" datasource="#request.datasource1#">
 delete from table_B
 where id = #thisid#
</cfquery>
					
<cfset qry="query3">
<cfquery name="query3" datasource="#request.datasource1#">
 delete from table_C
 where id = #thisid#
</cfquery>
															
<cfcatch type="Database">		
		
<cfset request.dbErr = "Y">
<cftransaction action = "ROLLBACK"/> 
<!--- Check for db connection error. --->
<cfif cfcatch.nativeerrorcode EQ "12571" 
OR cfcatch.nativeerrorcode EQ "1041">			
	<CF_dbActionMessage
	Header="Failure"					
	Message="An error occurred connecting 			
	to the database."					
	msgUrl="back"						
	BtnTxt="  Back  "					
	forwardOn="N">				
<cfelse>
	<!--- Some other database error occurred. --->
	<CF_dbActionMessage					
	Header="Failure"					
	Message="An error occurred deleting the record 		
	from the database."					
	msgUrl="back"						
	BtnTxt="  Back  "					
	forwardOn="N">	
</cfif>
<cfinclude template="pageThatDocumentsErrors.cfm">
</cfcatch>
</cftry>
		
<!--- Do a 2nd cftry because this delete 
is in a different database. --->
<cftry>
<cfset qry="query4">
<cfquery name="query4" datasource="#request.datasource2#">
 delete from table3
 where id = #thisid#
</cfquery>
					
<cfcatch type="Database">		
<cfset request.dbErr = "Y">
<cftransaction action = "ROLLBACK"/> 
<!--- Check for db connection error. --->
<cfif cfcatch.nativeerrorcode EQ "12571" 
OR cfcatch.nativeerrorcode EQ "1041">
	<CF_dbActionMessage					
	Header="Failure"
	Message="An error:connecting to the database."						
        msgUrl="back"						
        BtnTxt="  Back  "					
        forwardOn="N">
<cfelse>
 <!--- Some other Oracle error occurred. --->			
        <CF_dbActionMessage					
        Header="Failure"					
        Message="An error occurred deleting the record  
        from the database."					
        msgUrl="back"						
        BtnTxt="  Back  "					
        forwardOn="N">	
</cfif>
<cfinclude template="pageThatDocumentsErrors.cfm">
</cfcatch>
</cftry>

<cfelse>
<cfset request.checkcount=request.checkcount+1>
</cfif>
</cfloop>

<cfif request.dbErr IS "N"> 
<!--- No errors in either try/catch block --->
<cftransaction action = "COMMIT"/>	
<cfif request.checkcount EQ 0>			
	 <CF_dbActionMessage
	Header="Success"
	Message="Selected records were deleted."
	msgUrl="page.cfm"
	BtnTxt="Some Page"
	forwardOn="Y">
<cfelse>
        <CF_dbActionMessage
	Header="Warning"
	Message="Some data not deleted in use in 
	datasource2.table2 table."
	msgUrl="page.cfm"
	BtnTxt="Some Page"
	forwardOn="N">
	</cfif>
</cfif>
</cftransaction>

 

[FALCONSEYE]

yup, it's possible.

<cftransaction action="rollback">

all of your queries go here
<cfquery name=".." datasource="#db1#">
...
</cfquery>

<cfquery name=".." datasource="#db2#">
...
</cfquery>
...
</cftransaction>

if something goes wrong in between the cftransactions, everything will be rolled back. hope it helps...

http://www.jetansolutions.com

 

[MsPepperMintUSA]

I thought that one would start with <cftransaction action="begin">. With <cftransaction action="rollback"> and <cftransaction action="commit"> calls should be after the queries. Using your suggestion, where would I commit the transaction? I'm still getting the error...

Thanks for any additional suggestions.

 

[MsPepperMintUSA]

sometimes things work better in the morning, so I tried the suggestion again, but still received an application error which reads "More than one data source used in a CFTRANSACTION . Please note the different data sources reference 2 different databases in Oracle. Here's the sample code:

<cftransaction action="BEGIN">

  <cftransaction action="ROLLBACK"/>

  <cfquery name=".." datasource="db1">

  <cfquery name=".." datasource="db2">

  <cfif request.dbErr is "N">
    <cftransaction action="COMMIT"/>
  </cfif>
</cftransaction>

Did I place the call correctly? Is there another step that I did not include. Please advise.

Thanks.

 

[FALCONSEYE]

appearently, you cannot define more than one datasource within a cftransaction.

http://www.jetansolutions.com

 

[MsPepperMintUSA]

Adobe says you can.... see the article from Adobe:

http://www.adobe.com/devnet/coldfusion/articles/cftransaction.html.

I guess this is theory only and in practice it just does not work.

 

[FALCONSEYE]

actually, if you look at that page you will realize they don't define different datasources within one cftransaction tag. perhaps you can break your cftransactions in to the groups ?

http://www.jetansolutions.com