Array of cfquery col names

[Pete_Rogers]

Hi peeps - got a fiddly little problem.

I'm running a cfquery on a database retrieving column names that are stored in an array.

The cfquery works fine, and i can cfdump the data with no proolem at all, however once i try to put it out in a cfoutput tag, instead of the contents of the fields, the output shows the field names. I think it's because i've nested a cfloop for the field names inside thew cfoutput tag :

<cfoutput query="qGet_cust">
        <tr>
            <td>#MID(LEGAL_ENTITY_CODE,2,10)#</td>
            <cflock scope="session" throwontimeout="yes" type="readonly" timeout="10">
                <cfloop FROM="1" TO="#ArrayLen(SESSION.parameter_array)#" index="LOCAL.this_var">
                    <cfset LOCAL.this_column = "qGet_cust." & "#SESSION.parameter_array[LOCAL.this_var][1]#">
                    <td>#LOCAL.this_column#</td>
                </cfloop>
            </cflock>
        </tr>
    </cfoutput>

The first column is the field name LEGAL_ENTITY_CODE, and that outputs the cell data with no problem. However, the subsequent cells only show the column name rather than the contents

Header 1	Header 2	Header 3
Legal Entity	Internet Access Current Year	Calls and Lines Current Year
0003272773	qGet_cust.INTERNET_ACCESS_cyr	qGet_cust.CALLS_AND_LINES_cyr
0002808637	qGet_cust.INTERNET_ACCESS_cyr	qGet_cust.CALLS_AND_LINES_cyr
0001464359	qGet_cust.INTERNET_ACCESS_cyr	qGet_cust.CALLS_AND_LINES_cyr

How can i get the code to churn the contents of the query, rather than the column names ?? I'm running CF MX7. Thanks.

[ilssac]

I think the easier way to code that line would be:

<td>#qGet_cust[SESSION.parameter_array[LOCAL.this_var]][1]#</td>

You don't need the <cfset...> and if you where to use it you would either need to use the evaluate() function or another array notation, such as:

<td>#variables[LOCAL.this_column]#</td>

P.S.

I can not imagine any reason for the <cflock...> you are implimenting here.

Pete Rogers wrote:

However, the subsequent cells only show the column name rather than the contents

That is because you where expecting the the value of that variable to be evaluated as a variable a second time.  First to get the query.column.row variable name and then a second time to get the value of that variable.  Programming languages don't go around evaluating vairables multiple times very often.

[Pete_Rogers]

Ian,

thanks a lot for that, although unfortunately your line generates an error "Complex object types cannot be converted to simple values."

I did try the Evaluate function, and

<td class="data">#Evaluate(LOCAL.this_column)#</td>

does the trick, however i recall hearing somewhere that Evaluate is frowned upon due to it being resource hungry ? Is it okay to use, or should i be looking for a better solution ?

I'm cflocking becuase the array containing the column names is a client session array, and out of habit i always cflock when dealing with session variables. Am i being overly cautious ?

Cheers, Pete

[ilssac]

Yes the evaluate function is frowned upon.

The solution 97.86% of the time is to use array notation, which are what my first example attempted to do.  Is tht the one you tried that gave you the "Complex object types cannot be converted to simple values" error?

I realize that my second example, the #variables[local.this_column]# is not going to work.

But the first example should, #qGet_cust[SESSION.parameter_array[LOCAL.this_var]][1]#  If this one is throwing that error, that just means I did not put in the correct, complete structure that contains the column names into the first brackes of the query[column][row] structure.

[Pete_Rogers]

HI Ian,

#SESSION.parameter_array[LOCAL.this_var][1]#          generates the column names rather than the values in them

#[SESSION.parameter_array[LOCAL.this_var]][1]#        causes an error in the loop (context validation error for tag cfloop - no end tag)

#qGet_Cust[SESSION.parameter_array[LOCAL.this_var]][1]#          creates the Complex object tyoes cannot be converted to simple values

So far, the only code that works is #Evaluate(SESSION.parameter_array[LOCAL.this_var][1])#, which i'd rather not use as performance may become an issue.

Cheers, Pete

[Adam Cameron. · Accepted solution]

But the first example should, #qGet_cust[SESSION.parameter_array[LOCAL.this_var]][1]#  If this one is throwing that error, that just means I did not put in the correct, complete structure that contains the column names into the first brackes of the query[column][row] structure.

Quite.  From the original code, this is the correct column name:

                    <cfset LOCAL.this_column = "qGet_cust." & "#SESSION.parameter_array[LOCAL.this_var][1]#">

IE: the column name is "#SESSION.parameter_array[LOCAL.this_var][1]#"

So the correct query[column][row] representation of this would be:

qGet_cust[SESSION.parameter_array[LOCAL.this_var][1]][qGet_cust.currentRow]

Ian's used the [1] from the column name ref as a row ref (by accident).

--

Adam

[Adam Cameron.]

And I think what this demonstrates is that it probably is better to set the columnName var first, as there's such a mess of square brackets there, it's easy to make mistakes.

--

Adam

[Pete_Rogers]

Thanks a lot Adam, that's working much better now.

The fact that i had to store the col names dynamically really threw me, if i could have gotten away with just cfdump i would have

Thanks again Ian and Adam.