CF Snippets

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

Configure Query Caching

Use the cachedWithin or cachedAfter query options to cache a query for or after a specified timespan. This query will be cached and reused for up to 6 hours.

var sql = "SELECT * FROM users";
var queryOptions = {
  cachedWithin: createTimespan( 0, 6, 0, 0 )
};
var data = queryExecute( sql, params, queryOptions );

Return query as array

You may find manipulating an array object easier than manipulating a query object. Using the returntype option, you can ask queryExecute() to return an array for functional-style processing on that query data.

var usernames = queryExecute( 
  "SELECT id,name FROM users",
  {},
  { "returntype": "array" }
);
// Now we can use closures on the result
var newUsernames = usernames
  .filter( user => user.name CONTAINS "Born" )
  .each( user => user.name.replace( "Born", "Bourne" );

Pass a named SQL parameter with queryExecute()

Use parameters for all user-submitted data to protect against SQL injection.

var params = {
  username: { value: form.username, sqltype: "varchar" },
  password: { value: form.password, sqltype: "varchar" }
};
var data = queryExecute(
  "SELECT * FROM users WHERE username=:username AND password=:password",
  params
);

Get Generated Primary Key from an INSERT statement

If you use MySQL to generate database record ids for you (e.g. a PRIMARY KEY created via an autonumber field or UUID() call), use the result option to specify a variable name which will contain the result of the query - including a generatedKey field for retrieving the primary key of the new record.

var queryOptions = {
  result: "newUser"
};
var data = queryExecute(
  "INSERT INTO users (username, password)
  VALUES ( 'mike.born', UUID() )",
  {},
  queryOptions
);
writeOutput( "New user id: #newUser.generatedKey#" );

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