Skip to main content
Ben Nadel at cf.Objective() 2017 (Washington, D.C.) with: Ryan Nibert
Ben Nadel at cf.Objective() 2017 (Washington, D.C.) with: Ryan Nibert

Setting And Clearing Nullable Values In A Data Access Layer In ColdFusion

By
Published in , Comments (2)

As much as possible, I try to avoid NULL values in my database schema design. But, sometimes, NULL is actually helpful in reducing schema complexity. Unfortunately, ColdFusion only has partial support for null values (by default); which makes it a bit tricky to pass a "required-but-null arguments" into a data access layer (DAL) method. To play nicely with both ColdFusion and SQL, I've been leaning on "magic values" when interacting with the my data gateways.

ASIDE: You can enable "full null support" in ColdFusion as an application-wide settings; but, I've never done it before. I'm not sure what kind of can-of-worms that might open.

Consider this simple table design for a Task list item:

CREATE TABLE `task` (
	`id` int unsigned NOT NULL AUTO_INCREMENT,
	`description` varchar(255) NOT NULL,
	`isComplete` tinyint unsigned NOT NULL,
	`createdAt` datetime NOT NULL,
	`completedAt` datetime DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Note that the completedAt column is nullable. Which makes sense because if a task has not yet been completed then there is no date that would make sense in this column. Using a NULL value here probably makes sense in two cases:

  • When first creating the task, it's unlikely (though not impossible) that a task would be created with an already defined done-date.

  • When a user marks a task as complete; but then realizes that this is not actually true based on new evidence and needs to clear the existing column value.

When creating a new task record, I likely want all columns to be provided as part of the operation (personal preference). However, when updating an existing task record, I may not want all columns to be changed. As such, especially in this latter case, we need a way to differentiate "not provided" from "null", which ColdFusion traditionally treats as the same thing.

To keep this simple, I'm going to define a magic date on my Gateway component which is not null but which means null:

this.NULL_DATE = createDate( 100, 1, 1 );

Then, I can pass this value as an argument whenever I need to invoke my data access layer (DAL) with a NULL value:

<cfscript>

	taskGateway = new TaskGateway();

	// Create a new task as a pending (ie, not yet completed) task.
	taskID = taskGateway.createTask(
		description = "Clean up Xmas tree",
		isComplete = false,
		createdAt = now(),
		completedAt = taskGateway.NULL_DATE
	);

	// MARK AN EXISTING TASK AS COMPLETE - that's easy, we just pass-in the date at which
	// we want the task to be known as completed.
	taskGateway.updateTask(
		id = taskID,
		isComplete = true,
		completedAt = now()
	);

	// MARK AN EXISTING TASK AS INCOMPLETE! That's where things get tricky - how do we
	// differentiate between OMITTING the "completedAt" argument (ie, we don't want to
	// update it) vs. clearing the "completedAt" field (ie, setting it to NULL). The
	// "magic date" makes this simple!
	taskGateway.updateTask(
		id = taskID,
		isComplete = false,
		completedAt = taskGateway.NULL_DATE
	);

</cfscript>

As you can see, by using a "magic value", I'm able to include null values for completedAt in my method invocation. The beautiful thing about this is that we can now mark the completedAt argument as required when it has to be passed-in.

Here's my truncated data access component - note that I'm including the CFQueryParam tag wherever the arguments is required; but, I'm setting up a null attribute check which invokes a private method, isNullCompletedAt():

component
	output = false
	hint = "I provide data-access methods for the task model."
	{

	// This date is provided in order to make it easier to pass-in and handle a "NULL"
	// date for "completedAt". Instead of dealing with confusing optional arguments, we
	// can use a "magic date" to specify null. This allows us to avoid confusing optional
	// arguments and keep our SQL query logic simple.
	this.NULL_DATE = createDate( 100, 1, 1 );

	// ---
	// PUBLIC METHODS.
	// ---

	/**
	* I create a new task and return the generated ID.
	*/
	public numeric function createTask(
		required string description,
		required boolean isComplete,
		required date createdAt,
		required date completedAt
		) {

		```
		<cfquery name="local.results" result="local.metaResults">
			/* DEBUG: taskGateway.createTask(). */
			INSERT INTO
				task
			SET
				description = <cfqueryparam value="#description#" sqltype="varchar" />,
				isComplete = <cfqueryparam value="#isComplete#" sqltype="tinyint" />,
				createdAt = <cfqueryparam value="#createdAt#" sqltype="timestamp" />,
				completedAt = <cfqueryparam value="#completedAt#" sqltype="timestamp"
					null="#isNullCompletedAt( completedAt )#"
				/>
			;
		</cfquery>
		```

		return( val( metaResults.generatedKey ) );

	}

	// ... truncated for demo ...

	/**
	* I update the task with the given ID.
	*/
	public void function updateTask(
		required numeric id,
		string description,
		boolean isComplete,
		date completedAt
		) {

		```
		<cfquery name="local.results" result="local.metaResults">
			/* DEBUG: taskGateway.updateTask(). */
			UPDATE
				task t
			SET
				<cfif arguments.keyExists( "description" )>
					t.description = <cfqueryparam value="#description#" sqltype="varchar" />,
				</cfif>

				<cfif arguments.keyExists( "isComplete" )>
					t.isComplete = <cfqueryparam value="#isComplete#" sqltype="tinyint" />,
				</cfif>

				<cfif arguments.keyExists( "completedAt" )>
					t.completedAt = <cfqueryparam value="#completedAt#" sqltype="timestamp"
						null="#isNullCompletedAt( completedAt )#"
					/>,
				</cfif>

				t.id = t.id
			WHERE
				t.id = <cfqueryparam value="#id#" sqltype="bigint" />
			;
		</cfquery>
		```

	}

	// ---
	// PRIVATE METHODS.
	// ---

	/**
	* I determine if the given completedAt value is the "magic" NULL_DATE value.
	*/
	private boolean function isNullCompletedAt( required date value ) {

		return( value == this.NULL_DATE );

	}

}

I look at this and it feels clean. No special arguments being passed-in, such as clearCompletedAtField; no conditional inclusion of the CFQueryParam tags. Just simple, consistent SQL statements that lightly paper-over ColdFusion's handling of null values.

Now, in Clean Code: A Handbook Of Agile Software Craftsmanship, Robert C. Martin (Uncle Bob) cautions against the use of "magic values". And, for the most part, I agree with him. In this case, however, I do not believe it to be a problem because:

  1. The real value (ie, NULL) is what is ultimately stored in the database.

  2. The real value is what is returned from the database - ColdFusion handles NULL coming out of the database just fine.

  3. The "magic value" is encapsulated behind a semantically-named variable (NULL_DATE).

  4. The "magic value" is only ever going to be used by the one service that is responsible for creating new task records - nothing else in the application should have to know about this behavior.

Because of these conditions, I don't see this approach has having much of a downside. And, I like that it keeps my ColdFusion data access layer simple and easy to read.

Want to use code from this post? Check out the license.

Reader Comments

15,883 Comments

I added some line-breaks to put the:

null="#isNullCompletedAt( completedAt )#"

... on it's own line. It was really going off the side of the screen making it hard to see the whole point of the post 😜 That's one downside of using the null attribute - it makes the line of code a bit longer.

15,883 Comments

After writing this post, it go me thinking about the combination of isComplete and completedAt columns in my task table. In theory, you could use the completedAt column to store both the date the task was completed and the fact that it is completed. But, I think that overloads the meaning of the column in a way that I wasn't comfortable with. Put some thoughts down:

www.bennadel.com/blog/4376-considering-nullable-date-columns-as-a-representation-of-state-in-sql.htm

Your mileage may vary; but, I think having two columns has its advantages.

Post A Comment — I'd Love To Hear From You!

Post a Comment

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel