Something I learned today in my very rare use of CFTRANSACTION. I have a process that merges member accounts. This is needed when people create more than one account because they forget or can’t access their email account, etc. Because of the scale of this operation and it’s total behind-the-scenes nature, I’m using mostly CFQUERYs in conjunction with a few Transfer calls. The psuedo-code looks like this:
<cftransaction>
acct1 = transfer.get("member", id);
acct2 = transfer.get("member", id2);
memberships = acct1.getMemberships();
for (ii = 0; ii < memberships.length; ii++)
{
membergateway.mergeMemberships(memberships[ii], acct2);
}
membergateway.mergeAccounts(acct1, acct2);
if (success) cftransaction commit
else cftransaction rollback
</cftransaction>
Which resulted in the error:
Datasource convert verification failed.
The root cause was that: java.sql.SQLException: Usernames and Passwords for all the database tags within the cftransaction tag must be the same.
I was stumped! I don’t even use username/password in my CFQUERY blocks! Then I realized that Transfer did, and since it was between the CFTRANSACTION block, it was screwing up my manual queries. This is what my CFQUERY in my gateway normally looks like:
<cfquery name="select" datasource="#variables.datasource.getName()#">
SELECT foo
FROM sometable
WHERE id = '#arguments.id#'
</cfquery>
Where variables.datasource is a bean provided to my Gateway by Coldspring as defined in my coldspring.xml:
<bean id="datasource" factory-bean="ormService" factory-method="getDatasource" singleton="true" />
This is the bean that Transfer uses and it contains the DSN, username and password. Even though my username and password are stored in the CF admin, I had to add them to each of my CFQUERYs that appeared inside of the CFTRANSACTION to make it play nicely with Transfer:
<cfquery name="select" datasource="#variables.datasource.getName()#" username="#variables.datasource.getUsername()#" password="#variables.datasource.getUsername()#">
SELECT foo
FROM sometable
WHERE id = '#arguments.id#'
</cfquery>
After that change, the error messages disappeared. Also check my post on something else I learned today about CFTRANSACTION with SAVEPOINTs.
All Categories from Feedburner
Brian Kotek said:
on June 28, 2008 at 12:00 pm
Note that this actually has nothing to do with Transfer but is a requirement of using cftransaction at all: all of the queries within the transaction must specify identical datasource, username, and password attributes.
brian said:
on June 28, 2008 at 4:40 pm
Correct – the error message was confusing at first. It’s one of the catches of using black boxes (any black box, nothing specific about Transfer here except that as an ORM, it runs CFQUERY behind the scenes).