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

I see that this question is old, but it's still an "issue" on Lucee, and probably Adobe, in 2021. Here's the solution that I devised.

I wanted a way to send myself debug messages to a phone application I've written. They share access to a database.

Here is an adaptation of my solution. My actual code does some other things, so this hasn't directly been tested

public numeric function QueryQueue(required string sql, struct options = {}, struct params = {}, queryset = "default") {

  param name="request.queryQueue" default="#{}#";
  if (!StructKeyExists(request.queryQueue, arguments.queryset)) {
    request.queryQueue[arguments.queryset] = []
  }

  request.queryQueue[arguments.querySet].append({sql: "#arguments.sql#",
    options: arguments.options,
    params: arguments.params,
    removed: false});

  return request.queryQueue[arguments.queryset].len();
  // returning the length, and thus the position of the query,
  // so it can be specifically called if desired.
  // This is query QueryQueueExecute doesn't actually
  // delete elements, but marks them.
}

public any function QueryQueueExecute(required numeric pos, boolean remove = true, string queryset = "default") {
  if (!request.queryQueue[arguments.queryset][arguments.pos].removed) {
    var theQuery = QueryExecute(sql = request.queryQueue[arguments.queryset][arguments.pos].sql,
      options = request.queryQueue[arguments.queryset][arguments.pos].options,
      params = request.queryQueue[arguments.queryset][arguments.pos].params);
    if (arguments.remove) {
      request.queryQueue[arguments.queryset][arguments.pos].removed = true;
    }
    return theQuery;
  } else {
    return {recordcount: -1}; // a flag to show that the query wasn't executed, because it's already been "removed"
  }
}

public array function QueryQueueExecuteAll(boolean remove = true, string queryset = "default") {
  var queryArray = [];
  for (i = 1; i <= request.queryQueue[arguments.queryset].len(); i++) {
    queryArray.append(QueryQueueExecute(i, false));
    // false is deliberately set here, rather than passing the remove argument
    // since the array will be cleared after the loop.
  }
  if (arguments.remove) {
    request.queryQueue[arguments.queryset].clear();
  }
  return queryArray;
}

These functions let me queue the queries, and execute specific ones, or execute them all. There's also a flag to remove if desired or not, though I can't imagine why it wouldn't be.

In my case, I can execute run this in OnRequestEnd and in my ErrorHandler, since my use is for debugging.

Answer 2

Had the same issue and just moved my cftransaction tag out of the second (or first) datasource cfquery. This includes CFCs if you are using them throughout the code.

Answer 3

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.

Answer 4

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.