Skip to main content
C
c__wright1
Participant
December 20, 2018
Answered

How to create multiple datasources in application.cfc

  • December 20, 2018
  • 2 replies
  • 2719 views

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";
   }

This topic has been closed for replies.
Correct answer BKBK

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";

2 replies

BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
December 20, 2018

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
c__wright1Author
Participant
December 21, 2018

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>

D
Community Expert
Dave WattsCommunity Expert
Community Expert
December 20, 2018

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

Dave Watts, Eidolon LLC
WolfShade
WolfShade
Legend
December 20, 2018

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,

^ _ ^

D
Community Expert
Dave WattsCommunity Expert
Community Expert
December 20, 2018

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

Dave Watts, Eidolon LLC
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices