Skip to main content
P
Pete_Rogers
Participant
May 10, 2010
Answered

Array of cfquery col names

  • May 10, 2010
  • 1 reply
  • 1690 views

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 1Header 2Header 3
Legal Entity

Internet Access Current Year
Calls and Lines Current Year
0003272773qGet_cust.INTERNET_ACCESS_cyrqGet_cust.CALLS_AND_LINES_cyr
0002808637qGet_cust.INTERNET_ACCESS_cyrqGet_cust.CALLS_AND_LINES_cyr
0001464359qGet_cust.INTERNET_ACCESS_cyrqGet_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.

    This topic has been closed for replies.
    Correct answer Adam Cameron.

    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.


    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

    1 reply

    ilssac
    ilssac
    Inspiring
    May 10, 2010

    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.

    P
    Pete_RogersAuthor
    Participant
    May 10, 2010

    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
    ilssac
    Inspiring
    May 10, 2010

    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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices