ColdFusion 11, IIS 8, CFflush

I found that CFflush wasn’t working for me under CF11 and IIS8.

Following the instrucitons at both StackOverflow and Adobe didn’t help.

I read  CF911: Why/when you MUST update the web server connector […] and ColdFusion 11 IIS Connector Tuning which guided me in the right direction.

What you need to do:

  1. Open the ‘Web Server Configuration tool’. You can find that as C:\ColdFusion10\cfusion\runtime\bin\wsconfig.exe
  2. Remove the web server already configured there.
  3. Add a new web server.
  4. Click Advanced.
    1. Untick Enable Buffering.
    2. Click OK.
  5. Click OK, let it restart IIS.

reconfigure server

Restarting the ColdFusion service

When restarting the ColdFusion service sometimes IIS connections get stuck.

To prevent the connections from getting stuck, you have to make sure IIS is stopped before restarting ColdFusion. Tedious work within the Services Manager. Instead, create a batch file with these contents:

net stop W3SVC
net stop "ColdFusion 11 Application Server"
net start "ColdFusion 11 Application Server"
net start W3SVC

Just be sure to replace the ColdFusion service name with your version.

MySQL – report on sales by month for a given time period

Here’s a nice sql script to get a report of monthly orders and sales for a time period.

SELECT
 YEAR(DATETIME) AS `year`,
 MONTHNAME(DATETIME) AS `month`,
 SUM(subtotal) AS `subtotal`,
 count(*) AS orders
FROM orders
WHERE DATETIME BETWEEN '2014-04-01' AND '2014-07-01'
GROUP BY YEAR(DATETIME), MONTH(DATETIME)

mysql report sales

cfGrid – use full document width

One of the pains of using cfgrid is that the width has to be hard-coded in. As a developer, you can’t assume the user’s screen width, even if they are admins with directions/instructions. Fortunately, to get around this shortcoming is not too difficult.

First we’ll use javascript on the back office welcome page to get the screen width and save that as a cookie. In application.cfc we’ll test for that value first in the session scope, then in cookies, and take action appropriately. Finally in the template with a cfgrid we’ll insert that value as the width.

CMS Welcome Page

When the client signs in successfully, add some javascript to the <head> of the welcome page. Lets load jQuery to make this a bit simpler. Then add some nice cookie functions. At the end of the javascript, save a cookie ‘bodyWidth’ with value of the window’s width – minus 20 just for breathing room. This works within a frame, too, by the way. After that, you want to make sure that the previous (and perhaps incorrect) bodyWidth saved in the session is erased.

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script type="text/javascript">
function createCookie(name, value, days) {
 if (days) {
  var date = new Date();
  date.setTime(date.getTime() + (days * 24 * 60 * 60 * 1000));
  var expires = "; expires=" + date.toGMTString();
 } else var expires = "";
  document.cookie = escape(name) + "=" + escape(value) + expires + "; path=/";
}

function readCookie(name) {
 var nameEQ = escape(name) + "=";
 var ca = document.cookie.split(';');
 for (var i = 0; i < ca.length; i++) {
  var c = ca[i];
  while (c.charAt(0) == ' ') c = c.substring(1, c.length);
  if (c.indexOf(nameEQ) == 0) return unescape(c.substring(nameEQ.length, c.length));
 }
 return null;
}

function eraseCookie(name) {
 createCookie(name, "", -1);
}

$(document).ready(function(){
 createCookie("bodyWidth", $(document).width()-20, 14);
});
</script>
<cfset structDelete(session, 'bodyWidth') />

Application.cfc

In the ‘OnRequestStart’ function of application.cfc you’ll need this snippet. It checks to see if there is a session variable of ‘bodyWidth’. If not, it looks for the cookie. If no cookie, default to 1067. Of course you can set that default to whatever you’d like.

<!--- GRID WIDTHS --->
<cfif not isDefined("session.bodyWidth")>
 <cfif isDefined("cookie.bodyWidth")>
  <cfset session.bodyWidth = cookie.bodyWidth>
 <cfelse>
  <cfset session.bodyWidth = 1067>
 </cfif>
</cfif>

Template with cfGrid

Now the easy part. In your cfGrid tag simply use the width stored in session.

...
width="#session.bodyWidth#"
...

Remove Google’s ‘Play’ button with Stylish

I can’t imagine the discussion over at Google to put the Play button in the black Google bar. “Oh yes, this is definitely more important than anything under the ‘More’ menu.”

Anyway – banish it for good with a simple Stylish style. Create a new style and use this as the content:

@namespace url(http://www.w3.org/1999/xhtml);
/* Remove google's Play button */

#gb_78 {
 display: none !important;
}

No more Play button. Back to Work!

ColdFusion tag usage syntax

While beta testing web development software I was asked to provide a list of self-closing CFML tags. After some research I found there are 4 types of tags:

  • Self-closing
  • Self-closing requiring the terminating forward slash, or can be containers
  • Self-closing, must never have the terminating forward slash
  • Containers

Hopefully this may help someone else. As of Adobe’s CF9, here are the tags:

Self Closing Tags

cfabort
cfajaximport
cfajaxproxy
cfapplet
cfapplication
cfargument
cfassociate
cfbreak
cfcalendar
cfcol
cfcollection
cfcontent
cfcontinue
cfcookie
cfdbinfo
cfdirectory
*^ cfdiv
cfdump
! cfelse
! cfelseif
cferror
cfexchangecalendar
cfexchangeconnection
cfexchangecontact
cfexchangefilter
cfexchangetask
cfexit
cffeed
cffile
cffileupload
cfflush
* cfformitem
cfftp
cfgridcolumn
cfgridrow
cfgridupdate
cfheader
cfhtmlhead
cfhttpparam
cfimage
cfimap
cfimport
cfinclude
cfindex
cfinput
cfinsert
* cfinvoke
cflocation
cflog
cfloginuser
cflogout
cfmailparam
cfmap
cfmapitem
cfmediaplayer
* cfmenuitem
cfmessagebox
cfmodule
cfNTauthenticate
cfobject
cfobjectcache
cfparam
* cfpdfform
cfpdfformparam
cfpdfparam
cfpdfsubform
* cfpod
cfpop
* cfprocessingdirective
cfprocparam
cfprocresult
cfprogressbar
cfproperty
cfqueryparam
cfregistry
cfreportparam
cfrethrow
cfreturn
cfschedule
cfsearch
cfset
cfsetting
cfsharepoint
cfslider
cfspreadsheet
cfsprydataset
* cfstoredproc
*^ cfthread
cfthrow
cftrace
cftreeitem
cfupdate
cfwddx
* cfzip
cfzipparam

Container Tags

cfcache
cfcase
cfcatch
cfchart
cfchartdata
cfchartseries
cfcomponent
cfdefaultcase
cfdocument
cfdocumentitem
cfdocumentsection
cfexchangemail
cfexecute
cffinally
cfform
cfformgroup
cffunction
cfgrid
cfhttp
cfif
cfinterface
cfinvokeargument
cflayout
cflayoutarea
cfldap
cflock
cflogin
cfloop
cfmail
cfmailpart
cfmenu
cfoutput
cfpdf
cfpresentation
cfpresentationslide
cfpresenter
cfprint
cfquery
cfreport
cfsavecontent
cfscript
cfselect
cfsilent
cfswitch
cftable
cftextarea
cftimer
cftooltip
cftransaction
cftree
cftry
cfwindow
cfxml

* These tags can also be container tags.
^ These tags when self-closing REQUIRE the terminating forward slash.
! These tags must never have the terminating forward slash.

Trimming a decimal with coldfusion & regex

Today I had to import lots of measurements into a mySql table. The columns allow 3 places after the decimal. The data has mixed precision – some values use all 3 places, others use none. To make the output look clean I didn’t want to show all 3 places all the time. So numberFormat() was out. And unlike PHP, coldFusion’s trim() function does not take any parameters (trim trailing zeros and periods). I came up with using two regular expressions to remove any trailing zeros, then the trailing period if any.

Here is some example data:

  • 6.000
  • 0.375
  • 0.500

When using numberFormat(), the output is:

  • 6
  • 0
  • 1

… and numberFormat(val, 0.000) gives:

  • 6.000
  • 0.375
  • 0.500

Blech. So here’s the function that gave exactly what I wanted:

<cffunction name="trimDecimal">
 <cfargument name="value">
 <cfreturn REReplace(REReplace(value, "0+$", "", "ALL"), ".+$", "")>
</cffunction>

… trimDecimal(val) resulting in…

  • 6
  • 0.375
  • 0.5

Perfect.

Return a query of all CF Scheduled tasks

I was working on a scheduled task in a shared hosting environment. Which is pretty frustrating when you don’t have access to the CF Administrator.

Googling, I found some functions that would return information about scheduled tasks via coldfusion.server.ServiceFactory. That of course would not work since shared hosting environments have that locked down.

Then I came across an old post by Ben Forte. Instead of ServiceFactory he used an undocumented task called ‘__list’. It almost worked… except that it errored out on some task attributes. I guess a mismatch in versions of CF is to blame.

So I took that undocumented call, then rebuilt the rest entirely. I basically turned the data into tab-separated-values. I haven’t gotten around to learning reFind(), etc yet so please excuse the many replace() functions. It’s very low tech, but it works.

It won’t matter if you expect extra column names than __list gives (those columns in the returned query will be [empty string]s). And it also won’t matter if __list gives more than you’ve expected (those extra columns won’t be in the returned query). In either case, there will be no errors.

<cffunction
	name="getCFScheduledTasks"
	returntype="query"
	output="no"
	hint="Returns a query of CF Scheduled tasks on the server"
>
<!---
Thanks to Ben Forte for posting the undocumented call '__list'
--->
	<cfsavecontent variable='tasks'>
	    <cfschedule action='run' task='__list'>
	</cfsavecontent>

	<!--- TRIM WHITESPACE --->
	<cfset tasks = trim(tasks)>
	<cfset tasks = replace(tasks, chr(10), '', 'all')>

	<!--- REMOVE FIRST AND LAST BRACKETS --->
	<cfset tasks = mid(tasks, 2, len(tasks)-3)>

	<!--- CHANGE THE TASK DELIMETER --->
	<cfset tasks = replace(tasks, '}}},', '}}#chr(10)#', 'all')>

	<!--- CHANGE THE COLUMN DELIMETERS --->
	<cfset tasks = replace(tasks, '={{', chr(9), 'all')>
	<cfset tasks = replace(tasks, '},', '}#chr(9)#', 'all')>

	<!--- REMOVE THE ROW TRAILING BRACKETS --->
	<cfset tasks = replace(tasks&chr(10), '}}#chr(10)#', '}#chr(10)#', 'all')>

	<!--- REMOVE THE COLUMN TRAILING BRACKETS --->
	<cfset tasks = replace(tasks, '}#chr(9)#', chr(9), 'all')>

	<!--- TASK DATA COLUMNS --->
	<cfset columns = 'task,start_date,start_time,last_run,end_time,interval,operation,url,resolveurl,request_time_out,username,password,http_port,path,proxy_server,http_proxy_port,file,disabled,paused,publish'>

	<!--- CREATE A QUERY --->
	<cfset cfScheduledTasks=QueryNew(columns)>

	<!--- LOOP OVER THE ROWS & COLUMNS, ADDING TO THE QUERY --->
	<cfloop list="#tasks#" delimiters="#chr(10)#" index="row">
		<cfset QueryAddRow(cfScheduledTasks)>
		<cfloop list="#columns#" index="column">
			<cfloop list="#row#" delimiters="#chr(9)#" index="data">
				<cfif spanExcluding(data, '=') eq column>
					<cfset QuerySetCell(cfScheduledTasks, column, replace(data, column&'={', ''))>
				</cfif>
			</cfloop>
		</cfloop>
	</cfloop>

	<!--- WWWEEEE! --->
	<cfreturn cfScheduledTasks>
</cffunction>

<cfset cfScheduledTasks = getCFScheduledTasks()>
<cfdump var='#cfScheduledTasks#'>

Ben’s original code is at: http://www.forta.com/blog/index.cfm/2006/8/28/GetScheduledTasks-Function-Returns-Scheduled-Task-List

MySQL: Ignore null values when ordering results

Fast answer: use IFNULL() in your select statement.

Lets say you have some products that have a length attribute which is recorded in two different ways: inches and feet. In your CMS, the client enters EITHER inches OR feet. When generating a list of products sorted by length, you’d want the ‘inch’ products up top and the ‘feet’ products under them. At first, you’d probably try this:

SELECT id, title, lenIN, lenFT
FROM products
ORDER BY lenIN, lenFT

But that would give you the ‘feet’ products first since the inch values would be null. A second attempt:

SELECT id, title, lenIN, lenFT
FROM products
ORDER BY lenIN DESC, lenFT

That of course puts the products in the wrong ordering. Here is the fix:

SELECT id, title, IFNULL(lenIN, 1000) as tempIN, lenIN, lenFT
FROM products
ORDER BY tempIN, lenIN, lenFT

It comes from the magic of:

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2. So when inches is null, a value of 1000 is used. I picked ‘1000’ because I’m sure no product will be 1000 inches long (feet would surely be used at that point). The result is the product which have inches filled will be put at the top of the list, ordered correctly, followed by the products where inches were blank (and are now 1000 inches).