Default Application Datasources

What'd be even better is if you could set up multiple datasource aliases. So you could have your code omit the datasource attribute for the default, or use an alias that is defined for the application.

In the meantime, just set up a snippet in CFE or DW that does the typing for you. Same for CFQUERYPARAM.

I don't know if I agree with you on this one. I routinely work on applications that have multiple datasources. I don't see how an alias would be any different than the variable(s).

I completely agree with you, many of my applications only have one datasource. Only I would take it even a step further and ask to be able to define all of the datasources (their names, connection info, locations, everything) in the application.cfm/cfc as well as set one of them to a default. With my old hosting program, one of the biggest hastles was having to put in a request to have the datasource added, that as well as mappings.

I like this idea a lot. We currently also don't specify the username and password for our datasources in the CF administrator because of possible security risks. Instead, we have a control file for all of our datasources that we include in each application that has the username and password set as variables as well as all of our datasources. Then when we write a query, we just use those variables. It works very well (except for validating datasources in the CF admin), but it would be even better if it were automatic.

Great idea!!!

Why don't you write a custom tag for it?
Something like:
<pre>
<cfif thisTag.ExecutionMode is "start">
<cfparam name="attributes.name" type="string" />
<cfparam name="attributes.datasource" type="string" default="myDatasourceName">
<cfelseif thisTag.ExecutionMode is "end">
<cfquery name="query">
#thisTag.generatedContent#
</cfquery>
<cfset thisTag.GeneratedContent = "" />
<cfset caller[attributes.name] = query />
</cfif>
</pre>
Of course, you might have to write an additional custom tag to do what cfqueryparam does.

Gaah. Code format stuffed up... someone needs to port markdown to CF...

I always pass queries through a DAO object, or a seperate QueryUtility obect which does Query of Queries, so those are the only two places that I have <cfquery> tags. The DAO object has built-in DSN management (I usually work with 20-30 DSN's in a given app.. the DSN's change often for load balancing, new database builds, etc), so it's not an issue. It returns a "DAOReturn" bean of anything I could ever want to know about the return data (what SQL it actually ran, execution time, if there was an error it caught, etc), including automatically catching failed queries.

If you only ever use one DSN, just write one method that queries a DSN and pass SQL (and any other params you want to set) to it? Seems like a problem which could be solved by trying something different with the design of said app?

This is umm actually a bad idea in my book (if you are using an OO CFC based method of attack that is)

Context gives your application Persist Scope, meaning your Application can one day use XML as its storage, then the next it could use SQL.

What happens if you store information within Multiple DSN's? Audit Information goes in DSNA, while mainstream Data goes into DSNB (why? seperation of security for one).

I've built / used a config business object, and I pass that into all of my "Services/Managers/Herders/Builder" classes. This configuration object initializes itself based on an XML packet that I "couple" with the application. Now, at this point i don't simply have routines "getDSN()" for example.

I class my overall DSN information based on the persist service they provide, meaning i have a method within my config which is like: "getPersistService('security')" which depending on the DB Server type, returns appropriate DSN information.

Another classic mistake is folks sometimes use username + password, while other times they don't (ie shared hosting, its a must. Dedicated hosting, not so much).

Point is this, the only points of customization within my cf applications are:

- config.xml
- DAO_SQL, DAO_Oracle, DG_SQL, DG_Oracle etc..

The rest of the business logic doesn't give two hoots. The main reason for this is simply that while Application.cfc is great, its easily abused and in a nutshell Application.cfm is probably the best suited aspect of a coldfusion application which provides appropriate context to its intended use, resulting in promoting a better "re-use" capability within your code.

If i had to pick this setting, i'd much prefer Barneys approach whereby we leveridge some kind of Mapping/Alias to suite our CFQUERY code. To me thats a much easier and more elegant approach.

Elliot,

We've been looking for potential database load balancing software as well, and came across this: http://c-jdbc.objectweb.org/

Nice thing about CJDBC is that it's a multiplexing datasource, so in theory, you get load balancing without modifying your application code.

Note that I've not been able to look at this yet, but this is one of the ideas that's on the cards to investigate as a matter of performance improvements.

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.
http://www.bennadel.com/blog/1642-Learning-ColdFusion-9-Application-Specific-Data-Sources.htm