Codito Ergo Sum

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!

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

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

/* Build temp table of days in my range */
;WITH DayList (DayDate) AS

(
   SELECT @startDate

   UNION ALL

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

/* Grab the days and sub-query for auction count */
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
      /* necessary to do this so that the times dont mess up the comparison */
      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="save" access="public" returntype="void" output="false" hint="">
   <cfargument name="logData" default="" required="false" hint="Accepts a string to be logged as the 'action' instead of the default" />
   <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 = '' />
   
   <!---// Gather some information about what we will be saving //--->
   <cfset className = getClassName() />
   <cfset pkName = getTransfer().getTransferMetadata(className).getPrimaryKey().getName() />
   <cfset pkType = getTransfer().getTransferMetadata(className).getPrimaryKey().getType() />
   
   <!---// Determine if this record is a create or an update //--->
   <cfif getIsPersisted()>
      <cfset action = 'Updated' />
   <cfelse>
      <cfset action = 'Created' />
   </cfif>
   
   <!---// Invoke the underlying Transfer save function //--->
   <cfset getTransfer().save(this) />
   
   <!---// Get the primary key value //--->
   <cfset pkValue = this.getID() />
   
   <!---// Construct and save our transaction log record //--->
   <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) />
   
   <!---// If the user is logged in, add to the log //--->
   <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!

Read more...

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.

I updated the site with a template from http://www.styleshout.com/. What do you guys think? Some of the links are not live yet, but overall I think its lookin decent. I know, its *very* blue, but thats my favorite web color. =)

I was trying to configure my web server and coldfusion servers on seperate machines. My IIS server was setup to pull source from d:\\webroot. I copied the wsconfig.exe tool and its INI file from my coldfusion server root\\bin folder and ran it on my web server too hook them together.\n\nWhen I requested a file, I got a coldfusion message back file not found. If you look at the CF template path, the coldfusion server is looking for D:\\webroot\\blah.cfm (obviously that does not exist on the CF server, but only on the web server)\n\nI changed my IIS web server to point at itself using a UNC path instead of D:\\webroot, it became \\\\merovingian\\webroot, and now CF and IIS see the same path to my files and all is well.

Very cool social networking site. If you want to view my amazing, beautiful, outstanding and flagrantly fascinating profile, just click!

My Profile