Database Queries
CFML became famous in its infancy with how easy it was to query databases with a simple cfquery tag, no verbose ceremonious coding. No ceremony, just a plain datasource definition in the administrator and we could query the database with ease.
In modern times, we have many more ways to query the database and defining datasources can occur not only in the admin but in our application's Application.cfc or even define it at runtime or even within the query constructs themselves.
See Application.cfc for more information on how to leverage it for web development.

What is a Datasource?

A datasource is a named connection to a specific database with specified credentials. You can define an infinite amount of datasources in your CFML applications in the following locations:
    Global ColdFusion Engine Administrator
    The Application.cfc, which will dictate the datasources for that specific ColdFusion application
    Inline in cfquery or queryexecute calls
The datasource is then used to control the connection pool to such database and allow for the ColdFusion engine to execute JDBC calls against it.

What is a query?

A query is a request to a database. It returns a CFML query object containing a recordset and other metadata information about the query. The query can ask for information from the database, write new data to the database, update existing information in the database, or delete records from the database. This can be done in several ways:
1
// Tag syntax
2
<cfquery name = "qItems" datasource="pantry">
3
SELECT QUANTITY, ITEM
4
FROM CUPBOARD
5
ORDER BY ITEM
6
</cfquery>
7
8
// script syntax
9
10
qItems = queryExecute(
11
"SELECT QUANTITY, ITEM FROM CUPBOARD ORDER BY ITEM"
12
);
13
14
// Lucee datasource inline definition
15
queryExecute(
16
"SELECT * FROM Employees WHERE empid = ? AND country = ?", // sql
17
[ 1, "USA" ], // params
18
{ // options
19
datasource : {
20
class : "com.microsoft.sqlserver.jdbc.SQLServerDriver",
21
connectionString : "jdbc:sqlserver://#getSystemSetting("DB_CONNECTIONSTRING")#",
22
username : getSystemSetting("DB_USER"),
23
password : getSystemSetting("DB_PASSWORD")
24
}
25
}
26
)
Copied!
If you are using Lucee, the datasource can even be defined inline. So instead of giving the name of the datasource it can be a struct definition of the datasource you want to connect to.

Default Datasource

You can also omit the datasource completely from query calls and CFML will use the one defined in Application.cfc as the default datasource connection. This is a great way to encapsulate the datasource in a single location. However, we all know that there could be some applications with multiple datasources, that's ok, at least you can have one by default.
Application.cfc
1
component{
2
this.name = "myApp";
3
4
// Default Datasource Name
5
this.datasource = "pantry";
6
7
}
Copied!

Defining Datasources

If you want to use the ColdFusion Engine's administrators for registering datasources, you will have to visit each of the administrator's interfaces and follow their wizards.

ColdFusion Engine Administrator

Datasources :: Lucee Documentation
lucee_server
Data Source Management for ColdFusion

Application.cfc

You can also define the datasources in the Application.cfc, which is sometimes our preferred approach as the connections are versioned controlled and more visible than in the admin. You will do this by defining a struct called this.datasources. Each key will be the name of the datasource to register and the value of each key a struct of configuration information for the datasource. However, we recommend that you setup environment variables in order to NOT store your passwords in plain-text in your source code.
Application.cfc
1
component{
2
this.datasources = {
3
// Adobe Driver Approach
4
mysql = {
5
database : "mysql",
6
host : "localhost",
7
port : "3306",
8
driver : "MySQL",
9
username : "root",
10
password : "mysql",
11
options : value
12
},
13
// Adobe url approach
14
mysql2 = {
15
driver : "mysql",
16
url : "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true",
17
username : "",
18
password : ""
19
},
20
// Shorthand Lucee Approach
21
myLuceeDNS = {
22
class : "com.mysql.jdbc.Driver",
23
connectionString : "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true",
24
username : "",
25
password : ""
26
},
27
// Long Lucee Approach
28
myLuceeDNS = {
29
type : "mysql",
30
database : "mysql",
31
host : "localhost",
32
port : "3306",
33
username : "",
34
password : ""
35
}
36
};
37
}
Copied!
For the inline approach, you will just use the struct definition as you see in the Application.cfc above and pass it into the cfquery or queryexecute call.

Portable Datasources

You can also make your datasources portable from application to application or CFML engine to engine by using our CFConfig project. CFConfig gives you the ability to manage most every setting that shows up in the web administrator, but instead of logging into a web interface, you can mange it from the command line by hand or as part of a scripted server setup. You can seamless transfer config for all the following:
    CF Mappings
    Datasources
    Mail servers
    Request, session, or application timeouts
    Licensing information (for Adobe)
    Passwords
    -Template caching settings
    -Basically any settings in the web based administrator
You can easily place a .cfconfig.json in the web root of your project and if you start up a CommandBox server on any CFML engine, CFConfig will transfer the configuration to the engine's innards:
.cfconfig.json
1
{
2
"requestTimeoutEnabled":true,
3
"whitespaceManagement":"white-space-pref",
4
"requestTimeout":"0,0,5,0",
5
"cacheDefaultObject":"coldbox",
6
"caches":{
7
"coldbox":{
8
"storage":"true",
9
"type":"RAM",
10
"custom":{
11
"timeToIdleSeconds":"1800",
12
"timeToLiveSeconds":"3600"
13
},
14
"class":"lucee.runtime.cache.ram.RamCache",
15
"readOnly":"false"
16
}
17
},
18
"datasources" : {
19
"coldbox":{
20
"host":"${DB_HOST}",
21
"dbdriver":"${DB_DRIVER}",
22
"database":"${DB_DATABASE}",
23
"dsn":"jdbc:mysql://{host}:{port}/{database}",
24
"custom":"useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true&autoReconnect=true",
25
"port":"${DB_PORT}",
26
"class":"${DB_CLASS}",
27
"username":"${DB_USER}",
28
"password":"${DB_PASSWORD}",
29
"connectionLimit":"100",
30
"connectionTimeout":"1"
31
}
32
}
33
}
Copied!

Displaying Results

The query object can be iterated on like a normal collection through a for, cfloop or cfoutput construct.
In a Template
1
<cfoutput query = "qItems">
2
There are #qItems.Quantity# #qItems.Item# in the pantry<br />
3
</cfoutput>
Copied!
Using Loops
1
for( var row in qItems ){
2
systemOutput( "There are #row.quantity# #row.item# in the pantry" );
3
}
4
5
qItems.each( function( row, index ){
6
systemOutput( "There are #row.quantity# #row.item# in the pantry" );
7
8
} );
9
10
for( var i = 1; i lte qItems.recordCount; i++ ){
11
systemOutput( "There are #qItems.quantity[ i ]# #qItems.item[ i ]# in the pantry" );
12
}
Copied!
As you can see, there are many ways to iterate over the query. Choose the approach that suits your needs.

Multi-Threaded Looping

As of now only Lucee allows you to leverage the each() operations in a multi-threaded fashion. The queryEach() or each() functions allows for a parallel and maxThreads arguments so the iteration can happen concurrently on as many maxThreads as supported by your JVM.
1
queryEach( array, callback, parallel:boolean, maxThreads:numeric );
2
each( collection, callback, parallel:boolean, maxThreads:numeric );
Copied!
This is incredibly awesome as now you callback will be called concurrently! However, please note that once you enter concurrency land, you should shiver and tremble. Thread concurrency will be of the utmost importance and you must make sure that var scoping is done correctly and that appropriate locking strategies are in place.
1
myquery.each( function( row ){
2
myservice.process( row );
3
}, true, 20 );
Copied!

Using Input

Most of the time we won't have the luxury of simple queries, we will need user input in order to construct our queries. Here is where you need to be extra careful as to not allow for SQL injection. CFML has several ways to help you prevent SQL Injection whether using tags or script calls. Leverage the cfqueryparam construct/tag (https://cfdocs.org/cfqueryparam) and always sanitize your input via the encode functions in CFML.
1
// Named variable holder
2
queryExecute(
3
"select quantity, item from cupboard where item_id = :itemID"
4
{ itemID = { value=arguments.itemID, cfsqltype="numeric" } }
5
);
6
7
// Positional placeholder
8
queryExecute(
9
"select quantity, item from cupboard where item_id = ?"
10
[ { value=arguments.itemID, cfsqltype="varchar" } ]
11
);
Copied!
You can use the :varname notation in your SQL construct to denote a variable place holder or a ? to denote a positional placeholder. The cfqueryparam tag or the inline cfsqltype construct will bind the value to a specific database type in order to avoid SQL injection and to further the database explain plan via types. The available SQL binding types are:
    bigint
    bit
    char
    blob
    clob
    nclob
    date
    decimal
    double
    float
    idstamp
    integer
    longvarchar
    longnvarchar
    money
    money4
    nchar
    nvarchar
    numeric
    real
    refcursor
    smallint
    sqlxml
    time
    timestamp
    tinyint
    varchar
Please note that the types can be prefixed with cf_sql_{type} or just used as {type}.

Query Methods

There are also several query methods available in CFML that can help you manage queries but also create them on the fly (https://cfdocs.org/query-functions). Please note that you can also use chaining and member functions as well.
    queryNew()
    queryAddRow()
    queryAddColumn()
    queryColumnArray()
    queryColumnCount()
    queryColumnData()
    queryColumnExists()
    queryColumnList()
    queryCurrentRow()
    queryDeleteColumn()
    queryDeleteRow()
    queryEach()
    queryEvery()
    queryFilter()
    queryGetCell()
    queryGetResult()
    queryGetRow()
    queryMap()
    queryRecordCount()
    queryReduce()
    queryRowData()
    querySetCell()
    querySlice()
    querySome()
    querySort()
    quotedValueList()
    valueList()

Building Queries

You can use a combination of the methods above to create your own queries
1
news = queryNew("id,title", "integer,varchar");
2
queryAddRow(news);
3
querySetCell(news, "id", "1");
4
querySetCell(news, "title", "Dewey defeats Truman");
5
queryAddRow(news);
6
querySetCell(news, "id", "2");
7
querySetCell(news, "title", "Men walk on Moon");
8
writeDump(news);
9
10
11
users = queryNew( "firstname", "varchar", [{"firstname":"Han"}] );
12
subUsers = queryExecute( "select * from users", {}, { dbtype="query" } );
13
writedump( subUsers );
14
15
news = queryNew("id,title",
16
"integer,varchar",
17
[ {"id":1,"title":"Dewey defeats Truman"}, {"id":2,"title":"Man walks on Moon"} ]);
18
writeDump(news);
19
20
news = queryNew("id,title",
21
"integer,varchar",
22
{"id":1,"title":"Dewey defeats Truman"});
23
writeDump(news);
Copied!

Query of Queries

Query a local database variable without going through your database is another great way to query an already queried query. Too many queries?
1
users = queryNew( "firstname", "varchar", [{"firstname":"Han"}] );
2
subUsers = queryExecute( "select * from users", {}, { dbtype="query" } );
3
writedump( subUsers );
Copied!
Please note that using query of queries can be quite slow. An alternative approach is to use the modern queryFilter() operations to actually filter out the necessary data from a query, or querySort(), etc.

Returning Arrays of Structs or Struct of Structs

In the Lucee CFML engine (coming soon to Adobe), you can also determine the return type of database queries to be something other than the CFML query object. You can choose array of structs or struct of structs. This is fantastic for modern applications that rely on rich JavaScript frameworks and producing JSON.
This is achieved by passing the returntype attribute within the query options or just an attribute of the cfquery tag (https://cfdocs.org/cfquery)
1
users = queryNew( "firstname", "varchar", [{"firstname":"Han"}] );
2
subUsers = queryExecute( "select * from users", {}, { dbtype="query", returntype="array" } );
3
writedump( subUsers );
4
5
users = queryNew( "id, firstname", "integer, varchar", [{"id":1, "firstname":"Han"}] );
6
subUsers = queryExecute( "select * from users", {}, { dbtype="query", returntype="struct", columnkey="id" } );
7
writedump( subUsers );
Copied!

QB = Query Builder

We have created a fantastic module to deal with queries in a fluent and elegant manner. We call it QB short for query builder (https://www.forgebox.io/view/qb). You can install it using CommandBox into your application by just saying:
1
box install qb
Copied!
Using qb, you can:
    Quickly scaffold simple queries
    Make complex, out-of-order queries possible
    Abstract away differences between database engines
1
// qb
2
query = wirebox.getInstance('[email protected]');
3
q = query.from( 'posts' )
4
.whereNotNull( 'published_at' )
5
.whereIn( 'author_id', [5, 10, 27] )
6
.get();
Copied!
You can find all the documentation in our Ortus Books docs: http://qb.ortusbooks.com/
Last modified 1yr ago