Yesterday I posted about how you could use the "MOD (Modulo) with CFOUTPUT to build a table to display four pairs of repeating data from a query structure" and thanks to a response by Michael Zock, I realized that my example was not proofread so here is a redux example of Modulo driven column display from a query.

<!---USE NORTHWIND DATABASE TO BUILD QUERY--->
<cfquery name="GetInfo" datasource="Northwind">
   SELECT OrderID, ShipName
   FROM Orders
</cfquery>

<table border="1" cellpadding="0" cellspacing="0">
   <tr>
      <!---LOOP FOUR TIMES TO BUILD REPEATING HEADER--->
      <cfloop index="LoopIndex" from="1" to="4">
         <th>Order ID</th>
         <th>Ship Name</th>
      </cfloop>
   </tr>
   <!---OUTPUT QUERY--->
   <cfoutput query="GetInfo">
      <!---USE MODULO 4 TO DETERMINE IF CURRENT RECORD DIVISIBLE BY FOUR FOR TR--->
      <cfif GetInfo.CurrentRow MOD 4 EQ 1 OR GetInfo.CurrentRow EQ 1><tr></cfif>
         <td width="5%">#GetInfo.OrderID#</td>
         <td width="20%">#GetInfo.ShipName#</td>
      <!---USE MODULO 4 AND CHECK IF END OF QUERY FOR CLOSING TR OF EACH ROW--->
      <cfif GetInfo.CurrentRow MOD 4 EQ 0 OR GetInfo.RecordCount EQ GetInfo.CurrentRow>
         <!---CHECK IF LAST ROW HAS LESS THAN 4 RESULTS TO PAD TABLE--->
         <cfloop index="LoopIndex" from="1" to="#GetInfo.CurrentRow MOD 4#">
            <td width="5%"> </td>
            <td width="20%"> </td>
         </cfloop>
         </tr>
      </cfif>
   </cfoutput>
</table>
<br /><br />
<!---EXAMPLE REPEATED WITH QUERY MAXROWS 16 (4x4) TO PROVE CLOSING TR AND QUERY END CHECK--->
<table border="1" cellpadding="0" cellspacing="0">
   <tr>
      <cfloop index="LoopIndex" from="1" to="4">
         <th>Order ID</th>
         <th>Ship Name</th>
      </cfloop>
   </tr>
   <cfoutput query="GetInfo" maxrows="16">
      <cfif GetInfo.CurrentRow MOD 4 EQ 1 OR GetInfo.CurrentRow EQ 1><tr></cfif>
         <td width="5%">#GetInfo.OrderID#</td>
         <td width="20%">#GetInfo.ShipName#</td>
      <cfif GetInfo.CurrentRow MOD 4 EQ 0 OR GetInfo.RecordCount EQ GetInfo.CurrentRow>
         <cfloop index="LoopIndex" from="1" to="#GetInfo.CurrentRow MOD 4#">
            <td width="5%"> </td>
            <td width="20%"> </td>
         </cfloop>
         </tr>
      </cfif>
   </cfoutput>
</table>

I hope this more complete example better illustrates a use for the MOD (Modulo) than just alternating row colors for table rows!

If you find this post useful please leave a comment and let me know how you used the information.

Download zipped example HERE.

Here is an example of how you can use MOD (Modulo) with CFOUTPUT to build a table to display four pairs of repeating data from a query structure. Using this method will build the table with the information ITEM and TOTAL repeating four times across each row. In the event that the total number of records in the query are not divisible by four, the table cells will end early.

<table border="0" cellpadding="0" cellspacing="0">
   <tr>
      <th>ITEM</th>
      <th>TOTAL</th>
   </tr>
   <cfoutput query="GetInfo">
      <cfif GetInfo.CurrentRow MOD 4 EQ 1 OR GetInfo.CurrentRow EQ 1><tr></cfif>
         <td width="20%">#GetInfo.Item#</td>
         <td width="5%">#GetInfo.Total#</td>
      <cfif GetInfo.CurrentRow MOD 4 EQ 0 OR GetInfo.RecordCount></tr></cfif>
   </cfoutput>
</table>

This example may need to be tweaked for your purpose but it may help you avoid developing an unnecessarily complicated solution for a simple data display puzzle. Using MOD works because when the CurrentRow is divided by 4 evaluates as 0 when progressing through the query. First time through the loop the CurrentRow is 1 which is not divisible by 4 so it progresses to the next CurrentRow. This keeps going until it reaches a CurrentRow that is divisible by 4 then ends the row and starts a new one.

If you find this post useful please leave a comment and let me know how you used the information.

I recently attended a ColdFusion certification course. One of the things I learned in the course was the use of Stored Procedures(SP). SP are a great way to move your SQL statements out of your code and allows for code re-use. It also takes the strain off your ColdFusion server and forces your SQL server to crunch all the calculations/numbers (which is what it is intended for). This practice will help speed up your process time for your page.

One of the features of the cfstoredproc command is the cfprocresult command. The cfprocresult returns the resultset of your stored procedure as a query. If you have more than one resultset, the cfprocresult has an attribute to set which resultset you want.

For example, you create a stored procedure for finding the employees who are male or female in your organization. The stored procedure might look something like this:

BEGIN
   SET NOCOUNT ON;

   --Get Males

   SELECT   FirstName, LastName
   FROM   TableEmployee
   WHERE   Gender = 'M'

   --Get Females

   SELECT   FirstName, LastName
   FROM   TableEmployee
   WHERE   Gender = 'F'

   END

Within your code on your page, you can call these two results like this:

<cfstoredproc procedure="spEmployeesByGender" datasource="your_datasource">
      <cfprocresult name="Males">
      <cfprocresult name="Females" resultset="2">
   </cfstoredproc>

There is no need to put a resultset on the first cfprocresult. However, if you only wanted to return the Females in this example, you must include the resultset attribute.

You can also pass variables to your stored procedure using the cfprocparam attribute of the cfstoredproc tag. Here is an example of the cfstoredproc tag with cfprocparam and cfprocresult.

<cfstoredproc procedure="spEmployeesByGender" datasource="your_datasource">
      <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="M">   <!--- Send M for Males --->
      <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="F">   <!--- Send F for Females --->   
      <cfprocresult name="MaleEmployees">
      <cfprocresult name="FemaleEmployees" resultset="2">
   </cfstoredproc>

Now you can define vairables within your stored procedure to pass to your query statements.

@MyMaleGender VARCHAR(1), @MyFemaleGender VARCHAR(1)

   BEGIN
   SET NOCOUNT ON;

   --Get Males

   SELECT   FirstName, LastName
   FROM   TableEmployee
   WHERE   Gender = @MyMaleGender

   --Get Females

   SELECT   FirstName, LastName
   FROM   TableEmployee
   WHERE   Gender = @MyFemaleGender

   END

Our customers have a process where the lowest level of the organization submits weekly activity statuses up the chain. Each higher level may have 1 or more reporting sub levels and would require them to compile the results before submitting to their superiors. They have, for the longest time, been using email as their means of submission. The reports would come in as either messages in the email itself or Word documents attached to the email. There was no standardization among them. They would take hours cutting and pasting the comments before submitting up the ladder.

So, they came to us asking if their was a better way. We created a web application that displays a standard entry form with categories and text fields for each level to fill out. Once they submit the form, the data is collected in the tables. As the next level logs into the web application, the data from the tables would be compiled and then displayed to the screen.

In order to accomplish this, we use a Scalar-valued Function. The function receives attributes passed from the application and returns the compiled string to display. Here is how it works:

Table
UnitID   Date      CategoryID   CategoryContent   
1   6/13/2011   1      Unit 1 Cat 1
2   6/13/2011   1      Unit 2 Cat 1
3   6/13/2011   1      Unit 3 Cat 1
4   6/13/2011   1      Unit 4 Cat 1
1   6/13/2011   2      Unit 1 Cat 2
2   6/13/2011   2      Unit 2 Cat 2
3   6/13/2011   2      Unit 3 Cat 2
4   6/13/2011   2      Unit 4 Cat 2

Inside your cfquery tag you would include the function call like this

<cfquery name="CompileComments" datasource"YourDataSource">
SELECT DISTINCT
   UnitID, dbo.fnCompileComments('6/13/2011', '1', CategoryID) AS CompiledComments
FROM   TableName
WHERE   Date = '6/13/2011' AND UnitID = 1
</cfquery>

Function name in SQL is dbo.fnCompileComments. List the variables you are passing it and then declare the return Variable with AS.

The SQL function looks like this:

USE [DatabaseName]
GO
/****** Object: UserDefinedFunction [dbo].[fnCompileComments] Script Date: 06/13/2011 13:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fnCompileComments]
(
   @MYDate DATE,
   @MYUnitID INT,
   @MYCategoryID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
   DECLARE @Comments VARCHAR(MAX)

SELECT @Comments = COALESCE(@Comments + char(10) + char(13) + '|', + '') + CategoryContent
FROM   TableName
WHERE Dater = @MyDate AND
   UnitID = @MyUnitID AND
CategoryID = @MyCategoryID

RETURN @Comments

END

The expected result:
Unit 1 Cat 1
|Unit 2 Cat 1
|Unit 3 Cat 1
|Unit 4 Cat 1

Unit 1 Cat 2
|Unit 2 Cat 2
|Unit 3 Cat 2
|Unit 4 Cat 2

The user defined function uses the COALESCE function to compile the comments together. I use a char(10) and a char(13) to put a return line and carriage return between each comment. I also include a unique character "|" that I use for a specfic function elsewhere in the application.

Last year, I wrote a File Management application in ColdFusion9. It allows users to manage their uploaded content online through a web Graphical User Interface (GUI). Sometimes, when a user is moving a folder of files, or renaming a folder of files; the SQL will not execute the as expected. This seems to happen when the files are held open by another user.

As a result, the database does not update with the new information, but the ColdFusion manipulates the files and folders anyway. This causes the application to become unstable since it now has rouge files and folders that are not tracked by the database.

To help reduce this risk, I recently implemented a small level of error checking on the SQL statements using @@ROWCOUNT. This has only been tested with MS SQL2008 and ColdFusion9.

<cfquery name="SetFileValue" datasource="DataBaseName">
   SET NOCOUNT ON
   UPDATE   TableFile
   SET   FileValue = 'SomeValue'
   WHERE   FileID = 2

   SELECT ChangeCount = @@ROWCOUNT
   SET NOCOUNT OFF
</cfquery>

This will result in a value being assigned to SetFileValue.ChangeCount and I can check if it is greater than zero, meaning more that one result has occurred in the update query. I could also check with a SetFileValue.RecordCount, but this is making SQL do more work and returning a scoped value instead of a ColdFusion query result value. If you follow. The @@ROWCOUNT method above was aslo used because it can be retrieved now by MS SQL, not just ColdFusion.

Now I can check that the update actually occurred and resulted in at least one record change before performing the next step. In this case, renaming a file from one value to another on the server.

<cfif IsDefined("SetFileValue.ChangeCount") AND SetFileValue.ChangeCount GT 0>
   <cffile action="rename" destination="#FileNew#" source="#FileOld#">
</cfif>

If you find this post useful please leave a comment and let me know how you used the information.