#Value! error when using builtin POI in ColdFusion MX7

CF 7 (and 8, I think) has the java POI library builtin which means that (amongst other things) you can create native Excel spreadsheet files. cfSearching and Ben Nadel’s great blogs have more details.

I used Ben’s great POI Utility which is a set of custom tags that allows easy creation of XLS files with CSS styles for formatting. Fantastic stuff. However, when I created a cell containing the Excel COUNTIF() function, the cell displayed the #Value! error even though there was no error. By double clicking the cell then pressing enter, the error went away and the correct result was displayed.

After some googling I discovered that there was a bug in earlier versions of the POI java library that is shipped with CF7 (and 8??). So, nothing to do with the POI Utility, everything to do with the underlying Java.

Time to get the latest version of the POI Java jars and use Mark Mandel’s brilliant JavaLoader.cfc to load them into CF. If you need to know how to do that, see cfSearching’s post on that.

Once installed, the question was then how to change Ben’s POI Utility custom tags to use the new version of the jars instead of the builtin version. What I did was the following which solved the problem. I should say at the outset that I am not fluent in cf custom tags (I use objects instead) so there might be a more elegant way to acheive this.

First, add the following function to the POI/document.cfm page:

<cffunction name="getJavaClass" returntype="any" hint="" access="public">
	<cfargument name="javaLoader" type="any" required="yes" hint="">
	<cfargument name="className" type="string" required="yes" hint="">
 <cfscript>
	if (isObject(arguments.javaLoader)) {
		return arguments.javaLoader.create(arguments.className);
	} else {
		return CreateObject("java", arguments.className);
	}
</cfscript>
</cffunction>

This function will be used to load the java jars using the javaLoader if it is passed in, or the normal CreateObject if there is no javaLoader passed in.

Then, in poi/document.cfm:
after

		<cfparam
			name="ATTRIBUTES.Style"
			type="string"
			default=""
			/>

add the following attribute and variables declaration:

		<cfparam
			name="ATTRIBUTES.javaLoader"
			type="any"
			default=""
			/>

		 <cfset variables.javaLoader = ATTRIBUTES.javaLoader>

Then, change all occurrances of

CreateObject( "java",
eg
<cfset VARIABLES.WorkBook = CreateObject( "java", "org.apache.poi.hssf.usermodel.HSSFWorkbook" ).Init(

to

getJavaClass(ATTRIBUTES.javaLoader,
eg
<cfset VARIABLES.WorkBook = getJavaClass(ATTRIBUTES.javaLoader, "org.apache.poi.hssf.usermodel.HSSFWorkbook" ).Init(

Now, in poi/cell.cfm, change:

CreateObject( "java", "org.apache.poi.hssf.util.Region" ).Init(

to

VARIABLES.DocumentTag.getJavaClass(VARIABLES.DocumentTag.javaLoader, "org.apache.poi.hssf.util.Region" ).Init(

That fragment uses the function and variables that are located on the document.cfm page.

Finally, when you use the poi:document tag on your page, pass the instance of your javaLoader, eg:

<poi:document
	javaLoader="#variables.javaLoader#"
	name="REQUEST.ExcelData"
	file="#xlsfname#"
	style="font-family: verdana ; font-size: 10pt ; color: black ; white-space: nowrap ;">

After those small changes, the #Value! error went away! Yay!!

Thanks again to Mark, Ben and Leigh for making all this possible in the first place.

MySQL – left join on last (or first) record in the right table

After spending a few hours searching and testing this one I thought I’d better share it since I found many similar questions but no answers. I am not a MySQL expert so there may be a better way to do this. Please tell me if there is!!

This is a simplified example. I have a table of customers and any sales they may have made. I want to list ALL customers together with the LAST sale they made. ie even if there was no sale at all, I still want the customer data listed with NULLS for the sale data.

Sample Customer data:

custID	custFirst	custLast
1	John	Smith
2	Sally	Fields
3	Winston	Churchill

Sample Sales data:

saleID	saleDate	saleAmount	custID
1	2008-10-20 09:12:00	20	1
2	2007-12-03 18:45:00	14	1
3	2008-02-13 16:00:00	10	3
4	2006-07-08 18:00:00	30	3
5	2007-05-14 14:48:00	18	3
6	2008-10-22 19:00:00	57	1

And the result set from the query below:

custID	custFirst	custLast	saleID	saleDate	saleAmount	custID
1	John	Smith	6	2008-10-22 19:00:00	57	1
2	Sally	Fields	(NULL)	(NULL)	(NULL)	(NULL)
3	Winston	Churchill	3	2008-02-13 16:00:00	10	3

For John and Winston we have the most recent sale they made. Sally hasnt purchased anything yet.

This is the SQL:

SELECT
    customers.*, sale_tmp.*
FROM
    customers

    LEFT JOIN ( SELECT s1.*
		FROM sales as s1
		LEFT JOIN sales AS s2
		     ON s1.custID = s2.custID AND s1.saledate < s2.saledate
		WHERE s2.custID IS NULL ) as sale_tmp

    ON (customers.custID = sale_tmp.custID)

This relies on a couple of sql concepts linked together. The first is the idea that you can LEFT JOIN a temporary table just like a “real” table. So, this bit:

( SELECT s1.*
		FROM sales as s1
		LEFT JOIN sales AS s2
		     ON s1.custID = s2.custID AND s1.saledate < s2.saledate
		WHERE s2.custID IS NULL ) as sale_tmp

creates a temporary table called “sale_tmp” that we are LEFT JOINing to the customers table via the common custID columns. Dont worry about the table construction, just note that a table is being created here.

The second concept is called a within-group aggregate and this is the way that we get the most recent sale for each custID in the sales table. There is a very good explanation of that here:

http://www.artfulsoftware.com/infotree/queries.php?&bw=1395#101

So, to sum up, the technique is to create a temporary table (sale_tmp) that contains just the most recent sales for each custID in the sales table, then left join sale_tmp to customers.

If you wanted their first purchases, simply change

s1.saledate < s2.saledate

to

s1.saledate > s2.saledate

Here is the db dump if you want to play around.

CREATE TABLE `customers` (
  `custID` int(10) NOT NULL auto_increment,
  `custFirst` varchar(50) default NULL,
  `custLast` varchar(50) default NULL,
  PRIMARY KEY  (`custID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `customers` */

insert  into `customers`(`custID`,`custFirst`,`custLast`) values (1,'John','Smith'),(2,'Sally','Fields'),(3,'Winston','Churchill');

/*Table structure for table `sales` */

CREATE TABLE `sales` (
  `saleID` int(10) NOT NULL auto_increment,
  `saleDate` datetime default NULL,
  `saleAmount` double default NULL,
  `custID` int(10) default '0',
  PRIMARY KEY  (`saleID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Data for the table `sales` */

insert  into `sales`(`saleID`,`saleDate`,`saleAmount`,`custID`) values (1,'2008-10-20 09:12:00',20,1),(2,'2007-12-03 18:45:00',14,1),(3,'2008-02-13 16:00:00',10,3),(4,'2006-07-08 18:00:00',30,3),(5,'2007-05-14 14:48:00',18,3),(6,'2008-10-22 19:00:00',57,1);

Now I have that off my chest I can go back to using this in my real application.

Looping over a query with CFScript

It is often convenient to be able to loop over a coldfusion query with CFScript. There is a way to do this even though CF7 does not provide a documented way to do it (and I dont know about CF8).

By harnessing the underlying Java you can do the following:

while( qry.Next() ) {
    qryRow = queryRowToStruct(qry);
    writeOutput( "name=" & qryRow.firstname );
}

(subsititute your relevant column name for “firstname”)

The Next() method is a Java iterator method and can be used on CF queries.

The queryRowToStruct() function is a modified version of the one found at
CFLib.org

The purpose of the function is to return a CF structure containing the data at the specified query row number. See the link above for details.

You just need to modify the line
var row = 1;
to
var row = query.getRow();

getRow() is another Java iterator method and it returns the sequential row number from the query. The Next() method increments the row counter on the query and since the query is passed into the queryRowToStruct() function by reference, each call the getRow() will return the current row number, starting at 1.

Addendum: In order to allow the queryRowToStruct() function to be used as it was originally intended, you need to make another small change so that you can use the function without the iterator and only one parameter ie so that queryRowToStruct(query) will return the first record from the query.

Here is the complete (modified) function (credits to Nathan Dintenfass (nathan@changemedia.com)):

function queryRowToStruct(query){
	// By default, do this to the first row of the query
	//var row = 1;
	// No. By using getRow() we can call this function sequentially and get the next record each time.
	// Or, we can pass the row number we want as the 2nd parameter as before.
	var row = query.getRow();
	//a var for looping
	var ii = 1;
	//the cols to loop over
	var cols = listToArray(query.columnList);
	//the struct to return
	var stReturn = structnew();

	// getRow() will return 0 if next() hasnt been called.
	// So, to allow this function to work by calling it with one parameter and no previous call to next(),
	// we need the following:
	if (row eq 0) row = 1; 

	//if there is a second argument, use that for the row number
	if(arrayLen(arguments) GT 1)
		row = arguments[2];
	//loop over the cols and build the struct from the query row
	for(ii = 1; ii lte arraylen(cols); ii = ii + 1){
		stReturn[cols[ii]] = query[cols[ii]][row];
	}
	//return the struct
	return stReturn;
}

You can also still do this
qryRow = queryRowToStruct(query,3);
to get the third row (as per the original use of the function).

For other info on using Java and queries see:
href=”http://www.bennadel.com/blog/204-Using-ColdFusion-Query-s-Underlying-Java-Methods-For-Query-Manipulation-And-Logic.htm”

Ext – Adding a tree to the portal example

For a project I am working on I wanted to add a tree to a viewport. There is a portal example on the Ext web site that features an accordion control in the west panel. It took a while to figure out how to replace that accordion with a tree even though the method turned out to be quite simple.

See this post on the Ext forum for the key piece of information from another Ext user:
http://extjs.com/forum/showthread.php?t=14445

See this working version (and files to download) on my web site:
http://www.murrah.com.au/samples/ext/portal/portal.cfm

Ext JS library and Coldfusion

I am enjoying using the Ext JS 2.0 library and at the same time finding it very frustrating due to a lack of well documented examples. The library is a very comprehensive object oriented Javascript library that is aimed at building data rich web applications.

To help others (especially Coldfusion programmers like me) I have started an Ext & Coldfusion wiki page on the Ext site that will, I hope, become a central place for CF programmers to leave tips about how to integrate CF and Ext.

The wiki is at http://extjs.com/learn/Manual:Resources:ColdFusion

Hello all!

My blogging starts here.