Sending Emails with Data Virtuality

Motivation

Data Virtuality provides means to send Emails via existing SMTP servers and user access.

It is possible to send a message straight via a SQL command and this feature can also be used in order to configure Email notifications based on job runs.

Reading the current settings

The following command can be used to read the current settings. Please note that, due to security considerations, the account's password is not shown. The main procedure to get the current settings it UTILS.getSmtpConfiguration().

SELECT a.host, a.port, a.ssl, a.username
FROM (EXEC "UTILS.getSmtpConfiguration"()) AS a;;

Setting a valid SMTP configuration

Similar to retrieving the settings, the procedure UTILS.setSmtpConfiguration() can be used to change the configuration. The configuration has to be valid in order to work.

Note, that a restart of DataVirtuality is required for the changes to take effect. Running UTILS.getSmtpConfiguration() immediately after changing them will display the new settings but they are not yet used.

This is a sample configuration for a Gmail account

EXEC "UTILS.setSmtpConfiguration"(
    "hostname" => 'smtp.gmail.com',
    "port" => 465,
    "ssl" => true,
    "username" => 'myUsername',
    "password" => 'MyPa$$Wor!' );;

Sending Emails

A simple mail to multiple recipients

exec "UTILS.sendMail"(
 "Recipients" => 'someone@somedomain.net,anotherrecipient@somedomain.biz',
 "Subject" => 'testsubject',
 "Body" => 'Some Content' );;

There are two Recipients in the sample separated by a comma. The subject and body content can be provided using the input arguments Subject and Body, respectively.

Sending an Email with attachment (here: a csv file from the DataVirtuality Server's file system)

exec "UTILS.sendMail"(
"Recipients" => 'somerecipient@theirdomain.com',
"Subject" => 'Hello World',
"Body" => 'With attachment this time',
"AttachmentName" => 'export.csv',
"Attachment" => (SELECT f.file
                           FROM (EXEC ds_file.getFiles("pathAndPattern" => 'export.csv'))as f),
"AttachmentMimeType" => 'text/csv'
);;

The first parameters are used in the same way as before. This time however, we send an attachment by use of the three additional parameters. AttachmentName is the name that the file will have within the Email, AttachmentMimeType is the required type of the attachment (lists for these can be found online) and Attachment points to the BLOB of the attachment itself. 

Our example reads the data from a file system which we made accessible through a file data source that is named ds_file in our case.

Typical error messages

Error
JavaException: javax.mail.MessagingException: Could not connect to SMTP host: smtp.gmail.com, port: 455

Solution
Please check that you have entered correct server host address and port. For example '<remote-destination host="smtp.gmail.com" port="465"/>' is correct, but the above message shows that a wrong port was used.

Error
JavaException: javax.mail.MessagingException: Could not connect to SMTP host: smtp.gmail.com, port: 455, response: -1

Solution
Check the SSL settings. Having <smtp-server ssl="false" outbound-socket-binding-ref="mail-smtp"> but connecting to a secure port will lead to this error.

Error
JavaException: javax.mail.AuthenticationFailedException: 535-5.7.8 Username and Password not accepted. Learn more at535 5.7.8 http://support.google.com/mail/bin/answer.py?answer=14257 xm4sm2073790wib.9 - gsmtp

Solution
You have used invalid credentials. Have a look at them and change accordingly.