Volume V (Apr 2000-Apr 2001)

Be careful with very large CFLOOP's

Posted: 2/19/2001

Be careful when running very large CFLOOP's, such as may be used to test some sort of long-running result. Even if you output only a few lines within the loop, if you're not careful the page will have thousands of blank lines. This behavior may surprise some, who assume that any carriage returns created within the loop where the CF code was would be driven by compile-time removal of the code.

It's not, it's a run-time thing. Consider the following code:
<cfset starttime = gettickcount()>
<cfloop from="1" to="100000" index="i">
  <cfif not (i mod 10000)>
    <cfset timenow = (gettickcount()-starttime)/1000>
		hit #i# at #timenow# seconds<br>
This creates a loop that runs 100,000 times, which every 10,000 times prints out the number of seconds since the template was started. (There are two useful tricks there, if you didn't know them: how to have a loop "break" very x times via "not ... mod x" and how to use gettickcount() to track the time since startup of the template.)

But the important point in this tip is that, while the output of this will look like just 10 lines of code, if you look at the underlying HTML source, you'll see there are at least 100,000 lines in the HTML output!

And it's not because of the CFOUTPUT being outside the loop. Oh no, in fact, if it were inside the loop (even inside the CFIF), the number would jump to 200,000. I can't explain that.

But the bottom line is that the fact that the loop is being executed 100,000 times, even though nothing is suposed to happen inside it except every 10,000 times, it creates 100,000 mostly blank lines.

So what's the solution? Well, it's 2 steps. First it the good old:
<cfsetting enablecfoutputonly="Yes">
This will make it so that only the lines of code within a CFOUTPUT will send output to the browser. That's fine, but remember how I found that doing the CFOUTPUT inside the loop was creating double the lines, so I moved it outside the loop?

Now, I have to move it inside the loop. Otherwise, the fact that the loop is inside the CFOUTPUT causes the same 100,000 lines.

The combination of moving the CFOUTPUT into the loop and using CFSETTING causes us to now get only 10 lines of output.

By the way, if you want to play with this stuff on a machine with low resources, drop the loop to 1,000 and the mod check to 100! :-)

Be careful outputting from a CFHTTP?

Posted: 2/19/2001

Here's a problem that could trip you up. If you have code of the form:
<CFHTTP URL="http://someurl/sometemplate.cfm">
you may find that you never see the output "someothertext" after the output of the cfhttp.filecontent. How could that be?

Well, what if sometemplate.cfm (being a CF template) has debugging turned on? If so, then the CFHTTP.filecontent variable will contain its output and then IT's debugging output. Only if you think to scroll down past all that will you see the "someothertext" that was "supposed to be" shown just after the called page's output. Ouch!

What is the purpose of the CFHTTP ThrowOnError attribute?

Posted: 2/19/2001

The ThrowOnError attribute of CFHTTP serves two purposes (only one is particularly valuable). Normally, if CFHTTP encounters an error, it doesn't stop the program doing the CFHTTP. It simply reports the error in CFHTTP.FILECONTENT. (It also now reports it in CFHTTP.STATUSCODE, with additional statuscode information available, if any.)

An example of such errors may be "Connection Failed", as occurs when the connection to the remote page times out (using the TIMEOUT parameter of CFHTTP) or "Access Denied", as occurs if the remote server to which connection is being attempted uses web server authentication and you've not specified a USERNAME and PASSWORD. (Note that if the server uses digest authentication, CFHTTP can't connect at all, since it can't act as that kind of client.)

Anyway, normally, this error is made available in the variable CFHTTP.FILECONTENT, but before there was the CFHTTP.Statuscode variable it would be hard to distinguish an error from otherwise successful content in that variable. Even with the CFHTTP.Statuscode, you may want to better distinguish that the error has occurred, or at least handle it in a more traditional "exception handling" way, with ThrowOnError.

I said there were two uses: well, if you use that attribute (with a "yes" value) while doing the CFHTTP within a CFTRY/CFCATCH block, it is very handy to determine and handle that an error has occurred. Treat it as you would any exception you're trying to detect. (Note that the CFCATCH.MESSAGE variable will have the value that would have been in CFHTTP.STATUSCODE.)

But if you use the attribute (with a "yes" value) without surrounding the CFHTTP in a CFTRY/CFCATCH, then it will simply cause the tag to generate a CF error, which will terminate the template (probably not what you want).

When search in Studio's help feature doesn't work

Posted: 1/30/2001

If you find that an attempt to do a search in Studio's help feature fails to return any results, the help index may have become corrupted. Simply stop Studio, open the Verity\Collections folder (wherever you installed Studio, such as \program files\allaire\ColdFusion Studio4), delete the "Searchable Help" folder under that directory. When you next load Studio and do a search, the index will be rebuilt.

Using CFCACHE rather than the CF Scheduler to avoid re-executing dynamic pages based on data that doesn't change often

Posted: 1/25/2001

People often look at the CF Scheduler (or using the OS scheduler--see tips below about that) to cause CF to create an "html version" of a template such that visitors don't re-run a dynamic page that would be based on data that doesn't change all day.

For instance, assume you want to list all company employees but the list only changes via some batch job (or replication) run overnight. As such, there's no need to query the employee database each time the report is run, because it will be the same data all day.

In this case, rather than try to finagle the CF scheduler to create an HTML version of the file (which is an old way of doing things prior to Release 4), use the CFCACHE tag. There is an important new feature in Release 4 that a lot of people missed (and even the docs on the scheduler don't point it out).

It's nifty and rather simple. All you do is put the CFCACHE tag in the page to be executed by the users (the .cfm file you wanted to avoid them re-running). But after making this change, you do still have them run that template. The tag could is:

This will cause the page to run (if it's not yet been cached) and it will be kept in a cache so that future visitors won't really re-run the page. They still use the same the URL to run the page (no need to send them to an "html" version, as CF does that for them under the covers.) They'll just be shown the cached version.

One thing that's tricky: the timeout "datetime" should be set to a time in the past. It means that as long as the page was cached since that time, the cached page should be used, otherwise it's re-executed for real and then cached for future users.

For example, if you want a cached file to be no older than 4 hours when the user ran it, you'd code the following:
    CFCACHE ACTION="cache" TIMEOUT="#DateAdd("h", "-4", Now() )#"
Note the use of the DateAdd function, which is setting the date to 4 hours previous.

That's fine in some situations, but it uses a time relative to when the template is attempted (using the now() function). What if instead you want to make it so that it uses a cached version as long as it was created after a certain time today?

For instance, what if the process of updating the data always takes place by 6am. If you always tested for a cached time relative to when they execute the template, like "now - 1 day", they might get yesterday's results rather than todays (if the first person to cache it ran it at 5pm the night before and they're running it at 10am the next morning).

What you really need is to test for a cached result having been created after a fixed date/time, not a relative one. In that case, just don't use now(), alone, as the 3rd parm in the dateadd, subtracting time from it. Instead, create a fixed time. For instance, to set it to use the cached version as long as it was after 6am on the day the user runs the template, use the following for the TIMEOUT:

That creates a time that's 6 hours into today's date. There are other ways to create such date/time values. This will work. It creates just the date for today,and then adds 6 hours to it. Since CF presumes that a date without a time is at midnite, it works.

Note also that there is also the possibility that when you run the CFCACHE, you'll get an error "access denied". This is similar to the problems with CFHTTP and CFCHEDULE. If the page you want to run is on a server with web server authentication, then in order to browse the page (or more important, for CF to give the cached page to be browsed), you'll need to provide a username and password. Unfortunately, the Studio tag helps (tag editor, tag insight, etc.) don't list them (as of Studio 4.5.2). Just know they are there and may have to be used.

Note also that this tag is typically placed at the top of the page, but can be placed elsewhere to cause the caching to start only at that point. This can be tricky, so try it before relying on it.

Also, you may wonder what to do if the data does indeed change in the middle of the day. How do you then flush that cache? The solution is action="flush". Basically, you'd set up (in the page doing the updates) a tag that flushes the cached page based on that data (it's up to you to know which pages might be affected by the update). The tag would be:
    cfcache action="FLUSH" expireurl="templatename"
That "templatename" in a simple case would be the name of the template being cached. No need to provide the full url or any directory. Just the template name, so if the template cached was "report.cfm", just use that in the expireurl.

There's more to know about it, but that will suffice for most uses.

Note also that if you change the code in the template itself, that will also force a recache (that's a simple trick if you want to force a reflush but don't need or want to set up a programmatic flush). That's what the "sourcetimestamp" is for in the cfcache.map entry for the cached template, if you go looking at that cfcache.map file.

Finally, there are lots of other useful and interesting parameters. See the Allaire docs for more.

Running CF code from the command prompt (or a Windows scheduled task)

Posted: 1/24/2001

The last tip (below) talked about how to setup an alternative to the CF scheduler using Windows Scheduled Tasks or the AT command.

In either case, in order to run a CF template that way, you need to know how to run that from the command line. It's not obvious, and there are quirks.

The simple answer is that Allaire provides a command line program, cfml.exe, which you can use. It's in the "\bin" directory where CF server is installed, typically c:\cfusion\bin. Unfortunately, there's more to it than simply doing a "cfml.exe template.cfm"

First, you have to pay attention both to where the command (cfml.exe) is and where your code (template.cfm, in the example above) is. The latter will probably be in your \inetpub\wwwroot, or whereever you store CF templates.

So, if you're running this from the command line--and you're familiar with DOS prompt commands--you may think you can simply go to your \inetpub\wwwroot\ directory (or wherever the template is) and do a:

    c:\cfusion\bin\cfml.exe template.cfm
That still won't work. Turns out you need to provide the full path to the template (drive and root-relative path), so it would instead be:
    c:\cfusion\bin\cfml.exe c:\inetpub\wwwroot\template.cfm

So the bottom line is that you should just always provide the full path to the cfml.exe and also to the template. And this is true if you're running this from a scheduled task or AT command.

The last thing to discuss is where the output should go. Well, first, what is the output? Since this is a CFML template, it will likely be HTML, though it could just be a status report from the template. In either case, it's the same output that would have been run had you run the template via your browser (including debugging information, if that's turned on).

But when you run it from the command line, scheduler, or via the AT command, the output is not sent to any browser. In fact, it's not sent anywhere. It's returned to whatever is doing the command. So at the command line, it is just displayed in the command line window.

In the case of a scheduled task or AT command, it will simply be thrown away by the OS. If you want to save that output, there is a solution. Again, it calls on old DOS knowledge and skills.

You'd use the redirection symbol, or >, as in:

    c:\cfusion\bin\cfml.exe c:\inetpub\wwwroot\template.cfm > c:\template.log
where again you'll want to specify a full path and some file name to hold the output of the execution of the template. This tells the operating system to redirect the output of the first command (the cfml and whatever it does) to the file named after the symbol.

One last point, though, is worth noting. If you're doing all this to schedule the creation of an HTML page, with the idea that because the page is dynamic but doesn't change often, you're trying to avoid it being run as a dynamic template every time a user calls it, you're probably wasting a lot of time and energy. What you're doing is the old way to accomplish that objective. We'll look into the better alternative, CFCACHE, in the next tip. Until then, look it up in the CF reference manuals.

Using alternatives to the CF scheduler to schedule tasks

Posted: 1/24/2001

Often, people have difficulties using the CF Scheduler facility. That's too bad, because often the problems can easily be solved. We'll offer a tip on that later, because the Allaire docs do sometimes leave people confused.

In the meantime, it's worth noting that there are alternatives to that. In Windows NT and 2000, there's the AT command, available from the command line. See your OS help for more info, or simply type AT /? at the command line.

There are also screen-driven (rather than command line-driven) scheduling facilities in Windows 95 and 98

In fact, Windows 2000 brings together the best of both the AT command and a screen-driven interface for scheduling in the new Scheduled Tasks applet in the Control Panel. Check it out.

With any of these, you can set up a task to run at a particular date and time, and even on a recurring basis.

Problems w/ empty strings on insert/update

Posted: 1/12/2001

Have you ever found when trying to do an update or insert into a table that you get an ODBC error complaining:
    Field 'yourfieldname' cannot be a zero-length string

The problem is that you are trying to store an empty string (not null but literally "") in a column which, in the case of Access, has been defined to not allow that.

This is controlled by the "allow zero length" property listed for the column in design mode in Access. If that's set to "no" (the default), then you cannot store empty strings in the column.

The problem generally occurs when you are inserting/updating a table using form fields. If a form field has no value, it comes into your program as the empty string (""). If you try to simply store that value into the database as is, and this property is set for a column, you'll get this error

Two Solutions

There are two solutions: one is to change the database to set "allow zero length" property to "yes" for the column in question.

The other solution is that you can change your code to test for the empty string and store a null instead (assuming nulls are allowed for the column--that's controlled by the "required" property listed just above "allow zero length" in the Access design mode for a table)

The way to do that would be to put IF tests literelly inside your CFQUERY to test whether to store a NULL or the form field value, as in:

    <CFQUERY Datasource="...">
      UPDATE tablename
      SET column =
        <CFIF isdefined("form.fieldname") and form.fieldname is not "">
      WHERE ...

Of course, you need to fill out the UPDATE statement to reference all the columns you mean to update, and repeat that test for an empty string for each column that has this property set, and finish the requisite WHERE clause so that you update only the record intended.

Notice the use of null in the CFELSE clause. That's literally the word "null" and it's intentionally written there without quotes. The database (and SQL) know that that means you intend to store null there, not a word composed of the string "null". Remember, though, that you can't store null if the column is marked as "required" in the database. (While numeric columns don't have the "allow zero length", they do have the "required" property so this is an important point for those, as well.)

If the column is not set to "allow zero length" but is required, then you simply have to store something in the column. Neither the empty string nor null are allowed. In that case, use a test and message to the user to prevent them submitting the form if no value is presented (the "hidden field" technique of setting "_required" should work.

Finally, note that the "allow zero Length" property works independently of the "required" property. "Required" determines only whether a Null value is valid for the field. If the "allow zero Length" property is set to Yes, a zero-length string will be a valid value for the field regardless of the setting of the "required" property.

Left padding a number with at least 1 zero

Posted: 11/22/2000

Someone asked how to left pad a set of numbers with at least on leading 0 to the left of the decimal point, while also showing at least 2 rounded digits to the right, also padded with 2 0's.

In other words, the numbers .98,10.2,3.4567,11,987.012 should appear:


It turned out to be trickier than one would think. The challenge was not the right justification of the numbers (that could be done with a right-aligned table cell). The difficulty was specifying the correct pad for the numberformat function.

One approach that was tried was:

<CFLOOP list=".98,10.2,3.4567,11,987.012" index="y">
	<cfoutput>#NumberFormat(y, "0.00")#</cfoutput><br>
It didn't work. Though it should have worked, it wouldn't force 2 zeros to the right of the decimal (10.2 showed as 10.2 rather than the desired 10.20). (In fact, as of 4.5.1 SP2, this code now fails as an invalid format.)

And even the padding characters offered in the CF docs didn't do it (actually, this does now work as of 4.5.1 SP2, but in a release prior to that which I did not document, it failed):

<CFLOOP list=".98,10.2,3.4567,11,987.012" index="y">
  <cfoutput>#NumberFormat(y, "_____.00")#</cfoutput><br>
This failed to do the left padding of at least 1 zero (the 0.98 showed up as .98)--again, this is now fixed as of 4.5.1 sp2.

Adding a 0 to the left of the decimal, in that last example, would force the left padding with a zero, but it put one for ever placeholder setting specified (10.20 became 00010.20). Definitely not what was desired.

What finally worked was to use the following, using the little-known and terribly documented caret character (^): (again, this was the case in some release prior to 4.5.1 SP2, whereas now after that release, the following fails but the above works!)

<cfloop list=".98,10.2,3.4567,11,987.012" index="y">
  <cfoutput>#NumberFormat(y, "^^0.00")#</cfoutput><br>

Can't explain it. Just note that the number of carets is significant. The 2 carets and 0 are enough for numbers under 1000, as are shown in the list being processed. You'd need to add a caret for each increase in places to the left (one more for numeric values up to 10,000, two more for up to 100,000, etc.).

Failure to do so will cause loss of significant digits on the right of the output string. If 12345.678 were processed with the pad ^^^0.00, the value would be 12345.6. The correct pad is ^^^^0.00, which leads to 12345.68 (with the rounding of .678 to .68).

Access CFDJ Articles online

Posted: 4/26/2000

Hopfully you know about the great CF magazine, ColdFusion Developer's Journal. It's available both in print (at some newstands or by subscription) and online in PDF form (for subscribers).

Recently, they put all the past (and current) articles online in HTML form, and as of now anyone can look at them.
Update (6/12/01):
Unfortunately, the URL used to access these articles has changed. Before, it was in the form:
with the vvii representing the volume and issue, and from there you could see the url for a given article.

Now, the format for a given article is always:
where nnn is a unique article id. Therefore, it's no longer easy to know how to find a particular article if you don't have an access code. But you can still view any articles online, even without an access code, if you know the URL.

You can find links to the articles we've published there in our "articles" page.

Warning users of timeouts

Posted: 4/26/2000

This isn't our own tip, but it's one we think you'll really like. Daniel Jean, of Galileo Development Systems, wrote an interesting article in the May 2000 Issue of CFDJ. He shows how to build and use a custom tag to warn users (using Javascript) on their browser when they've been viewing/filling out a page for so long that they're about to timeout their session variables on the server.

There have been other custom tags to handle this, but the article explains the logic and javascript code. Check out: http://www.sys-con.com/coldfusion/article.cfm?id=108

The article doesn't offer the source code as a file (as many CFDJ articles do), but you can find it here:

<CFPARAM Name="Attributes.TimeOutMinutes" Default="30">
<CFPARAM Name="Attributes.WarningThreshold" Default="2">
<CFPARAM Name="Attributes.WarningMessage" Default="You will timeout in #Attributes.WarningThreshold# minute(s)!">

<SCRIPT Language="Javascript">

// Global variables
StartTime = new Date();
StartMils = Date.parse(StartTime.toLocaleString());
TimeOutMils = StartMils + (60000 * #Attributes.TimeOutMinutes#);
ThresholdMils = 60000 * #Attributes.WarningThreshold#;

function CountDown()
CurrentTime = new Date();
CurrentMils = Date.parse(CurrentTime.toLocaleString());
RemainingMils = TimeOutMils - CurrentMils;

if(RemainingMils < ThresholdMils)
timerLoop = setTimeout('CountDown()', 10000);

// run CountDown for the first time
Tips Contents:

| Home | ColdFusion | Articles | Presentations
| User Groups | Other Resources | Press Releases | Company

© 1998-2018, Charles Arehart, SysteManage
Our Practice Makes You Perfect