Ever had the need to insert a record, but only wanted to do so if the record doesn't already exist? Typically the first approach to this is two queries: one to check for the existence of the values you want to insert, the second to peform that insert IF the first query returned no records. Well, I was in such a spot today, but hated the idea of having to hit the db twice to perform a conditional insert. Though I'm sure I'm not the first one to come up with this approach, I thought I'd share it in case it helps someone else, too.

I'm using a MySQL database, so the query I'm going to demonstrate is written a little bit MySQL-specific, though it is easily translated into MSSQL-speak as well.

The Scenario

Okay, we're looping through a file where each line represents a record. For each of those records, we want to perform an insert IF that record doesn't already exist. Here's how I'm doing it:

 

First of all, please know that I highly recommend the use of <cfqueryparam> tags in every place that you see a variable being output. I ommitted that tag in this example for clarity's sake.

Walking through the code, first thing I'm doing is grabbing my values from the current file line. Next, the query. It is obviously an INSERT query using a select statement. The select statement is simply selecting the actual raw values, but ONLY if a record meeting that exact criteria does not already exist. If no record is selected in the WHERE's subquery, the values are inserted; otherwise, nothing happens at all.

Notice my use of the "LIMIT" attribute. This performs the equivalent action of DISTINCT, ensuring that only a single record is returned. Without limiting the results of the SELECT statement, we would get multiple results and would be inserting massive amounts of duplicates. For MSSQL users, simply add the DISTINCT keyword to your SELECT statement, as in "SELECT DISTINCT '#carrier#', '#claimnum#','#priorclaim#' FROM ...."

You know how ColdFusion is so robust that oftentimes there are those tags and functions that exist, yet you've never once had an occasion to use them? Well, today I used a tag for which I've previously not had a need: cfthread. I figured I'd share my use case and implementation in case it piques the curiosity of other CFTHREAD virgins.

The Scenario

Our company has a client who requested that we automate the portion of the process whereby a document originating from them is returned to them by us after we're finished with it. They've built some kind of "folder watching" process on their end, and so asked if we could sFTP the final file to their server. Digging in to the legacy code that performs the final processing, I identified the place where I could surgically place the code to perform this work. But, I didn't want to make the user to encounter any additional delays in page load time.

The Solution
Enter CFTHREAD, a sweet little tag that will allow me to very simply execute a chunk of code asynchronously and unattached from the page it resides within. By simply encompassing the autonomous bit of code within cfthread tags, CF will run it in parallel to the page itself. But, though I did want the upload itself to occur without hindering the execution of the remainder of the page, I DID want to give my user a final message indicating whether or not the upload was successful. In other, more relevant words, i wanted this autonomous process to join back up with the original request at the end, and update a message div on the page appropriately.

Since my standalone process was for sFTP purposes, I'm going to use that snippet of code in my example. Here is the section I needed to run on its own:

 

 

 

 

So, while the above code is executing (assuming we met our conditional criteria), the remainder of the page runs. At the end of the template, I use the following code to join the upload request back to the original request:

<!-- div to hold status message for parallel process... -->
<div id="uploading" name="uploading" style="color:red;">
 <cfif ftpingfile> 
  <br><br>Please wait. Uploading file to #targCarrier#... <img src="images/spinner.gif" align="absmiddle" /><br><br>
 <cfelse>
  <br><br>NO PDF UPLOAD ATTEMPTED. EITHER NO PDF WAS FOUND TO UPLOAD, OR THE CLIENT HAS NO FTP SETTINGS IN THE SYSTEM<br><br>
 </cfif>
</div>

<cfflush><!--- output the content of the page thus far to the browser so the user has something to see/do until our process finishes --->

<!--- if we attemptd to ftp a file... --->
<cfif ftpingfile>
 <cfthread action="join" name="FTPit" />
 <cfif cfthread.FTPit.status IS "Completed">
  <script>
   document.getElementById('uploading').innerHTML = "<br><br>Estimate Successfully Uploaded to <cfoutput>#targCarrier#</cfoutput>!<br><br>";
  </script>
 <cfelse>
  <script>
   document.getElementById('uploading').innerHTML = "<br><br><strong>There was a problem uploading the estimate. Here are the details:</strong><br><cfoutput>#JSStringFormat(cfthread.FTPit.error.detail)#</cfoutput><br><br>";
  </script>
  
  <!--- send details of failed attempt to Doug --->
  <cfsavecontent variable="failedFTP">
   <cfdump var="#cfthread#">
  </cfsavecontent>
  <cfmail to="dboude@adomain.com" from="administrator@adomain.com" type="html" subject="failed FTP attempt for invoice #InvHolder#">
   FTP process failed. Here are the details:
   <br>
   <cfoutput>#failedFTP#</cfoutput>
  </cfmail>
 </cfif>
</cfif>

That's it! So while the user is looking at and potentially interacting with their page as usual, the status message div I placed will be updated appropriately as soon as the upload finishes. Just to clarify, the browser will still show its "loading" status until the threaded ftp process finishes, since we told CF to join it back to the original request. But, because we joined it at the end, and we cfflushed prior to that, the user will see their typical output in the meantime.

The Scenario

You're writing code to perform authentication for a web-based app. Your users live in one table (Site_User), your list of site permissions live in another table (systemRoles), and the two are related via a junction table (jctUserRole) that manages the many to many relationship there. Your backend database: MySQL version 5 or better.

So, in an ideal world, you will be able to write a single query that will both authenticate the user AND retrieve a comma delimited list of their roles contained in a single field; if their credentials are good, your result will only have a single record in it. Typically, however, because of the way many to many relationships are normalized, the "one query" you want will be returning multiple rows...one row for each permission the authenticating user is related to. For example, here is the typical way one would write the authenticating query:

 

This produces a result that looks like this:

We would then be required to write additional code in order to more properly "package" up this user's list of permissions for use within the system.

But enter the wonderful world of MySQL 5, and the 'group_concat' function. Group_concat allows you to concatenate values into a list based on a specified grouping, and will produce a list of unique values delimited by whatever character you choose (but defaults to a comma). So transforming the above query just a little bit, we get:

 

 

And a result set that now looks like this:

Man, SO much cleaner and nicer, eh? Now you can simply treat the "roles" value as a list...explode it into an array if you want, or leverage ColdFusion's myriad of List functions to interact with it.

Since discovering group_concat, I've found several other uses for it as well, particularly when producing reports for my internal clients...it saves me a lot of iterating over result sets in order to produce value summaries.

You can read the documentation for this function here

SO, you too find yourself needing to write something that could really benefit from having its settings contained in some external resource. Let's see, what are the choices...we could possibly store our settings in the database and retrieve them at application initialization, if our architecture permits that; we could create an xml file that contains our settings in a nice, nested, readable manner; we could (as one person I mentioned this to suggested) put our settings into an INI file (ya think this person does a little too much .NET? :) ); or we COULD, as I have recently become enlightened to, put our settings into a CFC. Since I am especially partial to the idea of being able to have the choice of hard coding my settings and/or leveraging Coldfusion's dynamic personality to populate them, I went the way of using a CFC.

Allow me to preface the remainder of this post by saying thank you to those out there who have already traveled this road and have made their work open source. By dissecting several different code samples and doing a lot of experimentation, I have been able to enlighten myself as to the mechanics and theory behind it.

SCENARIO
For the sake of illustration, let's say I'm writing a plugin that will be used to provide shopping cart functionality to whatever app is implementing it. Of course, every app's needs, environment, and settings are different, so our plugin needs to be configurable. My goal then is to create a CFC that will contain a nested structure/array of settings that my shopping cart plugin CFC can utilize to control how it is implemented. Sounds simple, right? Or is it... :)

For those who want to skip right to the heart of the matter, you may do so now. For those who, like me, enjoy the layered learning process and the excitement of examining the pieces before putting the puzzle together, let's take a quick look at essential the concepts that allow using a CFC as a configuration file to work.

THE ESSENTIAL CONCEPTS

SCOPE
You already know about scopes in CF and are well versed in how and when to use them, I'm sure. You know, for instance, that if you create a variable within a template and ommit the scope, it is automatically put into the variables scope. You should also be aware, however, that any functions you create within your template also occupy a place within the variables scope. Take this template, for instance, and the accompanying dump:




Interesting, eh? Keep this in mind.

Now, you may also already be aware of the fact that within an instantiated CFC you have scopes as well. I won't spend any time elaborating on those scopes or the differences between them (did that in another post a while back). But I WILL hit you with a short CFC and a dump of its own internal variables scope. Based on our little experiment above with our template, look at the CFC internals and formulate an idea of what you would expect to see. You may see something surprising in this one.

Component A.cfc


Code to instantiate A and retrieve and output its variables scope:




As you probably expected, there in the variables scope are all the functions of the CFC (regardless of what access type we gave them). Additionally, we have the often shunned THIS scope...and looky what THIS is harboring. That's right boys and girls, a reference to (not copy of) the CFC instance. THIS, as you may or may not know, IS accessible from outside the CFC instance. So if we modify slightly the code we used above to instantiate the object, and add a line to give our object another attribute, a subsequent call to getMyVariableScope will reflect the results.

Code to instantiate A and retrieve and output its variables scope:




This is perfect behavior for what we're wanting to do! Oh...what is it we are wanting to do? Let's get to the next and last essential concept and see!

THE DECORATOR PATTERN
Here's a term I'm sure most of you have heard of. If you haven't heard specifically of this pattern, I'm sure you've at least heard of design patterns in general. Well, you can and should think of this one exactly as its name sounds. Let's say you figure out that the fastest way to meet chicks is to buy a dog (since you can't afford to buy a baby), so you grab a "mildly malformed" West Highland White terrier from the "we finance anyone" pet store down the street. You invest a few more bucks and take little Timmay to the groomer. When you pick him up later, he is now sporting a lovely red tartan neckerchief and smells of Eau de Tim McGraw. Timmay has been decorated! He's still a midly malformed Westie, only NOW he ALSO has been given the ability to attract hot chicks, AFTER he was born. I know, it was an elaborate analogy, but this is how I entertain myself. So then...since in our mind's eye we want our Config.CFC to be beautifully simple for the end user to populate and we don't want them to have to ignore (and try not to touch) lots of other support methods or worry about inheritance dependencies, what we ideally need to be able to do is provide them a clean, simple CFC that contains only a configure method. When they populate it (or code it to grab its values from elsewhere), we'll take it and then DECORATE it with a method that will allow us to retrieve that same configuration info! Okay, enough of concepts and ideas. On to the solution.

THE SOLUTION
The key players in this scene are:
"ShoppingCart.cfc", a plugin designed to provide shopping cart functionality to any application;
"Config.cfc", a cfc whose sole purpose in life is to provide the developer with a single place in which to maintain relevant ShoppingCart settings;
"configTest.cfm", a cheesy template who just kinda glues this stuff together for us.

First, configTest.cfm.


Straightforward. We're creating our shoppingcart cfc, calling the load method and passing in the class path to our configuration cfc. Then, calling shopping cart's getSettings method just to prove to ourselves that it worked.

Config.cfc.


This is a "hard coded" version, where all the settings are just there. You'll notice at least one of them taking advantage of a PRIME reason to do configuration this way: dynamic values!

ShoppingCart.cfc (scaled down to ONLY the relevant methods needed to illustrate the point of this blog post).


You'll want to study this one a bit, and let me point out a few things.

The last method, "_getConfig", serves no other purpose than to decorate our configuration object. Now, you may be thinking, "but I could just put that method inside my configuration object and tell my users to just ignore it!". Yeah, you could; but that's cheesy. So, to avoid smelling of aged Camembert, we hide this method here and add it to our configuration object within the "load" method.

Here's the results of calling configTest.cfm:


SUMMARY
In a nutshell, because an object always sports a THIS scope, AND because local functions always exist in the VARIABLES scope, it is a simple matter to attach a new method to an object. If the method you are attaching is written correctly, it can access the methods and values within the object that all of the native methods can. By leveraging this very cool relationship, you can move configuration settings to a CFC.


POST SUMMARY
You may also be thinking, "Dude, I or someone else I don't trust could wreak so much havoc in my code by decorating objects with well-written methods!". And dude, I do believe you're probably correct. I haven't researched much into the security ramifications, or how many ways one might find to defile the sacraments of "clean OO"; but, I know that with CF comes great power, and with great...oh, you know. We've chosen the more lenient world of CF, so it's up to us to respect those freedoms and work with them accordingly. If you don't feel good about that, well, there's always the more communistic "typed" languages, with their greater complexities and unbending rules. :) Hey, some people prefer domination ;) To each his own.

I found myself needing to update a MySQL table today with a calculated date, so thought I'd blog the sql in case it saves someone else some time later.

The Scenario

You have a table that contains a date field, but you need to know the date of the Thursday following that date. In this query, the number 5 represents the 5th day of the week (sunday=1, Monday=2, etc.), so if your scenario is looking for a different day of the week, just substitute your day's number everywhere you see the number 5 occurring in mine. The number 12 in the query below is really 7+5, so again, substitute your day's number for 5 in the equation 7+5 and plug in the result where you see 12. If you're looking for Saturday (day 7), you would put a 14 in place of my 12.

The Query 

To update an existing field in the table with the calculated date of the following Thursday, I used a modified version of the above query that looks like this:

I spent two days waiting to see if anybody would respond to my call for "code donations" regarding adding real security to canvasWiki, but no code came flying my way. Of course, in that time I could have just written my own, but I was being lazy. I did end up having to do it myself, though, so thought I'd share in case it saves someone else time later.

My security is based on a user having n roles. Since canvas is hard coded for sysop, admin, user, and all (via the "RoleList" parameter in the CanvasConfig bean in Coldspring.xml) those are the roles I used as well.

Table scripts (mysql):
user table

CREATE TABLE `user` (
`userid` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(35) NOT NULL,
`lastname` varchar(35) NOT NULL,
`username` varchar(35) NOT NULL,
`password` varchar(35) NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `user` (`userid`, `firstname`, `lastname`, `username`, `password`) VALUES
(1, 'doug', 'boude', 'dougboude', 'mamamiarocks'),
(2, 'suzy', 'queue', 'squeue', 'ihearthuckabees');

 

 

 roles table

 

 

 junction table

 

 

 MY version of the model/UserRecord.cfc's "login" method: 

 (note: Notice my sweet usage of MySQL's awesome "GROUP_CONCAT" function! Turns that field's values into a list...EXACTLY what we need!)

Besides having the above, you also have to set all of the security type parameters in the Canvasconfig bean of Coldspring.xml appropriately to enforce security. They're self-explanatory.

That's it!

Beyond that, I did make a minor tweek to views/dsp.navigation.cfm and views/layout.main.cfm in order to display my user's name and to make the logout link more prominent.

dsp.navigation.cfm change:

just after line 20 ("<div id="navcontainer">"), added this short if statement:

 layout.main.cfm
Just after line 31 ("<a href="#webpath#/index.cfm">#appTitle#</a>"), added this short if statement:
 

 

In setting up a new IIS7/Windows 2008 server, I had a need to automate the backup of my MySQL database and so pieced together a little batch file that I then scheduled via the Task Scheduler. The file first removes all backup files that are more than a week old, then performs a sql dump and finally zips the dump. Files are named according to the date and time they were created. Anyway, it took me quite a while to find all the pieces and parts I needed, so thought I'd post it here as a starting point for others who may be wanting to do the same.

Oh, one prerequisite for this particular script to function: You have to have a copy of winzip installed (I have version 12) WITH the optional command line utility, which is a separate download (http://www.winzip.com/downcl.htm). Hey, the whole deal costs $29 bucks...spring for it!

MySQLBackup.BAT

Since I do a lot of my development work on my laptop, and since the thought of having services running and consuming my precious memory that are NOT necessary all the time bugs the bajeebies out of me, and since I do development in CF and .NET on my machine (and use different versions of SQL server for each), I wrote some batch files to start and stop the services required for each platform. Some others might find them useful (at least as starting points), so I thought I'd share them here.

Batch file to start/stop CF with SQL Server:

CFDev.bat

@echo off
IF [%1]==[ON] GOTO START
IF [%1]==[OFF] GOTO STOP
:START
 net start "ColdFusion 8 ODBC Agent"
 net start "ColdFusion 8 ODBC Server"
 net start "ColdFusion 8 Application Server"
 CALL SQL2005.bat %1
 @echo "CF Started!"
 pause
 GOTO :EOF
:STOP
 net stop "ColdFusion 8 ODBC Agent"
 net stop "ColdFusion 8 ODBC Server"
 net stop "ColdFusion 8 Application Server"
 CALL SQL2005.bat %1
 @echo "CF Stopped!"
 pause
 GOTO :EOF
:EOF

(the above batch file calls SQL2005.bat...)
SQL2005.bat

@echo off
IF [%1]==[ON] GOTO START
IF [%1]==[OFF] GOTO STOP
:START
 CALL SQL2008.bat OFF
 net start "SQL Server (SQLE)"
 net start "SQL Server Browser"
 net start "SQL Server VSS Writer"
 GOTO :EOF
:STOP
 net stop "SQL Server (SQLE)"
 net stop "SQL Server Browser"
 net stop "SQL Server VSS Writer"
 GOTO :EOF
:EOF

I have two shortcuts on my desktop, one titled "Start CF" the other titled "Stop CF", with these properties, respectively:
C:\CFDev.bat ON
C:\CFDev.bat OFF

For my .NET stuff, SQL 2008 is required. Here are my batch files for those required services:

dotNetDev.bat

@echo off
IF [%1]==[ON] GOTO START
IF [%1]==[OFF] GOTO STOP
:START
 net start ".NET Runtime Optimization Service v2.0.50727_X86"
 CALL SQL2008.bat %1
 @echo ".NET Ready!"
 pause
 GOTO :EOF
:STOP
 net stop ".NET Runtime Optimization Service v2.0.50727_X86"
 CALL SQL2008.bat %1
 @echo ".NET Stopped!"
 pause
 GOTO :EOF
:EOF

(the above batch file calls SQL2008.bat)
SQL2008.bat

@echo off
IF [%1]==[ON] GOTO START
IF [%1]==[OFF] GOTO STOP
:START
 CALL SQL2005.bat OFF
 net start "SQL Server (SQLEXPRESS)"
 @echo "SQL 2008 Started"
 pause
 GOTO :EOF
:STOP
 net stop "SQL Server (SQLEXPRESS)"
 @echo "SQL 2008 Stopped"
 pause
 GOTO :EOF
:EOF

Again, I have a "Start DotNet" and "Stop DotNet" shortcut on my desktop with the following properties:
C:\dotNetDev.bat ON
C:\dotNetDev.bat OFF

That's all folks!

This is nothing revolutionary, but since it was new to me and it DID take me far longer finding the solution via Googling than it should have, I'm posting it here for my own and others' reference.

Let's say you need to do some search and replace of a specified email address within a BUTT LOAD of PHP files you inherited from your predecessor, but you don't want to have to download the whole can of worms via FTP, only the relevant files (I know, I know: why did my predecessor hard code the email address all over the place?). Anyway, this next snippet will give you back a list of all PHP files containing the text specified:

 

The results will look something like this:

 

Told ya, nothing special about this post! :)

Doug out.

For those PHPers out there who are doing an export to Excel, I thought I'd share the solution I came up with. I realize there are already a gabillion examples out there, but I merged some of the better approaches from a few of them that made it fairly elegant, I think (such as leveraging the implode, array_keys, and array_values functions).

Without further adieux...

 

 

I'm blogging this little snippet mostly so that I have a place to find it the next time I need it, but perhaps it'll come in handy for someone else as well.

I'm creating some Javascript function calls on the fly as I output some query results. One of the parameters in the JS function is the value of an item's title which may at times contain characters JS tends to barf on, such as the single quote: '

In order to automatically escape such characters as I create my JS call, I used CF's ReReplace function. This function utilized a regular expression in order to do a search and replace, so I just created a simple regex that contained a list of all the characters i wanted to be automatically escaped.

Snippet time.

The specific code that performs the replacement:

ReReplace(Ucase(videotitle),"(['|.|;|?])","\\\1","all")

Distilled version of the output code:

<cfoutput query="qryVideos">

<a href="##" onclick="playVideo('#videoID#','#ReReplace(Ucase(videotitle),"(['|.|;|?])","\\\1","all")#','#vidPathRoot#/#videoPath#');return false;">watch this video</a>

<br>

</cfoutput>

My scenario:

I'm using Scriptaculous and CF8 to create a "stacked" PDF based on a user's re-ordering of a list of uploaded files (you can check out the live app here). In order to do this simply, I'm making the file name part of the sortable list item's ID value, like so: ID="file~#filename#" . Well, Scriptaculous has a built in way of dealing with sortable item ID values, where it's looking for some text followed by an underscore followed by a numeric value. In order to break away from this assumption, the latest version of Scriptaculous (1.8.2) offers an option called "format" that allows you to specify a regular expression to override the default one.

So, here's my code that creates the unordered list of files:

and here's the JS I use to turn this list into a sortable:

Executing the following JS will show you that the regex is correct:

Not hard stuff, but it took me a while to piece it all together so I thought I'd share in case it saves someone else a little time.

I've been doing some research on i18n, locales, timezones, and all that jazz lately. Today I wanted to build a dropdown list of possible timezones, and came across a bit of Java code that I converted to CFSCRIPT. Thought I'd share it in case anybody else finds it useful. I will say that there are a LOT more timezones than I would have ever guessed (592 to be exact), and the results of my snippet probably aren't useful as-is to populate a dropdown; but, at least you do have a way to access what your CF server's JVM knows about, and you can filter it as you like.

Oh, I ran this code under CF8; not sure what it does under earlier versions.

The Code:

screenshot of the output:

 

A while back I had a project that required the submit button of the login form to be disabled unless both the username field AND the password field had values. There are probably several ways to skin that cat, but my good friend Boyan Kostadinov offered a more elegant solution (he's always good for the most elegant approach!), and I thought I'd share it with whoever else may benefit.

This solution uses the Prototype library (you need version 1.6). It also uses a small "login.js" file that Boyan wrote which looks for anything with the class "enableOnEntry" (the key to making this work) and then cycles through the form elements within that class, adding their IDs to an array (actually a delimited list, but it is split into an array later). Boyan then binds event listeners to each of the form fields which, while typing in values, will check to see if the submit button should be enabled or not. Anyway, you can read through the JS file if you like, but here's how to implement it, along with a working demo in this post:

1. acquire and reference the following js files in your template (in this order):
    prototype.js
    login.js
2. ensure that your form tag has the class "enableOnEntry";
3. ensure that your form has exactly one submit button (of type 'submit', not 'button');

That's it!

Working Demo:

For your convenience, here is the code used to create the working sample above:

 and here are links to the js files:

 

 prototype.js

login.js

Hope it helps.

The Scenario
You've created a web site that is secured, but you want the search engine spiders to be able to crawl the content. Okay, in this case it's not secured in the sense of needing a username and password, but is secured by requiring that the visitor first acknowledge some terms and conditions before they can access any other portion of the site. This initial requirement of a button click seems to stop all web spiders in their tracks, and thus your site's content never gets added to that search engine's indexes. 

I posted a question regarding this situation on HouseofFusion and did get one interesting answer, but it didn't really address my dilemma. The answer I received was that it is possible to configure Google Analytics to be able to log in to your site in its efforts to index content, but in investigating this further I found that it is only for the purposes of serving ads contextually, not search engine results. Since this site isn't serving any ads and my goal is to help people find their way to the site's front door based on the content BEHIND the door, I needed another answer. Here's what I ended up doing:

In my application.cfc, I have code in the onRequestStart method that checks to see if the user had already acknowledged the disclaimer (by looking at a session variable set to 'true' when they do). If true, allow the original request to go through; if false, redirect to the disclaimer.  I then created an additional, private method in my application.cfc that I called "isSpider" that checks the cgi.http_user_agent against a list of known spider agents, returning either true or false. So, before I check my session variable's value, I first call the isSpider method. If the visitor IS a spider, I set the session variable to true before I do the redirection check against it. Here are the relevant methods:

 

 

 

 

In my onApplicationStart, I create the string of partial spider user agent values:

 

It is true that there are literally hundreds of other spiders running around out there, but I chose to select only the top six that show up in my site analytics as being the ones most people find my other sites by rather than attempt to validate all possible indexers. I also opted to simply check the user agent for any occurrence of a specific substring rather than match against the entire string, for efficiency's sake, since each particular search engine can have several different user agents (and those could change at any time!). For instance, Google has (to the best of my knowledge) the following User Agent values for its spiders:

• Googlebot-Image/1.0 ( http://www.googlebot.com/bot.html)
• Googlebot/2.1 ( http://www.google.com/bot.html)
• Googlebot/2.1 ( http://www.googlebot.com/bot.html)
• Googlebot/Test ( http://www.googlebot.com/bot.html)

Hence, my choice to simply search the user agent for the string "Googlebot" in order to determine if it was a Google spider or not.

I found what appears to be a VERY comprehensive list of spider user agent values (and other metadata) at this url: http://www.user-agents.org/index.shtml . They also offer RSS and XML feeds if anybody wants to do something really cool with the data.

I also used the following spider simulation site in order to test my code changes: http://tools.summitmedia.co.uk/spider/
Their user agent value looks like the following: "K2-Summit (+http://tools.summitmedia.co.uk/spider/) leond@summitmedia.co.uk" , so I just added the value "K2-Summit" to my spiderlist variable in order to let them bypass the disclaimer acknowledgement.

Though the site I based this post on doesn't require username and password authentication, I do believe it would be a simple matter to apply the same principle to a site secured in that manner; when a known spider arrives (one that YOU want crawling your site), simply issue them a visitors pass in the form of manually set credentials and let them do their job!
 
I am by no means a search engine guru, so if anybody out there knows a better way, sees any gaping, dangerous holes in my solution, or just has any suggestions or comments, please do share!

Doug out.

Fusioncharts, as of version  3, exposed via Javascript a method called saveAsImage(), which is what one uses when one wishes to convert the native Flash object to a JPG. I recently finished up a project where I had to do that very thing, and ran in to one little (or not so little) hiccup that perplexed me for quite some time. Following are the scenario, symptoms, and the solution I found.

After the user clicks your "print" button on the display page, a new window is spawned in which we recreate and render our charts one at a time (The way I'm converting all of the charts into one printable page is a little convoluted, but you can read the dirty details of THAT in this post). After an individual chart is rendered, we use Javascript to call that chart's "saveAsimage()" method. This method transforms the Flash object into jpg binary data and metadata, and submits the results to a backend for transformation into an actual jpg. What we want to focus on in this post, however, is the Javascript that renders the chart and calls the saveAsImage() method, because it is within this small section of code execution, oh Best Beloved, where the symptoms are created and later manifested after JPG creation.

The symptoms:
Pie charts which are transformed to images are missing some of their labels!

Though I can see all of the labels just fine when the Flash object is rendered, the final image is missing one or more of those labels! Not cool.

Take a gander at this Javascript snippet:

I'm using Coldfusion for my web pages (thus the seemingly foreign tag '<cfoutput>').

 

I arrive at my page with the chartxml and other needed parameters nested in the equivalent of a hash object;

I transform it using Coldfusion to a JSON format that Javascript natively can read;

on page load, I  then create a new FusionCharts object named 'drilldownchart' and render it within a pre-existing div named 'printchart'.

The function "FC_Rendered" is a FusionCharts method that exists to allow one to execute code AFTER a chart has completely rendered. In my case, once the chart exists I want to immediately start the process of converting it to an image via the saveAsImage() method. Oh, and I'm using the Prototype framework as well, thus the utility function "$('drilldownchart')". It's the equivalent of saying "document.getElementById('drilldownchart')" for those who aren't familiar with Prototype.

So, the code executes flawlessly and I'm happy with it. Until I see the final result.
Where is my label???

I first thought that perhaps it was a font issue, converting from Flash to JPG, so I experimented by changing the label style to different sizes and font faces; no dice. I then asked myself the question, "what if the conversion is happening just before that label actually gets rendered?". Theoretically, this should NOT be possible since I'm using FusionCharts' built in FC_Rendered event method. But just to be safe, let me modify my Javascript as follows in order to add a slight delay....

By adding a setTimeout call in between the render completion and the saveAsImage() call, my final result came out exactly like it should have. You'll note that I even set the timeout delay to ZERO MILLISECONDS, so just the meager amount of time it took to even call the setTimeout method was enough time to allow the capture of all the metadata from the Flash chart.

 

Now my image looks like it should, with all labels intact. Aaaaah!

There you have it!

Doug out.

This post is for others who are using FusionCharts/FusionMaps in their web development (if you're not, this post is PAINfully long and could be deemed a waste of your time to read). FusionCharts/Maps is an amazing product for the price, and does some very very cool stuff when it comes to charting and creating thematic, interactive maps. It does however present some unique challenges when it comes to actually PRINTING these items, due to the fact that every Map and Chart is an embedded Flash object.

I have recently had to tackle this very problem and having come up with what I believe to be a workable (though not totally ideal) solution, thought I'd share it with whoever finds it useful.

(Consider hiring Doug for your Fusioncharts/Fusionmaps project!)

Here's the scenario...
I have a data details page that provides a lot of data tables and such, as well as a dropdown of the available charts. When a chart is selected from the dropdown, I grab the corresponding Chart XML from a Javascript Associative Array and feed it to a newly created FusionChart object which I then render within my target div. That part works beautifully!

some screenshots...

A bit of the data display page the user sees...

An example of the kind of charts FusionCharts produces...

 

 And yet another chart example...

Feel free to play around with the live app. To access the functionality specifically being talked about in this post, you will go to the search page (where you will see a map rendered) and then click on any state to drilldown to that data. The main Print button may not be wired in, but if not, there should be one at the very bottom of the drilldown output that you can click to fire off the process.

Printing this page out is another matter, because the charts being rendered are embedded Flash objects, and most browsers will NOT include an embedded Flash object when in the printed output. What needs to happen then is that, on print, I must convert all of my Flash chart objects to images first and output them within the page to be printed. So far so good, right?

Here comes the first fiery hoop to jump through. The nice folks at Fusioncharts provided a print mechanism that functions like this: The user or some interactive JS executes the rendered chart's 'saveImageAs()' method; this method gathers up all of the rendered chart's bitmap data, packs it all up into nice neat form variables, and submits it to the URL you provided in the Chart XML's "printURL" variable. The target URL must include the needed backend code to take the incoming bitmap data and transform it into JPG binary data. Once you have the JPG as binary, you can actually DO something with it!

example of the JS it takes to create a chart and convert it...

<script>
 //here's our chart xml in json form...
 var jschart = <cfoutput>#serializejson(chartxml)#</cfoutput>;
 function makechart(chartid){
  var myChart = new FusionCharts("/FusionCharts/pie2D.swf", "drilldownchart", "420", "420", "0", "1");
  myChart.setDataXML(jschart[chartid]);
  myChart.render("printchart");  
 }
 function FC_Rendered(DOMId){
  drilldownchart.saveAsImage();
 }
</script>

 

Doesn't sound like much of an issue, does it? Except for the fact that I have n number of charts that I need to convert to JPG, and the ONLY way to do that is to first render the chart and then call it's 'saveImageAs()' method. Each call of this method changes the current window's location to the new URL, thus forcing the user to leave the page they want to print and then watch as each chart is converted. Now, I personally was able to compromise on the fact that the user would have to watch the charts be converted (it's kind of a cool looking process anyway), but I was NOT okay with them being forced to leave their current page. A WHOLE lot of Googling and searches/messages on the FusionCharts forum proved completely unfruitful, and in fact I only found many other people who were facing my exact dilemma and had found no acceptable solution.

I finally came up with what I felt was probably the most elegant solution to be had given the firm constraints of FusionCharts (at least as of version 3.0.5). My solution is built using Coldfusion, with ModelGlue:Unity as the framework, but I do believe that the basic approach can be molded to fit any language/framework; I'll try to keep the walk-through as generic as possible.

Click this image for the full size diagram...

The user arrives at the data detail page and is shown a lot of tables and other data summaries, with a dropdown of available charts. As described above, selecting a chart leverages JS to grab the appropriate set of FusionCharts XML from an associative array and render that chart in the target div. A print button is provided on the page. Clicking the print button submits the following form, all of which is hidden:

 Here is an example of the UNserialized chart XML data in a CF structure:

 

 

 

 

 

 

 The submission of this form spawns a new window (target='_blank'), so there's the new window that I wanted for the user. Now, within the new window we begin a dance between the target template and the serverside code/backend that is performing the work. Between the two of them I manage a self-calling loop that:

1. renders a single individual chart;
2. calls the completed chart's 'saveAsImage()' method;
3. reloads the same page again, this time loading up the next chart and repeating steps 1 and 2.
4. when the last chart has been processed, I switch to the second portion of this processing template which is the final output of all printable data AND the charts which have now been converted to JPEGs.

Because I KNOW that anybody who has attempted to do this very thing is going to be interested, let's look at some of the details of how this template/server loop works. We must begin with the hidden form that we submitted from the initial display page.

THE FORM
endimagenumber: this value represents the total number of charts that will need to be rendered, and will serve as a marker to tell us when we are actually finished processing charts within the funky loop to follow. Because I have my chart XML sets contained in both a JS associative array AND a Coldfusion structure within my template, I can get this number by getting the length of either one.

chartXML: take the Coldfusion structure containing all of the chart XML sets, serialize it to a JSON string, then URL encode that string and voila! You have the value of this field. It of course contains the XML we will use to render our charts in the processing template to come.

fchartjs: this is the full path to the FusionCharts.js file. It is needful to include that library within the processing template. This value could be passed in a number of other ways (perhaps already available to all pages in your app as a global variable, etc.)

currentimagenumber: hard coded to '1', this is the starting point of chart processing. In my array/structure, the keys are sequential numbering, so if I have four charts to render, my structure keys will always be 1,2,3, and 4.

firstcall: this boolean flag tells my processing template that this is the first time it has been called..initial kickoff. It's important for me to know this because there are several setup type actions that need to occur initially.

printbody: this hidden field contains the entire, relevant (to be printed) HTML of the display page. In Coldfusion, it's easy to capture this by using the CFSAVECONTENT tags, which is what I did; in other languages I'm sure it's possible as well. I URLencode the content and pass it on to the processing template.

Okay, the only item left regarding the hidden form is the printURL value. Since I'm using Modelglue, it is in the form "index.cfm?event=processCharts".

Submitting the form to this URL will kick off some backend processing that accepts incoming bitmap data and creates JPEG binary data out of it, as well as some "loop maintenance" work (placing certain values into a persistent/session scope as needed, making sure that needed values are present for the actual template that will be loaded after this backend processing has completed, etc.). After the backend processing is finished, the target template is loaded (a .CFM page in my case). This page is essentially divided in half, with the top half performing chart rendering and saveImageAs() calls, and the bottom half outputting printable information and kicking off the 'window.print()' function.

Let's do a pseudo-code type walk through of what happens after the form is submitted...

First, the backend process is called (basically a method invoked on an object). This method receives the incoming bitmap data (completely non existent on the initial call), an assettID value (used for the purpose of keeping our chart JPEG binary data stored in session...we need to know where it is between calls), the current image number, the end image number, and the firstcall boolean flag we mentioned earlier. This method's purpose in life is to:

1. establish the presence of a session substructure to hold onto our JPG binary data if it doesn't already exist;
2. if this is our initial call, grab the 'printBody' value (all of our output, urlencoded) and stuff it into a session variable. We don't want to keep passing such a huge value back and forth, so we'll just hold on to it in session until we know we've completed our last pass. At that point, we'll pass it back to the template.
3. turn the incoming bitmap data into binary data;
4. place the values that will be needed by the template (.CFM page) into the request stream (in the case of ModelGlue, into the Event bucket)

After the backend process is finished, we load up our template (named "PrintChart.cfm" in this case). This template will:

1.  grab the values it needs/expects out of the request stream (Viewstate bucket)
2. determine if it needs to be processing a chart. If so, process the chart (render it, call it's saveAsImage()method, which automatically changes the page location to call the SAME url again, only this time with some bitmap data in hand!).
3. if all charts have been processed, output everything for printing. Once we've reached this point, there are no more calls to the print URL and we're done.

Outputting the JPG Binary Data

At the point of output, all of our charts have been converted to JPG binary data, which itself has been base64 encoded and is sitting snug in a structure within our session scope.  Here's a snapshot of what the finished JPEG encoded binary data looks like sitting in session:

Now, utilizing this data requires that we jump through ONE more little teeny weeny hoop. We need to create a template whose content type is image/jpeg and use the url to THAT template as the src value for our image tags. Take a gander at this short bit of code that actually performs ALL of the output for our printed page:

<cfoutput>
 <cfloop list="#assettIDlist#" index="i">
  Image #i#: <img align="top" height="420" width="420" src="index.cfm?event=getImage&imageid=#i#&assettid=#assettid#" /><br>
 </cfloop>
 #printbody#
</cfoutput>

Notice that in our final iteration, we are making sure we pass back the AssettID and a list of the individual image IDs. We then loop over those image IDs and pass the current value to the dspShowImage.cfm template.  Here is the code for dspShowImage.cfm:

 

<cfset imageid = viewstate.getValue("imageid") />

<cfif imageid IS NOT "0">
 <cfset item = "imagedata_" & imageid />
 <cfset imagecontent = viewstate.getValue(item) />

 <cfheader name="Content-Disposition" value="attachment; filename=""FusionCharts.jpg""">
 <cfcontent type="image/jpeg" variable="#imagecontent#">
<cfelse>
 Image not found
</cfif>

Because we are using Model Glue, there is actually a method that gets executed BEFORE this template renders, whose sole duty is to retrieve (and subsequently delete) the requested jpg binary data and place it into the Request stream. Here is that method (which is part of the FusionMapController.cfc included in the ZIP):

 <cffunction name="getImage" access="public" output="false" returntype="void" hint="I retrieve and put into Event the image byte data requested">
  <cfargument name="event" type="any" required="yes" />
  <cfset var imageid = arguments.event.getValue("imageID") />
  <cfset var assettid = arguments.event.getValue("assettid") />
  <cfset arguments.event.setvalue("got called",true) />
  <cfif assettid IS NOT ""><!--- we're accessing a collection of images within the printassetts structure --->
   <cfif structkeyexists(session.printassetts,assettid)>
    <cfif structkeyexists(session.printassetts[assettid],imageid)>
     <cfset tmpval = session.printAssetts[assettid][imageid] />
     <cfset arguments.event.setValue("imagedata_" & imageid,ToBinary(tmpval)) />
     <cfset structdelete(session.printAssetts[assettid],imageid) />
     <!--- if we just deleted the last item in this assett collection, remove the whole assett collection from printassetts --->
     <cfif structcount(session.printAssetts[assettid]) eq 0>
      <cfset structdelete(session.printAssetts,assettid) />
     </cfif>
    <cfelse>
     <cfset arguments.event.setValue("imagedata_" & imageid,"") />
    </cfif>
   <cfelse>
    <cfset arguments.event.setValue("imagedata_" & imageid,"") />
   </cfif>   
  <cfelse><!--- we were called with nothing but an imageid...it must exist within the printAssetts structure --->
   <cfif structkeyexists(session.printassetts,imageid)>
    <cfset tmpval = session.printAssetts[imageid].image />
    <cfset arguments.event.setValue("imagedata_" & imageid,ToBinary(tmpval)) />
    <cfset structdelete(session.printAssetts,imageid) />
   <cfelse>
    <cfset arguments.event.setValue("imagedata_" & imageid,"") />
   </cfif>
  </cfif>
 </cffunction>

 

DETAILS AND DOWNLOADS

It is a wee bit complicated, I know, but that is the generic walk through of it. Let me now include more visuals/code to be used as a starting point for whoever else has to be able to print out multiple FusionCharts.

A ZIP of all relevant files

Some of those files exposed here for convenience:

The Hidden Form that lives in the initial user display page:

<cfoutput>
<form name="test" id="test" action="#printchartURL#" method="post" target="_blank">
 <input type="hidden" name="endimagenumber" value="#structcount(jsChart)#" />  
 <input type="hidden" name="chartxml" id="chartxml" value="#urlencodedformat(serializeJSON(jsChart))#" />
 <input type="hidden" name="fchartjs" value="#fchartjs#" />
 <input type="hidden" name="currentimagenumber" value="1" />
 <input type="hidden" name="firstcall" value="true" />
 <input type="hidden" name="printbody" value="#urlencodedformat(drilldownhtml)#" />
</form>
</cfoutput>

 

The printChart.cfm template: click here to view code

The image data processing controller method:

The model method that performs the conversion of bitmap data to binary JPG:

 

 

 

 

 

 

Though a "minor" cosmetic issue at times, it can be challenging to come up with creative ways to accommodate what I consider to be IE's shortcomings regarding the control of form items, in particular select lists. With at least one project I'm currently working on, I have a select list that lives in a fixed width div, yet there are times when the text values of the options are wider than the div itself. The client respectfully requested that I find a way to make the full option text display whenever the user clicks on the select list, so I of course referred to my reliable friend Google. After investing a couple of hours exploring different approaches to the challenge, I found none that I could get to meet my needs in both Firefox and IE. Oh, by the way, Firefox AUTOMATICALLY displays the full option text when you drop down without any developer intervention needed.  Do you realize how much development time could be saved if IE worked the same as Firefox??? Anyway, I digress.

The solution I came up with (which I didn't find anywhere in all of my googling) was very simple: when a user clicks the select list, swap out the class to one without width restrictions. When they make a selection, swap the class back to one WITH width restrictions.

I make sure that I set my container div to a fixed width and set the overflow to 'hidden', that way when my select list suddenly grows, it doesn't force the div to widen OR overlap any adjacent divs. Without further adieux then, a simple before and after (bear in mind that if you're viewing this in Firefox, you won't see any problem with the first list; it's only BILL'S BROWSER that has the issue):

I am the problem select list:

This is option 1. This is the second option, a bit longer. Drink to me only with thine eyes, and I will pledge with mine...

---

I am the better select list:

This is option 1. This is the second option, a bit longer. Drink to me only with thine eyes, and I will pledge with mine...

Here is the code for the samples above:

<style>
.ctrDropDown{
    width:145px;
    font-size:11px;
}
.ctrDropDownClick{
    font-size:11px;

    width:300px;

}
.plainDropDown{
    width:145px;
    font-size:11px;
}
</style>
<div style="padding:4px;width:175px;height:75px;border:thin solid black;overflow:hidden;">
I am the problem select list:<br><br>
<select name="someDropDown" id="someDropDown" class="plainDropDown">
    <option value="">This is option 1.</option>
    <option value="">This is the second option, a bit longer.</option>
    <option value="">Drink to me only with thine eyes, and I will pledge with mine...</option>
</select>
</div>
<br>
<hr width="150px;" align="left">
<br>
<div style="padding:4px;width:175px;height:75px;border:thin solid black;overflow:hidden;">
I am the better select list:<br><br>
<select name="someDropDown" id="someDropDown" class="ctrDropDown" onBlur="this.className='ctrDropDown';" onMouseDown="this.className='ctrDropDownClick';" onChange="this.className='ctrDropDown';">
    <option value="">This is option 1.</option>
    <option value="">This is the second option, a bit longer.</option>
    <option value="">Drink to me only with thine eyes, and I will pledge with mine...</option>
</select>
</div>

One More Note:
This solution is a little bit less than perfect in that if you click the select list and don't choose a NEW value, the class doesn't switch back until you actually blur, or click off of, the select list. I have been unable to find a solution to that one anomaly, so if anybody out there has an idea, please do share!

Hope this helps somebody.

Doug out.

Last September I shared a post on an alternative to Ajax for client-side interactivity leveraging Coldfusion's WDDX. I'd like to take it a step further now and share an approach (and corresponding code) I often use in my Model-Glue apps when needing to create tiered or drilldown-type select lists withOUT having to make numerous calls to the server. The gist of this methodology is the same as in my previous post:

1. Retrieve all needed data sets for populating the drilldowns;
2. Convert those CF queries to a form that Javascript can manipulate;
3. Write the necessary functions to populate the dropdowns based on previous options selected.

Here is the working sample. Go ahead, play with it!




The main difference in this example is the fact that I'm not utilizing WDDX, but rather JSON to make the data Javascript friendly.

Let me show you what the queries look like for each tier of this particular drilldown:

Tier 1	Tier 2	Tier 3

An important item of note here is that the data set for a given tier must include the parent ID of the previous tier in order to perform the filtering operations you'll see soon.

Okay, so how to get the queries into a format Javascript can manipulate. The route I chose to go was to convert the queries to JSON via one of two methods, depending on what version of CF you're using. Since not everyone is using version 8 yet, I made this example compatible with anything 6 or higher (versions previous to 8 are dependent on outside conversion; I chose to use JSON.CFC. Version 8 can utilize the built in function "serializeJSON"). Here's the statement where the queries are transformed (using the commonly known custom tag "QuerySim" to create the data for this example):


You'll notice in the section where I rely on JSON.CFC, I am doing a replace on the lower case word "data" to make it upper case. This is to make the JSON string produced consistent with the one produced utilizing serializeJSON. Since Javascript is case sensitive, case consistency in the JSON is required if you wish to utilize only one javascript function to perform the select list population. You'll also notice the use of the optional secondary parameter in the serializeJSON function call. This is needed in order to produce a JSON string that can be accessed by Javascript exactly the same as the JSON.CFC string.

MG NOTE: Regarding the fact that I utilize this approach in Model-Glue apps...I have my controller return an already formatted JSON string to my view rather than return a query and then perform the transformation there. Many of my controller methods have an optional "returnJSON" argument that I use when I need a JSON string back rather than a query.

Okay, data sets are available to Javascript. Now to write a few Javascript functions that can spin through that data and populate the appropriate select list. Here are the functions needed to perform the necessary tasks:



Of note is the fact that we are working with three select lists. The first select list is always populated at load time and needs no Javascript intervention at all. Select lists "level_2" and "level_3", however, are being completely manipulated by the JS calls by one generically written function.

Last but not least, the HTML with the Javascript calls embedded in the select lists' onChange event:


Voila! That's it! instant, client-side drilldown with only a single call to the server!

Hope it saves someone a little time. ;)

Doug out.

Dynamically outputting query data in "X number of columns across" is no new thing, but since I found myself having to do that very thing this morning, I thought I'd share this sweet little snippet for the benefit of everyone (but mostly for my own snippet collection since I seem to tend to "forget what I once new"... lol).

In this example I'm going to output a series of short questions using the following query result:


WIth the final html and result looking like this:

<table width="100%">
    <tr>
        <td>Dead loved ones present</td>
        <td>Animal(s) as prominent player(s)</td>
        <td>"Significant Other" present</td>
    </tr>
    <tr>
        <td>Familiar Setting</td>
        <td>Unfamiliar Setting</td>
        <td>Awoke Emotional</td>
    </tr>
    <tr>
        <td>Dreamed you were Awake</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
    </tr>
</table>

Without further adieux, your snippet:

<cfset numColumns = 3 /><!--- set the number of columns to output --->

<!--- determine number of total rows to output --->
<cfif qryQuestions.recordcount mod numColumns eq 0>
    <cfset numrows = qryQuestions.recordcount/numColumns>
<cfelse>
    <cfset numrows = int(qryQuestions.recordcount/numColumns) + 1>
</cfif>

<!--- output the table! --->
<table width="100%">
    <cfloop from="1" to="#numrows#" index="i">
        <tr>
            <!--- create columns for this row 'i' --->
            <cfloop from="1" to="#numColumns#" index="j">
                <!--- calculate which record we're outputting --->
                <cfset qryRow = ((i-1)*numColumns)+j >
                <!--- ...as long as wer're not trying to access a query record beyond our 'recordcount' value... --->
                <cfif qryRow lte qryQuestions.recordcount>
                    <td><cfoutput>#qryQuestions["question"][qryRow]#</cfoutput></td>
                <cfelse><!--- output an empty 'filler' td... --->
                    <td>&nbsp;</td>
                </cfif>
            </cfloop>
        </tr>
    </cfloop>
</table>

Reactor's built-in validation produces a validationError structure when it encounters something amiss, and passes it back to your viewstate. That structure is actually a structure of arrays.

I DO use Reactor's validation, and found myself copying and pasting the same snippet for outputting the validation error results a time or two this morning... figured it might come in handy for other folks, too.



A couple of things to remember:

1. The "[name of error structure here]" value is determined by the value you gave it in your event



2. When calling the viewstate.getValue() method, you can specify a default value to use if the item isn't found. In my snippet, I specified that "if the validation structure isn't there, just give me back an empty structure so my code won't break".

That's it!

When building applications intended to service multiple clients, the ability to easily customize certain aspects is a must. In the health benefits management arena, this need couldn't be any greater, most notably with regards to terminology and phrasing. Whereas one client may wish to refer to their employees' usage of tobacco as "smoker certification", yet another will insist on calling it "tobacco usage".  In order to accommodate this dynamic requirement, my team and I came up with what we dubbed our "Lexicon" component, so I thought I'd share the basics of it here in case anybody else has a similar need.

The overall concept and approach is this:

• Every term is given a generic name;
• Every term will have a default value with the ability to have customized override values for specific clients;
• Every term is given a language identifier, to accommodate the fact that languages other than english may be needed;
• The component that performs lexicon lookups is made available to every display template in some global scope (application, session, request, viewstate (when using Modelglue), etc.);
• Terms are output inline within the display template via a "getTerm" method call;

Firstly, let's look at the table used to store the lexicon data:

• 'ID' is an autonumbering field;
• 'itemID' contains the generic reference to the term. We utilized a hierarchical naming convention that identified the specific area of the app the term applied to, but any identifier could be used here, including a simple GUID;
• 'ClientID' is either NULL (indicating this is the generic default term to use) or contains the ID of a specific client (indicating that we should use this client's term for this item);
• 'term' is the actual text that will be displayed;
• 'languageID' is the id of a language record from our 'languages' table (a simple list of languages we wanted to support). In our case, 1 = english, 2 = spanish, 4 = rap;

MSSQL script to generate the lexicon table:

CREATE TABLE [dbo].[Lexicon] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [itemID] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [clientID] [int] NULL ,
    [term] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [languageID] [int] NULL
) ON [PRIMARY]
GO

Simple enough, right? So, if we have a title that needs to be displayed within the Third Party Administrator pod (see illustration), we create an itemID of "pod.tpaadmin.title", providing a record for the default wording, one for our client 690 who wasn't happy with our default wording, a default spanish label, and a default rap label.

Now, we need a Lexicon object to perform lookups for us. The relevant method we'll be using ("getTerm") will look for this client's override value first. If it finds it, it will be returned. Otherwise we'll grab the default value. Here's our "getTerm" method:

<CFFUNCTION name="getTerm" returntype="string" access="public" hint="I return the appropriate term for a given itemID" >
    <CFARGUMENT name="termID" required="true" type="string" hint="I am the id of the term we're looking for. Typically this will be either a string or a UUID" />
    <CFARGUMENT name="languageID" type="numeric" required="no" DEFAULT="1" hint="I am the id of the language we want to use. default is 1 for english." />
    <CFARGUMENT name="clientID" required="true" type="number" hint="I am the id of the client associated with this lookup." />
    <CFARGUMENT name="DSN" required="true" type="string" hint="I am the DSN to use for queries." />

    <cfset var local = structnew() />
   
    <!--- look for the client's override value first... --->
    <CFQUERY name="local.getTerm" DATASOURCE="#arguments.DSN#">
        SELECT term
        from lexicon
        where
        clientID = <cfqueryparam value="#arguments.clientID#" CFSQLTYPE="CF_SQL_INTEGER">
        AND
        languageID = <cfqueryparam value="#arguments.languageID#" CFSQLTYPE="CF_SQL_INTEGER">
        AND
        termID = <cfqueryparam value="#arguments.termID#" CFSQLTYPE="CF_SQL_VARCHAR">
    </CFQUERY>
    <cfif local.getTerm.recordcount neq 1><!--- nothing client-specific returned, grab the default term --->
        <CFQUERY name="local.getTerm" DATASOURCE="#arguments.DSN#">
            SELECT term
            from lexicon
            where
            clientID IS NULL
            AND
            languageID = <cfqueryparam value="#arguments.languageID#" CFSQLTYPE="CF_SQL_INTEGER">
            AND
            termID = <cfqueryparam value="#arguments.termID#" CFSQLTYPE="CF_SQL_VARCHAR">
        </CFQUERY>
    </cfif>
    <CFRETURN local.getTerm.term />
</CFFUNCTION>

Last but not least, here's how we utilize the Lexicon object within our display template (using Model Glue's 'Viewstate'...could just as easily be application, session, or request):

<cfset lex = viewstate.getValue("lexicon") />

<span class="contentTitle">
 <cfoutput>#lex.getTerm(termid="landing.pagetitle",clientID="690",DSN="#application.dsn#")#</cfoutput>
</span>
<br />
<span class="subContentTitle">
 <cfoutput>#lex.getTerm(termid="landing.subtitle",clientID="690",DSN="#application.dsn#")#</cfoutput>
</span>

The lexicon also turned out to be very handy for managing the text to display within links. We have a seperate table containing links with generic descriptions, then utilizing the GUID of the link record as the termID, created lexicon entries to control what link text and language was displayed for a given client.

Pretty straightforward, eh?

Doug out.

Disclaimer: The code and samples in this post have been written as simply as possible in order to illustrate "how" the lexicon works. In actuality we utilized Reactor and Coldspring to operate our Lexicon. The reader is encouraged to use these snippets as a starting point, but by all means to come up with more efficient ways of execution, object setup, and architecture for his or her own purposes.

When I first began blogging, I was naive enough to think that porn bots (or whatever they're properly called) wouldn't find me. Wrong. So, I enabled the Captcha that comes built in to BlogCFM (yep, not CFC...I'm a rebel). That immediately thwarted their attempts at decorating my blog posts with colorful solicitations. Then a month or two later, I got a sudden influx of the same spam! So, I swapped out my captcha with something that would require some real thought: a math problem. That stopped them for another couple of months, then lo and behold it happened again. I really have no idea how the spam hackers do it, but it has resulted in yet another evolution in my efforts to stop the spam. So far it's worked solidly, so I thought I'd share it in case anybody else might find it useful.

It consists of a function that generates a question that must be answered, and the answer to that question. It will ask the commenter to figure out what letter is exactly X number of places before or after a randomly selected letter in the alphabet, then directs them to type their answer exactly Y number of times in the answer box. Upon page load the correct answer is saved to a persistent variable, then when the comment is submitted the answer typed (form.answer) is compared to the stored answer (session.answer).  Simple enough I think, but with enough randomness to make it something that can't be automatically breached without some real effort.

Here's the code for the function:

<cffunction access="public" name="genQuestion" output="false" returntype="struct" description="I generate a random question to use as an antispam key">
    <cfset var stReturn = structnew() />
    <cfset var firstnum = randrange(1,4) />
    <cfset var secondnum = randrange(1,4) />
    <cfset var letter = randrange(5,20) />
    <cfset var where = randrange(1,2) />
    <cfset var answer = "" />
    <cfset var i = "" />
    <cfset variables.numbers = "one,two,three,four" />
    <cfset variables.letters = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z" />
    <cfset variables.beforeafter = "before,after" />   
    <CFSET stReturn.times = randrange(1,4) />
    <cfset stReturn.question = "What letter comes " & listgetat(variables.numbers,firstnum) & " places " & listgetat(variables.beforeafter,where) & " the letter " & listgetat(variables.letters,letter) & "?<br>Type your answer exactly " & listgetat(variables.numbers,secondnum) & " times in the box below." />
    <cfif where eq 1>
        <cfset letter = letter - firstnum />
    <cfelse>
        <cfset letter = letter + firstnum />
    </cfif>
    <cfloop index="i" from="1" to="#secondnum#" >
        <cfset answer = answer & listgetat(variables.letters,letter)/>
    </cfloop>
    <cfset stReturn.answer = answer />
    <cfreturn stReturn />
</cffunction>

(the function returns a structure containing the keys "question" and "answer")

Here's some starter code for utilizing it:

Code to evaluate saving a comment...

<cfif isDefined("saveComment")>
    <cfif form.answer neq session.answer>
        <cfset errorMessage = errorMessage & "<li>Invalid anti-spam key.  Please try again.</li>#Chr(10)#">
    <cfelse>
        <!--- perform comment saving here --->
    </cfif>
</cfif>

Code for displaying question and capturing answer:

<cfset variables.spamquestion = genQuestion()>

<h3>Please answer the following question:</h3>

<CFOUTPUT><STRONG>#variables.spamquestion.question#</STRONG></CFOUTPUT><br>
Type in the answer to the question you see above:
<input type="text" name="answer" size="6" maxlength="6" required="Yes" Message="You must complete the anti-spam field.">

<!--- save the answer to session for evaluation after the form is submitted... --->
<cfset session.answer = variables.spamquestion.answer>

Hope it helps!   :)

EgoSurf...I'm not really sure what to make of it, but it's an interesting use of asynchronous processing, to say the least. Anyway if you haven't heard of it, it's a site that will do deep searches of various search engines for you and your associated web site, then score you based on the results. I thought it was interesting enough to put a formette in my side nav, so thought I'd share the code in case anybody else was interested in doing something similar. It took a little time to dig through and find the hidden fields and what values are expected, etc., so hopefully this will save someone some time. Without further adieux, here it is in all its splendor:

I'm doing this post because the term 'JSON' has continued to appear here and there within blog posts, conference sessions, articles, and emails that I consume as part of my professional growth regimen. Despite the fact that the term is so very often mentioned casually as if everybody has known about it since Kindergarten, the greater part of my understanding of JSON is barren except for the few clues I have managed to glean through context. So, I decided to take the time to get to know JSON a little more intimately, and learned some interesting things.

WHAT I THOUGHT IT WAS
Based solely on the info I managed to gather from "between the lines", I knew that JSON stood for JavaScript Object Notation, and that it was an alternative to XML when dealing with the results of Ajax calls. Knowing that much, I could deduce that it was basically "data in a string". But what it looked like, how to handle it, and why I would want to do it were still questions in my mind.

WHAT I FOUND IT TO BE
JSON is indeed a string representation of simple or complex data, just as is XML, only without the tags AND without the need to treat that string as a document type in order to transverse it elegantly. JSON contains two indicators: curly braces to indicate that a structure follows, and square brackets to indicate an array. Here's a structure written in JSON: {key1:"val1",key2:"val2",key3:"val3"} and a one dimensional array: ["val1","val2","val3"]. You can nest these types within one another, too; for example, the value of a structure key could be an array, and would be written as this: {key1:["val1","val2","val3"],key2:"val2"}. Pretty cool, eh?

Now, how to get data back and forth between JSON and raw CF types. It's a no-brainer using a tag made available as an open source project on RIA Forge by Andrew Powell: CFJSON . I downloaded it and was using it in less than two minutes on a test template. Very simple, very easy.

All of this good information led me to pose more questions, which I went on to get answers to.

• Were there any performance advantages of using JSON over XML?
• Was there significant differences in the length of the string produced by a JSON conversion as opposed to an XML conversion (significant in the realm of web services where that string will have to be sent over the wire)?
• Were there any compelling reasons to adopt JSON in leiu of XML or WDDX?

Following are the results of me finding out the answers to those questions.

THE EXPERIMENT
Working with a 500 row recordset, each row containing 52 fields of mixed data types, including some sql text, convert it to both JSON and XML and record the results of the time it took and the resulting string length. Do this in both a local template/cfc environment AND over the wire via a web service. I used CFJSON as the JSON converter, and Ray Camden's XML.cfc as the XML converter.

THE RESULTS

LOCAL TESTS
1 JSON XML String Size 539906 1250561 Time (ms) 36374 2046	2 JSON XML String Size 539906 1250561 Time (ms) 36156 2640	3 JSON XML String Size 539906 1250561 Time (ms) 37103 2156

WEB SERVICE TESTS
1 JSON XML String Size 539906 1250561 Time (ms) 36581 2390	2 JSON XML String Size 539906 1250561 Time (ms) 37832 2496	3 JSON XML String Size 539906 1250561 Time (ms) 37644 2406

The string size was consistent, as expected. JSON produced a string that was 57% smaller than the same data represented as XML. Even so, the actual difference in the time it takes to send the larger string doesn't even come close to compensating for the additional time it took to produce the shorter JSON string. It took 1,370% LONGER to produce the JSON string than it did the XML string. Wow, at this point it's almost a no-brainer that I would NOT want to use JSON for much if anything. But this huge difference got me to thinking: What was so vastly different about the way the XML was being produced and the way the JSON was being produced? So I dug into the CFCs to find out.

THE DIFFERENCE
Both CFC methods make use of lots and lots of looping and string concatenation. The only glaring difference was in the way this was done. XML.CFC leverages the Java.lang.stringbuffer object and CFJSON uses straight CF string manipulation. So, I altered the portion of the method in CFJSON.CFC that converts queries to utilize the java.lang.stringbuffer object as well. The results were VERY favorable! check out these time tests after I made the switch:

CFJSON.CFC TESTS AFTER STRINGBUFFER CONVERSION
TEST	LOCAL TIME(ms)	WEB SERVICE TIME(ms)
1	2156	4437
2	2125	3843
3	2031	4022

Wow. The conversion to JSON now runs neck in neck with the XML conversion, simply by leveraging the stringbuffer object. Sweeeeet.

Okay, now that I know that JSON data strings are significantly smaller, just as fast to create, how about manipulation? How painful is that? To find out I decided I was going to use my JSON data in a javascript function. I scaled down the size of the data set to only five records and a handful of fields for this test. Check out how simple it was to 'dump' my data into javascript and to access it afterwards:

<script>
    function showMeSomeJSON(){
        var objQuery = <CFOUTPUT>#jsondata#</CFOUTPUT>;
        var i = 0;
        for(i=0;i<objQuery.recordcount;++i){
            alert(objQuery.data.due_date[i]);
        }
    }
</script>

CONCLUSION
I have come to no solid conclusion as of yet, but I must say that I'm leaning heavily towards JSON in lieu of XML. It's got a smaller footprint all the way around, has the ability to capture complex nesting, and is really straightforward to navigate using Javascript. Additionally, the same way CFJSON can encode complex data, it also DECODES it on the receiving end, turning it back into something that CF recognizes.

Oh, and I think I'm going to email the CFJSON guys and let them know about the significant performance improvement I got when switching to stringbuffer. They may have already experimented in this arena, but just in case, I'll let them know.

That's it for my personal "JSON Demystification". :)

Doug out.

P.S. If you would like to see an actual JSON representation of my five record query, here it is:

{"recordcount":5,"columnlist":"complete,date_desired,dept,details,developer,due_date,est_hours","data":{"complete":["100%","100%","0%","100%","0%"],"date_desired":["Less Than 2 weeks","Less Than 1 week","More Than 2 weeks","Less Than 1 week","Less Than 1 week"],"dept":["Eligibility","Marketing","Benplan_com","Repricing","EDI"],"details":["1. For rehires, if a rehire date is present on the file and the rehire date is greater than the date of hire, then I need to use it.\r\n\r\n2. Cobra reason - make sure the term reason for a benefit is being put on the AE2 file. This is tied in with Scott\'s work.\r\n\r\n3. Some dependents have termed benefits but no effective dates, also no effective date for the employee. I need to find out where these are coming from.\r\n\r\n4. Terms need to be removed from the file 60 days after the employment term date. This will actually be added to the TSS AE2 file as well.","Dave Lawson, SWI, would like for us to send him the annual report we created for him that gives him EE and Dependent data for Group #s 90947 and 90947P.","- We will send?? or receive daily RX claims\/deductible file updates to one or two PBM\'s--Innoviant for sure and maybe Catalyst.\r\n"," 1) Show correct network for each group.  2) Change elapsed days to start counting on the day the claim was sent out. ","Correct the SSN validation on inbound EDI returned repriced claims. "],"developer":["Dan Crouch","Kelly Young","Gemma Anthony","Kelly Billen","Kelly Billen"],"due_date":["09\/09\/2004","8\/19\/04","NA","08\/30\/2004","08\/13\/2004"],"est_hours":["",1,400,1,1 ]}}

To anyone who has not yet breached the subject of model-glue event security, it can potentially be confusing at first, so I thought I'd share my approach to it in case it helps save someone a little time.

Understanding and being able to visualize the life-cycle of a Model-Glue event is a prerequisite to really grasping event security, so let me share my take on what a brief overview of that life-cycle is.

1. A request is made, notifying model-glue to execute a specific event (eg; http://www.somesite.com/?event=fireinthehole )
2. MG looks in its modelglue.xml file to find out what messages to broadcast to listening controllers ( <broadcast><message name="blowitup" /></broadcast> ) for the 'fireinthehole' event
3. controllers listening for the "blowitup" message execute their corresponding functions ( <controller name="bombController" type="controller.eodGuy"><message-listener message="blowitup" function="BlowInPlace" /></controller> )
4. MG executes any relevant result actions (acts as an 'if' statement almost) if they exist
5. MG renders any views that are defined for this event
6. The event lifecycle is over.

So, now that we know the flow of a named event within Model-Glue, it's time to add in a security check to make sure the current user has permission to execute that event. In my scenarios typically I have private and public events (those that can be executed without being logged in (such as the 'login' event itself), and those that require previous authentication (such as 'manageAccount')) and events requiring a specific role (such as viewing billing reports).
What we will effectively do is slide in some functionality between the event request and the execution of the event itself by leveraging OnRequestStart. This functionality will either allow the named event to pass on through OR redirect the user to the event we want them to arrive at. For instance, if they attempt to access an event that requires login and we intercept that event, we'll redirect them to the login page.

Here's the process for putting the named event check functionality in place:

1.Create a function that checks the current event name against a given list of event names;
2.Register that function to be called at 'onRequestStart';
3.In the Modelglue.xml file, create an event called "modelglue.OnRequestStart";
4.Within the modelglue.OnRequestStart event, register named results and provide appropriate redirection values;

What's going to happen then during the event life-cycle is that, before the actual named event executes, any controllers listening for the 'onRequestStart' message will execute their functions. One of those functions will have the sole job of verifying that the event being requested is allowed to be called in the current session state (logged in, not logged in, is an admin, etc.) If the event is good to go, the function is finished. If the event should NOT be allowed to execute, the function will set a model-glue named result. Next, since we defined an event called modelglue.OnRequestStart, that event is evaluated before any named events. The only thing we have defined for it to do is to look for specific named results and if one of them is found, perform the appropriate redirection. If none of the results being watched for are present, the named event executes normally.

I know at this point there must be a lot of questions on how to actually implement what I've been describing at a high level, so here are the same steps with snippets you can use:

1.Create a function that checks the current event name against a given list of event names;
In our scenario, let's assume two things: that we have events we want to require security, and events that we want to require that the person logged in also be an administrator.

<CFFUNCTION name="checkEvent" access="public" returntype="void" output="false">
    <CFARGUMENT name="event" type="any">
    <CFSET var eventname = arguments.event.getValue(arguments.event.getValue("eventValue")) />
    <CFSET var user = arguments.event.getValue("currentUserObject") />
    <CFIF not user.getUserID() and not listFindNoCase("login,signup,forgotpassword,sendpassword", eventname)>
        <!--- if we aren't logged in AND the event we're calling is NOT a public event... --->
        <CFSET arguments.event.addResult("LoginNeeded") />
    <cfelse><!--- we are logged in. If this event is in our list of events requiring admin login, check to see our user is an admin. if not, redirect them to home. --->
        <cfif listFindNoCase("admin.home,admin.billing,admin.creditAccount", eventname)
                and not user.getRole("Admin")>
                <CFSET arguments.event.addResult("AdminNeeded") />
        </cfif>
    </CFIF>
</CFFUNCTION>

2.Register that function to be called at 'onRequestStart';

<controller name="MyController" type="controller.Controller">
    <message-listener message="OnRequestStart" function="checkEvent" />
</controller>

3.In the Modelglue.xml file, create an event called "modelglue.OnRequestStart";
and
4.Within the modelglue.OnRequestStart event, register named results and provide appropriate redirection values;

<event-handler name="modelglue.OnRequestStart">
    <results>
        <result name="LoginNeeded" do="login" redirect="true" />
        <result name="AdminNeeded" do="home" redirect="true" />
    </results>
</event-handler>

As with anything Model-Glue or OO, there's always greater levels of detail to be expounded upon, but these snippets are more for illustrative purposes than actual 'out of the box' code, so I'm leaving a lot of the details to you. Such as where you should really store your lists of named events, or how your user object is always present, empty or populated, regardless of whether or not the user is authenticated, etc.

Anyway, hope this helps get somebody over the hump!

Someone recently asked about how to specifically ensure that a submitted email address is unique when using a model-glue generic commit, so I thought I'd share an example since I recently had to do that very thing.

I'm assuming for the remainder of this post that the reader is already familiar with Model-Glue in a practical sense, and at least knows of the existence of Reactor's automagic validation. Still, I'll try not to leave out too many relevant details.

Okay, the scenario:

I have a secured app, and I want to give new users the opportunity to sign up for an account. I'm using email address as the user name since in theory it should always be unique to an individual. (note: I opted NOT to set up my user table so that the email address field has a unique index on it) So, the user clicks "Sign me up!", I present them with a form to fill out, one field being their email address. They submit the form, and here's where we dive down under the waves to see what's happening...


The form submits to the event "inspector.create", which in the modelglue.xml file reads as follows:

<event-handler name="inspector.create">
    <broadcasts>
        <message name="ModelGlue.genericCommit">
            <argument name="recordName" value="UserRecord" />
            <argument name="criteria" value="" />
            <argument name="object" value="User" />
            <argument name="validationName" value="UserValidation" />
        </message>
    </broadcasts>
    <views></views>
    <results>
        <result name="commit" do="inspector.newuser" redirect="true" append="email" preserveState="false" />
        <result name="validationError" do="inspector.signup" redirect="false" append="" preserveState="true" />
    </results>
</event-handler>

Notice we're using a generic commit to handle this, which works because all of the needed data resides within form fields that are named exactly as their database field counterparts (eg; in my table there's a field called 'email', in my form there's a form field named 'email', etc.).
Now, the fact that we have specified an argument named "validationName" in our generic commit means that before the form data is committed to the database, Reactor is going to invoke the aid of one of the CFCs it auto-generated for us in order to "validate" the info submitted. By default, validation consists of checks that were created based on your table's metadata (unique indexes, datatypes, null not allowed, etc.), but Reactor was kind enough to provide us a convenient place to extend and customize that default validation if we so desire. In my scenario, since I did NOT choose to put a rule in place specifying that my email field should be unique, I added a custom method to perform that check.

To locate the CFC for adding custom validation, look in \model\data\reactor\Validator\, and find the cfc named after your target table. In my case, it's UserValidator.cfc

By default, the guts of the Validator CFCs you'll be working with look similar to the following:

<cfcomponent hint="I am the validator object for the Section object.  I am generated, but not overwritten if I exist.  You are safe to edit me."
    extends="reactor.project.myprojectname.Validator.SectionValidator">
    <!--- Place custom code here, it will not be overwritten --->
</cfcomponent>

I decided to add two more methods of validation to my user object:

1. make sure they typed their password in twice the same way,
2. and make sure their email isn't already used in the system

Ah, but when you look at the content of my CFC, you'll see there are THREE methods present, and NOT just two. Take a gander:

<cfcomponent hint="I am the validator object for the User object.  I am generated, but not overwritten if I exist.  You are safe to edit me."
    extends="reactor.project.myprojectname.Validator.UserValidator">

    <CFFUNCTION name="validate" access="public" hint="I validate an  record" output="false" returntype="any" _returntype="reactor.util.ErrorCollection">
        <cfargument name="UserRecord" hint="I am the Record to validate." required="no" type="any" _type="reactor.project.housefacks.Record.UserRecord" />
        <cfargument name="ErrorCollection" hint="I am the error collection to populate. If not provided a new collection is created." required="no" type="any" _type="reactor.util.ErrorCollection" default="#createErrorCollection(arguments.UserRecord._getDictionary())#" />
        <CFSET validatePasswordVals(arguments.UserRecord, arguments.ErrorCollection) />
        <CFSET validateEmail(arguments.UserRecord, arguments.ErrorCollection) />
        <CFSET super.validate(arguments.UserRecord, arguments.ErrorCollection) />
        <CFRETURN arguments.ErrorCollection />
    </CFFUNCTION>
   
    <CFFUNCTION name="validatePasswordVals" access="public"  output="false" returntype="reactor.util.ErrorCollection">
        <CFARGUMENT name="UserRecord" hint="I am the Record to validate." required="no" type="reactor.project.myprojectname.Record.UserRecord" />
        <CFARGUMENT name="ErrorCollection" hint="I am the error collection to populate. If not provided a new collection is created." required="no" type="reactor.util.ErrorCollection" default="#createErrorCollection(arguments.UserRecord._getDictionary())#" />
        <!--Blue is only allowed as a selection for Sky for people  whose weathercode = 5-->
        <CFIF  arguments.UserRecord.getPassword() is not arguments.UserRecord.getConfirmPassword() >
            <CFSET arguments.ErrorCollection.addError("user.password.notconfirmed") />
        </CFIF>
        <CFRETURN arguments.ErrorCollection />
    </CFFUNCTION>
   
    <CFFUNCTION name="validateEmail" access="public"  output="false" returntype="reactor.util.ErrorCollection">
        <CFARGUMENT name="UserRecord" hint="I am the Record to validate." required="no" type="reactor.project.myprojectname.Record.UserRecord" />
        <CFARGUMENT name="ErrorCollection" hint="I am the error collection to populate. If not provided a new collection is created." required="no" type="reactor.util.ErrorCollection" default="#createErrorCollection(arguments.UserRecord._getDictionary())#" />
        <cfset var userGateway = "" />
        <CFIF  arguments.UserRecord.getEmail() is not "" >
            <cfset userGateway = reactorfactory.createGateway("user").getByFields(email=arguments.UserRecord.getEmail()) />
            <cfif userGateway.recordcount IS NOT 0>
                <CFSET arguments.ErrorCollection.addError("user.email.alreadyexists") />
            </cfif>
        </CFIF>
        <CFRETURN arguments.ErrorCollection />
    </CFFUNCTION>
</cfcomponent>

The method present here that you may not have anticipated is called "validate", and is the exact same name as the method you would find in Reactor's core user validation object. So, what have we in effect done, boys and girls? That's right! We have (choose your favorite word, they both mean the same thing) overloaded/overriden the main "validate" method, in order to ensure that not only the original, auto-generated validation methods get called, but also the two new ones we added after the fact.

Let's take a closer look at our version of the "validate" method (this will only take a second, there are a couple of important things to note in there).

First off, you'll note that every validation method requires two arguments: an incoming record whose values are being validated, and the errorcollection where we (dang, this makes too much sense!) collect our errors.
Second, notice that we are FIRST executing our custom validation methods, then afterwards executing the auto-generated "validate" method by calling the object we extended, directly, via a call to "SUPER". Very cool, eh? Even though we initially overloaded our validate method in order to ensure that it got called rather than the core version of it, we were STILL able to call the original version as well. (By the way, that is a little trick I learned from Doug Sims' blog www.evenamonkey.com).

Alright then, we have submitted our form, used generic commit to perform validation, that validation called our extended object, executed the local custom methods first, then the system validate method. If any errors were encountered, our generic commit would have added a result named "ValidationError" to the event bucket (see the modelglue.xml snippet above), thus redirecting us back to the original page (where we have code in place looking for the presence of the error collection). If no errors were encountered, we're directed forward to the next event in the chain, and all is well.


One Mo Thang

Ah, one last thing that is of great importance to be aware of regarding Reactor validation: The Dictionary. The dictionary is an xml file that is specific to a validation object. In our example, since we have a userValidator object, there also exists a \model\data\reactor\Dictionary\userdictionary.xml file. This file is used to look up and translate any errors encountered so that the user is presented with readable text rather than a cryptic message. When you add custom validation methods, you also need to add dictionary entries. Consider the following snippet from my userdictionary.xml file:

<?xml version="1.0" encoding="UTF-8"?>
    <User>
        <email>
            <label>email</label>
            <comment/>
            <maxlength>100</maxlength>
            <scale>0</scale>
            <invalidType>The email field does not contain valid data.  This field must be a string value.</invalidType>
            <invalidLength>The email field is too long.  This field must be no more than 100 bytes long.</invalidLength>
            <notProvided>The email field is required but was not provided.</notProvided>
            <alreadyexists>That email address is already being used. Please select another email address. If you have forgotten your password, return to the main login screen and select "Forgot Password"</alreadyexists>
        </email>
        <password>
            <label>password</label>
            <comment/>
            <maxlength>50</maxlength>
            <scale>0</scale>
            <invalidType>The password field does not contain valid data.  This field must be a string value.</invalidType>
            <invalidLength>The password field is too long.  This field must be no more than 50 bytes long.</invalidLength>
            <notProvided>The password field is required but was not provided.</notProvided>
            <notconfirmed>The password you typed in is not the same as the "confirm password" value.</notconfirmed>
        </password>
    </User>

Look back at the custom method "validateEmail" we added earlier, and notice that if our validation fails, we're adding an error that looks like

<CFSET arguments.ErrorCollection.addError("user.email.alreadyexists") />

The syntax of that message is no coincidence...it's the same syntax you would use to access an item in an XML file. Fancy that! 'user' denotes the user dictionary; 'email' denotes the particular table field; and 'alreadyexists' is a term I just made up, and indicates that Reactor should look for a tag called 'alreadyexists' in order to find the correct translation for this error. Thus, you'll notice the tag

<alreadyexists>That email address is already being used. Please select another email address. If you have forgotten your password, return to the main login screen and select "Forgot Password"</alreadyexists>

in the <email> section of our dictionary file.


To Sum it all up!

Okay, so in a nutshell, if you have a form being submitted and you want to ensure that the email address is unique (AND you haven't put a rule in place within the database itself so specifying this):

1. add a method to your customizable validator CFC for the target table;
2. add a 'validate' method to the same CFC in order to overload the system version of the same;
3. within your custom 'validate' method, execute your custom method first
4. within the same, execute the system version of validate using "SUPER.Validate()"
5. edit your dictionary file to add a translation for your new custom error

That's it!

Doug out.

I have a CFC method with one argument that is an incoming, single-row query:


This method will call another internal method X in order to perform some work, but method X will be looking for the query data as arguments (necessary because method X is also used by other methods who will pass in discrete argument values).

So, I need to "convert" my 'remoteData' argument to actual argument values before I call the other method.



Pretty simple, but not necessarily so straightforward when having to treat queries as a structure of arrays, so thought I'd share it.

As an aside, I went ahead and toyed with converting an entire query to a structure dynamically, too. Following is the snippet I used:


Pretty cool. Here are the dumps:



Doug out.

If you ever find yourself with the need or desire to build a streaming audio library but don't want to mess with incorporating REAL Server or some other formal product to do it, I came across a cool little workaround that I thought I'd toss out there just "FYI".

In my experiment, the test audio files are in MP3 format, but I assume that any format that can be natively played by Windows Media Player will work just as well.

The basic steps:

• Upload MP3s to their new home;
• Create a template containing a link to a "pointer" file;
• Create the pointer file, which itself contains links directly to the MP3(s);

Okay, let's say I have two songs I want people to be able to play, but I don't want them to have to wait until the entire file downloads before it starts playing. I create my main template with a link to a pointer file, like so:


I then create the actual pointer file, which itself contains complete URLs to the audio files:

Important: This pointer file is saved with a "M3U" extension

Now, when I click the link in the main template, I'm prompted to open Windows Media Player. When I do, the first song immediately begins to play even before it has completely downloaded. In addition, I see in my media player's playlist all of the mp3 files listed in my pointer file, and have the ability to skip around.



You can see it in action by clicking here.

If you've ever tried to migrate or export database diagrams from one database to another in SQL Server 2000 or older, you may have noticed that no provision is made for doing so within the export wizard. Why the engineers would overlook THAT little detail I have no idea. But I came across a solution and so thought I'd put it out there just in case anybody else ever has the same issue.

Diagrams live in a system table called dtProperties, which is a system table. Every database has this table present, so no need to look in the Master database for that one.

What we're going to do is a basic insert into the dtproperties table of our destination database from our source database, using the following sql:

(Note: If the version of SQL server being used is older than version 2000, omit the field named 'uvalue' as it does not exist in previous versions)

Bear in mind that the above sql assumes that no other diagrams currently exist in your destination database. Executing the above sql against a dtproperties table that has existing diagrams is a really bad idea since you might be inserting rows with identical IDs.

If you can't see your database's system tables and wish to peruse it, right click on the sql server name in Enterprise Manager, click Edit Server Registration Properties, and make sure the item labeled "show system databases and system objects" is checked, as in the following illustrations:



That's it!

Keeping response times down and interactivity high has and always will be two important priorities with web interfaces of any kind. For standard html interfaces, Ajax is all the buzz and is great when it’s necessary to maintain interaction with live data. But when a static version of the data will do just fine, there’s at least one other alternative that you may want to consider….

The <CFWDDX> tag provides a way for us to bridge the gap between dynamic data processed on the server side (specifically, queries returned by CFQUERY), and Javascript on the client side. What this does is give us the ability to transform query results into a form that we can manipulate via javascript, opening up some very useful potential for interactive Dynamic HTML.

In a nutshell, the process goes like this:

• A CFQUERY tag is run;
• The result is fed to CFWDDX with the ACTION attribute set to CFML2JS, which produces a Javascript-ready recordset;
• The JS function that works with the recordset is created

That’s it. Now, for some of the nitty gritty details

In my example, I’m displaying a list of employees for the user to select from. What I want though is that whenever an employee is selected from the list, their detailed information is displayed to the side so that the user can determine if this is the right person or not. Like so:

(go 'head! click it!)
 

Ajax would work for this! Everytime an employee’s name is clicked on, we could make a call back to the server and grab that employee’s detailed information, then, using Javascript, output it to the designated portions of the template without ever reloading the page (yet still executing an HTTP call over the web). Or, we could just go ahead and retrieve the employees’ details during our initial query, since we had to retrieve their names and IDs anyway in order to produce the select list.

<!--- Using QuerySim to create the cfquery... --->
<cf_querysim>
    UserInfo
    userid,fname,lname,ext,title,dept,email
    200|Joe|Blow|2235|Supervisor|Reporting|jblow@nowhere.com
    300|Doug|Boude|2112|WaterBoy|Recreation|dboude@nowhere.com
    100|Stan|Cox|1225|Developer|IT|scox@nowhere.com
    400|Jim|Pickering|1513|Designer|IT|jpickering@nowhere.com
    500|John|Smith|1112|CEO|Management|JASmith@nowhere.com
    600|John|Smith|1123|Janitor|Building Maintenance|JLSmith@nowhere.com
</cf_querysim>

We could then take that query and create a Javascript-friendly version of it like so:

<script>
    <cfwddx action="cfml2js" input="#UserInfo#" toplevelvariable="users">
....
</script>

(Note that the attribute “TopLevelVariable” contains the value that will be used to name our recordset; that is the name it will be referenced as within our JS function.)

Within the body of our page, we’ll go ahead and create the select list just like we always do, only let’s add a function call to the onClick event that we can use to dynamically populate the targeted portions of our template:

<select onClick="getUserInfo(this.value);" size="8">
    <option value="" selected></option>
    <cfoutput query="userInfo">
        <option value="#userid#">#lname#, #fname#</option>
    </cfoutput>
</select> 

Additionally, let’s create the placeholders for our employee details:

<table>
    <tr>
        <td><STRONG>First Name:</STRONG></td><td><span id="fname"></span></td>
    </tr>
    <tr>
        <td><STRONG>Last Name:</STRONG></td><td><span id="lname"></span></td>
    </tr>
    <tr>
        <td><STRONG>Title:</STRONG></td><td><span id="title"></span></td>
    </tr>
    <tr>
        <td><STRONG>Department:</STRONG></td><td><span id="dept"></span></td>
    </tr>
    <tr>
        <td><STRONG>Extension:</STRONG></td><td><span id="ext"></span></td>
    </tr>
    <tr>
        <td><STRONG>Email:</STRONG></td><td><a id="email" href=""></a></td>
    </tr>
</table>

(Note: In my example, I use SPAN tags as placeholders as well as an Anchor tag, but any valid document object (form fields, div tags, etc.) can be targeted as placeholders, as long as they have a unique ID value)

At this point, our query has been converted into a ‘WDDXRecordset’ object. Behind the scenes, it’s a complex Javascript array, but the nice folks at Adobe wrapped it up for us with a set of functions that make it easy to get at the data using JS.  For example, to retrieve the first name from the first row, the call would look something like

Var thisName = getField(0,’fname’);
....

(Note that our first row is referenced with zero instead of one.)

Now all we need is a function to retrieve the employee details. It’s as simple as a Javascript FOR loop, leveraging the functions provided by the WDDXRecordset object:

function getUserInfo(userid){
    var i = 0;
    for (i=0; i < users.getRowCount(); i++){// loop through our wddx recordset...
        if(users.getField(i,'userid') == userid){//if the current record matches the ID passed in...
            document.getElementById('fname').innerHTML = users.getField(i,'fname');
            document.getElementById('lname').innerHTML = users.getField(i,'lname');
            document.getElementById('title').innerHTML = users.getField(i,'TITLE');
            document.getElementById('dept').innerHTML = users.getField(i,'dept');
            document.getElementById('ext').innerHTML = users.getField(i,'ext');
            document.getElementById('email').href = 'mailto:' + users.getField(i,'email');
            document.getElementById('email').innerHTML = users.getField(i,'email');
            return false; //ends this function call
        }
    }
    //if we made it here, then we found no match in our loop above; just blank everything out.
    document.getElementById('fname').innerHTML = "";
    document.getElementById('lname').innerHTML = "";
    document.getElementById('title').innerHTML = "";
    document.getElementById('dept').innerHTML = "";
    document.getElementById('ext').innerHTML = "";
    document.getElementById('email').href = "";
    document.getElementById('email').innerHTML = "";
}  

(Note: Although our function only utilized the getField() and getRecordCount() methods,  several more are available to us. You can take a gander at what else the WDDXRecordset provides at this LiveDocs link)

You can grab the entire template here if you'd like to see it all together.

Gotchas/things to remember:

• some web hosts don't provide access to the CFIDE/Scripts directory. If this is the case, then you'll need to grab a copy of wddx.js and put it in your webroot, then reference it within a script tag prior to creating any wddx-powered functions, like so:

<script src="wddx.js"></script>//< - - - making sure wddx.js is loaded...
<script>
    <cfwddx action="cfml2js" input="#UserInfo#" toplevelvariable="users">
    function getUserInfo(userid){
        ...(remainder of js)

   

• When referencing query fields, the js is not case sensitive (a getField(1,'title') will work as well as getField(1,'TITLE') ); However, javascript itself IS case sensitive, so referencing the target span tag you must use the exact case of the ID (getElementById('title') is NOT the same as getElementById('TITLE') )
• The WDDXRecordset object contains a zero-based array, meaning that the index number of the first row will actually be referenced as zero rather than one (getField(0,’fname’) retrieves the value for first name from the first record)

That's it!

Doug out 

Nearly every time the question of storing documents or images in a database comes up, the answer is almost always "don't do it", and for very good reasons. There are those times, however, when it's very much the appropriate thing to do. In my case at my day job, we have multiple production servers that are maintained by another (very protective) team, and the method of load balancing in place is NOT conducive to keeping server content in sync. Therefore, when we want to post a new document or image on the web, it may very well be hours before said file is actually replicated to all production servers. In order to make an image or document immediately available, we decided that storing them within the database would resolve our challenge. Since the final solution did involve some research and a few fiery hoops, I figured I'd share it with the rest of the Ether  in case it might come in handy for someone else. Following is the general process and code snippets involved with making this happen.

Basic Process:

• File is selected via a form within a document admin utility;
• Selected file is read into binary and stored in a "Documents" table
• Links to stored documents/images contain the UUID of the target document. Table is queried for that UUID, binary is retrieved from database.
• File's binary is provided to a CFCONTENT tag, along with the mime type, and result is delivered to requestor.

Before we go any farther, please pay heed to these three items:

 1. All of the following code and snapshots are in regard to CFMX 6.1 and higher. The process described below is similar for CF 5, but there are some additional caveats and steps involved (Email me for particulars if you're still using CF5).

 2. You MUST allow the retrieval of BLOBs within your ColdFusion datasource where your document table lives, as in the following snapshot:


3. Below is the script needed to create the sample table in MSSQL. The table used to store the documents should have a field of type IMAGE (if using MSSQL) in order to store binary data directly. If your database doesn't support a field type for binary data, then you'll need to create a field of type TEXT or its equivalent. Any binary data needing to be stored in a TEXT field will have to be BinaryEncoded/BinaryDecoded using Base64 as the second parameter in order to store and display it in this manner.

SQL Script:

On with the actual code then! Here are the details/snippets:

File selection
File selection is done via a form field of type "File", as in

Important: In the FORM tag, it is required that you set the ENCTYPE equal to "multipart/form-data", as in

Storing hierarchical navigation data in a single table is a snap. Maintaining that data, however, can be a little bit of work (inserting sub items, removing sub items, etc.). What I wanted to share in this post is the solution i built for myself to perform navigation administration, where the navigation is stored in a single table with each record related to some other record in the same table. Bear in mind that I built it  for my eyes only, so isn't necessarily what I would give a customer to use. But, it didn't take an inordinate amount of time to build and it gets the job done, so I am kinda fond of it for those reasons. Without further adieux then, let's take a gander at it.

Following is a snapshot of the admin screen for maintaining the navigation. You'll notice it also incorporates a rudimentary sort of security.


So, in the scenario where I want to insert a sub-nav item somewhere, I simply add the new item's info to the "Add New" section at the bottom, making sure to designate its parent in the "Parent ID" column (choosing 'None' if this is a top level item). The sort order column is used to indicate where under that parent this particular item should show up. Right now, I have nothing in place to prevent me from having the same number twice, so I just have to look at the other children for this parent to determine what the sort order value should be.

Removing a nav item is as simple as checking the delete box. I have my update coded so that if a nav item is removed, its children are automatically removed as well.


Here are the relevant tables I use to store the navigation data (note that ev_nav has a relationship back to itself via an aliased instance of itself in the diagram):


If anybody is interested in getting a copy of the actual files (self-posting template, cfc, and mdb), feel free. Also, consider yourself warned, it's kinda fugly and wasn't meant for public review, but it should provide a good base for something prettier.

Ever found yourself in a situation where you need to output values from a query, but you're unable to explicitly reference the columns by name? Perhaps the query is a "Select *", or the column names are being dynamically aliased within the sql based on the date. Whatever the reason, never fear, there are solutions to the challenge.

 

One's first instinct may be to call upon the trusty ol' Evaluate function, which will definately work. But what would your friends say if they saw you using a function that has such an evil reputation as a process hog? Here's at least one alternative way to output a query when you don't know the column names ahead of time.

The key lies in the fact that queries can also behave like structures. (they can also behave as one dimensional arrays, if the field names are known ahead of time). To leverage this behavior, we're going to use the column name as the first key and the rownumber as the second key. Like so:

For the query "myQuery", the value of "Firstname" in the first record would have been output.

In comparison, if we had known the field name ahead of time and wanted to grab that field's value from a specific record, we could have treated our query as a one dimensional array and referenced it like


outputting the value for firstname in the second record of the data set.

One final example

Following is an example that outputs a query in table format, without knowning anything about the query ahead of time
(We're using QuerySim to fabricate our data set. If you don't have a copy you can get it here.):

Some of us are fans of using CFSCRIPT for certain portions of our code. For myself, I’ll typically create my UDFs (User Defined Functions) in this manner…it just looks cleaner to my eye. There can be challenges to doing this, however, due to the fact that certain commonly used CF tags just do not have equivalent CFSCRIPT function calls, like CFQUERY or CFTHROW. It is, however, still possible to incorporate them.

I think a code sample is worth a thousand words, so consider the following sample:

 

Step 1 is to turn any needed CF tags into functions using the CFFUNCTION tag, defining the tag’s attributes as arguments to the CFFUNCTION.

Then, within our CFSCRIPT section we can refer to the newly created function by name, passing in the pertinent arguments. That’s it!

One thing to consider with this is that you can't build a sql string that includes the cfqueryparam tag and have it work, so you'll have to compromise on this and just pass in your where clause values in native sql format (single quotes, etc.).

Keep it beautiful.  Doug out.

Preface to this Series

As with most coding challenges, there are always near infinite ways to overcome them. To say one approach is better than another is nearly always a matter of perspective and goals, but what I use as my personal measuring stick is the spirit of a quote by the late great Buckminster Fuller. It reads, “When I am working on a problem I never think about beauty. I only think about how to solve the problem. But when I have finished, if the solution is not beautiful, I know it is wrong.” I have adopted this quote as my personal and professional creed when it comes to problem solving. It also happens to be the motivation for this series, as I hope to inspire my peers to let their coding be a reflection of who they are as people. After all, we are artists and coding is the expression of our talents.

  

If the solution is not beautiful, it is wrong. With that in mind, let’s take a look at the first item of discussion that has led to a lot of “wrong” solutions out there. It’s one of the most basic of things, yet so often is done in such an ugly manner: outputting complex query data.

 

The Power of <CFOUTPUT>

For the most part, what I am about to share will not apply when talking about simply outputting a query, but rather comes heavily into play when we are given the complex task of outputting composite data from several sources that must be grouped and summarized. That, as many of you know, is where things can start to get really ugly and, without an understanding of one of our beloved CFOUTPUT’s lesser used attributes, there’s little hope of giving it any elegance at all.

  

The Scenario

 You are working on an application that manages projects for a large telecommunications company. Every project can be composed of several tasks, and each task can have multiple people assigned to it. Your challenge of the day is to produce a report showing all projects and their associated details. Here are the tables where your data is stored:

 

 

Here is the mockup you were given to duplicate for the report:

Now, time to build a solution. The more beautiful approach consists of two steps:

1. Getting creative with our SQL statement,
2. and nesting CFOUTPUT tags.

Creative SQL

The goal here is to retrieve EVERYTHING we need for our report in one query, the results of which will look like this:

Here is the SQL statement for returning a query set like that previously shown:

 

 

SELECT Projects.projectName as A_ProjectName,  Tasks.taskName as B_TaskName, Roles.Rolename as C_RoleName, , Projects.CreatedDate AS projectCreationDate,
    Tasks.Description, Tasks.CreatedDate AS taskCreationDate,People.Firstname, People.Lastname, People.Title,
    People.Email
FROM ((((Projects INNER JOIN projectTasks ON Projects.projectID = projectTasks.ProjectID)
   INNER JOIN Tasks ON projectTasks.TaskID = Tasks.taskID)
   INNER JOIN taskAssignment ON Tasks.taskID = taskAssignment.TaskID)
   INNER JOIN People ON taskAssignment.PersonID = People.personID)
   INNER JOIN Roles ON taskAssignment.RoleID = Roles.roleID
ORDER BY A_ProjectName, B_TaskName, C_RoleName

 

Besides proper joins, the one thing about this SQL statement that is absolutely IMPERATIVE in order for us to be able to leverage our CFOUTPUT tag is the ORDER BY clause. The reason is this: Coldfusion will be processing our query sequentially, one row at a time starting at the top, and if everything isn’t in just the right order, we’ll get some unexpected and unpredictable results. If we DO have it in the right order, however, CFOUTPUT can do a lot of work in a few lines.

Nesting CFOUTPUT Tags

The second thing we’re going to do is something that many of you have seen CF complain about during your development: nest CFOUTPUT tags. Though usually it is true that you cannot nest CFOUTPUT tags, there is one situation where not only can they be nested, they should be: when utilizing the attribute GROUP.

 

Now that we have our data set, let’s resist that first impulse to use CFLOOP to output it and rather use CFOUTPUT, like this:

 

<h2>Projects</h2>

<ul>
 <cfoutput query="qrydataset" group="a_projectName">

  <l>

    #a_projectName#

  </li>

        </cfoutput>
</ul>

 

By telling CFOUTPUT to output the query “qrydataset”, we’re telling it to loop over that query and for each line output whatever information we have specified between the opening and closing CFOUTPUT tag. BY SPECIFYING THE GROUP attribute, however, we’re telling CFOUTPUT to loop through the query, but only perform an output for EACH UNIQUE VALUE of the field specified in GROUP.  The result then looks like this:

 

Projects

 

 

• Baghdad Wireless Infrastructure Reconstruction Bid
• Sensitivity Training for Executives
• Verizon Acquisition

Even though in reality those project names occur multiple times in our query result, the GROUP attribute has limited output to only unique values. Cool, eh?

Real quick, let’s take a look at what can happen if our query had not been sorted on the ‘A_ProjectName’ field:

Projects

• Baghdad Wireless Infrastructure Reconstruction Bid
• Sensitivity Training for Executives
• Verizon Acquisition
• Sensitivity Training for Executives

Random values show up wherever they were within the query; not good.

 

Okay, so now we’ve got CFOUTPUT giving us a nice list of projects by name. Let’s add in the tasks for each project. This is where it gets really cool, as we nest a CFOUTPUT tag:

 

(Notice that in the nested CFOUTPUT tag we did not have to re-specify the QUERY attribute)

It’s important to be able to visualize what’s happening here. The outer CFOUTPUT is the master and is looping over our entire query one row at a time starting with the first row. Using the GROUP attribute, we instructed the outer tag to only perform output upon encountering a unique value of ‘A_ProjectName’. Now, here is an important item to comprehend: Because the outer tag gives “scope”, or “limits” to any CFOUTPUT tags nested within it, our innermost CFOUTPUT tag is only going to cycle through those query rows that are in our current ‘A_ProjectName’ group. In other words, if our current unique value of ‘A_ProjectName’ is “Blah”, then our inner CFOUTPUT tag is only going to loop through those query rows whose ‘A_ProjectName’ is “Blah”. Once our ‘A_ProjectName’ value changes, let’s say to “Pooh”, our nested CFOUTPUT tag will once again be able to loop, but this time only over those query rows whose ‘A_ProjectName’ is “Pooh”. Here is what the results look like so far:

Projects

 

 

• Baghdad Wireless Infrastructure Reconstruction Bid
  • Needs Analysis
  • Remote Worker Recruitment
• Sensitivity Training for Executives
  • Assemble Curriculum Committee
  • Technical Writer Recruitment
  • Train Instructors
  • Course Marketing
  • Course Screening
• Verizon Acquisition
  • Assess Value of Verizon
  • Assemble PR Committee
  • IT Analysis

We’re not finished yet, though. We need to output the people assigned to tasks and their roles, so let’s add another nested CFOUTPUT!

 

As in the last iteration of our CFOUTPUT code, our most inner nest was instructed to only perform its output functionality upon encountering a unique value of ‘C_RoleName’. Because it is nested within the second level CFOUTPUT tag, it will restart its looping every time a unique value for the second level loop is encountered, limited by that second CFOUTPUT to only applicable query rows . Here are the results:

Project

• Baghdad Wireless Infrastructure Reconstruction Bid
  • Needs Analysis
    • Business Analyst
  • Remote Worker Recruitment
    • HR Admin
• Sensitivity Training for Executives
  • Assemble Curriculum Committee
    • Business Analyst
  • Technical Writer Recruitment
    • HR Admin
  • Train Instructors
    • Trainer
  • Course Marketing
    • Business Analyst
  • Course Screening
    • Trainer
• Verizon Acquisition
  • Assess Value of Verizon
    • Business Analyst
  • Assemble PR Committee
    • Business Analyst
  • IT Analysis
    • Developer

Let’s add one more nested CFOUTPUT, this time without a GROUP attribute, in order to output the individuals assigned to specific task roles, but without regard to unique values:

 

And here are our results now:

Projects

Baghdad Wireless Infrastructure Reconstruction Bid

• Needs Analysis
  • Business Analyst
    • Shawna Easton (SE@bla.com)
    • Bjorn Jager (BJ@bla.com)
• Remote Worker Recruitment
  • HR Admin
    • Maximus Minimus (MM@bla.com)

Sensitivity Training for Executives

• Assemble Curriculum Committee
  • Business Analyst
    • Joan Goodbody (JGB@bla.com)
• Technical Writer Recruitment
  • HR Admin
    • Shawna Easton (SE@bla.com)
    • Joan Goodbody (JGB@bla.com)
• Train Instructors
  • Trainer
    • Joan Goodbody (JGB@bla.com)
• Course Marketing
  • Business Analyst
    • Maximus Minimus (MM@bla.com)
    • Bjorn Jager (BJ@bla.com)
    • Simon Horwith (SH@bla.com)
• Course Screening
  • Trainer
    • Shawna Easton (SE@bla.com)

Verizon Acquisition

• Assess Value of Verizon
  • Business Analyst
    • Bjorn Jager (BJ@bla.com)
    • Maximus Minimus (MM@bla.com)
• Assemble PR Committee
  • Business Analyst
    • Bjorn Jager (BJ@bla.com)
    • Debbie Reynolds (DR@bla.com)
  • HR Admin
    • Desislava Nikolava (DN@bla.com)
• IT Analysis
  • Developer
    • Omar DuPuis (ODP@bla.com)
    • Doug Boude (dougboude@gmail.com)
    • Simon Horwith (SH@bla.com)

 

Now, a few more items and some formatting, and we have the code to output the requested report:

 

<h2>Projects</h2>
<ul>
 <cfoutput query="qrydataset" group="a_projectName">
  <li>
   <font size="+1" color="red">#a_projectName#</font> <strong>Created</strong>: #ProjectCreationDate#
   <cfset TaskCounter=0>
   <ul>
   <cfoutput group="b_taskName">
    <cfset FTEcounter=0>
    <cfset TaskCounter = TaskCounter+1>
    <li>
     <font color="red">#b_taskname#</font> - #Description#
     <ul>
      <CFOUTPUT GROUP="c_rolename">
       <li>
        <strong>#c_rolename#</strong>
        <ul>
        <CFOUTPUT>
        <cfset FTEcounter=FTEcounter+1>
         <li>
          #firstname# #lastname# (#email#)
         </li>
        </CFOUTPUT>
        </ul>
       </li>
      </CFOUTPUT>
     </ul>
     #repeatString("nbsp;     #firstname# #lastname# (#email#)
         </li>
        </CFOUTPUT>
        </ul>
       </li>
      </CFOUTPUT>
     </ul>
     #repeatString("-",40)#<br>
    &am
         </li>
        </CFOUTPUT>
        </ul>
       </li>
      </CFOUTPUT>
     </ul>
     #repeatString("-",40)#<br>
     <strong>Total FTEs Assigned for #b_taskName#</strong>: #FTEcounter#<br>
     #repeatString("-",40)#
    </li>
   </cfoutput>
   </ul>
   <strong>Total Tasks for #a_projectName#</strong>: #TaskCounter#<br>
   #repeatString("-",40)#
  </li>
 </cfoutput>
</ul>

Notice that our report required counts at different levels. We took advantage of the fact that every time an outer CFOUTPUT tag changes unique values, the nested CFOUTPUT tag starts over. Notice the “<CFSET [bla]Counter = 0>” calls. At those points in the code we are resetting our counters. Then within the nested CFOUTPUT where the value we’re concerned about counting is being output, we increment those same counters, later outputting them wherever needed. Cool stuff, eh?

 

 

 

And the final results:

Projects

• Baghdad Wireless Infrastructure Reconstruction Bid Created: 5/6/2005
  • Needs Analysis - Analyze the specifics of the GSA RFP
    • Business Analyst
      • Shawna Easton (SE@bla.com)
      • Bjorn Jager (BJ@bla.com)
    ----------------------------------------
    Total FTEs Assigned for Needs Analysis: 2
    ----------------------------------------
  • Remote Worker Recruitment - Recruit needed team members for remote assignment
    • HR Admin
      • Maximus Minimus (MM@bla.com)
    ----------------------------------------
    Total FTEs Assigned for Remote Worker Recruitment: 1
    ----------------------------------------
  Total Tasks for Baghdad Wireless Infrastructure Reconstruction Bid: 2
  ----------------------------------------
• Remote Worker Recruitment - Recruit needed team members for remote assignment
  • HR Admin
    • Maximus Minimus (MM@bla.com)
  ----------------------------------------
  Total FTEs Assigned for Remote Worker Recruitment: 1
  ----------------------------------------

Total Tasks for Baghdad Wireless Infrastructure Reconstruction Bid: 2
----------------------------------------

Sensitivity Training for Executives Created: 4/1/2006

• Assemble Curriculum Committee - Assemble committee to draft curriculum for STE tra
  • Business Analyst
    • Joan Goodbody (JGB@bla.com)
  ----------------------------------------
  Total FTEs Assigned for Assemble Curriculum Committee: 1
  ----------------------------------------
• Technical Writer Recruitment - Recruit technical writers to finalize curriculum
  • HR Admin
    • Shawna Easton (SE@bla.com)
    • Joan Goodbody (JGB@bla.com)
  ----------------------------------------
  Total FTEs Assigned for Technical Writer Recruitment: 2
  ----------------------------------------
• Train Instructors - Train instructors on course curriculum
  • Trainer
    • Joan Goodbody (JGB@bla.com)
  ----------------------------------------
  Total FTEs Assigned for Train Instructors: 1
  ----------------------------------------
• Course Marketing - Market course internally to management
  • Business Analyst
    • Maximus Minimus (MM@bla.com)
    • Bjorn Jager (BJ@bla.com)
    • Simon Horwith (SH@bla.com)
  ----------------------------------------
  Total FTEs Assigned for Course Marketing: 3
  ----------------------------------------
• Course Screening - Assemble sampling of executives to screen the cour
  • Trainer
    • Shawna Easton (SE@bla.com)
  ----------------------------------------
  Total FTEs Assigned for Course Screening: 1
  ----------------------------------------

Total Tasks for Sensitivity Training for Executives: 5
----------------------------------------

Verizon Acquisition Created: 4/3/2005

• Assess Value of Verizon - Perform an in depth analysis of Verizon financials
  • Business Analyst
    • Bjorn Jager (BJ@bla.com)
    • Maximus Minimus (MM@bla.com)
  ----------------------------------------
  Total FTEs Assigned for Assess Value of Verizon: 2
  ----------------------------------------
• Assemble PR Committee - Put together team of representatives to devise PR
  • Business Analyst
    • Bjorn Jager (BJ@bla.com)
    • Debbie Reynolds (DR@bla.com)
  • HR Admin
    • Desislava Nikolava (DN@bla.com)
  ----------------------------------------
  Total FTEs Assigned for Assemble PR Committee: 3
  ----------------------------------------
• IT Analysis - Analyze existing Verizon IT capabilities
  • Developer
    • Omar DuPuis (ODP@bla.com)
    • Doug Boude (dougboude@gmail.com)
    • Simon Horwith (SH@bla.com)
  ----------------------------------------
  Total FTEs Assigned for IT Analysis: 3
  ----------------------------------------

Total Tasks for Verizon Acquisition: 3
----------------------------------------

 

 

I hope that you found this information useful, and may I encourage each and every one of you to consider using this approach the next time you see a template starting to get long, ugly, and IO heavy when outputting complex and nested data sets.

 

A friend asked me a good question, so thought I'd share it and the answer for whomever it may help in the future.

Q:  What is the equivalent of the SQL statement "SELECT TOP 10 * FROM myTable" in Oracle?

A:  SELECT * FROM myTable WHERE ROWNUM < 11

Dr Doug Says:

If you’re churnin’ out a web app cause your sales guy said you would,

And the PM he’s a pushin’ cause he knows you’re just that good;

But the requirements call for nav that stretches to infinity,

Then it may be time to think about recursive hierarchy….

Oh, recursion is a big word to describe a special function

That can chase its tail and catch it if it’s written with some gumption!

You could rightly call it Schizo, as to why, here’s the scoop:

It will call itself AND ANSWER in a never ending loop!

The secret to its mental state that makes it strange, yet stable

Is the special way it uses scope to give its vars a label.

If a variable is private then it’s labeled V-A-R,

While the public ones are public with no label needed thar!

And so, oh best Beloved, this function’s prelude now is ended;

But I must insist that you press on to find it and befriend it!

For no programmer’s app is done, regardless of the version,

Unless it boasts, at least a mite, the power of Recursion!

On THAT note…
Let’s say that you are designing an app and you want to give the user the ability to manage his navigational tree to an infinite level; the ability to add items under items under items under items to his or her heart’s content. From a database point of view, the simplest way to store relational data like this and meet the requirement of infinite relationship levels is a single table with a structure something like the following:

A few quick comments about the fields. ParentID contains the id of the record that the item is a child of. Records with a parentID of zero are top level; they have no parents.

Okay, so storing the hierarchical data is no problem and the way we chose to do it is both elegant and makes perfect sense! The CHALLENGE, however, (there are no problems, only challenges) is how to retrieve and properly display this data while maintaining its hierarchical relationship. For our example, the requirement will be to retrieve the navigational data and output it using html Unordered Lists, maintaining the data’s hierarchy. Here is where the power of the recursive function is a perfect fit.

Recursion can be a difficult thing to visualize in action, so let’s first go through how one could retrieve and display this data if recursion did not exist.

First of all, we must know up front how many levels deep our data goes so that we know how many nestings to use in our code. In this case, none of the data goes more than 4 levels deep. Assuming our data has been retrieved into a query named qryNavigation, the following ColdFusion code will display our information as a properly nested unordered list:

Walking through this code, the first thing we have is a query containing all of the nav items. Step 1 is to create the opening UL tag for our unordered list. Next, we retrieve all of our top level (parentID = 0) nav items and loop through them. For each one, we check to see via a Query of Queries if that particular nav item has children. If so, we retrieve that items children and loop through those. For each of the children, we check to see if THEY have children. If so, we loop through them, repeating the process one more time. Opening and closing UL and LI tags are added appropriately to form a string containing a properly formatted html unordered list which is then output to the browser.

That is a lot of code, and as you can tell, it limits the data to 4 levels deep while our requirement called for infinite levels. Not the solution we’re looking for. We need one with a little more imagination, grace, elegance, and usability.

I’m just going to lay it on you; here is the equivalent recursive function that accomplishes the same thing, but without limitations and with less code:

Let’s walk through this recursive process. Step 1 really comes last, and that is our initial call to this function that kicks the whole process off like the first domino falling. From here on out, we will cease to use the actual value of the parent ID and simply refer to it as ‘X’; this will make it easier to walk through the code. We call the function passing a value of zero to the level argument (which lets us keep track of where we are in our recursive calls) and a value of X to the parentID argument (used when retrieving navigation subsets via query of queries). Our first call to the function sets up the two private (VAR scoped) variables for use by this instance of the function. Since we are at level zero, it then initializes the values of two public variables: navigation and menustring. Menustring is our cumulative variable, meaning that with every call to GenerateNav, we will be appending more and more items to it.

Our next step is to execute the query objNav which retrieves all items from the complete set of nav items that have a parentID of X. Once retrieved, we have our X level items and begin looping through them. For each item returned, we check to see if there are any children. If there are (drumroll please…), then we make a recursive call to GenerateNav, passing it a parentID equivalent to the current item’s ID, and a level equal to the current level plus one.

The recursive call to GenerateNav sets up its two private instances of checkForKids and objNav, skips the initialization of the public variables, then selects all nav items for the parentID that was just passed in. Those children are retrieved, we step through them, and for each of them that are found to have children themselves, we again call GenerateNav passing in the appropriate values for that particular instance of the function.

When we’ve gotten through every item in our first initial objNav query (which would have been all items with a parentID of zero), we add the closing UL tag to our public variable Menustring and then return it. Notice we enclose the code to add the closing UL tag and to return Menustring within a CFIF that looks at the level. This prevents Menustring from getting returned prematurely by any of our recursive calls.

When a recursive function calls another instance of itself, you can rightly picture that it just managed to perform mitosis and now has an identical brother performing the exact same work, only with its own private variables. Whatever variables were declared public are shared between the first and its brother, and any subsequent brothers that are spawned. As each clone completes its task, it disappears back into the ether until finally there is only the original instance left, and when it has completed its task, it returns the final value and itself disappears, completing the entire process. This is recursiveness in a nutshell. Beautiful, isn’t it?

You may also be wondering how useful it is to output your navigation in an unordered list. Bear in mind that I kept it as simple as possible and did not include fields to hold URLs and other linking information that would normally be present in a navigation table. Those can be added later and included within the information being appended to your Menustring variable, along with CSS class names based on level. Upon output, it is a relatively simple thing to create a Stylesheet to format your unordered list as a flyout menu (no javascript required), such as is shown here:

I hope you found this information both useful and inspiring, and will be looking for places to leverage it within your own application development. As the late great Buckminster Fuller once said, “…if the solution is not beautiful, then it is wrong.”, so let's keep it beautiful folks.

I've had several requests for a copy of a function i wrote that calculates business time between two datetimes, so figured I'd go ahead and make it available via my blog.

It's kind of a long function, but I built it to be thorough and do a lot of validation and such. Also, there are  several supporting function included as well that it uses (not all of which are mine...credit given where applicable).

I couldn't find any documentation ( I thought I had written some, but it was probably just an email I sent to my coworkers), but it's fairly straightforward. Here's a sample call:

<cfset thesebizmins=bizMins(begin_dt,end_dt,8,17,12,"n")>

************** Download the Code Here ********************

Often when hosting a site somewhere, it can be a slow process to get a mapping created or modified. Then there are also those times when all you really need to be able to do is SEE them so you can verify paths and what not.

Using snippets harvested from google, I wrote myself a simple little MappingUtil.cfm page that I use to perform the tasks previously mentioned, and now i share it here with those who may be interested.

WARNING:
A few things you should think about before using this tool:

1. This code uses undocumented methods and is not supported by Adobe. It is not guaranteed to work against every version of ColdFusion server.
2. Most of the time, yours isn't the only site living on a particular host server. This tool will allow you to see and manage ALL mappings, whether yours or not, therefore you must be very careful not to delete someone else's mapping.
3. Performing your own back door mapping management without the admin's knowledge could get you kicked off of your current host.
   
4. Never drink and manage mappings at the same time.

Okay, cautions out of the way, below is the code. It should be saved in a template name that matches the form action. Currently that is "mappingutil.cfm". If you wish to save it as a different name, be sure to edit the form action as well.

********************************  The Code  ***********************************************************

When developing CF webservices on a server that you don't have admin access to, it can be very frustrating (until you figure out the root cause) when you are making changes to your service and yet when you call it, the change doesn't seem to show up. Well, this is because ColdFusion caches its definition of the webservice, so subsequent changes can only be seen if that cached version is purged first.

This can be done via the CF administrator, but since many of us don't have access to the administrator, I share with you another solution...a backdoor to refresh these cached services.

Deleting a cached service will totally remove it from the cache (it'll be re-cached next time somebody calls it), while refreshing it will delete and then call the webservice again to grab a fresh copy of it. If running this on a shared server, you will see EVERYBODY's cached web services. Deleting or refreshing all services will do no harm, so no need to be concerned about touching someone else's stuff.

The code below should be copied into a single cf template. It's self calling in a generic way (using cgi.script_name), so no need to modify it in any way.

********************** The Code ************************************************************

Scope within a CFC is incredibly important, and even moreso when you start using Object Oriented Frameworks for your application. It is essential to understand it in order to save yourself a lot of mind numbing troubleshooting, especially when writing recursive functions.

The following information is also contained in my OO Lexicon, but I felt like it was sufficiently encapsulated (that's an OO term ) to go ahead and re-post it here.

THIS, VARIABLES, and VAR Scopes

These are three of the variable scopes that are found within the world of a ColdFusion object or component, and three scopes that can make you pull the rest of your hair out when you don’t know how to think about them. In a nutshell, you’re looking at three increasing levels of variable privacy, from most liberal to most private.

The “THIS” scope holds items that can be directly accessed from anywhere inside OR outside of the object itself. Consider the following example of an instantiation of the myTest.cfc that has a variable called THIS.GLOBALVAL within it’s INIT method:

The component code...

The call from the outside template...

 

 

 

In this example, because the variable GlobalVal was put into the THIS scope within our object, our application could directly access it as a property. Cool, if that’s what you intended to happen. Not cool if it wasn’t.

 

 

 

The VARIABLES scope within a component object is a scope that can be accessed by any method within the object at any time, in real time. In other words, if our component had set up a variable called Variables.LimitedVal, all methods will be sharing that one instance of the variable. If method one sets it to “5”, and later the app calls method two which reads that variable, it will see the value “5”. Any attempt, however, from outside the object itself to manipulate that value will result in an error. The following would FAIL:

The component code...

The call from the outside template...

 

 

 

 

 

 

And finally, the VAR scope. This scope is one which can be seen only from inside of the actual method itself. For example, I can have three methods, each that use a variable with the same name that was initialized within the VAR scope, and no method will ever see the variable used by the other methods. It is a VITAL thing that you initialize your private variables in the VAR scope inside of your methods, because by default they are set up in the VARIABLES scope, and who knows WHAT havoc will occur if you have methods sharing variables that were intended to be private. Consider the following sample of initializing a variable in the VAR scope:

 

 

Only the ‘sampleMethod’ method will be able to see and manipulate those variables set using the ‘var’ scope. VAR is so private, that every other method within our component could initialize variables with the same name within their own var scope and no overlap would occur.