Multiple Datasources with CFTRANSACTION

Question

I have run into the following error:

Datasource names for all the database tags within the cftransaction tag must be the same.

This has come about from the following code:

transaction action="begin" {
  try {
    var data = {};

    data.time = getTickCount();

    addToLog("Persist", "Started persist operations");

    doClientPersist();
    cleanUp(arguments.importId);

    addToLog("Persist", "Completed the persist operations successfully", ((getTickCount()-data.time)/1000));

    return true;
  } catch (any e) {
    transactionRollback();
    data.error = e;
  }
}

The transaction is effectively wrapping allot of lower level methods within doClientPersist(). One such call, which is deep within our frameworks database abstraction layer, fetches (SELECTs) longitude and latitude information from a separate datasource (lets say the Postcode data source) - This datasource is strictly read only.

<cffunction name="getLatitudeAndLongitude" access="package" returntype="query" output="false">
  <cfargument name="postcode" type="string" required="true" />
  <cfset var qPostcode = ''/>
  <cfquery name="qPostcode" datasource="postcodesDatasource">
    SELECT 
      a.latitude, 
      a.longitude
    FROM 
      postcodes AS a
    WHERE 
      a.postcode = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#postcode#"/>
  </cfquery>
  <cfreturn qPostcode/>
</cffunction>

<cffunction name="getPostcodeCoordinates" access="public" returntype="struct" output="false">
  <cfargument name="postcode" type="string" required="true"/>
  <cfscript>
    var data = {};

    data.postcode = getFormattedPostcode(arguments.postcode);
    data.valid    = isValidPostcode(data.postcode);
    data.coords   = {};

    if (data.valid) {
      data.query = getLatitudeAndLongitude(data.postcode);
      if (isQuery(data.query) && data.query.recordCount) {
        data.coords["latitude"]  = data.query["latitude"][1];
        data.coords["longitude"] = data.query["longitude"][1];
      } else if (data.valid == 2) {
        /** No match, try short postcode (RECURSIVE) **/
        data.coords = getPostcodeCoordinates(trim(left(data.postcode, len(data.postcode)-3)));
      }
    }
    return data.coords;
  </cfscript>
</cffunction>

Reading into the issue, the docs say the following:

In a transaction block, you can write queries to more than one database, but you must commit or roll back a transaction to one database before writing a query to another.

Unfortunately, as mentioned above, the code fetching this postcode data is completely unrelated to the actual persist operation, because it executes a web of lower level methods that cannot be changed I am unable to commit the "top level" transaction before making the call to the remote datasource.

Is there anyway that I can wrap the "top level" method within a transaction and still have the call to the "postcode" datasource - It would be silly for us to have to duplicate the postcode information for each client, however the operation MUST be rolled back if something goes wrong.

Thanks in advance.

Answer 1

As I can see it, you have basically two choices.

1) Query your data outside of the transaction. Depending on the specifics of your application, this could be moving that method before the transaction block, splitting up the method and moving part of it before the transaction block, pre-fetching the data into RAM (holding the data perhaps as a query in a variable) and then having your method use this pre-fetched data rather than querying the database directly.

The upshot of all of these solutions, however, is the same. That is that the SELECT query itself is performed outside of the transaction.

If that is impractical for whatever reason, then on to...

2) Use the same datasource. Note that you do not have to use the same database, just the same datasource. So, you can database.tablename syntax in MySQL.

With just quick searching, I found a good example of this: Querying multiple databases at once

Someone with better Google-fu than I could probably come up with better examples pretty quickly.

The basics though is that you use FROM database.tablename instead of just FROM tablename in your query.

I believe this would require the databases to be on the same MySQL server however.