Skip to main content
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with: Seth Johnson and Bob Chesley
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with: Seth Johnson ( @SethNJohnson ) Bob Chesley ( @bobchesley )

Datasource Names For All The Database Tags Within CFTRANSACTION Must Be The Same

By on
Tags:

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

11 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

15,640 Comments

@Pinal,

Very interesting. I have never really referred to data source name within the query value itself. Pretty clever!

10 Comments

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.

15,640 Comments

@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.

50 Comments

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.

15,640 Comments

@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.

7 Comments

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

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel