Checking for constraints in SQL Server

From time to time, you may find that you need to test for the existence of a SQL Server table constraint before you perform some action, such as dropping or altering a constraint or table. Below is a snippet I have found useful.

BTW, I got the idea from this snippet and based it, on this StackOverflow article:


Official ColdFusion Community Site

A month or two ago Adobe launched a ColdFusion Community site. I point this out, because it seems to me like there was little in the way of announcing it.

The web address is: and it appears to be done in the ColdFusion based open source CMS, Mura.

It's official! Adobe has announced a ColdFusion Summit 2013.

Per the official ColdFusion blog (

"We recently announced that Adobe will be holding our first ever ColdFusion Summit conference!

Event Details:

October 24th and 25th

Manadalay Bay Resort & Casino, Las Vegas, Nevada

Registration cost: $250

Each paid registration will receive a FREE copy of ColdFusion Builder (MSRP value of $299!)"

Oracle, CFQueryParam and Decimal Places

Though I have worked with ColdFusion for about 14 years now, I only recently encountered an issue where a ColdFusion query did not match correctly on a value with 3 decimal places. Moreover, the issue originated in our QA sandbox but not in the development sandbox.

So there were a few things going on here. First, my development sandbox was using the Oracle JDBC thin client, while the QA environment was using the included Oracle "thick" client. Second, the query worked perfectly in my sandbox and in Oracle SQL Developer.

In the query itself I was using a CFQUERYPARAM, as everyone should. (You are using CFQUERYPARAM, all the time, right?) I had the CFSQLTYPE set to "CF_SQL_NUMERIC." I tried switching to "CF_SQL_DECIMAL" but still no joy. Then it dawned on me, I had not specified a scale; that is how many decimal places this field has, and guess what? The default scale is zero, and thus the built in Oracle Client was truncating the decimal places!

This field has at most three decimal places, and the fix was to simply add the SCALE attribute of 3 to the CFQUERYPARAM for the field. Now my query is all happy, happy, joy, joy on both boxes.

CF Quick Tip: Lower case structure keys

Recently I needed to return a JSON representation of a ColdFusion structure, but I needed one of the Key's to be all lower case. I knew that CF natively uppercase's Key names, but figured there had to be a way to change this.

It turns out that you can make the key value all lower case, or even mixed case, the trick is to enter it like a named array element. Here's an example:

Example Dump:

Heck, try it for yourself, I have whipped up a simple example page: Lowercase Structure Keys Example

Checking available free space from ColdFusion

Earlier today I noticed on Ray Camden's site a blog post about determining the amount of free space on a drive from ColdFusion. It looks like in CF10, a new function, getFreeSpace('path') was added that will return the amount of free space in MB back. Here's the article:

For those of us not yet on ColdFusion 10, the following article outlines how you can drill down to the Java File class to get the free space.

In particular, pay attention to the second part, because with the .init() outlined, you can pass in an exact path. This can be handy with AWS, local file shares, etc.

So if you aren't on CF10 yet, you could put together your own custom CFC, to return the amount of free space, something as simple as:

With the function above, you can run a simple test case such as...

Your dump should look something like this:

ColdFusion 10 has been released

Adobe has just release ColdFusion 10. Looks like the price tag has increased slightly, but I think it is still a great deal.

I have played with the ColdFusion 10 beta for a few months and really like a lot of the improvements. Perhaps the biggest under-the-hood change is that it now runs atop of Tomcat instead of JRun. There is a whole host of new and improved features including the new hotfix installer that is built into the administrator tool.

Check it out:

ColdFusion debugging inside Eclipse

It was recently brought to my attention that with ColdFuson 8 (and above) that Adobe has provided some tools to help developers run real-time debugging in Eclipse. I have played around with it a little bit at work, and I have to say this has been sorely missed. It was available back in versions 4 and 5 in the Homesite+ tool, but was never ported over to Dreamweaver after Macromedia bought Allaire.

One of my favorite things with this, is the ability to dive into the various variables scopes and see what the current values are. This allows you to set breakpoints so you can easily see what is happening, without having to add in CFDUMPs or CFABORTs. Plus it can be used remotely, so you could have a remote test instance with this enabled that would allow you to remotely debug.

Here are some really good resources to get you started with this:

Bound CFDIV content caching

Recently I had a CFDIV that was bound to a SELECT box with the intent of showing more details about the selected item. The CFDIV was bound to a CFC method which looked up results from the database and returned the results as an unordered list. But as I was working with this, I ran into instances where the data was not changing as expected. I quickly realized I had a caching issue.

I had heard about a similar issue with CFSELECT, and that a solution seemed to be to use

but that didn't seem to have an effect on the CFDIV.

I believe that the real problem is in Firefox 7.0.1 and it's internal caching. In fact, I did notice that with the Web Developer Toolbar, if I told it to "disable cache" that I got the expected results. Which is good for me, but I cannot expect the end-users to disable their browser cache, and they shouldn't have to do so. So I came up with a simple method of attaching a timestamp for each call.

First I created a function to capture a change event on the SELECT box, when called it sets the event's timestamp to a form object. See the JavaScript below:

function setTimeStamp(strObj, event) {
if(event.timeStamp) {
var ts = event.timeStamp;
} else {
var ts = new Date().getTime();

if(document.getElementById(strObj)) {
document.getElementById(strObj).value = ts;
} else if(document.getElementsByName(strObj)) {
document.getElementsByName(strObj).value = ts;

Next, I added that function to the onChange event of my SELECT box, then I added a hidden field and populated it with the current timestamp.

<select id="category" onChange="setTimeStamp('my_timestamp', event)">

<input type="hidden" id="my_timestamp" value="#dateFormat(now(),'yyyymmdd')#.#timeformat(now(),'HHmmss')#" />

To finish it up, I altered my CFC method to expect a time stamp parameter, and I altered my CFDIV bind so that it was now bound on the SELECT box and the hidden timestamp.

<cfdiv id="mySubCategories" bind="cfc:lib.categoryMgr.getSubCategories({category},{my_timestamp})" bindOnLoad="true">

So now every time my select box value changes, the field my_timestamp gets updated to the millisecond, and the built in AJAX methods fire the request, and Firefox does not cache the result.

CF Quick Tip: Destroying Variables

You can effectively destroy a variable by setting it to an empty string such as...

<cfset arrMyBooks = "" /> <!--- Set the array of Books to an empty string --->

So why would you want to do this? You do it to free up memory when a variable/object is no longer needed, but you still have a bit of processing left to do on your page. Why? Because it is better to pro-actively release resources when not needed, than to not do so and run the risk of pushing memory usage past 90% of the allocated heap. And as you may know, when you push past that 90% memory utilization is where you start to see issues, especially if the JVM garbage collector is not keeping up.

Rumored: Adobe to release info about the next version of ColdFusion at RIACon

According to both Ben Forta and Raymond Camden, it looks like Adobe is set to release some details of a new version of ColdFusion at this August's RIACon outside of D.C.

Check it out:

CF Quick Tip: Display a CFINPUT element from a CFC

Suppose you want to write a helper function in a ColdFusion Component library, that will draw a HTML FORM field for you and you need to do so using a CFINPUT tag. The ColdFusion compiler will complain if the CFINPUT tag is not inside a CFFORM tag inside that function. This can be a problem if you wish to make this a portable function to be used in any number of form's.

There is an easy way to get around this. Wrap the CFINPUT element in a CFFORM with an undefined action. Then use a HTML comment tag around the and tags.


<cffunction name="drawDateField" output="true" returntype="void">
<cfargument name="fieldName" default="" required="true" />
<cfargument name="dateTime" default="#now()#" />

<!-- <cfform name="foo" action=""> -->
<cfinput type="text" name="#arguments.fieldName" value="#arguments.dateTime#" onBlur="doSomeFancyValidation()" />
<!-- </cfform> -->


So now, we can use our object on any form in our site that has access to our CFC. It would be a simple call such as:

<cfset objDateLib = createObject("component", "lib.cfc.dateTimeObjects") />

<cfform name="TVScheduler" id="TVScheduler" action="#">
Start Date = #objDateLib.drawDateField('StartDate', now())#

Be sure to use HTML comment tags and not CFML comment tags. The CFML engine needs to see the CFFORM or it will throw an error. The HTML comment tags will keep your browser from thinking that there are nested FORMs.

CF Quick Tip: Removing Non-Numeric Characters from a string

Suppose for a moment that you have a variable that you want to ensure it only contains numeric data. For instance a telephone, PIN, or credit card number.

ColdFusion makes this extremely simple to using a regular expression with the reReplace and reReplaceNoCase functions, as I will demonstrate below. For those new to ColdFusion the "re" in reReplace and reReplaceNoCase stands for "regular expression."

<cfset variables.myDirtyPhoneNbr = '(555) 222-4444'>
<cfset variables.myCleanPhoneNbr = reReplaceNoCase(variables.myDirtyPhoneNbr, '[^[:digit:]]', '', 'ALL')>

<!--- Now let's display the cleaned phone number --->
<!--- myCleanPhoneNbr should now be: 5552224444 --->

With a few exceptions, ColdFusion supports Perl compliant Regular Expressions. For more information, check out the online documents at:

You can test out the removal of all none digits at the link below: Remove Non-Digit Character Example.

Great tutorial website

A good friend of mine and former cohort at echo, told me about this website called NetTuts+ recently. They have a ton of great net tutorials ranging from PHP regular expressions to HTML5 to advanced CSS.

Give them a look:

Don't forget about ColdFusion Custom Tags

Since ColdFusion introduced Components (CFCs) I've seen a lot less mention of custom tags. But it seems to me like there are still good uses for custom tags. One place that comes to mind is your application's look & feel.

For instance a recent project required that I build a security challenge question micro-app for two web applications that utilize the same back-end. Think of it like a "Pro" and "Light" version of the same application. The originators of the web app built two different CF applications to talk to the same database. So with challenge question portion I built a custom tag to wrap all the content, then all of the forms were just simple, plain Jane HTML. Then based on our URL we initiated passed in the look & feel ID to the wrapper. BAM! We have two distinct product views, from one source code.

Moreover this turned into a proof of concept for a larger project the client had on their books. They were planning to integrate the two code-bases into a single code base. However, they originally thought they would have to give up the look and feel of one of the two applications. We have started migrating this concept to the pro-version of the application and will soon begin testing of providing the "light" view on the PRO app.

So if you haven't used custom tags in a while, just keep an open mind that there may be some instances where these could be valuable to you.

ColdFusion query of a query and encrypt/decrypt issues

Recently I have been working on some security enhancements for a client and I ran into an interesting bug when using a combination of ColdFusion's query or a query feature and the encrypt/decrypt functions.

You see when using the default encryption algorithm, CFMX_COMPAT, it apparently may choose to encrypt with some non-printable ASCII characters. So let's suppose you encrypt a pass phrase as it is going into the database, and you don't run a trim there, because you have already trimmed the phrase and you think to yourself, "Hey this function isn't going to return white-space." And you would be dead wrong. We found that in some instances, depending on the KEY value, the phrase, "father father" would be encrypted and stored with non-printable characters.

OK, so what's the big deal? Well as it turns out when you use a query of a query, ColdFusion 7 (have not confirmed with 8 and 9, yet) automatically trims string values. So when we would decrypt the value stored, "father father" became "father fathes". Note the "s".

Unfortunately we did not catch this before it went to production and ended up with some egg on our face, as users started complaining that pass-phrases were not validating. After much head scratching we found where the query of a query's result-set had was returning a trimmed value and giving us grief.

Our short-term solution was to alter our CFC's validation function to query directly against the database for the value. You see we had been trying to be slick, and load the user's various questions and pass phrases as part of the CFC's initialization, then simply query against the object's global query results. Which served to reduce database calls, all good in theory, but the whole auto-trim a string part killed us.

Great blog on international address web forms

I ran across this blog today while doing some research for a internationalization project I'm working on. It is worth a read if you are trying to decide how to handle international addresses.

An example of when Not to use a User Defined Function

First and foremost, I love user defined functions and think that they are one of the best features of ColdFusion and SQL Server. Custom functions are fantastic....

... until they are used in the wrong fashion. This is typically a sign of either inexperience or lack of fore thought on how the application would grow.

Recently I ran across a project that made poor use of UDFs. Briefly, the project has a function that when passed in a office location id, it queries the database and return the name of that location. Its a simple little function and when editing one record at a time is quite effective.

But what happens when you want to show a list of all employees and their office locations? Well if you were to loop through a recordset of your employees and while doing so you called this particular function, you would end up re-querying the database for each and every employee. In fact, that is exactly what was happening on this page. Did I mention that we had roughly 600 employee records (that's another story) on this page?

In this case, this was very poor programming. The page ran 601 queries against the database, when the results could have been gathered in 1 query. You see the employee table had the office location id, so a simple JOIN against the Location table provides us with the name of our location.

Moreover, calling the function on each iteration of the employee recordset resulted in the page taking a minimum of 625ms to return and sometimes much longer. Whereas by removing the call to the function and altering the query to perform the JOIN, the page now routinely loads in under 80ms.

ColdFusion 8.0.1 Now Available

Just in case you haven't heard already, Adobe released ColdFusion 8.0.1 yesterday. It adds 64 bit support for most platforms, provides updates to several of the Ajax engines (YUI, EXT JS, Spry), an update of FCKEditor, and addresses a number of issues.

Check out the Release Notes at: CF 8.0.1 Release Notes

Slow page load in Firefox and Safari, but not IE and Opera?

Recently I was asked to look into an issue for a Macintosh user, using Safari. It seems that some of the pages on one of my clients sites was not loading very swiftly. As I started looking into the issue I decided to take a baseline using Internet Explorer 6 on a Windows XP box. Then I moved to Firefox. When I got to Firefox, sure enough several pages were taking more than a minute to load, in some cases more than 3 minutes. These sames pages loaded in under 20 seconds using IE and Opera.

Next I fired up a Macbook and took a look at the same site using Safari. Low and behold I had the same issue as Firefox on my Windows box. So I decided to test the site with Firefox and Opera for Macintosh, and again Firefox was slow and Opera was fast.

To add to the problem, I found that testing the development versions of this site ran without a problem in all of the browsers. So I started thinking of what differences existed between the two environments. The biggest difference being that production has an added network appliance that validates you are allowed to access the page you have requested. But even with that in mind the question remains, why would IE and Opera be able to request and receive a response quicker than Firefox and Safari?

As it turns out, I happened to notice that some of the URLs a user would click on were open-ended directories.


<a href="http://localhost/Reports" >Reports</a>

However, if I were to change that link to be:

<a href="http://localhost/Reports/" >Reports</a>

Notice that I added a / after the directory named "Reports".

After making the change, the page loaded in seconds on all of four browsers. While I have not confirmed it yet, I suspect that part of the problem is related to how each browser sends the HTTP Request, and that part of the problem was with the network appliance.

Adobe on the rise, but no mention of CF

PCWorld recently released this article (,140782-page,1/article.html# ) on how Adobe is becoming a powerhouse now that the Macromedia tools have been integrated. While there is plenty of love for Flash and a mention of LiveCycle, there are no comments on Flex or ColdFusion. I think the author, Elizabeth Montablano, missed the bigger story on how using ColdFusion and Flex developers have a huge edge in building data-driven RIA's.

Upcoming ColdFusion blogs

I have some topic ideas to blog about (see list below) but would like some feedback as to what interests you most.

Upcoming topics:
* Creating SQL Server Scheduled Tasks on the Fly from ColdFusion.
* A con of enabling UNICODE on your data-source.

Please feel free to submit your own suggestions.

BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.