Highlighted

How to create multiple datasources in application.cfc

New Here ,
Dec 20, 2018

Copy link to clipboard

Copied

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

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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

Views

607

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

How to create multiple datasources in application.cfc

New Here ,
Dec 20, 2018

Copy link to clipboard

Copied

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

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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

Views

608

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Dec 20, 2018 0
Adobe Community Professional ,
Dec 20, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 20, 2018 1
LEGEND ,
Dec 20, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 20, 2018 0
Adobe Community Professional ,
Dec 20, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 20, 2018 0
LEGEND ,
Dec 20, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 20, 2018 0
Adobe Community Professional ,
Dec 20, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 20, 2018 0
New Here ,
Dec 20, 2018

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 20, 2018 0
Adobe Community Professional ,
Dec 20, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 20, 2018 0
New Here ,
Dec 21, 2018

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 21, 2018 0