Home of the Spider

Query of Queries in Coldfusion MX

I recently started working on converting my messy, legacy, procedural code to CFC's. Lets just say I love CFC's. It makes coding a joy, and maintenance a breeze.

I wrote one component for categories. When you load this component, it loads all information for this category, including a list all sub-categories and products in this category. The number of products in the category are used in various places throughout the site when I render a link to the category, so I pull the products when the category is loaded.

One of the features of the site is a price filter, allowing you to view products within a specific price range. Because I load all the products when the category is loaded, rather than going back to the database to get the products matching the filter, I use Query of Queries to get only the matching products from the original query.

However, a customer pointed out that the price filter returning incorrect results. Say you filtered the results to view products under $20, it would show all products where the price began with a 1. The customer, who is a web developer for a large accouting firm, suggested that the price was being stored as a text string, and this result was because it was doing a text comparison, not a numeric comparison. I know that the database is storing it as a decimal, but it got me thinking about my Query of Queries, and how Coldfusion is assigning data types to the columns.

A little research showed me a big issue. Coldfusion MX does not allow you to assign the data type of the columns in a query (later versions do allow you to, but I haven't tested it with Query of Queries.) There is also almost no documentation about which Coldfusion functions can be used in QoQ, aside from UPPER() and LOWER(). And for reasons I'm not quite sure about, possibly because the field had 4 numbers after the decimal??, Coldfusion decide my price was a text field, and there was nothing I could do about it.

To solve this, I have to something thats truly a last resort. I actually loop through all the products, build a list of matching products and the QoQ pulls only those products. I know thats not the "right" way to do things, but I really wanted to avoid having to run another query to pull the products from the database.

---

Item of the Day: Blancodiamond 511-709 - Blanco Sink Silgranit

Coldfusion CFC Local Variables

I'm embarrassed to admit that I haven't done much development with CFC's. Considering my background as a Java developer, there really is no excuse.

So I'm trying to make up for lost time. Almost all the development I'm doing these days involves CFC's. Many times I'm moving existing code in to new CFC's, but the benefits are great. Its the single most important feature in the language.

Of course, my CFC honeymoon came to a crash ending. I have a component that certain information on each link on the site. The data is stored in a database, but because there are many lookups for each page rendered, I didn't want to do a database lookup. Instead, I created a component that stores the data in a struct, keyed on the URL. The component is stored in the Application scope, so the data is pulled once, and the lookup happens in the component.

Each element in the struct is actually another struct with a bunch of information regarding that link. The function urlLookup first initializes the return variable called "info"

<cfset info = "">

then does the lookup

<cfif structKeyExists(urls, key)>
    <cfset info = urls["#key#"]>
    <cfset info.foundResult = true> <!--- flag this as a valid result --->
</cfif>

because I'm expecting a struct from this function, if no URL was found, I create a struct and set the foundResult to false

<cfif info is "">
    <cfset info = StructNew()>
    <cfset info.foundResult = false>
</cfif>

then return info

<cfreturn info>

Mysteriously, I kept getting errors "Element FOUNDRESULT is undefined in URLS". Can you spot the error? Remember, this object is in the application scope, so many requests are hitting it at once.

The problem is in this line

<cfset info = "">

the variable "info" is a global variable, not local to the function. If a second request enters the function before a previous request finishes, it re-initializes the global variable "info," which is then returned. FOUNDRESULT will not be found because the variable is an empty string!

The solution is a to add a little keyword "var," so the line of code looks like this

<cfset var info = "">

this makes the variable local to the function, so each call to the function creates a new variable local to that call. It is recommended that all variables, including query names, be initialized with the var keyword (referred to as "varing" your variables.) 

---

Item of the Day: KWC Domo 10.061.033 - Kitchen Faucet from KWC

Coldfusion CFC in Sub-folders

Its one of those little things that can make the all the difference.

I manage a bunch of websites. By far the most confusing one is the one that has all the files stored in the root folder. Why is this so confusing? Because you cannot gain any understanding of the relationship between the files just by looking at the files and directory structure.

A properly structured directory structure can tell me so much about the files without reading them. I can guess what a file in "webroot/shopping-cart/google-checkout/callback" should do, even without opening the file. Finding and fixing bugs is so much easier in a properly structured directory.

As I began doing more and more of my new development in CFC's, this issue bothered me. I know how to instanciate an CFC in the root folder. Say you have a CFC "shoppingCart.cfc" in the root, you instanciate it like this: <cfset cart = CreateObject("component", "shoppingCart")>. But I didn't know how to instanciate a CFC in sub-folders.

As it turns out, its quite simple (that seems to be a ColdFusion mantra.) Say our shopping cart CFC is located in "webroot/siteComponents/cart/shoppingCart.cfc". To instanciate, you do this:
<cfset cart = CreateObject("component", "siteComponents.cart.shoppingCart")>

---

Item of the Day: Blancodiamond 511-708 - Silgranit Kitchen Sink by Blanco

Installing ColdFusion

After successfully migrating the data to the new server, I started installing some of the software needed to run the website. The most critical one is ColdFusion, so I started there. I already have a license for ColdFusion, but not for the latest version (version 7), only for version 6. A call to Adobe support gave me a download location for the version 6.

But when I try to run the installation program, nothing happens. It just stops, with no error message or anything, just gone.  Back on the phone I go to Adobe support. While on the phone with them, I log back on to the server to try one last time, and as I log in I get bombarded with Windows Security message telling me that Windows, in its infinite wisdom, blocked the installation program from running. I did not get any such notification at the time of installation, only after logging off and back on to the server. One tick to tell Windows to allow the program to run fixed that problem.

After completing the installation, I get an error message from ColdFusion that some of the connectors could not be installed because port 51010 is not open, and I should install the connectors manually. If port 51010 must be open for the installation to complete successfully, why don't they test for that right at the beginning and inform you before the installation begins?

I was eventually able to successfully complete the installation by connecting through a VPN to the host network, logging on to the machine locally, and running the installation that way.

But here is what I don't understand. MySQL and Tomcat, both open-source programs, installed without a hitch in minutes. So why does ColdFusion, a commercial product, make you go through hoops to install?

---

Item of the Day: Fireclay Kitchen Sink: Franke Islander Work Top MHK712