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: https://stackoverflow.com/questions/2499332/how-to-check-if-a-constraint-exists-in-sql-server

Enjoy!

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: http://coldfusion.adobe.com/coldfusion/ 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 (blogs.coldfusion.com)...

"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: http://www.raymondcamden.com/index.cfm/2013/1/22/Using-ColdFusion-to-check-available-disk-space

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.

http://www.jozza.net/blog/measuring-disk-space-in-coldfusion/141/

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

ColdFusion.bindHandlerCache[objectName].call();
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)">
....
</select>

<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.

More Entries

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