Using column names from dynamically named variables in Coldfusion

Question

I want to create a function that will loop through an arbitrary query and perform an insert into another table based on the arbitrary column names.

For instance, the idea here would be to output

(data, data, data...)
(data, data, data...)

Ultimately, I'm going to do an insert query. For now, I just want to output the rows.

EDIT: I can't just do an INSERT/SELECT because the prod data and dev data are on different servers. So I have to first collect the data from the table on the prod server into a CF query object, and then loop through it and insert into the table on the dev server.

Code:

<cffunction name="copyProdToDev">

    <cfargument name="devDatasource" >
    <cfargument name="prodDataSource" type="string">
    <cfargument name="devTableName" type="string">
    <cfargument name="prodTableName" type="string">
    <cfargument name="dateColumnName" default="none">

    <cfquery name="ProdData" datasource="#prodDatasource#" timeout="60">
        SELECT *
        FROM #prodTableName#
    </cfquery>

    <cfset columnNames = ProdData.getColumnNames()> 
    <cfset numColumns = ArrayLen(columnNames)>

    <cfloop query="#ProdData#">
        (
        <cfloop index="colNumber" from="1" to="#ArrayLen(columnNames)-1#">
            <cfoutput><dynamic column name for colNumber>,</cfoutput>
        </cfloop>
        <cfoutput><dynamic column name for numColumns></cfoutput>
        )<br />
    </cfloop>

</cffunction>

While it is possible, can you not just insert the data directly with an INSERT/SELECT ...? Is the target table structure the same? — Leigh, Nov 03 '15 at 17:36
There is. It's because the production data and the dev data are on different servers. So, I cant just do `insert into a select * from b`. I'll update my question to reflect that. — abalter, Nov 03 '15 at 17:40
What RDBMS are you using? SQL Server will allow you to link servers, so you can run a query on one that will retrieve data in another. — Scott Stroz, Nov 03 '15 at 18:16
@ScottStroz We are not allowed to have linked servers here :(. Tight security. — abalter, Nov 03 '15 at 18:19
But you can pull data into a 3rd system that functions as the 'link'? Seems counter productive. — Scott Stroz, Nov 03 '15 at 18:35
It's a DOE facility. I have no control over the servers. And never shall they meet. Dan Bracuk's answer does solve the problem. — abalter, Nov 03 '15 at 18:42
*And never shall they meet* That is putting it mildly, I am sure ;-) BTW, getColumnNames() is an undocumented method. Unfortunately, there is no direct alternative last I checked, but you can do the same thing with `getMetaData(queryName)`. The function returns an array of structures. The column name is under the "name" key. Using an array loop, ie `currentArrayElement.name` — Leigh, Nov 03 '15 at 18:51
@Leigh -- what makes this a different question is that in the other post (which I did find), it was not explicitly shown that the column loop is inside a query loop. I was unsure of how they were getting the object called `query`. Actually, I did just notice this, which mine is pretty much a duplicate of: http://stackoverflow.com/questions/2581394/how-to-get-a-dynamic-attribute-name-in-cfloop-over-query-in-coldfusion?rq=1 if you put the two together -- the query loop and then the nested column loop. — abalter, Nov 03 '15 at 19:05
They don't 'meet' except where the ColdFusion connects them. ;-) — Scott Stroz, Nov 03 '15 at 19:17
@abalter - Yes, this question comes up pretty frequently, so there are a *lot* of threads on it, all with minor variations (all essentially doing the same thing: loop through query + column list/array). That was just the thread I happened to pick. Ultimately, the solution was what is in the link - ie "use array notation". Since you already had both the column names - and the inner loop - it seemed like you already had the rest figured out. — Leigh, Nov 03 '15 at 19:17

score 5 · Accepted Answer · answered Nov 03 '15 at 17:59

5

Array notation is your friend.

<cfoutput>
<cfloop query = "ProdData">
<cfloop array = "#ProdData.getColumnList()#" index = columnName>
#prodData[columnName][currentrow]#
closing tags and formatting stuff

answered Nov 03 '15 at 17:59

Dan Bracuk

20,699
4
26
43

Bingo! Didn't know that `cfloop` had `currentrow`. – abalter Nov 03 '15 at 18:14
1

Technically, it's `cfquery` that has currentrow. – Dan Bracuk Nov 03 '15 at 20:11
That's what I thought. However, I was able to access `currentrow` inside a ` – abalter Nov 03 '15 at 20:45

score 0 · Answer 2 · answered Nov 03 '15 at 16:41

0

You could get the list from this variable

ProdData.columnList

But be aware that the order of the columns is not totally respected, this article might help: http://www.bennadel.com/blog/644-query-columnlist-does-not-return-true-column-ordering.htm

answered Nov 03 '15 at 16:41

André Ferrari

179
7

You can get them in the correct order by using `getColumnNames` as I did in my code. I believe that is since CF 8 or so. http://www.richarddavies.us/archives/2009/07/cf_columnlist.php – abalter Nov 03 '15 at 17:03
Ok I am sorry. So your difficulty is getting the value of the columns, is it? If so, you can use the evaluate function: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f4e.html and do something like Evaluate("ProdData.#columnNames[colNumber]#") – André Ferrari Nov 03 '15 at 17:22
Evaluate does work, but id widely cautioned against. – abalter Nov 03 '15 at 17:24
Well, idk if this works, but have tried looping the query using index and getting your value like this: ProdData["#columnNames[colNumber]#"][i] .You would have to loop using a index from 1 to the recordCount instead of looping the query. I am not sure it would work. – André Ferrari Nov 03 '15 at 17:29

Using column names from dynamically named variables in Coldfusion

2 Answers2