Send EMail from HANA

[Former Member]

Hello,

I have a stored procedure with parameters ToEmail,EmailSubject,EmailBody

My requirement is When I call the procedure, By taking the inputs and by using some HANA Builtin function It will send email.

I am not familiar with EMail BuildIn Function to send email.

Please help me.

[thomas_jung · Accepted solution]

I'm sorry but there is no functionality provided to do this.  We will in the future have an API that allows you to send mail from XSJS and River, but not from SQLScript.

[Former Member · Accepted solution]

Hey Tom,

You could write MIME to an operating system file, right, and pick that up with a cron process using sendmail? It would be a bit of an evil hack, but it could work.

Maybe someone could write a XSJS library to do this.

John

[thomas_jung · Accepted solution]

No.  Neither SQLScript nor XSJS have an API which allow you to write to the operating system file system.

[Former Member · Accepted solution]

You could use the EXPORT command and use a transient table with a single line to write a CSV? You can call EXPORT from SQLScript, right?

[former_member195440 · Accepted solution]

Or use the outbound http APIs in XSJS to either an intermediary server or email server with the email details?

Depends on what you use as your email solution (Exchange/Sendmail/gmail etc.).

[thomas_jung · Accepted solution]

John Appleby wrote:

You could use the EXPORT command and use a transient table with a single line to write a CSV? You can call EXPORT from SQLScript, right?

Have you tried that?  I couldn't get EXPORT to work from SQLScript. I took a sample that works fine from the SQL Console and when I put it in SQLScript Procedure it produces a syntax error. That would be quite the work around, but it doesn't appear the syntax is supported from SQLScript.

[former_member182302 · Accepted solution]

Hi John,

I think we cannot use IMPORT / EXPORT in stored procedure/ function. We were initially trying to create a procedure which can dynamically frame a IMPORT statement dynamically and execute it. But since it was not allowing we were framing it in Shell script and then logging into the SAP HANA using HDBSQL and executing the statement.

Correct me if we can do that in the procedure itself?

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Yeah you're right. So the only workaround then is to write some Python/C++ which connects to HANA, reads the table and clears it out after sending an email.

We then schedule the Python once a minute with cron.

With Python this would be pretty easy to do, with HANA/JDBC and sendmail APIs.

[former_member182302 · Accepted solution]

Hello & Former Member

I found a workaround, If i directly give EXPORT command it is giving syntax errors. But if i frame the statement dynamically by keeping it in a "Variable" and then use EXEC to execute the query it is working.

Please find the screenshot below:

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

So I've written the code, here you go: http://scn.sap.com/docs/DOC-52751

Tom has an email from HANA in his inbox

John

[Former Member · Accepted solution]

Yep that will work. You could pick this up form the file system directly.

[former_member182302]

Hi Krishna,

You may want to log the details into a table using the Stored Procedure and then use SMTP function from SAP BODS to trigger a email.

Regards,

Krishna Tangudu

[aravindmail0295]

krishna.tangudu : Are there any facilities to directly mail the data from procedure to the inbox through SQl code.