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 ;-)
myDBR Team, Key Master
Duane,
take a look at the dbr.mail.log.proc-command.
--
myDBR Team
duane, Member
OK - Iäll give that a try...look like what I needed (and missed!)
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='name@domain.com' so then I can either update that messageid=3 has been successfully sent and/or that recipient='name@domain.com' 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:
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 error....in 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)
myDBR Team, Key Master
We'll take a look at it if we can provide more detailed logging.
--
myDBR Team
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
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
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?
myDBR Team, Key Master
Templates should work just fine. How does your code look like?
Have you checked the documentation?
--
myDBR Team
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@myemail.com', 'Name of Recipient'; SELECT 'dbr.mail.log.proc', 'sp_emailsend_status', 'inRowID=RowID', 'inRecipient=Recipient'; END
Template
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?
myDBR Team, Key Master
OK,
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.