CF Snippets

Datasource Configuration

CFML is really good at hooking up to a database. These datasource snippets should get you rolling with most CF-supported database platforms, from H2 to Postgres to Microsoft SQL Server.

Select query using named datasource

If you do not have a default datasource configured, you'll need to use this variant to specify which datasource to use.
var queryOptions = { datasource: "appMain" };
var data = queryExecute(
  "SELECT * FROM users", {}, queryOptions
);

Using Environment Variables in Datasource Configuration

Use environment variables for datasource configuration using the server.system.environment struct of environment variables.
this.datasources["myApp"] = {
  class: "com.microsoft.sqlserver.jdbc.SQLServerDriver"
  , bundleName: "mssqljdbc4"
  , bundleVersion: "4.0.2206.100"
  , connectionString: "jdbc:sqlserver://localhost:1433;DATABASENAME=#server.system.environment.DB_NAME#;sendStringParametersAsUnicode=true;SelectMethod=direct"
  , username: server.system.environment.DB_USER
  , password: server.system.environment.DB_PASS
};

Microsoft SQL Server Datasource Configuration

Connect to an MSSQL database from CFML by placing this datasource struct configuration in your Application.cfc.
this.datasources["myApp"] = {
	class: "com.microsoft.sqlserver.jdbc.SQLServerDriver"
, bundleName: "mssqljdbc4"
, bundleVersion: "4.0.2206.100"
, connectionString: "jdbc:sqlserver://localhost:1433;DATABASENAME=app;sendStringParametersAsUnicode=true;SelectMethod=direct"
, username: "admin"
, password: "myPassword"
};

Set the Default Datasource in Application.cfc

Sometimes it's a little nicer not needing to specify the datasource in your DB queries. When you have an application which mainly queries a single database, go ahead and set the default datasource - it's simple and saves you typing.
// in Application.cfc...
// datasource configuration
this.datasources["myDB"] = {
  // datasource config here...
};
this.datasource = "myDB";

//  you can now omit datasource settings from queryExecute() calls and  tags.
var rows = queryExecute( "SELECT * FROM pages" );

HSQLdb Datasource Configuration

Connect to an HSQL flat-file database from CFML by placing this datasource struct configuration in your Application.cfc.
this.datasources["hsqldb"] = {
  class: "org.hsqldb.jdbcDriver"
  , connectionString: "jdbc:hsqldb:file:#expandPath( '/PATH/TO/FILE' )#"
};

H2 Datasource Configuration

Connect to an H2 flat-file database from CFML by placing this datasource struct configuration in your Application.cfc.
this.datasources["h2"] = {
  class: "org.h2.Driver"
  , bundleName: "org.h2"
  , connectionString: "jdbc:h2:#expandPath( '/db/h2db' )#;MODE=MySQL;"
};

Get New Snippets In Your Inbox

No spam, not too many emails.