Skip to main content
K
KomputerMan_com-L7dcfe
Inspiring
May 5, 2011
Question

CFTRANSACTION with multiple datasources

  • May 5, 2011
  • 2 replies
  • 1563 views

I am writing a data migration utility that pulls data from a temp database, located in a DMZ, through the firewall and stores the data in the production database inside the firewall.  Because of the firewall the data is stored on two different databases and as a result I need to use two different datasources (DSNs) to talk to them.  After each record is wrote to the production database I would like to set a flag in the DMZ database showing the record has been processed.  This works fine 99% of the time… but…

Because I am using two different DSNs every now and then the “set the completed flag” write process fails.  When this happens I get duplicated records in the production database.  What I would like to do is wrap the entire process inside a CFTRANSACTION.

These are the steps I would like encapsulate inside the CFTRANSACTION tag:

<CFTRANSACTION>

                <CFQUERY name=”GetTmpData” datasource=”DSN_DMZ”>Read Data</CFQUERY>

                <CFQUERY name=”WriteProdData” datasource=”DSN_PROD”>Write Data</CFQUERY>

                <CFQUERY name=”SetFlag” datasource=”DSN_DMZ”>Set Flag</CFQUERY>

</CFTRANSACTION>

Any thoughts on how to accomplish this???

Thanks in Advance for Any and All Help!!!

Kurtis   ~|:-)

This topic has been closed for replies.

2 replies

D
Dan_Bracuk
Inspiring
May 5, 2011

Edit your insert query to exclude records it already has.

D
Community Expert
Dave WattsCommunity Expert
Community Expert
May 5, 2011

You won't be able to do this directly from CF with anything built into CF. This kind of thing is called a distributed transaction.

You might see if you can do this from within one of the databases itself, if the databases can potentially talk to each other. Otherwise, you'll basically have to maintain the state somehow in your application until all transactions have been completed. For example, you could copy the original records into a separate table in the existing database before changing them, then copy them back if you need to roll back.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Dave Watts, Eidolon LLC
K
KomputerMan_com-L7dcfeAuthor
Inspiring
May 6, 2011

Because I am migrating one or more records at a time what I did was create a mirror table inside the firewall.  In that table I store the PK value (along with all of the other data) of the record being migrated.  During the migration process I generate a list of PK’s I am migrating from the DMZ through the firewall.  I use that list to run a query against the mirror table.  If any matches are found I stick them into a structure.  Then as I loop through my data processing routines I check to see if the PK value of the record being processed is in the structure or not.  If it is I don’t process the record but still set the flag as processed later on.  It works but it is as cheesy as it gets!!!

Have an Ordinary Day...

Kurtis   ~|:-)

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices