db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "SendEmailRoutine" by DanDebrunner
Date Fri, 13 Jan 2006 03:57:29 GMT
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

------------------------------------------------------------------------------
  
  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
+ 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.
@@ -19, +19 @@

    * 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.
@@ -65, +70 @@

    InternetAddress recipient = new InternetAddress(toAddress);
  	
    // Create a Session to send e-mail from static profile
-   Session session = Session.getInstance(profile);
+   Session session = Session.getInstance(MailTest.profile);
  	
    // Create the message	
    MimeMessage myMessage = new MimeMessage(session);
@@ -90, +95 @@

    String content)
  throws Exception
  {
-    sendSMTP(toAddress, subject, content);
+    MailTest.sendSMTP(toAddress, subject, content);
     return 0;
  }
  }}}
@@ -124, +129 @@

  
  -- 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)
+ 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;
+ 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';
+ }}}
+ 

Mime
View raw message