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!!!

Well, I can't see why it would "lock" the use of multiple datasources. You can define the default and omit it; when you set it up, CF use the setting. When omitted, use default. Great idea!! How can we "vote" on this new feature?

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.

<cfset request.db = "blah">

<cfquery datasource="request.db">

Why not creating a custom tag for that?

<cf_myquery>

</cf_myquery>

Did not see the others posts...

You can't use cfqueryparam with custom tags?

The way we currently do things here is to set Request.Datasource in the Application file (depending on live/test/dev mode) and use <cfquery name="something" datasource="#Request.Datasource#">..</cfquery> - but it would be very nice to cut out those thirty five characters.

(the key benefit lost without cfqueryparam being bind variables)

Generally all my queries are performed via functions,(for reusability) and all functions have the last parameter set to optional which is the datasource. If none is specified when the fuction is called it automatically substitutes the CMS.defualtdatasource vai <cfparam>.

I do think it would be nice if you could specify a default in the <cfapplication> tag. Better would be being able to specify a list of datasources to which coldfusion would choose the first available one and perform load balancing.

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.

I would like to answer back to HKS, who gracefully provides us a code snippet for a custom tag that feed the CFQUERY tag with default values when none are specified.

However, this tag is broken, and won't never work, because of the way ColdFusion handles quotes inside CFQUERY's: for security concerns, all expressions dumped using the ## notation, inside the body of any CFQUERY, is first processed in order to replace all ' by \'. This way, SQL injection is avoided. For further information, see the comments below the CFMX8 documentation page for CFQUERY. These comments include also a way to make this custom tag work.

Suppose now you have such a tag. I personnally use a similar thing in my developments, and consider the drawbacks it brings along: - error reporting. The SQL error is not reported to have happened in the location the custom tag is called at, but in the custom tag itself. Ok, you can trace back where the error is by looking at the stack and, why not, to create your own error handler to beautify all this mess. - more annoying, the nice feature of automatic quote escaping goes away...

I'll be glad if anyone here knows how to turn on this escaping thing again.

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