Casting to Scientific Notation

I came across an issue a couple of months ago, where I had received a bug report that the numbers in a report we were generating were incorrect. There were two percentages that when added together should have totaled to 100%.

To get this percentage we have very large numbers that are added together and used cast(sum(NUMBERS) AS varchar) to obtain them. I couldn't find any correlation with the size, but some of the numbers were returned by the query as scientific notation, so instead of getting 1227630000 we got 1.22763e009. We then required ColdFusion to do some more calculations to determine the percentages, but ColdFusion didn't know how to handle the scientific notion so it just didn't and we added 1.22763 instead of 1227630000, which is a huge difference when you are calculating values.

To fix this issue we just removed the cast around the sum statement.

Unnecessary Curly Brackets in ColdFusion's CFScript

Recently I came across an issue where a variable was being set, when the expected result was that it shouldn't have been. The code was written in CFScript for ColdFusion 9, and involved some rogue curly brackets.

In essence, the code was trying to perform an if/else statement using CFScript syntax, with the executable blocks surrounded by curly brackets - however, it was missing the else before the second block. Below is a very basic example of what the code looked like:


Input Tags and Double Quotes

Today I came across a support issue that took me a little while to figure out.

The Original Problem - A user said that the field in a form was smaller than it used to be because the text he entered was being cut off and asked if it could be made bigger.

What It Could Have Been - First I checked the database thinking that if it was varchar(50) I could make it bigger, but when I looked it was of type text which has a max of over 2 billion characters. So, this wasn't where the problem was unless they were trying to type a novel into the text area. Then, I looked at the Input box which was a textarea which didn't have a maxlength set for it, I couldn't find the default maxlength for a textarea, but when I input what the user was trying to input I was able to do it with no problem so this wasn't the issue either.

The Real Problem - I then noticed that this was a two part form the first page (Form 1) collected some information and then continued to a second page (Form 2) that wanted more detailed information about answers on Form 1. After Form 2 was filled out the whole thing was submitted and inserted into the database. In order to do this all the fields from Form 1 had to be hidden input fields on Form 2. When I viewed the form details on Form 2 I could see that the field I had just entered was cut off. To test I did two dumps of the form variable I wanted one at the top of the page and one just before it was set into the hidden input value both on Form 2. They were both complete and as expected, but the hidden input value was still cut off. I then realized that the user put part of the text he inputted in double quotes so when the value field in the hidden Input hit the first double quote it assumed that it was finished and ignored the rest of the text.

The Fix - I found this fix online <input name="width" value="#HtmlEditFormat(Form.Width)#" /> it allows the full text to be inputted without cancelling out early even if there are double quotes.

The solution was found here.

Query of Queries to the rescue

Sometimes SQL is slow when using a LIKE operation. Recently I came across a situation where a rather complex query that regularly returned anywhere up to 1K+ rows was running great and returning results within seconds. As soon as a LIKE statement was inserted into the query to filter that query by first and last name of "Users", it slowed to a crawl. The query went from return results on average from 2 seconds to 50 seconds. After identifying the LIKE against the name columns was the cause we tried optimizing and indexing the table columns we were searching, but this only brought the query time down to 30 seconds which was not acceptable.

This is where I had to think creatively. If the original SQL query without the LIKE in it returned results in an average of two seconds why not just filter that query by name. This is where Coldfusions Query of Queries came to the rescue. By taking my LIKE statement out of my SQL and then re-querying the results with a QoQ I was able to get immediate results when filtering by name. In my opinion Colfdusions Query of Queries rocks.

view plain print about
1<cffunction name="filterQuery" >
2        <cfargument name="searchQuery">
3        <cfargument name="search_name" required="false" default="">
5        <cfset var returnQuery = ''>
7        <cfquery dbtype="query" name="returnQuery">
8            SELECT * FROM arguments.searchQuery
9            WHERE 1 = 1
11            <cfif arguments.search_name neq ''>
12                AND
13                (
14                    LOWERR(first_name) LIKE '%#LCase(arguments.search_name)#%')
15                    OR
16                    LOWERR(last_name) LIKE '%#LCase(arguments.search_name)#%')
18                )
19            </cfif>
20        </cfquery>    
22        <cfreturn returnQuery>
24    </cffunction>

Do you use StructKeyExists over isDefined?

We have an official best practice of using StructKeyExists over isDefined in our ColdFusion applications.

Based on my understanding (detailed in this stack overflow forum post), StructKeyExists is more efficient and accurate, because it knows exactly which scope it needs to evaluate, whereas isDefined still has to check all available scopes.


Expedia makes $12 million by removing one form field

This is an old news story, but the lesson definitely still holds true today.

Too many form fields and confusing potential customers and website visitors can cost you a LOT.

ConversionVoodo wrote a great article about how Expedia changed one form field and saw a $12 million dollar profit increase.

This is worth a few minutes of your time. It definitely brings home the point that less is more in form design and small changes in UI and UX can have HUGE impacts on your business and bottom line.

Getting estimations right is important

Getting estimations right is important. Sometimes when being asked to estimate how long it will take to complete and deliver a project it can seem overwhelming. It can be equally hard to estimate how long a task will take when the task being asked of you is coming up with a solution to solve a problem that is unknown. It can feel like being presented a 1000 piece puzzle and being asked "how long will it take you to complete this?". However hard or overwhelming it is, getting it right the first time is important.

Recently I had a new furnace installed in my home. The old furnace was on its last legs and I had been considering replacing it for sometime. It finally came time to bite the bullet and spend the time and money to replace it when I had to manually ignite the beast multiple times a day because it stopped turning on when the thermostat was calling for heat. So the process began to find a replacement for it by calling multiple local HVAC companies and getting estimates.

As a handy man who always does my own home repairs this was the first time I was exposed to this process. An estimator for each company I called arrived at my home and assessed the situation, the cost of parts and labor, and the time it would take to get the job done and created an estimate. The final number presented to me by each company was a proposal clearly stating "this is how much it will cost to get the job done" . After weighing my options on different estimates I made a choice and signed a contract.

The choice I chose was estimated at one day of work to install a new 95% efficient furnace for $$$ amount of dollars. It turns out the estimate was wrong.

The first day available to install was right in the middle of the polar vortex of 2014 (did not know it when agreeing to it). The company employees showed up and started the install. By the end of the day with no furnace running my home was 40 degrees and the unfortunate news was relayed to me by the HVAC employees that they needed another full day.

Another full day? Wasn't the estimate for 1 day? I had not planned for this, I have a family and a 16 month old child and with the indoor house temperature at 40 degrees it was not ok. Luckily I have family close by that we could stay with for the night. Long story short, the employees left and came back the next day and finished the install by the end of the next day and it was at no extra cost to me (other than stress).

Had the estimator estimated correctly he would have doubled the amount of time the install was going to take. The HVAC company was out the extra cost of another full day of labor for two employees, and I the customer was unhappy because I expected it to take as long as the time stated on the estimate. This real life situation is a great example of why it is important to come as close as possible to estimating how long any project will take.

Thanks for reading.


CFSpreadsheet Exception: Invalid column 257 specified

I was importing some data into our system the other day via cfspreadsheet, when I encountered a strange error, "Invalid column 257 specified." I haven't seen this error before, and didn't find much documentation on it. However, I was able to figure out the cause and solution, allowing me to detail my findings below.


Happy Holidays

Happy Holidays everyone here is some nerdy humor for you


Working with Interruptions and Rubber Ducking

I was reading through some tech-related aggregators recently and found a cartoon called, "This is why you shouldn't interrupt a programmer". It really touched a chord with me and I felt this was a good opportunity to talk about a concept known as Rubber Ducking.


Previous Entries / More Entries