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
);
Get New Snippets In Your Inbox
No spam, not too many emails.