Best practice for Datasource use in a CFC

Question

I have an application which uses context sensitive datasources. Currently I keep the datasource information stored a such

reqeust.DB.Datasource = "DatasourceName";
request.DB.Username = "DatasourceUsername"
request.DB.Password = "DatasourcePassword"

I then overwrite the variables depending on the context, so each cfquery tag has the attributes datasource="#request.DB.Datesource#" ... etc ...

I want to start moving to more CFC centric frameworks like Coldbox, but I just don't see how this would work.

Do I need to pass in a datasource object into the init statement of the CFC? This seems like it would be a super PITA.

score 3 · Accepted Answer · answered Nov 07 '10 at 07:07

3

With CF9, you can this.datasource in Application.cfc as the default datasource. Unfortunately, it doesn't seem to have a way to set username/password

Either

A.) use an Dependency Injection framework such as ColdSpring (only suitable for singleton Services), Lightwire or Coldbox's own DI solution (Wirebox). and inject the datasource/username/password through the init constructor or setters.

B.) set <Datasources> in Coldbox.xml.cfm, see: http://wiki.coldbox.org/wiki/ConfigurationFile.cfm

<!--Datasource Setup, you can then retreive a datasourceBean
   via the getDatasource("name") method: -->
<Datasources>
  <Datasource alias="MyDSNAlias" 
                  name="real_dsn_name" 
                  dbtype="mysql" 
                  username=""
                  password="" />
</Datasources>

answered Nov 07 '10 at 07:07

Henry

32,689
19
120
221

So what does the CFQuery tag look like in the cfc from either option? – Tyler Clendenin Nov 07 '10 at 18:20
Oh, and the idea here is the application accesses different datasources depending on who is logged in, so I need to be able to set the datasource dynamically from either the results of a query or from session variables. There are two datasources used on any given request, one is the authentication datasource, and the other is set based on the results from the authentication. – Tyler Clendenin Nov 07 '10 at 18:26
Your Service layer should handle the logic, and maybe call your DAO layer with the datasource variable passed in through argument scope? Cache your 2 datasources in Service layer's variables scope, injected by your Dependancy Injection framework? – Henry Nov 08 '10 at 02:28
Just a footnote that since CF9.0.1 you can now specify a username and password when setting the application wide datasource by using a struct. – CfSimplicity Mar 30 '11 at 14:22

Anthony · Answer 2 · 2010-11-09T00:48:20.453

Even if your objects only get initialized at request level, it seems like it should be less of a pain to work with in this fashion.

<cfscript>
request.DB.Datasource = "DatasourceName";
request.DB.Username = "DatasourceUsername";
request.DB.Password = "DatasourcePassword";

request.randomDAO = createObject('component','DAOStuff.randomDAO');
request.randomDAO.init(DBObject = request.DB);

request.someQuery = request.randomDAO.someGetter();
request.someOtherQuery = request.randomDAO.someOtherGetter();
request.aThirdQuery = request.randomDAO.aThirdGetter();
</cfscript>

As opposed to:

<cfscript>
request.DB.Datasource = "DatasourceName";
request.DB.Username = "DatasourceUsername";
request.DB.Password = "DatasourcePassword";
</cfscript>

<cfquery name="request.someQuery" 
    datasource=request.DB.Datasource 
    username=request.DB.Username 
    password=request.DB.Password>
    --SOME SQL HERE
</cfquery>

<cfquery name="request.someOtherQuery" 
    datasource=request.DB.Datasource 
    username=request.DB.Username 
    password=request.DB.Password>
    --SOME SQL HERE
</cfquery>

<cfquery name="request.aThirdQuery" 
    datasource=request.DB.Datasource 
    username=request.DB.Username 
    password=request.DB.Password>
    --SOME SQL HERE
</cfquery>

If it is safe for your data objects to exist at an application level (assuming here that the data source for the object will not change at run-time and that you have written thread-safe CFCs) You can store and initialize DAOs at application level and then each request has wonderfully simple code like:

<cfscript>
request.someQuery = application.randomDAO.someGetter();
request.someOtherQuery = application.randomDAO.someOtherGetter();
request.aThirdQuery = application.randomDAO.aThirdGetter();
</cfscript>

Best practice for Datasource use in a CFC

2 Answers2