Sending e-mail from SQL routines with JavaMail

WORK IN PROGRESS - the following is a simple subset of some e-mail I have working in Derby. I haven't tested it, but it is based on working code.

It's very easy to send e-mail using JavaMail from a Derby Java procedure or trigger.

This contains some examples of how to do it, see it as a set of examples rather than a complete application for you to use. For any e-mail there are several pieces of information needed, and your specific application may need to fetch these from various locations:

from address e.g. dbmailer@acme_widgets.com, hard coded in Java/SQL, configured in database, elsewhere.
from name e.g. "ACME Special Offer" , hard coded, in Java/SQLconfigured in database, elsewhere.
reply-to address e.g. customer_service@acme_widgets.com
to address e.g. tpyically from table (e.g. customer), but could be fixed for alert kind of messages.
subject - "Special Offer on Widgets", hard coded in Java/SQL, configured in database, elsewhere.
message content - HTML, plain text, from file, from database.
mail transport, e.g. SMTP server.

Here's a IBM DeveloperWorks article on JDBC and JavaMail, it's written for DB2 but Java procedures and functions should be portable across different databases.

http://www-128.ibm.com/developerworks/ibm/library/i-email/

So, let's take a simple example where the mail profile is hard-coded, but the to-address, subject and content are passed into a SQL routine. I'll also assume SMTP is used to send the e-mail.

These imports are needed for the code.

package testing;

import javax.mail.*;
import javax.mail.internet.*;
import java.util.Properties;

Some static code in the class to set up a Properties object used to open a mail Session.

public class MailTest
{

  // Set up the mail profile in the static initializer of the class

  private static final profile Properties;

  {
    profile = new Properties();
    profile.put("mail.transport.protocol", "smtp");
    profile.put("mail.smtp.host", "smtp@acme_widgets.com");
    profile.put("mail.smtp.port", "25");  
  }

Create a simple Java method to send e-mail, this can be used in a procedure.

public static void sendSMTP(
  String toAddress,
  String subject,
  String content)
throws Exception
{
  InternetAddress from = new InternetAddress("dbmailer@acme_widgets.com");
  InternetAddress recipient = new InternetAddress(toAddress);
        
  // Create a Session to send e-mail from static profile
  Session session = Session.getInstance(MailTest.profile);
        
  // Create the message 
  MimeMessage myMessage = new MimeMessage(session);
  myMessage.setFrom(from);
  myMessage.setSubject(subject);
  myMessage.setText(content);
  myMessage.addRecipient(Message.RecipientType.TO, recipient);
        
  // Send the message           
  javax.mail.Transport.send(myMessage);

}

Sometimes useful to add another method that can be used in a function.

public static int sendSMTP_F(
  String toAddress,
  String subject,
  String content)
throws Exception
{
   MailTest.sendSMTP(toAddress, subject, content);
   return 0;
}

Now, onto the SQL. Two routines, one procedure, one function that call the above method.

CREATE PROCEDURE SEND_MAIL(
  TO_ADDRESS VARCHAR(320),
  SUBJECT VARCHAR(320),
  BODY VARCHAR(32000))
LANGUAGE JAVA PARAMETER STYLE JAVA
NO SQL
EXTERNAL NAME 'testing.MailTest.sendSMTP';

CREATE FUNCTION SEND_MAIL(
  TO_ADDRESS VARCHAR(320),
  SUBJECT VARCHAR(320),
  BODY VARCHAR(32000)) RETURNS INT
LANGUAGE JAVA PARAMETER STYLE JAVA
NO SQL
EXTERNAL NAME 'testing.MailTest.sendSMTP_F';

Then they can be simply called like

-- Use the procedure, passing in the content from the application.
CALL SEND_MAIL('fred@yahoo.com', 'Special Offer', ?)

-- Send an e-mail customers in good standing with a special
-- offer based upon the customer type.
select SEND_MAIL(c.email, 'Special Offer!!', M.email_text)
FROM CUSTOMERS C, MAILINGS M
WHERE C.BALANCE > 1000.0 AND C.TYPE = M.CUST_TYPE AND M.OFFER_TYPE = 'special'

-- Send a Welcome e-mail when new customers are added.
CREATE TRIGGER WELCOME_CUSTOMER
AFTER INSERT ON CUSTOMER REFERENCING new_table AS newtab
FOR EACH STATEMENT
MODE DB2SQL
SELECT SEND_MAIL(c.email, 'Welcome to AcmeWidgets', M.email_text)
FROM newtab C, MAILINGS M
WHERE C.TYPE = M.CUST_TYPE AND M.OFFER_TYPE = 'welcome'

SendEmailRoutine

Sending e-mail from SQL routines with JavaMail