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:
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):
When I run this alternative query (instead of the earlier cfquery and cfloop and cfquery). I get an error message:
What am I doing wrong? How can I fix things?
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>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>When I run this alternative query (instead of the earlier cfquery and cfloop and cfquery). I get an error message:
Variable CLIENTNAME is undefined. What am I doing wrong? How can I fix things?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Dear _agx_:
Just a quick follow on clarification to your answer: You commented that:
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> <cfquery name="TidyNames">
UPDATE tempClients
SET clientname = '#TidyClientNames(clientname)#'
</cfquery>
> SET clientname = '#TidyClientNames(clientna
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(variable
WHERE clientcode = '#clientcode#'
</cfquery>
</cfloop>
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(variable
WHERE clientcode = '#clientcode#'
</cfquery>
</cfloop>
ASKER
I learn something new every day!