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 2009 (Lansdowne, VA) with:

Learning ColdFusion 9: Application-Specific Data Sources

By Ben Nadel on
Tags: ColdFusion

As I stated in my blog post on ColdFusion 9 implicit struct and array usage, I think that it's sometimes the little feature upgrades that make the biggest difference in the long run. Another little feature in ColdFusion 9 that's going to make our lives a lot easier is the addition of application-specific data sources. While this application property is oddly missing from the beta documentation, if you've followed ColdFusion blogs, you've probably seen that in ColdFusion 9's Application.cfc, you can now define your query datasource as an attribute of the THIS scope:

  • <cfcomponent
  • output="false"
  • hint="I define application settings and event handlers.">
  •  
  • <!--- Define application settings. --->
  • <cfset this.name = hash( getCurrentTemplatePath() ) />
  •  
  • <!---
  • Define the data source to be used by all CFQuery tags
  • within this application. With this in place, you will not
  • need to define a Datasource attribute in and of your
  • CFQuery tags.
  • --->
  • <cfset this.datasource = "ben" />
  •  
  • <!--- Define page settings. --->
  • <cfsetting showdebugoutput="false" />
  •  
  • </cfcomponent>

Notice that Application.cfc now has a "this.datasource" property. With this defined, I no longer need to include a Datasource attribute in any of my CFQuery tags. To test this, I set up a simple index.cfm page that creates, populates, and then queries a table in the embedded Apache Derby database:

  • <!--- Drop the exist table. --->
  • <cfquery name="drop">
  • DROP TABLE girl
  • </cfquery>
  •  
  •  
  • <!---
  • Create the girls data table. The database I'm using
  • is an embeded Apach Derby database, hence the really
  • odd auto-increment syntax.
  • --->
  • <cfquery name="create">
  • CREATE TABLE girl
  • (
  • id int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  • name varchar(30) NOT NULL,
  • hair varchar(30) NOT NULL,
  • PRIMARY KEY (id)
  • )
  • </cfquery>
  •  
  •  
  • <!--- Insert a records into the girl. --->
  • <cfquery name="insert">
  • INSERT INTO girl
  • (
  • name,
  • hair
  • ) VALUES (
  • <cfqueryparam value="Tricia" cfsqltype="cf_sql_varchar" />,
  • <cfqueryparam value="Brown" cfsqltype="cf_sql_varchar" />
  • )
  • </cfquery>
  •  
  •  
  • <!--- Query for girls. --->
  • <cfquery name="girls">
  • SELECT
  • id,
  • name,
  • hair
  • FROM
  • girl
  • </cfquery>
  •  
  •  
  • <!--- Output query records. --->
  • <cfdump
  • var="#girls#"
  • label="Girls (Derby)"
  • />

Notice that none of the CFQuery tags on the page define anything more than the Name attribute. The Datasource value which we would normally need now feeds of the application-specific Datasource property in the Application.cfc. When we run the above code, we get the following CFDump output:

 
 
 
 
 
 
ColdFusion 9 Application-Specific Datasource Property Allows Global Datasource Definition For Every CFQuery Tag. 
 
 
 

It works perfectly.

Just like most other application properties, this value can be changed on a per-page-request basis. Meaning, if you had this code in your Application.cfc:

  • <!--- Randomly set the app-specific data source. --->
  • <cfif (randRange( 1, 2 ) EQ 1)>
  • <cfset this.datasource = "ben" />
  • <cfelse>
  • <cfset this.datasource = "ben_bunk" />
  • </cfif>

... half of your page requests would error out. This, of course, is a silly example; but, if you needed to, you could easily extend your root Application.cfc and override the Datasource property. Keep in mind that this datasource property is a property of the application and not of any specific CFQuery tag. This means that if you create a CFC with one datasource and cache it, changing the datasource in the Application.cfc post-caching will still trickle down into the cached CFC, causing errors.

In the past, ColdFusion has been quite lenient for those of you who refuse to take up Application.CFC and abandon Application.CFM. Well, the "Datasource" property is not available on the CFApplication tag. If you want to use this (and other application-specific properties such as Mappings and CustomTagPaths) you need to be using Application.cfc. And, once you do, I think you'll find that this small upgrade will make life a whole lot nicer.




Reader Comments

@Rahul,

Yeah... people seriously need to start using Application.cfc. It really just offers so many more advanced features!

The change from application.cfm to application.cfc is going to be that much more easier with the extra functionality added to cfscript.

I come from a PHP background and believe that while markup has it's places (ie: cfquery), scripting is much easier to read and code.

If only we could drop in and out of cfscript while in the middle of loops and similar.

P.S: I have also been searching on how to change my avatar too.

Hey Ben, Its Cool! What if we would like to go for extra attributes like username/password. can we also describe the same in the (this) scope in application.cfc to make it easier for a bit of security or not

@Misty,

From what I can tell, there are no username / password attributes for the data source. Like Andrew is saying, you just need to set that up in the CFAdmin and refer only to the data source in the code.

As someone who is used to used passing username and password via the CFQuery tag traditionally, this will actually be a nice "forced" change :)

Yup, I wish it would let me do something like
<cfset this.datasource = "mydb,myusername,mypwd">

Why? Because some people want that added security of passing the username and password everytime. Of course then you get the people who want to encrypt the connection string.

BTW speaking of hash, what does hashing the current path into this.name do for you? Or do I need to go read other posts?

@Don,

I know exactly what you are saying. My pre-CF9 applications all use datasource / username / password in the CFQuery tag for the flexibility. But, I think this is one of those situations where I am going to let the language decide for me. If they only allow me to put my datasource name, well then, I guess I need to start putting my username / password in the data source itself (in the CF Admin).

I know that's not the best line of logic; but gosh, I really do want to use the centralized datasource value!

As far as the hash() in name, I've actually had a number of people recently ask me about that. It helps to keep the application name unique without having to worry about copy-pasting files. Check this out:

http://www.bennadel.com/blog/1845-Making-Sure-Your-ColdFusion-Applications-Are-Uniquely-Named.htm

I know it might seem silly to not name applications. But, unless you use the name for something meaningful, this approach just gives me one less thing to worry about.

I am also looking for a way to set the datasource username/password for the application as well...not so much to avoid having to set it in the administrator...but because on an Oracle database, the user is the schema name...you will often have many applications using the same database (and thus that can use the same datasource) but based on the login, will use a separate schema in that database. Having to set up datasources for every application is not really something we want to have to do.

FYI - it does appear that the 9.01 allows you to set the datasource using a struct that includes the username/password. So yeah for that!

@Mary Jo,

Oh cool - I was not aware that that was part of the updater. Man, I really need to get my hands on 9.0.1 and start playing with it. Thanks for the tip!

My problem with the this.datasource is that the datasource name is hardcoded. Does anyone know how to make it dynamic?
I defined my default ds in coldspring and tried to set the this.datasource inside the onapplicationstart function ;) but it did not work.
Any ideas?

Hi,
I'm new to all of this, so i used this.datasource like this :

  • <cffunction name="onApplicationStart">
  • <!--- Set the datasource --->
  • <cfset this.datasource="myDSN" />
  • </cffunction>

It works once and after it throws that :
The value of the attribute datasource, which is currently '', is invalid ???

please help to solve this prob.

Thanks,
Houssem

@Houssem - I can relate to your problem since I also tried something like that. If you dump the this scope, you are actually going to see the value you assigned to the datasource but...

@CFKelvin, @Houssem,

The properties of the THIS scope for per-application configuration have to be set in the pseudo constructor of the Application.cfc (the space outside the CFFunction tags). If you do it inside one of the event handlers (ex. onApplicationStart()), it's already too late.

@Houssem, I am not sure why it is giving you an error about the value as being invalid, though. I would just expect your CFQuery tags to fail. Although, in CF9, it might look at some internal property if you are not defining a datasource property on the CFQuery tag (which is not be set in time in the Application.cfc).

A lot of people have asked for this to be able to be set within an event handler. I personally don't understand this. Perhaps it's just because I don't use ColdSpring? I guess it forces you to think about datasources in a certain way.

You can always call one of the methods *from* the pseudo constructor. So, if you want to add logic for the per-server configuration, you can, at the least, encapsulate it within a method of the Application.cfc (if you don't want to clutter up the pseudo constructor).

Ben,

Any ideas on why this will not work for CFSTOREDPROC tags? Seems like this is only for CFQUERY, but I cannot figure out why they'd only do it for that.

I get the same error even with this.datasource defined outside of all cffunction in application.cfc. Do you think this is a bug in cf9?

Thanks for your help --RR

I just updated a project I'm working on to take advantage of this.datasource. Neat way to reduce code, but one limitation I discovered after doing a global find and replace is that CFInsert and CFUpdate don't recognize the new property. I had to put the old "application.dsn" variable back in on those tags. Shame, as imagine how compact they'd look written out like this...

<cfupdate tablename="account">

By the way, is there any way to get the value of the this.datasource property for general use? The "THIS" scope seems to not be available at the page level as doing <cfdump var="#this#"> throws an error. Any ideas?

@Houssem,

I'm sure you figured this out by now, but if not:

I got that error "the value of the attribute datasource which is currently '' is invalid" when I had my application set to timeout immediately on each load for testing. In my application.cfc I had <cfset this.applicationTimeout = createTimeSpan( 0, 0, 0, 0 ) />.

Very foolish of me because when it came time for the queries to run the this.datasource application variable was gone because I timed it out!

@Ben,

No offence, but if people were really wanting advanced features they would be using a platform like ASP.NET MVC.

CFML is so structurally compromised as a tag-based scripting language that it's hard to imagine it ever really earning the description 'advanced'.

But I guess every little bit helps.

While this is very cool, the flip side is that I'll rarely, if ever, be able to use it as we generally use 2 datasources per application. One read only and one read/write.

This is for both security (code that does 'reads' shouldn't have authorisation to write) and performance (SELECTs can be send to slave db servers etc).

And I'd rather not go down the path of *some* cfqueries using datasource attributes and some not.

Still, very cool if you can get away with a single datasource.

@Gahiggidy and all: Did you ever find a way to get the name of the default datasource from elsewhere in the app besides Application.cfc? It's not in the application scope. Getting that info would be very helpful.

@Ben,

I am running into the same problem as Houssem and cfkelvin. My reason for wanting to change the application datasource inside a function is that I want to change datasource after the cflogin in my onRequestStart. This way the user has more limited access to the database until after he or she logs in. Unfortunately, it sometimes runs the cfquery using the public datasource instead of the logged in datasource. How else could I achieve the same affect? Do I need to specify the datasource on every single cfquery? @Michael Sharman?

@Myself and Ben

Sorry, I think I solved my own problem. Not sure if this is a bug or not.

In my application.cfc, when setting the default datasource, I simply put my datasource in a function and called that function. Like So.

  • <cfset setDatasource()>
  • <cffunction name="setDatasource" output="false" access="private" returntype="void">
  • <cfset this.datasource = "PUBLIC">
  • </cffunction>

Then I was able to change this.datasource in my onRequestStart function without any oddities. I haven't receive an error yet. Does this make sense?

Hello,
In CF9 Application.cfc I guess this.datasource="magic" only giving options to set default datasource if it is not defined in cfquery.
If I have multiple data sources connecting multiple RDBMS,is there any way even in CF 9 + versions to define multiple datasources in Application.cfc

Hey There Just Wanted To Give You A Quick Heads Up And Let You Know A Few Of The Images Aren't Loading Properly. I'm Not Sure Why But I Think Its A Linking Issue. I've Tried It In Two Different Web Browsers And Both Show The Same Results.