How to send emails from HANA

We had a discussion on Send EMail from HANA, so I thought I’d knock together some code to do it. Here you go.

First, setup a table to store the emails, and a stored procedure to write them into that table. You can give EXECUTE privileges to any user so you can let anyone send email.

DROP TABLE SYSTEM.EMAIL;

CREATE TABLE SYSTEM.EMAIL (

  EMAILTO VARCHAR(255),

  EMAILFROM VARCHAR(255),

  SUBJECT VARCHAR(255),

  CONTENTS VARCHAR(2000)

);

DROP PROCEDURE SYSTEM.SEND_EMAIL;

CREATE PROCEDURE SYSTEM.SEND_EMAIL

       (      IN EMAILTO VARCHAR(255), IN EMAILFROM VARCHAR(255), IN SUBJECT VARCHAR(255), IN CONTENTS VARCHAR(2000) )

       LANGUAGE SQLSCRIPT AS

BEGIN

INSERT INTO “SYSTEM”.“EMAIL” (EMAILTO,EMAILFROM,SUBJECT,CONTENTS) VALUES(EMAILTO,EMAILFROM,SUBJECT,CONTENTS);

END;

CALL SYSTEM.SEND_EMAIL(‘,‘,‘Email from HANA’,‘HANA Phone Home’);

SELECT * FROM SYSTEM.EMAIL;

Now we have an email in our table, but we need to send it. For this, I used some simple python.

#!/usr/bin/python

import dbapi 

import smtplib

from email.mime.text import MIMEText

con = dbapi.connect(‘localhost’, 30015, ‘SYSTEM’, ‘PASSWORD’)   

cur = con.cursor() 

cur.execute(‘select * from system.email’)

email = cur.fetchall()

cur.execute(‘truncate system.email’)

for item in email:

  msg = MIMEText(item[3])

  msg[‘Subject’] = item[2]

  msg[‘From’] = item[1]

  msg[‘To’] = item[0]

  s = smtplib.SMTP(’emailserver’)

  s.sendmail(item[1], item[0], msg.as_string())

  s.quit()

And now, we need to schedule it. Make sure you chmod +x the script so you can execute it directly from cron. We do this using sudo crontab -e

* * * * * /root/hanaemail.py

And now, your emails will be sent once a minute. This is a pilot script and doesn’t do proper error handling – to use this in production, you should make it a bit tougher. But it shows the concept.