Can I specify a dynamic datasource at runtime?

Question

My application is hosted on a central server which serves many customers. It now needs to cross-reference information with the database servers that reside onsite at the customer's location.

I want to store the details of the customer's server alongside their account details (e.g dbname, host, port etc).

However depending on who is logged into the application, I need to supply their data connection details into a <cfquery> function in order to perform lookups. Something like this:

<cfquery name="rsOrders" datasource="{dynamically provided connection string}">
SELECT *
FROM
CompanysDBTable
</cfquery>

I understand that there is an Administrator API which creates a data source programmatically, however using that would mean an extra process in my system and what would happen if the data source details were to be updated by the customer?

So is there any way to do it on the fly like above? That is, supplying the data connection string within the <cfquery> tag.

Or is there a better way of doing this altogether?

Answer 1

You can create application-specific datasources at runtime in ColdFusion 11. See the docs: "Application-specific datasources in Application.cfc". I also discuss this in my blog: "Defining datasources in Application.cfc".

An example would be:

// Application.cfc
component {

    this.name = "DSNTest02";
    this.datasources = {
        scratch_mssql_app    = {
            database    = "scratch",
            host        = "localhost",
            port        = "1433",
            driver      = "MSSQLServer",
            username    = "scratch",
            password    = "scratch"
        },
        scratch_embedded_app    = {
            database    = "C:\apps\adobe\ColdFusion\11\full\cfusion\db\scratch",
            driver        = "Apache Derby Embedded"
        }
    };
    this.datasource    = "scratch_mssql_app";

}

That's the closest you can get with ColdFusion.

If you were to use JDBC directly, you could just give a connection string when creating the connection, but then your code would need to contend with the record sets returned by the JDBC driver, which would not be CFML query objects.