We help IT Professionals succeed at work.

cfquery update with cffunction fails

ShanghaiD
ShanghaiD asked
on
I have a cffunction name="TidyClientNames" to perform complex REGEX modifications on ClientName strings.  I want to use this cffunction to update a MySQL table field.

I can do this successfully by querying the target MySQL table and then looping over the result when I then execute an UPDATE query for each iteration of the loop, as follows:

<cfquery name="OldNames">
  SELECT clientcode, clientname 
  FROM tempClients
</cfquery>

<cftransaction>
  <cfloop query="OldNames">
    <cfquery name="update">
      UPDATE tempClients
      SET clientname = '#TidyClientNames(clientname)#'
      WHERE clientcode = '#clientcode#'
    </cfquery>
  </cfloop>
</cftransaction>

Open in new window


However, execution time is slow and I want to speed this up if I can.

I tried to call the cffunction more directly in a single cfquery UPDATE as follows (but this fails):

<cfquery name="TidyNames">
  UPDATE tempClients
  SET clientname = '#TidyClientNames(clientname)#'
</cfquery>

Open in new window


When I run this alternative query (instead of the earlier cfquery and cfloop and cfquery). I get an error message:

Variable CLIENTNAME is undefined. 

Open in new window


What am I doing wrong?  How can I fix things?

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
> I tried to call the cffunction more directly in a single cfquery UPDATE as follows (but this fails):

Right.  Because it's a ColdFusion function you can't use it on a db column.  All CF code is executed first. Then the generated sql is sent off to your database.  CF doesn't know anything about your db and vice versa. The only way to apply a CF function to each record is to loop. No way around it.

> However, execution time is slow and I want to speed this up if I can

Unfortunately you can't do much.  You could possibly speed things up a little, by batching multiple UPDATE's in a single cfquery.  Pick a reasonable batch size like 100 or 200 updates max.  Note, your dsn must support multiple statements for it to work.


<cfset batchSize = 100>
<cfloop from="1" to="#oldNames.recordCount#" index="startIndex" step="#batchSize#">
      <cfset endIndex = min(startIndex+batchSize-1, oldNames.recordCount)>

          <cfquery name="update">
               <cfloop from="#startIndex#" to="#endIndex#" index="row">
                   UPDATE tempClients
                   SET clientname = '#TidyClientNames(oldNames.clientname[row])#'
                   WHERE clientcode = '#oldNames.clientcode[row])#';
               </cfloop>
         </cfquery>

</cfloop>

Author

Commented:
Thank you for your clear explanation.

I learn something new every day!

Author

Commented:
Dear _agx_:

Just a quick follow on clarification to your answer:  You commented that:
Right.  Because it's a ColdFusion function you can't use it on a db column.  All CF code is executed first. Then the generated sql is sent off to your database.  CF doesn't know anything about your db and vice versa. The only way to apply a CF function to each record is to loop. No way around it.
but in my
<cftransaction>
  <cfloop query="OldNames">
    <cfquery name="update">
      UPDATE tempClients
      SET clientname = '#TidyClientNames(clientname)#'
      WHERE clientcode = '#clientcode#'
    </cfquery>
  </cfloop>
</cftransaction> 

Open in new window

the same Coldfusion function works on the db column, so I still really do not fully understand why that Coldfusion function cannot also work in the other query
<cfquery name="TidyNames">
  UPDATE tempClients
  SET clientname = '#TidyClientNames(clientname)#'
</cfquery> 

Open in new window

as I tried to do.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
> SET clientname = '#TidyClientNames(clientname)#'

It's because you're looping through a query. So the "clientName" inside the function is actually a query variable, not the database column.  To better illustrate, it's akin to:

<cfloop query="OldNames">
    <cfset variableOfNameToUpdate = clientName>
    <cfquery name="update">
      UPDATE tempClients
      SET clientname = '#TidyClientNames(variableOfNameToUpdate )#'
      WHERE clientcode = '#clientcode#'
    </cfquery>
  </cfloop>

Explore More ContentExplore courses, solutions, and other research materials related to this topic.