[Db-derby Wiki] Update of "SendEmailRoutine" by DanDebrunner

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by DanDebrunner:
http://wiki.apache.org/db-derby/SendEmailRoutine

New page:
= 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 maybe 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.

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(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
{
   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';

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.TYPE;
}}}