Datasource Names For All The Database Tags Within CFTRANSACTION Must Be The Same
For a long time, I had an application that was getting the following ColdFusion CFTransaction:
Data source dsn.name verification failed. The root cause was that: java.sql.SQLException: Datasource names for all the database tags within CFTRANSACTION must be the same. The specific sequence of files included or processed is: C:\...
This was an old application that has several versions running at a given time including two on the local development machine - one for internal development and one for client beta testing. As such, I assumed that the above error was being caused by DSN conflicts between the two local applications. Since our DSN values are stored in per application variables, the only thing that I could think of to explain this error was that somehow, one application was overwriting the other application's DSN mid-page. That way, somehow, a transaction might have one query with one DSN and then a second query with another DSN.
To get to the bottom of this, I poured over the code looking for ways in which ColdFusion would let this happen. The application names were totally different and file-location dependent. No possible Application name hijacking. I went over and over the queries in the CFTransaction and nothing seemed to be suspect. I couldn't figure anything out. Sadly, I never got to the bottom of this problem.
Then, yesterday, Spencer Stickland, who has since taken over the project, told me that he finally cracked the problem. After digging through numerous forums and developer resources, he came across this Adobe ColdFusion Tech Note: ColdFusion MX 6.1 and MX 7: Incorrect cftransaction error thrown. In the tech note, it explains that if you use CFExit in the middle of the CFTransaction tag there is a problem with the DSN not be released from the thread; this doesn't cause a problem with the current request, but will cause the above error to be thrown on subsequent ColdFusion requests that contain different DSN information.
I guess the lesson here is that you shouldn't put too much control logic inside of a CFTransaction tag.
Reader Comments
Great Post!
The workaround for the problem of CFTRANSACTION with different data source we have used earlier was.
Use the same datasource but prefix the table with desired database.
Following solution is not good and many times gives unexpected results. Not RECOMMENDED. I am listing it here for fun.
<CFTRANSACTION>
<cfquery datasource=firstone>
SELECT * FROM TableName
</cfquery>
<cfquery datasource=firstone>
SELECT * FROM secondone.dbo.TableName
</cfquery>
</CFTRANSACTION>
Regards,
Pinal
@Pinal,
Very interesting. I have never really referred to data source name within the query value itself. Pretty clever!
As to me maybe very interesting but I can understand more of it!
If I hadn't dug around in some obscure message board posts, I would have never found the problem. Seems like that Adobe KB post should have been one of the top results in Google, but oh well. :)
I'd like to test the old version out in CF8, just to make sure they resolved the error. The solution we had to go with is anything but elegant.
@Spencer,
Still glad you found it. I gotta imagine that it will work in ColdFusion 8, although maybe it wouldn't be an issue at all with the newer version.
@Pinal
The database.datatable concept worked for my scenario.
Should cftransaction be used for every query to the mysql database?. I understand that it groups querys, if they use one anothers info. But would you put it around a single query, and do a rollback to close the connection if unsuccessful. Looking for a php close mysql connections tag or something like. This cftransaction was a solution suggested by my host. Good thing your blog is here ben - hard to find good cfml info.
@James,
CFTransaction is really only needed when you intent to rollback a database modification if something were to go wrong. I suppose you could also use it to influence the locking (Isolation attribute), but that level of understanding goes beyond me. So, you probably wouldn't need anything like this for a SELECT statement.
Thanks Ben thats what i was looking for !_!
@James,
No problem.
Thanks Mr. Ben, Thanks Mr. Pinal.
Great post, great solution.
always find the cf knowledge here! Thanks Ben!
this error can also result from defining username/password in cfquery in some places and not in others within the cftransaction tag