Email success/failure result within function?

(11 posts) (2 voices)


  1. duane, Member

    Hi again,

    Within the email extension, if the email succeeds/fails it is shown on-screen in the report. But I want to capture the result so it can be used programmatically. e.g. record status code/string and date in a field or other variants of that.

    Is this possible now? If so, how? I couldn't find a way yet, but you are the experts ;-)

  2. myDBR Team, Key Master

    take a look at the dbr.mail.log.proc-command.

    myDBR Team

  3. duane, Member

    OK - Iäll give that a try...look like what I needed (and missed!)

  4. duane, Member

    Hi again,

    I tried this out and it is not quite what I was hoping for. What I was looking for a way to log the success/failure of each message (e.g. identified by an id) which went to each recipient so that I could update the message/recipient record if it has successfully sent. e.g. return messageID=3, status=success, recipient='' so then I can either update that messageid=3 has been successfully sent and/or that recipient='' has been successfully sent messageid=3.

    What 'dbr.mail.log.proc' does is just tell me per emailing (not per recipient nor message) what the results are after they have all gone out. If there is only 1 fixed recipient or just 2-3 and all emails are successfully sent, then this is manageable, but if this went to 200 people (or 2000...), then the to_address value would be very long and need to be parsed to record in each recipient row matched to the email address. Plus there would be no indication of which recipient email failed to send, thus it couldn't be resent as Iäd have no idea who did/did not receive it.

    But if one of the emails fails (I tested with a deliberately invalid format email address like '.com' and with 'qwerty@com' as two of three email addresses the query returns), the process sends the valid emails (message: "Sent 0 mail with no errors" (for '.com' seemed to make it hang but still logged "Sent 0 mail with no errors").

    I never received a message/error result so I guess these occur only if the smtp server fails?

    So I guess I see two options:

    1. Construct queries where there are multiple rows with multiple recipients in a loop instead with one email each so I can individually capture the recipient email address and error message from dbr.mail.log.proc-command - and make sure all emails match a valid format pattern and hope the pattern gets them all ;-)
    2. You could allow something to pass custom values to the 'dbr.mail.log.proc' procedure like: SELECT 'dbr.mail.log.proc', 'sp_mail_log', 'inMessageID=messageID', 'inVersion=versionid'; so that for each row in the recipient-subject-message query it calls sp_mail_log separately (and allow additional columns to be specified after the basic 4 use to send the email).

    Options 1 is within my power now but then the only 'new' information the dbr.mail.log.proc-command gives me is the success/error status (every time it will be either "Sent 1 mail with no errors" or "Sent 0 mail with no errors").

    A different type of improvement on the results of the dbr.mail.log.proc-command would be to (a) return a general success/failure value (vs "Sent 1 mail with no errors") and if there is an error type (e.g. if "Sent 0 mail with no errors" it might say 'invalid address' or 'smtp authentication error' or something else.

    Of course, the constraints you face might be that the email library myDBR uses doesn't allow this type of granular per-recipient feedback and error message and only gives a dump of the which case option 1 is what I'll have to resort to and parse the "Sent 1 mail with no errors" message for if it say 0/1 mail and has the phrase "no errors" or not.

    Hope this detailed feedback is helpful - the product is great and always improving, and at least I have a work-around (loop to send each row from the query individually so I can log it more granularly)

  5. myDBR Team, Key Master

    We'll take a look at it if we can provide more detailed logging.

    myDBR Team

  6. myDBR Team, Key Master

    There are now improvements into the error logging. The dbr.mail.log.proc stays the same but it does record individual mails.

    myDBR Team

  7. myDBR Team, Key Master

    You could allow something to pass custom values to the 'dbr.mail.log.proc' procedure like: SELECT 'dbr.mail.log.proc', 'sp_mail_log', 'inMessageID=messageID', 'inVersion=versionid'; so that for each row in the recipient-subject-message query it calls sp_mail_log separately (and allow additional columns to be specified after the basic 4 use to send the email).

    Extra user-defined parameters for the dbr.mail.log.proc are now supported in the latest build.

    myDBR Team

  8. duane, Member

    Great! I tried this but haven't yet got it working. Perhaps because I am using dbr.record with a template to generate the email body? I have the two custom variables at the end of the query, but since it goes via the template, it is slightly different I suspect.

    Can you give any suggestions?

  9. myDBR Team, Key Master

    Templates should work just fine. How does your code look like?

    Have you checked the documentation?
    myDBR Team

  10. duane, Member

    Yes - I checked and used the documentation. The main differences from the example with my report are the use of a template, dbr.record and dbr.mail.recipient (vs a row value).

    So the error I get is "Missing mail log procedure sp_emailsend_status parameters: inRowID,inRecipient"

    The simplified format of my code is:
    PROCEDURE `sp_DBR_template_hiring_email`(inRowID INT) BEGIN

    SELECT 'dbr.template', '#email_template', 'none'; SELECT 'dbr.record', 'begin'; SELECT e.fieldA AS FieldA, e.fieldB AS FieldB, e.fieldC AS FieldC, e.fieldD AS FieldD, e.rowid AS RowID, e.recipient AS Recipient FROM mytable e WHERE e.rowid = inRowID; SELECT 'dbr.record', 'end';

    SELECT 'dbr.mail', 1; SELECT 'dbr.mail.nobr.html', 1; SELECT 'dbr.mail.recipient', '', 'Name of Recipient'; SELECT 'dbr.mail.log.proc', 'sp_emailsend_status', 'inRowID=RowID', 'inRecipient=Recipient'; END


    The template uses Fields A-D but not the last two.

    The code of sp_emailsend_status is like:
    PROCEDURE `sp_emailsend_status`(in_msg varchar(255), in_error text, in_to_address varchar(255), in_errornous_attachment_names varchar(255), inRowID INT, inRecipient varchar(100)) BEGIN insert into dbr_email_log(sent_at, message, error, to_address, errornous_attachment_names, jobid, send_status) values ( now(), in_msg, in_error, in_to_address , in_errornous_attachment_names, inJobID, IF(inSendStatus IS NULL, NOW(), inSendStatus)); END

    Anything I am missing / have incorrect?

  11. myDBR Team, Key Master

    some changes are required. Not sure if you are want to send custom mail for each recipient or one mail with multiple recipients.

    Mailing format is as follows (using HTML like you do) :

    SELECT 'dbr.mail', 1;
    select email, name, subject, html_body
    from mydb.mail_recipients;

    When you use dbr.record, the content of the recorded elements are used as the mail body, skipping the fourth parameter in the final result set.

    select 'dbr.record', 'begin';
    -- Whatever content you want to send as mail
    select 'dbr.record', 'end'; select 'dbr.mail', 1; select email, name, subject, null
    from mydb.mail_recipients;

    And when you want to use logging with custom fields, just include the extra fields into the result set:

    select 'dbr.record', 'begin';
    -- Whatever content you want to send as mail
    select 'dbr.record', 'end'; select 'dbr.mail.log.proc', 'sp_emailsend_status', 'in_extra_field=extra_field'; select 'dbr.mail', 1; select email, name, subject, null, extra_field
    from mydb.mail_recipients;

    myDBR Team


You must log in to post.