CFLib.org – Common Function Library Project

arrayCollectionToQuery(arrayColl)

Last updated March 3, 2010

author

Adam Tuttle

Version: 0 | Requires: CF7 | Library: DataManipulationLib

Description:
For people that are more comfortable working with queries than arrayCollections, this should help.

Return Values:
cfquery object

Example:

<cfset testData = [
{Num=2, String='fubar!', Bool=true, Date=CreateDate(2009,05,2)},
{Num=4.8, String='bufar!', Bool=false, Date=CreateDate(2009,06,1)},
{Num=6, String='futbol!', Bool=true, Date=CreateDate(2009,07,12)},
{Num=8, String='string!', Bool=false, Date=CreateDate(2009,08,9)},
{Num=10, String='data type!', Bool=true, Date=CreateDate(2009,09,18)},
{Num=12, String='yes', Bool=false, Date=CreateDate(2009,10,6)}
] />

<cfdump var="#testData#" label="data in">
<cfdump var="#arrayCollectionToQuery(testdata)#" label="data out">

Parameters:

Name Description Required
arrayColl Flex array collection Yes

Full UDF Source:

/**
 * Converts a Flex ArrayCollection object to a ColdFusion Query object
 * 03-mar-2010 added arguments scope
 * 
 * @param arrayColl 	 Flex array collection (Required)
 * @return cfquery object 
 * @author Adam Tuttle (adam@fusiongrokker.com) 
 * @version 0, March 3, 2010 
 */
function arrayCollectionToQuery(arrayColl){
	var qResult = 0;
	var columnList = structKeyList(arguments.arrayColl[1]);
	var typeList = '';
	var numericType = '';
	var k = 0;
	var i = 0;
	for ( k in arguments.arrayColl[1] ){
		if (isNumeric(arguments.arrayColl[1][k])){
			//decimal or integer?
			numericType = 'integer';
			for ( i = 1 ; i lte arrayLen(arguments.arrayColl) ; i = i + 1 ){
				if (arguments.arrayColl[i][k] - fix(arguments.arrayColl[i][k]) eq 0){
					numericType = 'decimal';
					break;
				}
		}
			typelist = listAppend(typeList, numericType);
		} else if (isSimpleValue(arguments.arrayColl[1][k])){
			typeList = listAppend(typeList, 'varchar');
		} else if (isBoolean(arguments.arrayColl[1][k])){
			typeList = listAppend(typeList, 'bit');
		} else if (isDate(arguments.arrayColl[1][k])){
			typeList = listAppend(typeList, 'date');
		} else {
			//we can't throw() in cf8, so uh...
			return "All keys in your array collection must be of one of the following types: Numeric (Int or Float), String, Boolean, Date. The following key contains data that is not one of these types: `#k#`";
		}
	}
	qResult = queryNew(columnList, typeList);
	for ( i = 1 ; i lte arrayLen(arguments.arrayColl) ; i = i + 1 ){
		queryAddRow(qResult);
		for (k in arguments.arrayColl[i]){
			if (not isNumeric(arguments.arrayColl[i][k]) and not isSimpleValue(arguments.arrayColl[i][k]) and not isBoolean(arguments.arrayColl[i][k]) and not isDate(arguments.arrayColl[i][k])){
				return "All keys in your array collection must be of one of the following types: Numeric (Int or Float), String, Boolean, Date. The following key contains data that is not one of these types: `#k#`";
			}
			querySetCell(qResult,k,arguments.arrayColl[i][k]);
		}
	}
	return qResult;
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson