How to create multiple datasources in application.cfc

[c__wright1]

I am using ColdFusion 11 and having an issue creating data sources to more then one database.

The application works when I have one data source in the application.cfc, but when I add a second data source the second one does not work and I receive an error "Datasource main_db2 could not be found". 

Can anyone provide me with sample code on how to set up two or more data sources using application.cfc ?

Current code that works for one data source in application.cfc.

// Application.cfc
component {
        this.name = "DSNTest";
        this.datasources = {
                main_db1 = {
                    database = "CUS124",
                    host = "C008945DE.us.int444.com\WWGRRD06",
                    driver = "MSSQLServer",
                    username = "abc123x",
                    password = "password123" }
                };

   this.datasource = "main_db1";
   }

[Dave Watts]

Being able to set the datasource in Application.cfc is basically just a shortcut for having to set it explicitly in your CFQUERY and CFSTOREDPROC tags. I'm pretty sure you can't set multiple ones and have them automatically work the same way, because how would your tags know which one to use?

But you can create as many variables as you want, and use them in your CFQUERY and CFSTOREDPROC tags. You can create two or more variables in Application.cfc, then simply reference them in those tags:

Application.cfc:

// this will be used by all CFQUERY and CFSTOREDPROC tags that don't have a DATASOURCE attribute

this.datasource = "main_db1";

// to use these, you'll need to specify a DATASOURCE attribute in your queries

this.datasource_1 = "other_db1";

this.datasource_2 = "other_db2";

some_other_page.cfm:

<!--- this query will use main_db1

<cfquery name="query_default_db">

...

</cfquery>

<cfquery name="query_other_db" datasource="#other_db1#">

...

</cfquery>

<cfquery name="query_other_db" datasource="#other_db2">

...

</cfquery>

Dave Watts, Fig Leaf Software

[WolfShade]

Waitaminnit.. am I missing something?  Shouldn't that be:

<cfquery name="query_other_db" datasource="#this.datasource_1#">
...
</cfquery>
<cfquery name="query_other_2" datasource="#this.datasource_2#">
...
</cfquery>

V/r,

^ _ ^

[Dave Watts]

I'm sure there are a fair share of typos in my original response. A lot of times, I'm just typing these things on my phone or tablet. So caveat emptor on my code samples. But I think it should be application.other_db1 and application.other_db2 in the CFQUERY tags' DATASOURCE attributes in my example.

Dave Watts, Fig Leaf Software

[WolfShade]

Oh, I didn't realise that #this# and #application# are interchangeable.  Though I suppose it does make sense since the application is typically named in the this scope.  (D'OH!  Imagine me doing a Homer Simpson facepalm.)

V/r,

^ _ ^

[Dave Watts]

So, I don't think that you can use "this" to refer to the Application scope from outside Application.cfc/cfm itself. I'm not in front of a CF server to verify that, though. In any case, the main point stands: you can't have multiple datasources stand in for the one you specify in the Application scope itself. That's the important part, I think.

Dave Watts, Fig Leaf Software

[c__wright1]

Sorry, I may have worded my question poorly.

What I am looking to do is to create a variable for each database (on different database servers) that I can use in the stored procedure calls to the two databases.

eg.

<cfstoredproc procedure="CUS124.main.selectAlerts" datasource="Main_db1"  returncode="yes">

<cfprocresult name ="alerts">

</cfstoredproc>

<cfstoredproc procedure="SSQ56.dbo.selectStats" datasource="Main_db2"  returncode="yes">

<cfprocresult name ="stats">

</cfstoredproc>

My issue is that I am migrating from ColdFusion 10 where the DSNs were set in the ColdFusion administrator to ColdFusion 11 and need to create the DSNs in the application.cfc but I have not been able to figure out the syntax.

[BKBK · Accepted solution]

c..wright  wrote

Current code that works for one data source in application.cfc.

// Application.cfc
component {
        this.name = "DSNTest";
        this.datasources = {
               main_db1 = {
                    database = "CUS124",
                    host = "C008945DE.us.int444.com\WWGRRD06",
                    driver = "MSSQLServer",
                    username = "abc123x",
                    password = "password123" }
                };

   this.datasource = "main_db1";
   }

You're almost there! The only problem here is that you have repeated the name of the datasource. The names have to be distinct. Something like:

// datasources created here as an alternative to creating them in CFAdmin

this.datasources = {

        Main_db1    = {

            database    = "databasename1",

            host        = "localhost",

            port        = "1433",

            driver      = "MSSQLServer",

            username    = "BKBK",

            password    = "1234ab"

        },

        Main_db2    = {

            database    = "databaseName2",

            host        = "localhost",

            port        = "1433",

            driver      = "MSSQLServer",

            username    = "BKBK",

            password    = "1234ab"

        },

       Main_db3    = {

            database    = "databaseName23",

            host        = "localhost",

            port        = "3306",

            driver      = "MySQL5",

            username    = "root",

            password    = "ab4321"

        }

    };

// Name of main application datasource, created in CFAdmin

this.datasource="Main_db";

[c__wright1]

Thanks to everyone for their help.  I hope you all have a Merry Christmas!

The sample syntax was enough for me to resolve my issues.

My final code:

<cfscript>

component

{

this.name = "DB1";

this.datasources =

    {

        DB1    = {

            database    = "CUST",

            host        = "CSVT000089EA.us.test.com\WWABC06",

           // port        = "49503",

            driver      = "MSSQLServer",

            username    = "master_admin",

            password    = "Password123"

                },

       DB2    = {

            database    = "INV",

            host        = "CSVT000089EA.us.test.com\WWABCD06",

          //  port        = "49503",

            driver      = "MSSQLServer",

            username    = "user1",

            password    = "Password456"

                }

    };

    this.datasource = "DB1";  

}

</cfscript>