Skip to main content
Dileep_NR
Dileep_NR
Inspiring
January 13, 2010
Question

CFMAIL QUERY TIME OUT HANDLING

  • January 13, 2010
  • 1 reply
  • 1674 views

Hi All


I am facing issue with CFMAIL QUERY=”QUERYNAME”.  I have 3 steps in a mail sending process as follows

  1. Insert list to a table with mail      send flag is FALSE

  2. Send mail

  3. Update mail send flag to TRUE

Issue that am facing is when request time occurs after sending some mails(eg query has 100 records and after sending to 15 emails) the update process in not done. That means when check the data base it shows that no mail has sent. Actually 15 mails were. How we handle this in cfmail query

Sample code:-

<!--- log table insertion  --->

<cfoutput query="qrycontactlist">

<cfstoredproc datasource="xyzdsn" username="uname" password="pwd"

procedure="usp_insert_xyz">

<cfprocparam cfsqltype="CF_SQL_INTEGER" value="#contactid#">

</cfstoredproc>

</cfoutput>

<!--- newsletter sending  --->

<cfmail  query="qrycontactlist"

to="#email#"

from ="#from#"

failto="#failto#"

subject="#subject#"

TYPE="HTML"     >

#body#

</cfmail>

<!--- update newsletter sent flag --->

<cfoutput query="cfevent.data.qrycontactlist">

<cfquery name="qryUpdateMailUpdateSatus12" datasource="xyzdsn" username="uname" password="pwd">

UPDATE

xyz

SET

mailstatus  = 1

WHERE

contactid =<cfqueryparam  value="#contactid#" cfsqltype="cf_sql_integer"/>

</cfquery>

</cfoutput>

    This topic has been closed for replies.

    1 reply

    Owainnorth
    Owainnorth
    Inspiring
    January 13, 2010

    Hi there

    If I've understood you correctly, you want to update each row in the list as it's sent? If so, don't use CFMAIL QUERY, use your own loop as you've got far more control. Check this out:

    I assume mailstatus is some kind of status id you use? Well let's say 1 is sent, 2 is failed. You can then query back later any that have failed.

    <cfset qrycontactlist = whateverQueryToGetYourContacts />


    <cfloop query="qrycontactlist">

       <cftry>

         <!--- First send the email --->

         <cfmail to="#email#" from ="#from#" failto="#failto#" subject="#subject#" TYPE="HTML">

            #body#

          </cfmail>

         <!--- if we're here the mail sent, mark as successful --->

         <cfquery name="qryUpdateMailUpdateSatus12" datasource="xyzdsn" username="uname" password="pwd">

          UPDATE    xyz

          SET       mailstatus  = 1

          WHERE     contactid = <cfqueryparam  value="#contactid#" cfsqltype="cf_sql_integer"/>

         </cfquery>

         <cfcatch>

           <!--- if we're here the mail failed, mark it as so and move onto the next --->

           <cfquery name="qryUpdateMailUpdateSatus12" datasource="xyzdsn" username="uname" password="pwd">

            UPDATE    xyz

            SET       mailstatus  = 2

            WHERE     contactid = <cfqueryparam  value="#contactid#" cfsqltype="cf_sql_integer"/>

           </cfquery>

         </cfcatch>

      </cftry>

    </cfloop>

    Think that's what you're after.

    Dileep_NR
    Dileep_NRAuthor
    Inspiring
    January 14, 2010

    Hi,

    Thanks for your replay.

    We have to send more 20,000 mails(with attachment) in a 'single button click'

    But i think "cfmail query" has more performance than "cfloop query"

    [<cfmail query="qrycontactlist">

    is more performance than

    <cfloop query="qrycontactlist">]

    Could you please advice "cfmail query" or "cfloop query" is more efficient?

    Owainnorth
    Owainnorth
    Inspiring
    January 14, 2010

    Hi there

    I've recently written a pretty bombproof CF mailing system, and the most important thing we learned was this: don't go for efficiency, go for reliability. It's better that a page takes twenty minutes to run but doesn't fail half way through than runs quickly but fails. Unless you send a mail then mark it as sent, you have no way of knowing where your script crashed out and are forced to constantly re-send the mails to your entire list until it's worked.

    People tend not to appreciate that

    Your limiting factor here is not Coldfusion and it's not your database; it's the SMTP engine. Whether it's Windows IIS or Linux Sendmail (which we unsurprisingly found to be far more efficient) it takes a fixed amount of time to initialise an SMTP connection, complete a transaction and close the connection. A lot of the time you're being held up by factors outside your control, such as people's SMTP servers being busy and causing a backlog into your MTA which may stop accepting new mails. If the MTA does decide to stop accepting new connections, your page will crash.

    Incidentally I don't think there would be any performance differences between the two - one does a java loop around an email automatically, the other you've specified. Same thing really.

    The other issue you may find is firewalling - if your server is the other side of a decent firewall you may well have your connection severed if the page takes more than 1 minute to run, which it will. Annoyingly, this means you can't get any output from your page. To get around this, you need to keep a connection going - each time you send an email, do a <cfoutput> with the mail recipient, then a <cfflush>. This will keep a constantly updating progress page in your browser in real time.

    If you're sending to that many people I'd strongly suggest using my method of individual updates rather than the bulk update, that will simply never work.

    O.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices