ColdFusion – Using SQL String in CFQUERY with Quotes

Today a coworker was frustrated over a problem that seems like it should work.

He was using a SQL string and using that inside a CFQUERY like so:

<cfset sqlString = “SELECT id, name FROM users WHERE name = ‘John’ ” />

<cfquery name=”myQuery” datasource=”myDsn”>
#sqlString#
</cfquery>

This looks like it should work but it doesn’t. Any single quote will be changed to two single quotes so when the query actually executes, the query looks like this:

SELECT id, name FROM users WHERE name = ”John”

At first glance it looks like John is surrounded by double quotes but it’s not. It’s surrounded by two single quotes.

This obviously throws an error complaining about the double single quotes.

The solution is to replace the double single quotes with a single quote like so:

<cfquery name=”myQuery” datasource=”myDsn”>
#ReReplace(sqlString, “””, “‘”, “all”)#
</cfquery>

It’s hard to see what’s going on so I’ll expand the replace code

  #ReReplace(sqlString, “””, “‘”, “all”)#

Note that the double single quotes are surrounded by double quotes and the single quote is surrounded by double quotes.

We are passing sqlString to the ReReplace function and telling it to find all double single quotes and replacing them with single quotes.

Voila! It works!

Hope this saves someone  from pulling out their hair trying to figure out why it’s not working.

Amazing Date Range Picker

I use Twitter Bootstrap with my project to quickly build visually appealing websites.

When displaying reports I usually use two separate date pickers to define the “from” and “to date ranges.

Today I found out about an amazing date range picker made by Dan Grossman.

Usability wise it’s really awesome. You can customize pre-defined ranges or allow the user to select their own range.

The code is simple. Just like any other date picker:

$('#element').daterangepicker(options, callback);

It also allows you to define a time range along with the date range.

I really recommend that you check it out. You can download it here and check out more usages.

CFWheels – Set NULL Values

Haven’t posted in awhile so I thought I’d post this little tidbit in case people find it useful.

People might try to set fields to NULL in CFWheels by passing in “NULL” to the model but that actually doesn’t work.

The way to do it is to simply set the field to an empty string which will be interpreted as NULL by CFWheels.

This came in handy today when I had to overwrite some fields and set them to NULL in one of my projects.

That’s it! Enjoy!