SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Data Virtualization Tools and Software Denodo Platform and Data Virtuality Tutorials and Downloads for SQL Database Developer

Send Log Files as Email Attachments on Data Virtuality using SQL

In this Data Virtuality tutorial, I want to share with data virtualization platform administrators and developers how they can use Data Virtuality functions and procedures from UTILS library like sendMail to share the server logs via email. If you have already completed SMTP configuration on your Data Virtuality instance, you can extend your solutions developed on the server by adding the functionality to share files via email.

To enable sending files via email with SQL code on Data Virtuality, following steps should be completed:
SMTP configuration on DV server,
Adding the folder where the files are defined as a new data source

SMTP configuration can be done on Window > Preferences menu option on Server Preferences > SMTP Configuration screen.

SMTP configuration on Data Virtuality server using Amazon SES service

In this sample, my data virtualization platform is configured to use Amazon Simple Email Service aka Amazon SES service from AWS cloud platform provider.

As the following step, you can add the file folder where the files that you want to send via email should be added as a new File data source to Data Virtuality application.
File data source enables developers to add a directory path as the data source including its content. So a single File data source can include multiple files.

File data source connector on Data Virtuality for server log files

On a Linux installation the log files are stored in the directory /opt/datavirtuality/dvserver/standalone/log/ so I added them as a new File data source as seen in following screenshot.

display log files on Data Virtuality using File connector and SQL code

Data Virtuality SQL developers can execute following sample code to send email with the server log and boot log files in the attachments to a specific email address.

sample SQL code for Data Virtuality developer

Let's look at the SQL code in detail.
Whenever the data virtualization platform administrator or developer adds a new data source of type File, it is possible to display the list of files using either getTextFiles or getFiles procedures.

Calling the native procedure getTextFiles() enables the SQL developers to access the files and its content to manipulate like applying string functions.
For example, if you look at the below SQL code, you will realize there are two attachments to this sample email.
One of the attachments is server.log file. On the other hand, since the server.log file is a big file it is not possible to send it via email. So I had to take only the last 40000 characters from that file and attach it to the email for SMTP email. Since the output of getTextFiles procedure is text, I could apply RIGHT() string function.

It is important to note that the attachments to the email should be binary. For this reason the content from server.log which I selected only the last n characters should be converted to binary format. This is managed by calling the TO_BYTES() function

For the second attached file boot.log, I fetched the file by calling getFiles() function which returns binary output. Since I have already the binary content and because I don't need to read and change its context by applying string SQL function.

exec "UTILS.sendMail"(
 "Recipients" => 'datavirtuality@kodyaz.com',
 "Subject" => 'Mail Subject',
 "Body" => 'Mail Body',
 "Attachments" => array(
  (select to_bytes(right(file, 40000), 'UTF-8')
   from (call "DV_Server_Log.getTextFiles"() ) a
   where filePath like '%server.log')
 ,(select file
   from (call "DV_Server_Log.getFiles"() ) a
   where filePath like '%boot.log')
 "AttachmentNames" => array(
 "AttachmentMimeTypes" => array(
) ;;
SQL Code Sample to Send Email with Attachments on Data Virtuality

I hope this Data Virtuality tutorial which includes sample SQL code is useful for data virtualization platform users. To summarize the tutorial is showing how to attach server log files and send via email based on the SMTP configuration of the Data Virtuality server.

Data Virtualization

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.