Adding mail processing to your database app

   I'm not sure how many people know that part of the APEX team, Sharon Kennedy,  runs ProMedmail  which is probably one of the scariest and most active internet APEX applications.  It is "The global electronic reporting system for outbreaks of emerging infectious diseases & toxins, open to all sources."  So by a scary APEX app I mean you can get your latest updates on Anthrax in Bangladesh, Dengue Fever , and about everything else going on in the world.

  The reason the "mail" part is in the name is that as you may have guess it's a mail list which anyone can subscribe to here.   It ends up on the website by some custom code that reads the an inbox and inserts that into a table in the database which is then exposed by APEX.  I've taken that custom code and made it quite generic.  I'm going to make this available on  samplecode.oracle.com in a project named email2sql so http://email2sql.samplecode.oracle.com.  This is working and it's a really easy way to add email processing to any application.

To try this out there's just a couple steps.
1) Checkout the project
2) Edit the settings.xml for email and sql
2.5)  If you use the tables in the settings.xml already create the table with the mail.sql
3) Run "ant run"  ( if you need ant it's at http://ant.apache.org/bindownload.cgi )


In the settings.xml is 2 entries sql and attachment.sql, these control what to do and have all the binds available in the example.  This can be plsql instead of sql also.

<entry key="sql">
 insert into mail(id,to_address,subject,msg_body,rcvd_date,sent_date,msgid,from_address) 
values(:ID,:TO_ADDRESS,:SUBJ,:BODY,:RCVD_DATE,:SENT_DATE,:MSGID,:FROM_ADDRESS)</entry>
<entry key="attachment.sql">
insert into mail_attachments(id,content,filename,content_type,content_size) 
values(:ID,:CONTENT,:FILENAME,:CONTENT_TYPE,:CONTENT_SIZE)</entry>

Also for Promedmail we use hudson to schedule this to run every 5 minutes.  If you've not looked into hudson it's very simple to get started with also.  I did a talk on this back in Feb at RMOUG.  But more on that later.

Please let me know of any issues or suggestions.