Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2008 (Washington, D.C.) with:

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

Posted by Ben Nadel
Tags: ColdFusion

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

Reply to this Comment

@Pinal,

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

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.