Creating a function in Coldfusion to dynamically convert a multidimensional array to a query object

Question

The problem I'm running into is the API I'm using for this particular project uses periods in the keys which is not allowed in query column names. So I need to figure out a way to rename the array keys to remove the periods (or replace them with an underscore). Here is the code I have so far, which is working, but throws an error when hitting the "accumulator.addRow" line. The error being that periods aren't allowed in column names.


    function arrayToQuery(varChapterRoster) {
        return varChapterRoster.reduce(function(accumulator, element) {
            element.each(function(key) {
                if (!accumulator.keyExists(replace(key,".","_","all"))) {
                    accumulator.addColumn(replace(key,".","_","all"), []);
                }
            });
            //writeDump(element);
            accumulator.addRow(element);
            return accumulator;
        }, QueryNew(""));
    }
    // writeDump(varChapterRoster);
    writeDump(arrayToQuery(varChapterRoster));

Specific error: Column names must be valid variable names. They must start with a letter and can only include letters, numbers, and underscores.

I got past the "keyexists" and "addcolumn" lines by using the replace function, but replace won't work on "element" (throws an error) on the addRow line.

Changing tactics, here is a CFFiddle with some dummy data in it of the method I'm trying now. I think it is more straight forward, but getting another error that I bet is related to the periods again.

user_1479535 · Answer

Answer based on original post. Demo


    function arrayToQuery(varChapterRoster) {
        if(varChapterRoster.len() == 0){
            return QueryNew('');
        }
        return varChapterRoster.reduce(function(accumulator, element) {
            Row = QueryAddRow(accumulator);
            element.each(function(key) {
                QuerySetCell( accumulator , replace(key,".","_","all") , element[key] , Row );
            });
            return accumulator;
        }, QueryNew(replace(StructKeyList(varResults[1]),".","_","all")));
    }
    // writeDump(varChapterRoster);
    writeDump(arrayToQuery(varResults));

Answer based on your cffiddle. Demo

Creating a function in Coldfusion to dynamically convert a multidimensional array to a query object

1 Answers