Learning ColdFusion 9: Application-Specific Data Sources
Posted July 13, 2009 at 10:27 AM by Ben Nadel
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.
Reader Comments
And so is the cool ORM integration, which wont be available to Application.cfm users
@Rahul,
Yeah... people seriously need to start using Application.cfc. It really just offers so many more advanced features!
A nice feature, making CF just a little more convienient.
Oh, and how do I get an avatar for my comments?? :)
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
Wouldn't you just set the other attributes like username and password for the datasource in CF administration?
@Adam
Go to http://en.gravatar.com/ and sign up. A ton of sites out there use them. Then whenever you post to a site that uses them (and use the same e-mail), it will show up. :)
Ah, got it. I had a Gravatar account, but under an older email address. This should work much better.
Bah!!! Hmm, oh well.
@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.
