DataVirtuality 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 accounts 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 affect. 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
"hostname" => 'smtp.gmail.com',
"port" => 465,
"ssl" => true,
"username" => 'myUsername',
"password" => 'MyPa$$Wor!' );;
A simple mail to multiple recipients
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)
"Recipients" => '[email protected]',
"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 name ds_file in our case.
Typical error messages
JavaException: javax.mail.MessagingException: Could not connect to SMTP host: smtp.gmail.com, port: 455
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.
JavaException: javax.mail.MessagingException: Could not connect to SMTP host: smtp.gmail.com, port: 455, response: -1
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.
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
You have used invalid credentials. Have a look at them and change accordingly.