Sunday, 4 May 2014

Defining datasources in Application.cfc

G'day:
This feature of both Railo and now ColdFusion hasn't been talked about as much as I think it should have been. Both CFML platforms - as of 4.1 in the case of Railo, and 11 in the case of ColdFusion - allow one to define entire datasources in Application.cfc. Not simple reference to existing DSNs created in CFAdmin (or via the Admin API, if you've ever tried that), but defining the whole thing right there in Application.cfc.

From the ColdFusion perspective, this isn't really a "new" feature; it's the restoration of an old feature that went AWOL in CFMX6.0. CF5 used to support this sort of dynamic DSN definition on the <cfquery> tag, but that was dropped from CFMX6, and has only just been restored now. It seems "backwards compatibility" wasn't always so important in CF, eh?

But anyway, back in the 21st Century...

ColdFusion 11

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

}

Here I'm defining two DSNs: one to my scratch SQL Server instance, and one to an Apache Derby DB. Also note that having defined a DSN, I can then use it as my default DSN too.

One thing to note here is that I need to have my password in clear text. From my perspective, this invalidates this functionality for a production environment as I would never write down one of my passwords in clear text, just on principle. You'll see when we look at the Railo approach to this, they've thought of this. I suppose in a Windows environment one could use Windows authentication between the CF and DB server, which would remove the need for the user and password completely? I've not tried that. I also dunno if that sort of approach is an option on non-Windows, and non-SQL Server.

Having defined the DSNs, I can just use them as per normal:

<cfset colours = queryExecute("SELECT * FROM colours ORDER BY id")>
<cfset numbers = queryExecute("SELECT * FROM numbers ORDER BY id", [], {datasource="scratch_embedded_app"})>
<cfdump var="#[colours,numbers]#">

Railo

Railo added this functionality in for 4.1 (Dec 2013). It works much the same, but there's a coupla differences:

this.datasources = {
    scratch_mssql_app    = {
        database    = "scratch",
        host        = "localhost",
        port        = "1433",
        type        = "MSSQL",
        username    = "scratch",
        password    = "encrypted:3c5501c80c3f07ac0d0f8b745a28c439c7ddb1b2de5bf701"
    }
};

Firstly, Railo expects a type, not a driver as per ColdFusion. Secondly - and more importantly - Railo does not require to expose the password in clear text.

Railo also makes it super easy to work out what needs configuring for the DSN. Simply go into the admin, and create the DSN, and then re-edit it, and Railo shows you the code to configure it in Application.cfc instead (having copied it, one can then remove the DSN from the Admin):


(They need to work on their CSS a bit ;-)

Notice how one can just set the DSN up as URL instead of individual settings too. I opted to use individual settings to keep it similar to the ColdFusion example code.

That's about all there is to say on this. I think I'll raise an enhancement request to get CF to have the code-template functionality as per above, as well as the encrypted password option (3754573). It's a shame this functionality was not available in the public beta as I'd've been able to make the suggestion before ColdFusion 11 Beta got release.

--
Adam

at