asked on

cfquery update with cffunction fails

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?

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ShanghaiD

ASKER

Thank you for your clear explanation.

I learn something new every day!

ShanghaiD

ASKER

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.

_agx_

> 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>