Sending Emails - Using DB or Web Application

Question

I am currently working on specifying a piece of software whereby generated emails will be a large part of the system.

On investigation I can pull all the information I require out of the database and create an email with attachments and send them using the database's built in mail functionality (both Oracle and SQL Server).

My other option is to generate the email in my application code.

As I don't need any fancy formatting on the email, what would be the best way to do this? There seems to be no difference in terms of achieving my goal; the only benefit of using the DB is that I can set the email to be sent on the trigger of a row being updated.

Answer 1

I'd do it in the application code. Leave the data storage to the database, and leave the application logic to the application.

Answer 2

We have send emails both ways in applications developed for our corporation. The application method has worked out far better in the long run. Error handling is taken for granted in applications, but the database engine may or may not die due to address format errors, etc. and it has been difficult to trace fault later.

Answer 3

If you put it in the application code it will be easier to break the process into small testable pieces.

Put as much of the logic as possible into generating the message without sending it out, so that you can test most of your code without actually sending and receiving email.

Answer 4

I would think the real answer depends on how critical it is that the email gets sent once and how much error checking you want to do. Let me explain:

We have a system for incident reporting that sends an email when an incident is entered. Not realizing the volume of emails we would have it originally just sent off a batch of 100 at a time (SMTP limits) and loped through groups if there was more than that- after some sales we found that upwards of a few 1000 were being sent on each entry and it could take almost a minute to send them all - creating a mail thread was not a real good match for this so we created a mail que and had a job loop through those every 5 minutes and send any that hadnt been sent - mistake - if the que got held up or if there were so many emails in the que that it took longer than 5 minutes to run it would start again and we had some emails getting sent 2, 3 4, even 10 times - might have been bad design but whatever

We finaly just moved the whole thing to SQL Servers mail que and it has been running like a champ - the real lesson (in my opinion is) is do all the processing however you need to into your program but once you know the emails are to be sent and to whom pass that off to something that specifically handles email sending (CDO, Sql Server mail , Oracle whatever)

You can still test all the logic you want in your app but let the email be taken care of by something built to handle email.