(to skip to the very heart of this post, click here. otherwise humor me and read until you get to it  )

So I was sitting there working on a CF-based resume builder app when my boss walks in and asks me to take over responsibility for our company's database and then implement a solid backup plan and policies. I'm still new to this job and so haven't been privy to anything database as of yet, but, how hard can it be? I heartily accept (because accepting requests makes the boss happy) and then proceed to track down all the particulars of our database so I can make this all happen.

The skinny is that our current web application is a very large and disorganized hodge podge of PHP files that run against a mySQL database sitting on an over-priced, shared hosting Linux server somewhere. I FTP the PHP files to my pc and dig through them for some clues about our database and find hard-coded (all OVER the place) the server name, username, password, and database name. "Cool", I say to myself, "I can just use the mySQL gui and continue to explore this puppy. Well, apparently direct connectivity to anything but "localhost" was blocked, so I had two choices for access: phpMyAdmin or telnet sessions via puTTY.

phpMyAdmin does have a feature to allow me to create all the scripts needed to recreate all tables and insert their data. But I wanted a "real" backup as well via mysqldump if possible, so decided to use puTTY and see what I could make happen.

Bear in mind that the last time I did any meaningful work from a command line was somewhere in the late 90s, and I consider doing things that way "last resort", cryptic, and way over complicated. (my personal opinion is that individuals who purposefully CHOOSE to do things the harder more cryptic way when a more user friendly approach exists probably do so in order to make themselves feel smarter. Yeah, I could use the old DOS 'debug' command to find my way into an embedded bios in a piece of hardware I have installed and run its processes that way...but when I have a gui available that does the same thing??? c'mon). Okay, so anyway, I'm staring at a lovely black window with a green square cursor, and an hour later I have managed to piece together what I think is a good start to a bash shell script that will do a "check table" on all of my database's tables, run repair on any that report an error, do a mysqldump on my database, and then ftp the resulting file to a remote server. But, I have a problem (one that consumes the next two and a half hours): my script won't run without immediately throwing a very ambiguous and meaningless error.

I add "-x" after the initial "#! bash" statement so I can maybe get more info, but there's none to be had. After many, many iterations of the same line of code (trying to do a mysql "show tables"), I accidentally happened upon a blurb somewhere out in the ether that caused me to consider the character(s) being used as linefeeds in my script file. I ran a quick regex replace, replacing all \r\n with \n, and lo and behold the script worked!

The Moral of the Story: If you HAVE to write shell scripts in a PC environment, make sure you do a regex replace of all \r\n with \n!

Oh, and do I feel smarter for having managed to create a working shell script from within a PC environment? No. I mean, I do feel a sense of accomplishment that I hung on to this bull and rode it until it submitted to me. But more so I rather feel frustrated that I have to jump through so many fiery hoops to accomplish what ought to be mostly straightforward. Why's the world gotta be so complicated? Oh well, that's another rant altogether.

Hope this saves someone else some time!

Doug out.

Okay, this is just too cool so I thought I'd share it.

Let's say you have access to a database on a remote server somewhere, and there's data there that you would love to have a copy of locally. Options, options, options...well, if we aren't using SQL Express we have lots of options for migrating the data. But in our case, we ARE using SQL Express, so we don't have easy access to linking servers and other more advanced commands. Wouldn't it be just perfect then if we could simply do a SELECT * INTO MyNewLocalTable from [remote table]? Turns out that I CAN if I turn on an option that is turned off by default. So first, I execute these two lines in a query window (I'm using SQL Server Management Studio Express):

 

Then, I execute THIS query (well, one like it with suitable values substituted):

 

Worked like a charm! Now I have a local copy of the remote data. In my case I migrated a little over 80,000 rows and it took about a minute or less. Saweeeet.

Doug out.

My most recent project has caused me to have to be "unicode aware" at times (something I've never had to do before), and so I am learning a lot about encoding and display of special characters as I go along. My latest challenge related to this topic involved a User Manager section I created, wherein the users could very well have names that contain special characters (foreign names). This particular section performs its updates, deletes, and inserts via Ajax calls and client-side JS manipulation of a JSON data set. My Ajax is performed via the Prototype library, my code is all ColdFusion living within the Coldbox framework, I'm using Coldspring to manage my object relationships, Transfer is my ORM, and my backend database is MSSQL 2005.

The Challenge: Data that contained special characters was being successfully inserted/updated via my Ajax calls, but the JSON data set returned via those calls did NOT contain those special characters (or contained an incorrect interpretation of them, like numbers, question marks, etc.). A quick check of the database verified that the data was indeed stored in the tables properly.

Setting the Stage
At this stage in the game for me, the smorgasbord of terms, acronyms, and concepts revolving around properly handling unicode is a bit foggy for me. (On a side note, I WISH someone who has the full understanding would put together a simple "checklist" of "Things you need to do in order to handle special characters in ColdFusion"!) From what I currently understand, the physical template you write has to be "encoded" properly (set within the IDE you are using); The database you are using has to have the proper encoding(called Collation in MSSQL 2005); The fields in your table have to be of the proper type to store unicode text(ie: 'nVarchar' instead of 'Varchar', etc.); your browser has to have the proper languages associated in order to display certain sets of special characters(Tools/Internet Options/Languages in IE); your JS functions, if living in a separate file, must have the page encoded properly(again, via your IDE); your ColdFusion datasource has to have the checkbox for "Enable High ASCII characters and Unicode for data sources configured for non-Latin characters " checked; and to top it all off, after having handled all of that, your JS functionality still yet needs to have ITS encoding types set in the proper place.

That sounds like a LOT of fiery hoops just to be able to deal with special characters, right? Well I'm with ya...it's on the verge of being a nightmare for someone who's never had to deal with it. And I do realize that for some of you reading this, the first time YOU tried to deal with special characters, everything just frickin "worked right out of the box" and you probably didn't have to do but one or two of those things, at the most. I say that you got lucky that things were configured just so for the particular character set you were dealing with, and even though from your perspective it didn't seem like that big of a deal, the fact is having an understanding of what's going on behind the scenes can be pretty doggone important anyway, just in case you suddenly get the directive to start storing characters from some other encoding scheme that you AREN'T prepared for out of the box.

Okay, so back to my challenge. Here's the nutshell of how my process flows:

My initial page load is provided with a query of all of the users in the system. That query is then translated to a JS object using a line in my template like the following:

When a user is chosen for edit, I load up the values for that user from the client-side data set into form fields, allow them to be edited, then submit the form values back to Coldbox via an Ajax call where the record gets updated. After the update occurs, my event grabs a fresh copy of the user query (which now contains the updated record), serializes it, and returns it as a JSON string to the Ajax call. Here's the line of JS that performs the Ajax call:

new Ajax.Request(saveURL,{parameters: myparams, method:'post',onCreate:showWorking,onComplete:postSave});

Here is the line in my handler(controller) that returns the data to the call:

<!--- grab a fresh copy of the users to pass back as json to the call, sans a view --->
<cfset arguments.event.renderData(type="plain",data=serializeJSON(variables.userService.getAllUsers(activeOnly=false),true),contentType="application/json) />

 

Bear in mind that the "getAllUsers" method call you see is the exact same method call being used during the initial page load to retrieve the data, which DOES contain the special characters as it should.

So here is where the problem manifests itself. The JSON string that the "postSave" method is provided with has the special characters stripped out! Poof, they are just gone. Okay, so let me go and investigate some of the optional parameters that Prototype provides for its Ajax.Request method and see if any of them might apply in this situation....  Ah, here are a few! 'encoding', 'evalJSON', 'sanitizeJSON'. Well, playing with all three of these resulted in zero changes to the symptoms. Sheesh, I've encoded everything I can possibly think to encode...what else is there? After a lot of google time, skimming page after page of semi-related (but not directly relevant) info, I came across a tiny little tweek to the contentType being returned that I tried, and lo and behold it frickin worked! Here is the new line that returns a CORRECT data set to my Ajax call:

The difference: adding in "charset=UTF-8" to the contentType of the data being returned. Apparently THAT'S what JS was looking for all along.

I hope this helps someone else avoid a huge loss of time. And again, for those of you out there who know this stuff inside and out and can actually visualize how it all works in your head, it sure would be an assett to the community if you could put that info into a kind of "checklist" a person could use to make sure they have all of their Unicode ducks in a row when trying to deal with special characters! Pretty please?

Doug out.

Building upon my last post regarding the setup of dual development environments and how to keep them in sync, this time I want to focus on keeping the development database in sync and how I have approached it.

In my setup, I have an office PC and a home laptop, both running SQL Express as my database server. The goal: to have all changes performed from either location be reflected on both. Here's a diagram (I love pictures) of my current setup:

In reality, a database is nothing more than a file, so in theory then, if we can manage to keep that file synchronized across different machines, we should then be able to keep our database synchronized as well. With that in mind, here are the steps taken to accomplish my goal:

MACHINE 1
1. Create an account with FolderShare (www.foldershare.com), or your favorite file syncing service;
2. On machine 1, create a folder to house your database (when you create a database in SQL Express, it wants to know WHERE the actual database file should reside on your hard drive...you'll be pointing it to this directory when the time comes);
3. Create a share on the FolderShare site that points to the directory you created in step 2;
4. Fire up SQL Management Studio, connect to your local database server, and create a new database (again, being sure to make sure the location of the data and log files are the directory you created);

All done on the first machine! Straightforward stuff. Now for the other machine.

MACHINE 2
1. On machine 2, create a folder to house your database, just as you did on machine 1. You can name it anything you like, and it can live on any drive you like.
2. Using FolderShare, direct the share you created earlier to be synchronized with the folder you created on this machine;
3. Go get a cup of coffee and give FolderShare time to synchronize the directories (copy down the MDF and LDF sql database files);
4. IMPORTANT!  Open up your Control Panel -> Services utility and scroll down to the "SQL Server (SQLEXPRESS)" service. Right click it and choose properties. Click on the "Log On" tab, and click the "Local System Account" radio button. Click 'OK', then RESTART THE SQL SERVICE;

changing SQL Server's log on credentials

5. Fire up your SQL Management Studio and connect to your local database server;
6. Right click 'Databases' and choose 'Attach'. Click the 'Add' button in the resulting dialogue, then navigate your way to the folder you created in step 1;
7. Select the MDF file that should now be there and complete the attach process.

screen shot for attaching the database

VOILA! You are now using a synchronized copy of the same MDF that your other dev machine is! This is the exact setup I have running, and it's working pretty doggone good if I do say so myself.

Okay, all that having been said, please note a few caveats I have found when using this arrangement:

1. Only ever have one SQL Management Studio open at a time. If you are doing inserts or modifying tables from both machines at once, funky things happen and FolderShare suddenly becomes suicidal. In theory you should only be working from one machine at a time anyway, so this shouldn't be too much of an annoyance.
2. Remember that synchronizing takes a few minutes to kick off sometimes, so you'll have to allow a little time when going from one machine to the other before your changes will be visible.
3. If you do NOT perform the services step outlined in the tasks for machine 2, your database will attach, but it will be read-only, and that won't be much help to you.

That should be it! If anybody else has any tricks they use for keeping their dev environments in sync, we're all ears!

Finding the shortest route from diagram to tangible product can be tricky depending on the tool you use to create your Entity Relationship (database) diagrams. I'm using Visio 2003 Professional for mine, and so I went on the hunt for a way to transform diagrams into SQL 2005 Scripts (Visio Professional doesn't include any export features for ER diagrams). The final solution isn't the absolutely most elegant, but I'd call it live-able at least. The basic steps are:

1. Export your diagram to XML;
2. Apply an XSL stylesheet to the XML that generates SQL 2005-compatible scripts.

Exporting XML from VISIO 2003 Pro

Visio Professional doesn't include the luxury of exporting to XML, so I discovered a very sweet third party tool aptly called "Toolbox" made by a generous company named Orthogonal (the tool is free!). Installing Toolbox creates a floating toolbar in Visio that allows you to export the currently open ER diagram. To install simply download the Orthogonal "Toolbox" product, run the setup, restart Visio (if it was open during install), and open an ER diagram. The toolbox should be floating there in your window. If you don't see it, you can go to View,Toolbars and make sure it is checked as being visible.

Creating the SQL Scripts

In my scenario I opted to just go the route of using Internet Explorer to view the finished XML code since it will automatically apply any referenced stylesheets. Because Toolbox let us choose a stylesheet to apply at the time of export, a reference was added to the XML file so when we view it in IE we see a nicely formed SQL 2005 script to create our tables and their relationships. Here's a snapshot of the Toolbox dialog box:

The finished product:

On the subject of the XSLT...Orthagonal provides an XSLT file that creats the script, but without any linebreaks or anything to make it readable. I found a modification to this XSLT on this guy's site , and I modified it a little further in order to account for autoincrementing identity fields, table names that might also be reserved words (who ever does that?), and default values for bit type fields. My version is at the end of this post.

Final Notes
The XML exported by Toolbox doesn't seem to capture anything you put into the "default value" for entity columns. Because of this, I hard-code a rule into my XSLT that says if the field type is bit and NULL is NOT allowed, make the default value true. Also, although Visio does allow you to say that an column is an identity column, it does NOT give you a way to specify that it should autoincrement. I added one more rule to my XSLT that says if the column is INT and is IDENTITY, script out the autoincrement functionality as well.

The XSLT file has some HTML embedded in it in order to allow for an eye appealing output when viewed in IE. If you are going to be applying the stylesheet via another mechanism, you'll probably want to go through the XSLT and strip out the generated HTML first.

TIP: If you weren't aware of it, you can have multiple pages within one Visio ERD document (as in the screenshot at the beginning of this post). The Toolbox export will export one xml file for every diagram on every tab, so if you want to get things done in one shot just consolidate your Visio documents into one!

That should be it. Here are the relevant links for you to get started creating SQL 2005 scripts from your Visio 2003 ER diagrams:
my version of the XSLT file
link to get Toolbox
sample of the XML produced by Toolbox (without xslt applied)
sample of the XML produced by Toolbox (WITH xslt applied!)

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!

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