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
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
"hostname" => 'smtp.gmail.com',
"port" => 465,
"ssl" => true,
"username" => 'myUsername',
"password" => 'MyPa$$Wor!' );;
Simple Email to Multiple Recipients
"Recipients" => 'email@example.com,firstname.lastname@example.org',
"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.
"Recipients" => 'email@example.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
|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||Please 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||Please check that you have entered the correct credentials.|