Learning ColdFusion 9: Application-Specific Data Sources

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:

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.

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

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:

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.

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?

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

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?

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!

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

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?

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.