Sending Emails with Data Virtuality

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

A message can be sent straight via a SQL command, and this feature can also be used to configure email notifications based on job runs.

Reading 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 is

UTILS.getSmtpConfiguration()

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

Setting 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.

Sample Configuration for a Gmail Account

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

Sending Emails

Simple Email 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.

Email with Attachment

In this example, the attachment is a csv file from the Data Virtuality 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 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);
• 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 named ds_file in our case.

Typical Error Messages