CFLib.org – Common Function Library Project

arrayCollectionToQuery(arrayColl)

Last updated March 3, 2010
Download UDF

author

Adam Tuttle Adam Tuttle

Version: 0 | Requires: ColdFusion MX7 | Library: DataManipulationLib

 
Rated 1 time(s). Average Rating: 5.0

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:

<cfscript>
/**
* 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;
}
</cfscript>

Search CFLib.org


Latest Additions

Alan McCollough Alan McCollough added
forceBoolean
1 day(s) ago

Shawn Porter Shawn Porter added
DeMoronize
4 day(s) ago

Chris Carey Chris Carey added
readPropertiesFi...
5 day(s) ago

Randy Johnson Randy Johnson added
lastDayofWeek
7 day(s) ago

Top Rated

James Sleeman                                     QuickSort
Rated 5.0, 3 time(s)

Barney Boisvert indentXml
Rated 5.0, 3 time(s)

Nathan Dintenfass                                 queryColumnsToSt...
Rated 5.0, 3 time(s)

Kevin Pepperman generateSsccAsn
Rated 5.0, 3 time(s)

Created by Raymond Camden / Design by Justin Johnson