Preamble

I love ColdFusion. I’ve been developing websites, applications, and software using ColdFusion for over 20 years. I’ve struggled with the reputation ColdFusion has, and I’ve watched the customer and developer base dwindle. I believe that bringing new developers into the fold starts with the basics. We all started programming somewhere, and I believe there is a dearth of “basic training” available for ColdFusion newcomers. This series is intended to help with that and provide a resource to developers who are just getting started with ColdFusion.

Assumptions

Articles written in this series will assume a few things:

• You have little to no knowledge or experience building web based applications.
• You are using a modern version of ColdFusion. As of the time of this writing, I will assume that new developers and new development will be utilizing ColdFusion 2021 or ColdFusion 2018.
• As such, please understand that there may be some limitations in older versions. Consult documentation for the version of ColdFusion you are coding against for compatibility.

Querying data from a database in CFML

One of the most common tasks a developer needs to perform is to query data from a back-end database. Whether this information is related to customer records, or simply a list of products available, hitting a database and retrieving information is a basic task that can be handled easily with CFML.

Really, really, really basic relational databases

Going with the assumption that this series is for new developers, I’m not going to assume that you have any idea of what a database system is or how it works.

Relational databases store data in tables. Those tables act like a spreadsheet. There’s columns that contain different types of data and rows that represent individual records.

Let’s assume you’ve got a database running on a Microsoft SQL Server. Assume there’s a table in that database called “companies” and that the companies table contains fields for company_id, company, and active.

• company_id is an integer that is unique to the company.
• company is a string that contains the name of the company.
• active is a boolean (true/false) field that determines if a company account is active in the system. Let’s assume that we intend to create business logic that states that inactive companies prevent all of their users from being able to sign in.

Let’s also assume there’s another table in this database called “users” and that users table contains fields for user_id, company_id, first_name, last_name, email_address, password, and active.

• user_id is an integer that is unique to the user.
• company_id is an integer that relates to the company_id of a company record in the companies table. This cross relation of tables is what makes the database relational.
• first_name is a string that contains the users first (given) name.
• last_name is a string that contains the users last (family) name.
• email_address is a string that contains the users email address.
• password is a string that contains the users password. Throwing security considerations out the window, the passwords are stored in the clear in the users table.*
• active is a boolean (true/false) field that determines if a user account is active in the system. Inactive users are not allowed to sign in.

* Don’t ever store passwords in the clear. Ever. If you see passwords stored in plain text in any database, close the table, stand up, slowly walk away from the computer, and rethink your entire life.

Really, really, really basic SQL

SQL is a complex and powerful language for interacting with records in a database. Volumes have been written about its use and most of it goes beyond the scope of this article, but in order for you to have some simple information, I’m going to discuss one type of statement; the SELECT statement.

The SELECT statement does just what you’d expect. It selects information from a table in the database. The basic syntax of a SQL SELECT statement is “SELECT (list of fields) FROM (table[s]) WHERE (conditions are met)” For example, let’s say we were trying to authenticate a user against the users table I described above and return not only the user, but the company for which they work. We want to make sure that:

• A user record exists for the email address provided,
• The password provided matches the password in the database,
• The user record active field is set to true,
• And the company record active field is set to true.

The syntax for a SQL statement like this might be:

	SELECT
		users.first_name, users.last_name, companies.company
	FROM
		users
		INNER JOIN companies on users.company_id = companies.company_id
	WHERE
		users.email = 'someone@company.com'
		AND users.password = 'secret_password'
		AND users.active = 1
		AND companies.active = 1

When you see a SQL statement like this, it’s mostly human readable… look carefully and you’ll see we are telling the database to select the first, last and company names from the users table, and we’re joining that table with the companies table on the common field, and there’s four criteria that need to be met. It’s actually quite straightforward.

Let’s assume you have the basic SQL Server database established with some tables and some records in each one. You have your SQL statement that you can use to retrieve the data you need.

Setting up a Datasource

From a ColdFusion standpoint, the first step in querying a database involves setting up a datasource. A datasource is a setting in the ColdFusion Server Administrator that establishes a relationship between the ColdFusion server and the database server. Think of it like adding the contact information of the database into ColdFusion’s address book. Once the two servers know how to talk to each other, they can communicate freely.

A data source is required to query information from the database even if the ColdFusion Server and database server are running on the same machine. For the sake of simplicity, I’m going to assume that this is the case in our scenario. I’m going to assume that the preferred method of connecting to the database is by using the port number 1433, which is the default SQL Server TCP/IP communication port.

Depending on the SQL engine you are using, there may be additional steps to perform in order to ensure the database is accessible to ColdFusion. In SQL Server, you will need to have the TCP/IP Protocol enabled in the SQL Server Configuration Manager.

Methods of Querying Data

The <cfquery> tag

The most basic means of executing a SQL query against a database is to use the tag. The tag has many attributes, but for the sake of keeping this article simple and easy to digest, I’m going to focus on two: name and datasource. (A full list of attributes available to the tag is available at https://cfdocs.org/cfquery)

Consider the following code example:

<cfquery name="authenticate" datasource="my_datasource">
	SELECT
		users.first_name, users.last_name, companies.company
	FROM
		users
		INNER JOIN companies on users.company_id = companies.company_id
	WHERE
		users.email = '#email#'
		AND users.password = '#password#'
		AND users.active = 1
		AND companies.active = 1
</cfquery>

Fundamentally, this will work, and it will retrieve data from the database. You are running a SQL statement to retrieve the user and company where the email and password match the record in the database.

Realistically, this is not the best way to code though for one primary reason: security. Because variables are being submitted to the server, extra caution should be taken to make sure the data in those variables is secure.

The <cfqueryparam> tag

<cfqueryparam> is a powerful ColdFusion tag. Not only does it help secure your SQL queries by verifying the type of data being passed, it also can speed up your queries and make them more efficient.

Any variable you pass into a query should be wrapped in a <cfqueryparam> tag. Make it a habit, right from the start. Here’s how it works; any ColdFusion variables you have nested within a query should be parameterized. This locks the variable to the expected data type, and can set other limitations, like the maximum length of the value it will accept.

A full list of attributes available to the <cfqueryparam> tag is available at https://cfdocs.org/cfquery

Let’s take another look at our query, this time with <cfqueryparam> tags added.

<cfquery name="authenticate" datasource="my_datasource">
	SELECT
		users.first_name, users.last_name, companies.company
	FROM
		users
		INNER JOIN companies on users.company_id = companies.company_id
	WHERE
		users.email = <cfqueryparam cfsqlype="varchar" value="#email#">
		AND users.password = 
		AND users.active = 1
		AND companies.active = 1
</cfquery>

The queryExecute() function

If you’re familiar with CFML, you’ll know that ColdFusion code can be written in tag based or script based syntax. The <cfscript> based equivalent to the <cfquery> tag is the queryExecute() function. The queryExecute function takes three parameters:

• SQL – the SQL statement that is to be executed.
• Params – a structure that represents the parameters for the query,
• Options – a structure that represents options available to the query, like a results variable, etc.

Consider the following code example:

<cfscript>
	authenticate = queryExecute(
		SQL = "
			SELECT users.first_name, users.last_name, companies.company
			FROM
				Users
				INNER JOIN companies on users.company_id = companies.company_id
			WHERE
				users.email = :email
				AND users.password = assword
				AND users.active = 1
				AND companies.active = 1
		",
		params = {
			email = { cfsqltype = "varchar", value = email },
			password = { cfsqltype = "varchar", value = password }
		},
		options = {}
	);
</cfscript>

Both of these code examples will extract information from the database and store it in a variable called authenticate.

Again, this is a very basic overview of how to query a database using ColdFusion. There’s much more you can do and many ways you can connect to a database.

More ways to learn ColdFusion

For more information on learning ColdFusion, https://www.learncfinaweek.com is an excellent information source that covers many of the basic concepts of ColdFusion development.

Documentation on Adobe ColdFusion can be found at https://helpx.adobe.com/support/coldfusion.html or check out the community supported CF Docs at https://cfdocs.org.

http://trycf.com and http://cffiddle.org are both websites that allow you to execute ColdFusion code without having to run an instance of ColdFusion.

CommandBox from Ortus Solutions is an excellent, free way to spin up and execute ColdFusion code locally.

Happy coding!