3 Replies Latest reply on May 5, 2011 6:05 PM by KomputerMan.com-L7dcfe

    CFTRANSACTION with multiple datasources

    KomputerMan.com Level 1

      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:


                      <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>



      Any thoughts on how to accomplish this???


      Thanks in Advance for Any and All Help!!!

      Kurtis   ~|:-)

        • 1. Re: CFTRANSACTION with multiple datasources
          Dave Watts Adobe Community Professional

          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



          • 2. Re: CFTRANSACTION with multiple datasources
            Dan Bracuk Level 5

            Edit your insert query to exclude records it already has.

            • 3. Re: CFTRANSACTION with multiple datasources

              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   ~|:-)