IE Caching Wastes Hours of My Life

I should have known better. I should have remembered. I've seen it before, but it bit me again: IE's abusive caching destroys the quiet peace of developing with JavaScript libraries like Prototype.

Here's the issue: I was updating the content of a <div> on a page via a form field on the same page. Very simple stuff. It worked flawlessly on Safari and Firefox (no surprises there), but failed on IE7. I tried the same code on a different machine running the exact same version of IE7 down to the xxx.xxx.xx.xxxx build, and it worked just fine. I tried it on a third machine and it failed. WTF?

The culprit, as I should have remembered from reading endless blog posts every day, was IE's overly aggressive caching strategy. Now on the Web application in question, we're setting HTTP headers to "No-Cache" to avoid these kinds of issues, and that works well for full page reloads. It does not, however, always work for XMLHttpRequest calls.

The default setting for IE is to "Automatically" check for new versions of Web pages when requests are made. If you look under "Tools" -> "Internet Options" -> "Browsing History" -> "Settings," the default is to check for new versions of Web pages "Automatically." However, IE's idea of "Automatically" doesn't take in to account XMLHttpRequests.

The solution is simple: append a random value to each AJAX request and the problem vanishes because now each URL being requested is "unique" and IE's default, aggressive, abusive caching strategy is foiled.

JavaScript example:

var cfcURL = 'myService.cfc';
var randomizer = Math.floor(Math.random()*50000);
var params = 'method=getContent&differentiator=' + differentiator;   
var myAjax = new Ajax.Request(cfcURL, {method:'get',parameters:params,onComplete:udpateSomeDiv});

Hours of my life wasted on something that should just work.

Oh, that's right, that's Apple's motto, not Microsoft's.

Converting a Numeric Field to a Numeric Field

When we create the first version of an application, we're prescient enough to always know the exact functional and storage needs of the application, no? I recently ran in to a bit of a problem caused by some poor planning on my part (or maybe it was the guy who wrote the original version of the app before me), and ran up against an unpleasant datatype conversion issue in SQL Server.

There's a field in the database for this particular app which stores relatively small numeric values — point values for each question in a quiz. This field is currently defined as a NUMERIC(4,2) in SQL Server. For some of you, this may have immediately raised a red flag. Why is this field limited to such a small range of numbers? Sometimes my long experience developing Web-based applications gets the better of me, and I concern myself with the limits of storage from an old-school "an 8GB hard drive costs $400!" point of view. And there are reasonable arguments to be made for limiting the size of fields within a database. However, my original reasoning was something like "Well they're not going to need values above 1000 nor decimal accuracy past two places, so why bother?"

You can see where this is going, right?

Now I have to support decimal accuracy to three places (and, sooner, or later, someone will ask me for four places) because in the world of scores for quizzes and exams, there's a big difference between 3.125 and 3.13.

So my problem was this: how to change the definition of the database field from NUMERIC(4,2) to a more standard NUMERIC(18,5)? Well you can't just fire up SQL Server Management Studio, right click the table, select "Design" and change the datatype. You'll get an internal conversion error from the database engine.

The next approach was to create a new column with the correct datatype and copy the contents of the old column in to the new one, drop the old column, and rename the new column with the old column name. In SQL, it's pretty trivial to copy one column to another. You just run:

UPDATE table
SET column1 = column2

However, when copying from a NUMERIC to a NUMERIC field, the database engine again throws the internal conversion error.

In this article from the SQL Server docs on CAST and CONVERT, there's a figure about 1/2 way down that shows which datatype conversions are supported in SQL Server. You'll notice that NUMERIC to NUMERIC is supported but it "Requires explicit CAST to prevent loss of precision or scale that might occur in an implicit conversion." So all I have to do is CAST() the origin field and we'll be OK, right?

Nope. Yet more internal conversion errors.

What I ended up doing was this, and I probably should have done everything in the database, but I was done wasting time and knew I could get this one time conversion done quickly using ColdFusion. I simply <cfquery>ied the database to pull all values from the original column, but CASTing the values as FLOATs. I then <cfloop>ed over that query and UPDATEd the table with the new values. SQL Server had no issues in going from FLOAT to NUMERIC(18,5). I was then able to drop the old column, rename the new column, and everything was good again.

I'm sure that if my SQL-Fu was better, I could have created a temp table, copied the data in there, done the CAST to FLOAT in the copy, then pulled from that temp table and updated the new column in the original table with the new values from the temp table. If anyone would like to provide me some samples to get started, or has alternate suggestions, I'm more than willing to give them a go.

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