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”

About these ads

One thought on “Looping over a query with CFScript

  1. Actually, in order to allow the queryRowToStruct() function to work WITHOUT an iterator (as it was originally intended to do) I needed to make a further small changed to the function. I have added that to the original post above.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s