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.

Answer 2

So I've been a bit confused on how to resolve this one. I've accepted Steve's answer as he gave me the idea (thanks), but added the code below to show a simple example of the solution.

For me the datasource data could not be duplicated and the code above needed its wrapping transaction. So it only really left me with one solution, a half baked one in my opinion...

<cffunction name="methodA" access="public" returntype="query" output="false">
  <cfset var q = ""/>
  <cfquery name="q" datasource="test_1">
    SELECT id, name FROM table_a
  </cfquery>
  <cfset methodB = methodB()/>
  <cfreturn q/>
</cffunction>

<cffunction name="methodB" access="public" returntype="query" output="false">
  <cfset var q = ""/>
  <cfquery name="q" datasource="test_1">
    SELECT id, name FROM table_b
  </cfquery>
  <cfset methodC = methodC()/>
  <cfreturn q/>
</cffunction>

<cffunction name="methodC" access="public" returntype="void" output="false">
  <cfset var q = ""/>
  <!--- 
  This will error with the following: 
    Datasource test_2 verification failed. 
    The root cause was that: java.sql.SQLException: 
    Datasource names for all the database tags within the cftransaction tag must be the same.
  <cfquery name="q" datasource="test_1">
    INSERT INTO test_2.table_z (`id`, `name`) VALUES ('1','test'); 
  </cfquery>
  --->

  <!--- This is the same query, however I have reused the datasource test_1
  and specified the DATABASE test_2 --->
  <cfquery name="q" datasource="test_1">
    INSERT INTO test_2.table_z (`id`, `name`) VALUES ('1','test'); 
  </cfquery>

</cffunction>

<cftransaction action="begin">
  <cfset data = methodA()/>
  <cftransaction action="commit"/>
</cftransaction>

So if it is not clear, the solution for me, was to remove the reference to the second datasource test_2 and use test_1 datasource instead. This means hard coding the second test_2 database name within the query. Obviously this can be done dynamically, however it does cause problems for existing queries as they need to be changed. In addition to this, should the second datasource be a different database platform, like MSSQL, this wont work. Thankfully, this wasn't the case for me.