Coldfusion - Complex object error when trying to cfloop over query. Version discrepancy?

Question

Preamble: So we have some working applications that we need to move over to a new server because we are retiring an older one, and as a result I've had to install a new instance of CF.

This application works fine on the older server, which is running ColdFusion version "9,0,0,251028" Standard edition (via ColdFusion Administrator).

On the newer server, I'm using CF 2016 version 2016.0.0.298074 Developer edition (It's the first thing that popped up on a google search so I went with it).

Now the issue: there is a piece of code giving an error that says:

Complex object types cannot be converted to simple values.

The expression has requested a variable or an intermediate expression result as a simple value. However, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values. The most likely cause of the error is that you tried to use a complex value as a simple one. For example, you tried to use a query variable in a cfif tag.

The error occurred in G:/Gumbo/components/modules/resource/ResourceAdmin.cfc: line 282 Called from G:/Gumbo/admin/modules/resource/action.cfm: line 34 Called from G:/Gumbo/admin/action.cfm: line 19
281 cfloop query="getseq">
282 <cfif getseq.resourceCategoryID IS temp><cfset newseq = getseq.displaySeq ><cfbreak></cfif>
283 </cfloop>

The offending line is 282. The code in question:

<cfloop query="getseq">
      <cfif getseq.resourceCategoryID IS temp><cfset newseq = getseq.displaySeq ><cfbreak></cfif>
</cfloop>

From my research, I've noted that apparently cfloop doesn't work with query parameters on some versions of ColdFusion, but what I don't understand is why a NEWER version is therefore causing me this error.

So my question is:

Is there a way to reacquire this older version of CF somehow? Keep in mind I have the CF9 source folder on my older computer, but I wasn't sure if there's a way I can take the source files and move them over or manually install it or the ins and outs of that. Could it be as easy as copying the older source files into the new CF source on the newer server?
What would be an easy alternative to change the code mentioned? I am completely unfamiliar with CF as this is an older project that I inherited upon taking this job. I'd prefer to get the exact version on the newer system, but changing the code is the only viable alternative.

Any insight would be appreciated.

EDIT:

Here is the entire offending function:

<cffunction name="updateResource" access="public" output="false" displayname="Update a Resource">       
    <cfset VARIABLES.dateUpdated=DateAdd("d", 0, arguments.dateUpdated)>    

    <cfquery datasource="#this.datasource#">
      update md_rlm_resource
      set published=<cfqueryparam cfsqltype="cf_sql_tinyint" value="#arguments.published#">,
          resourceName=<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.resourceName#">,
          resourceNumber=<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.resourceNumber#">,
          resourceAuthor=<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.resourceAuthor#">,
          resourceFile=<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.resourceFile#">,
          dateUpdated=<cfqueryparam cfsqltype="cf_sql_timestamp" value="#VARIABLES.dateUpdated#">,
          shortDescription=<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#arguments.shortDescription#">
      where resourceID=<cfqueryparam value="#arguments.resourceID#" cfsqltype="cf_sql_integer">
    </cfquery>    

    <cfquery name="getseq" datasource="#this.datasource#">
      select displaySeq, resourceCategoryID
      from md_rlm_resourcecategoryrel
      where resourceID=<cfqueryparam value="#arguments.resourceID#" cfsqltype="cf_sql_integer">   
    </cfquery>

    <cfquery datasource="#this.datasource#">
      delete from md_rlm_resourcecategoryrel
      where resourceID=<cfqueryparam value="#arguments.resourceID#" cfsqltype="cf_sql_integer">
    </cfquery>

    <cfif IsDefined("arguments.resourceCategoryIDs")>
      <cfset resourceCategoryID = ArrayNew(1)>
      <cfset resourceCategoryID = ListToArray(arguments.resourceCategoryIDs)>

      <cfif #ListLen(arguments.resourceCategoryIDs)# gt 1>
        <cfset tmp1 = #ArrayLen(resourceCategoryID)#>
        <cfelse>
        <cfset tmp1 = "1">
      </cfif>

      <cfloop INDEX="idx" FROM="1" TO="#tmp1#">     
        <cfset newseq = 1>

        <cfif #tmp1# gt 1>
           <cfset temp=resourceCategoryID[idx]>
        <cfelse>
           <cfset temp=resourceCategoryID>        
        </cfif>

        <cfloop query="getseq">
          <cfif getseq.resourceCategoryID IS temp><cfset newseq = getseq.displaySeq ><cfbreak></cfif>
        </cfloop>

        <cfquery datasource="#this.datasource#">
          insert into md_rlm_resourcecategoryrel
             (resourceCategoryID, resourceID, displaySeq)
          values
            (
            <cfif #tmp1# gt 1>
            <cfqueryparam CFSQLTYPE="cf_sql_integer" VALUE="#resourceCategoryID[idx]#">,
            <cfelse>
            <cfqueryparam CFSQLTYPE="cf_sql_integer" VALUE="#resourceCategoryID#">,
            </cfif>       
             <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.resourceID#">,
             <cfqueryparam cfsqltype="cf_sql_float" value="#newseq#">)
        </cfquery>
      </cfloop>   
    </cfif> 

    <cfquery datasource="#this.datasource#">
    DELETE FROM md_rlm_resourceregionrel 
    WHERE resourceID=<cfqueryparam value="#arguments.resourceid#" cfsqltype="cf_sql_integer">
    </cfquery>

    <cfloop index="regionele" list="#arguments.regionID#" delimiters=",">
        <cfquery datasource="#this.datasource#">
        INSERT INTO md_rlm_resourceregionrel (resourceID, regionID) 
        VALUES (<cfqueryparam value="#arguments.resourceid#" cfsqltype="cf_sql_integer">, #regionele#)
        </cfquery>
    </cfloop>
</cffunction>

You can find archived version of ColdFusion on cfrepo gpickin.com/cfrepo As for your error what is temp? if you need to see what the variables are then you can do a cfdump to inspect it. — John Whish
temp is a resourceCategoryID that is acquired from another database table and compared to make sure the IDs are the same from what I can tell. It isn't contributing to the issue to my understanding because it all works fine on the other version. Checking out the repo now, thanks for the referral! — Cody Rogers
We're in the middle of the same migration (CF 9 to 2016). I'd suggest you focus on refactoring your code base to address discrepancies like this rather than stick with an older version of CF. Have you dumped out the contents of each row of the query and dumped the value of "temp" for each iteration of the loop? You might have bad data that is interpreted differently by 2016. We've run into things like invalid function syntax that CF 9 runs, but not 2016. We have a project going out specifically to address these kinds of code discrepancies before we release 2016 in production. — Adrian J. Moreno
Can you give us the exact error, copy and pasted from the screen, rather than a paraphrase. The on-screen error will also show the exact statement causing the error, and it's not from the code you have given us here. Also when you say "I've noted that apparently cfloop doesn't work with query parameters"... where you say "parameter", do you mean "attribute"? Like you think <cfloop query> itself doesn't work on some version of CF? This is not the case. Unless a new bug has been introduced into CF2016 (entirely likely). Finally can you give us a proper stand-alone repro case of this? — Adam Cameron
Edited to include the full error message and code snippet of the entire function. Basically the function edits a resource through our CMS and then updates the associated databases. — Cody Rogers

Adam Cameron Adam Cameron · Accepted Answer · 2016-03-02T07:53:33

From this:

<cfset resourceCategoryID = ListToArray(arguments.resourceCategoryIDs)>
...
<cfif #tmp1# gt 1>
    <cfset temp=resourceCategoryID[idx]>
<cfelse>
    <cfset temp=resourceCategoryID><!--- temp is now an array --->        
</cfif>

temp (terrible variable name, btw) could be an array.

And later you do this:

<cfif getseq.resourceCategoryID IS temp>

You cannot compare arrays with the IS operator: IS compares simple values. That's why you're seeing the error.

As an aside, you're not var-ing any of your variables in that code, which is fairly poor form, and has potential for "unexpected behaviour" in your code.

Coldfusion - Complex object error when trying to cfloop over query. Version discrepancy?

1 Answers