reset or clear cfquery

[lleemon]

I am working with some old cold fusion code (see below for details) and there is a process where it does a cfquery  from a database which has the name of 'vRecipients'.

Later in the code it does a cfmail using the vRecripients query to send emails.   Recently we had an issue where in a loop it used the same email.   Is there a way where for each loop I can clear the cfquery?

Sample code:

<cfif param_Queried EQ 0>
    <CFQUERY datasource="#dsn#" name="vRecipients" username="#cf_username#" password="#cf_password#">
SELECT EmailAddress FROM table WHERE field = '#param_ClientID#'
    </CFQUERY>
</cfif>

<CFMAIL query="vRecipients" to="#EmailAddress#" from="email@domain.com" subject="Subject" TYPE="HTML">
Body of email goes here
</CFMAIL>

server.ColdFusion.ProductLevel = Professional
server.ColdFusion.ProductName = ColdFusion Server
server.ColdFusion.ProductVersion = 5, 0, 0, 0
server.OS.AdditionalInformation = Service Pack 4
server.OS.BuildNumber = 2195
server.OS.Name = Windows NT
server.OS.Version = 5.0

[JR__Bob__Dobbs]

I don't understand the question.  What does "clear the query" mean?

Does the same email addresses occur multiple times in vRecipients and you only want to execute the CFMAIL tag once for each distinct email address?

[lleemon]

Here is kind of the sudo overview:

1) Look in a holding folder for files to upload:

-file_x_A100.pdf

-file_z_B100.pdf

2) Loop through each file

3) based on what the 2nd parameter is in the file name is how it it is handled.  (ie: x and z)

4) I then call the code that I listed originally and based on this value we query a user table which will grab a certain email address to send to

<cfif param_Queried EQ 0>
    <CFQUERY datasource="#dsn#" name="vRecipients" username="#cf_username#" password="#cf_password#">
SELECT EmailAddress FROM table WHERE field = '#param_ClientID#'
    </CFQUERY>
</cfif>

<cfif param_Queried EQ 1>
    <CFQUERY datasource="#dsn#" name="vRecipients" username="#cf_username#" password="#cf_password#">
SELECT EmailAddress FROM table WHERE field = '#param_ClientID#'
    </CFQUERY>
</cfif>

<CFMAIL query="vRecipients" to="#EmailAddress#" from="email@domain.com" subject="Subject" TYPE="HTML">
Body of email goes here
</CFMAIL>

So what appears to have happened is the first loop ran fine, found a email then sent.    The next loop something happening and the CFQUERY was rerun so it used the last loops vRecipients value and sent an email to that person instead of someone else.    So, we need a way for each entry into #4 section we want to clear the CFQUERY so if it doesn't run it won't use last loops data.

[JR__Bob__Dobbs]

Based on the statement " it used the last loops vRecipients value " it sounds like there may be some logic/flow control in the code that needs revision. Is vRecipients in a shared scope that persists after the CFMAIL is executed? What would occur if the value of param_Queried was neither "1" nor "0"?

Rather than use two different query objects with the same name you might consider re-factoring your code so that a query object is passed to a CFFUNCTION that handles mail. This might make it easier to troubleshoot. In step 4 you would place a call to a mailing function within the CFIF statements.

[Dan_Bracuk]

The following suggestion will improve efficiency and probably make it simpler for you to solve the stated problem.

As you loop through the uploaded files, do not query any database and do not send mail.  Instead, create a list, array, query, or whatever to hold the items of interest.  Then process that data so that you end up with a list of unique Client Id values.  Then run a single query to get the email addresses and you need.  Finally, send your mail.

[lleemon]

Thanks guys.

I kind of thought something like that was going to be the preferred way but just wondered if there was special function to clear a cfquery instead.

[ilssac]

lleemon wrote:

just wondered if there was special function to clear a cfquery instead.

As the others have been trying to guide you to the proper solution for your real problem this may not be good advice.

But a recordset (aka query) variable is just like any other variable and can be "cleared" by resetting it to some other value, no special function required.

I.E.

<cfquery name="testQry"...>
  SELECT aField,bField,cField
  FROM aTable
</cfquery>
<cfdump var="#testQry#"> <!---Dump out the record set returned by the previous <cfquery...> block--->
<cfset testQry = ""> <!---Reset the testQry variable to an empty string--->
<cfdump var"#testQry#"> <!---Dump out the new value of 'testQry' which will not display much output--->

[lleemon]

ilssac - that's exactly what I was looking for.

Thanks.  I will give it a shot.

[Brian Meloche]

That sounds like more of a SQL issue than a issue, to filter out

duplicate records. Try this instead:

SELECT DISTINCT EmailAddress FROM table WHERE field = '#param_ClientID#'