Im a speed freak. Thats why I try to structure my SQL statements in such a way that I can just have Coldfusion loop and output them, without any Query of Queries, swapping data to arrays, or other such nonsense.  Today, I needed to return a query object that would find any auctions in a given date range for a calendar display. My problem was, I wanted each company (4 companies) to return 1 record per day that would show the number of auctions on that day, even if the day had no auctions at all. I know I could have done my auction query grouped by date, then looped a month and QoQ back to my auction result, but thats messy!

I could even have made a table containing every date for the next 20 years and joined too it, but that seems too manual to me.

Here is what I came up with. This uses recursion to create a recordset with each date in my requested range, then joins to the recordset in memory.  How cool is that?  Its zippy fast, and super easy to do a grouped output with.  Here is the SQL, hope it helps someone!

<span class='cc_comment'>/* Declare start and ending dates */</span>
DECLARE @startDate as datetime
SET @startDate = <cfqueryparam cfsqltype=<span class='cc_value'>"cf_sql_date"</span> value=<span class='cc_value'>"#startDate#"</span> />

DECLARE @endDate as datetime
SET @endDate = <cfqueryparam cfsqltype=<span class='cc_value'>"cf_sql_date"</span> value=<span class='cc_value'>"#endDate#"</span> />

<span class='cc_comment'>/* Build temp table of days in my range */</span>
;WITH DayList (DayDate) AS

(
   SELECT @startDate

   UNION ALL

   SELECT DATEADD(d, 1, DayDate)
   FROM DayList
   WHERE (DayDate < @endDate)
)

<span class='cc_comment'>/* Grab the days and sub-query for auction count */</span>
SELECT
   A.DayDate as AuctionDate,
   B.Name as Company,
   B.Url as CompanyURL,
   (SELECT
      Count(*)
   FROM
      auction
   WHERE
      companyID = B.ID
      AND active = 1
      AND deleted = 0
      <span class='cc_comment'>/* necessary to do this so that the times dont mess up the comparison */</span>
      AND CAST(FLOOR(CAST( startDate AS float)) AS datetime) = A.dayDate
   ) as AuctionCount
FROM
   dayList A, Company B

ORDER BY AuctionDate ASC

I recently learned about a neat feature of Coldspring that allows me to create remote proxy's of beans. I went through the doc and set everything up per examples, and my remote proxy's were generated, woohoo! So, I setup my function to return a proper value and I bind a cfgrid to the remote object. I invoke it, and get:

CFGRID: The response is empty

Weird.. so I try to call the CFC directly in the url, and I get a completely blank page. Fast forward about 2 hours of digging through CF and IIS logs trying to figure out why no error is being throw and no output is being displayed, then I find a web post about using cfc's and the onRequest function. For some reason, if you have anything in your onRequest, your call to the remote cfc will fail and give you an illuminating blank response! Here is what I placed in my onRequestStart function to fix it up, and now remoting and web service requests are working perfectly ;)

 

 

This is a quick and dirty blog entry, just wanted to hop on and blog about a method Im using to log database changes or additions done via Transfer.

All of my Transfer objects have decorators that were auto-generated by the very slick Illudium PU36 Code Generator, which basically adds a simple validate method. Normally a transfer decorator will extend transfer.com.TransferDecorator. Well, I have changed all of my decorators to extend a custom decoratorUtility, so that I can create shared functions across all transfer objects. Here is what my utility looks like:

<cffunction name=<span class='cc_value'>"save"</span> access=<span class='cc_value'>"public"</span> returntype=<span class='cc_value'>"void"</span> output=<span class='cc_value'>"false"</span> hint=<span class='cc_value'>""</span>>
   <cfargument name=<span class='cc_value'>"logData"</span> default=<span class='cc_value'>""</span> required=<span class='cc_value'>"false"</span> hint=<span class='cc_value'>"Accepts a string to be logged as the 'action' instead of the default"</span> />
   <cfset var className = '' />
   <cfset var pkName = '' />
   <cfset var pkType = '' />
   <cfset var pkValue = '' />
   <cfset var action = '' />
   <cfset var user = getAuthUser() />
   <cfset var logDate = now() />
   <cfset var log = '' />
   
   <!--<span class='cc_comment'>-// Gather some information about what we will be saving //---></span>   <cfset className = getClassName() />
   <cfset pkName = getTransfer().getTransferMetadata(className).getPrimaryKey().getName() />
   <cfset pkType = getTransfer().getTransferMetadata(className).getPrimaryKey().getType() />
   
   <!--<span class='cc_comment'>-// Determine if this record is a create or an update //---></span>
   <cfif getIsPersisted()>
      <cfset action = 'Updated' />
   <cfelse>
      <cfset action = 'Created' />
   </cfif>
   
   <!--<span class='cc_comment'>-// Invoke the underlying Transfer save function //---></span>   <cfset getTransfer().save(this) />
   
   <!--<span class='cc_comment'>-// Get the primary key value //---></span>   <cfset pkValue = this.getID() />
   
   <!--<span class='cc_comment'>-// Construct and save our transaction log record //---></span>   <cfset log = getTransfer().new('transactionLog') />
   <cfset log.setCompanyID(0) />
   <cfset log.setLogItem(className) />
   <cfset log.setlogID(pkValue) />
   <cfset log.setlogDate(logDate) />
   <cfset log.setlogData(arguments.logData) />
   <cfset log.setlogType(action) />
   <cfset log.setlogIP(cgi.REMOTE_ADDR) />
   
   <!--<span class='cc_comment'>-// If the user is logged in, add to the log //---></span>   <cfif isNumeric(user)>
      <cfset log.setlogUser(user) />
   </cfif>      
   <cfset getTransfer().save(log) />      
</cffunction>

This simply overrides the auto-generated save() function and will first get information about the transfer object to be saved, log it (using transfer still), then invokes the built-in save function. Obviously this has possibilities outside of logging, you could do security checks here, you could add utility / encryption functions here (think encrypting all data in your database, and then decrypting it after reading all in 1 spot)

Anyone else doing something like this?

Getting a web hosting deal is not a problem. The problem is to decide between startlogic or bluehost or the complex netfirms.

When I first started writing reports, I would jump through all different hoops to output grouped data.  Read on to see how Coldfusion and grouped output can make these reports a snap!

I have looked around some online, and not found a nice pretty image gallery for free, in CF, that I liked. So, here goes my effort to write one.

I started it this past week, and while I am off next week I hope to get the majority of it complete. Im writing this with CF8 in mind, and plan on packing up Model-Glue, transfer, coldspring, jQuery, and anything else I need in the mix into 1 easy to distribute file. I plan on using the new cfimage functions to manipulate / thumbnail images, and the plan is to make this super easy to add new images via a bulk upload into a specific location, then running an import page that will allow captioning and titles. I will use the same page style as my blog, BluePigment 1.0 by Styleshout

I still need to choose a license, but I hope that this can be my 1st open-source project, and hosted on RiaForge.