@mail - Send emails and attach files

Only in DbVisualizer Pro

This feature is only available in the DbVisualizer Pro edition.

Mail with DbVisualizer

The @mail client side command is used to send emails from scripts executed in the SQL Commander and the command-line interface, dbviscmd. It offers full email setup including attaching multiple files and embedding data from files in the body of the email. There is also the capability to group settings for a mail server setup in a Mail Server Account enabling a simple reference for a mail server definition in the @mail command rather than a lot of parameters.

Here is a basic example with mail server parameters:

@mail
To="<recipient>"
Subject="<subject>"
Body="<body>"
MailServerHost="smtp.gmail.com"
MailServerPort="587"
MailServerUser="<user>"
MailServerPassword="<password>";

The following are the supported parameters for the @mail command:

Parameter	Default	Values
Subject		The subject
To		Defining the recipients of the mail. Multiple email addresses may be specified separated with comma: E.g.: John <john.doe@nowhere.com>, jane.doe@nowhere.com
Cc		Defining Cc recipients of the mail
Bcc		Defining Bcc recipients of the mail
From	Same as MailServerUser	This is by default the same address specified in MailServerUser. The mail server may reject sending from another email address depending how it is configured
ReplyTo	Same as From (if specified) or MailServerUser	An address to which replies should be sent
Body		The mail message body. Read more in Body parameter
FileSource		File attachment parameter Should specify the path to the file being attached. The path can be absolute or relative to the working directory: E.g: FileSource="/tmp/file.txt" Read more in FileSource parameter
FileSource_AttachType	Attach	Specifies how files are attached. Possible values: - Attach: Add the file as an attachment - Inline: Add the file as inline. Note that it is not supported to specify inline attachments and message body in the same email. - Embed. Since inline attachment and body text can not be specified at the same time DbVisualizer adds support to include text file content from multiple files in the message body. By setting the AttachType to Embed DbVisualizer will not also attach the file. More about this in the section about specifying message content (Body)
FileSource_ContentType		Optional, determined by the type of file attached.
MailHeaders		For adding mail headers to the mail. E.g: MailHeaders="X-Priority=""1""" The above mail header will result in a mail marked as important. For information about this and other headers please use online resources as the interpretation of mail headers may differ between different mail clients
MailTemplate		Offers the possibility to use a file as a template for the mail. Read more in MailTemplate parameter
The following parameters are used to setup the mail server connection
MailServerHost		The host name or IP address of the outgoing mail server
MailServerPort		The port number of the outgoing mail server
MailServerUser		The user account for the outgoing mail server
MailServerPassword		The user account password for the outgoing mail server. Note that the password is in plain text. The solution is to setup a mail server account
MailServerSecurity	None	For connecting to the mail server securely. Possible values are: - None (secure connection not enabled) - STARTTLS - SSL
MailServerProperties		Comma separated list of properties sent to the mail server. Eg.: MailServerProperties="mail.smtp.starttls.enable=""true"" mail.smtp.auth=""true""" Note the double-quotes of the values. I.e =""true"" is correct. Using ="true" would render an error
MailServerAccount		Refers to the name of a mail server account defined in Tools->Tool Properties->Mail Server Account. By defining a mail server account, MailServerAccount is the only parameter you will need when using @mail. Read more in Defining Mail Server Accounts

FileSource parameter - attaching files

The FileSource parameter should identify the path to an absolute or relative file that will be attached to the email. Files may be attached as a regular attachment, inline (for content and mail clients supporting it), and embedded as plain text (useful for text data).

Example of basic use resulting in the files being attached:

FileSource="C:\Data\file1.csv" FileSource_AttachType="Embed"
FileSource1="C:\Data\file2.xlsx"
FileSource2="C:\Data\file3.html" FileSource2_AttachType="Attach";

Restrictions with AttachType:

If using AttachType="Inline" there must only be a single file attached. If multiple files, these will be attached
Having Body specified and AttachType="Inline" is not allowed. The file will then be attached
AttachType="Inline, Embed" is not valid. File will then be embedded.
A text file may both be attached and embedded in the body of the email, use AttachType="Attach, Embed"

One more FileSource related parameter is theFileSource_ContentType. Use this to specify a MIME content type representing the file being attached.

For embedded files, check the next chapter for information how to specify where in the body content it should be inserted.

Body parameter

The Body parameter specifies the plain text content of the mail. With variables (place holders) any files with AttachType="Embed" can be specified which when the email is sent is replaced with the file content. In addition there are a collection of variables that can be included in the body related to the script execution. Read more about these in Expanding variables in the Body.

Example:

FileSource="C:\Temp\sales.csv"
FileSource_AttachType="Embed"
Body="Dear Mgmt, 

Please find the requested product information:

{{FileSource}}

/Staff"

Sample output:

Dear Mgmt, 

Please find the requested product information:

id  brand   length  width   hp  weight
1   Audi A4 48      76      28  44
2   Audi A6 96      6       51  86

/Staff

Defining Mail Server Accounts

Sending an email in DbVisualizer requires mail server related information that includes userid, password, server name, port, etc. These parameters are prefixed "MailServer" for the @mail command. Instead of specifying these as parameters a Mail Server Account can be defined in Tools->Tool Properties and under the Mail Server Accounts category.

11.0 mail server accounts

Use the left most button Add new ... to add a new account. Note choosing one of the top ones from the menu will create a new account from a named template. The template contains predefined values for well known mail servers. E.g the one named Gmail defines a temple for accessing an existing gmail account. Unless google changes connection data for gmail the only change that would be required is changing the User Name and Password.

The data entered for Mail Server Account are

Name	The name of the account. Example: "My Gmail" Corresponds to the MailServerAccount parameter for the @mail command.
Description	Optional short description of the mail server account
Host Name	The name or IP adress of the mail server. Example: smtp.gmail.com Corresponds to the MailServerHost parameter for the @mail command.
Port	The port number of the mail server. Example: 587 Corresponds to the MailServerPort parameter for the @mail command.
User Name	The mail account user name. Example: someuser@gmail.com Corresponds to the MailServerUser parameter for the @mail command.
Password	The mail account password. Example: "password for someuser@gmail.com" Corresponds to the MailServerPassword parameter for the @mail command.
Connection Security	For connecting to the mail server securely. Example: STARTTLS Corresponds to the MailServerSecurity parameter for the @mail command.

Testing a Mail Server Account

By selecting an account in the list and pressing the mail icon in the toolbar it is possible to send a test mail.

Generating client side commands @mail

Select an account in the list and press the copy icon in the toolbar. Two menu alternatives exist:

"Copy to clipboard as @mail command with all parameters" generates a @mail command with all mail server parameters as defined for the mail server account.

"Copy to clipboard as @mail command with MailAccountServer parameter" generates a @mail command with only the MailAccountServer parameter.

MailTemplate parameter

The MailTemplate parameter is used to specify a template file for the final email and some of the settings such as To, Subject, Body, etc.

Note:

The Body parameter must be defined as the last parameter in the template. Please see the section about Specifying Mail Content for a description of how to specify the Body.

Any parameters defined for the @mail command line will override the ones in the template. Example:

@mail MailTemplate="template.txt" 
Subject="A better subject" 
MailServerAccount="gmail";

And the template.txt file:

To: jane.doe@somewhere.com 
Subject: Hey take care of this! 
Body: The report
of the last ...

The mail will be sent to "jane.doe@somewhere.com" and the subject of the mail will be "Hey take care of this!".

Usage Examples

Sending mail with inline attachment

The following script exports an HTML file, sends an email with that file attached as inline (most email clients will render the HTML in the body of the email). Note that Body parameter is not defined.

@export on;
@export set filename="g_actors.html" Format="HTML";
SELECT * FROM ACTOR WHERE last_name LIKE 'G%';
@export off;

@mail Subject="The actors" To="first.last@somewhere.com" MailServerAccount="gmail" 
FileSource="g_actors.html" FileSource_AttachType="Inline";

Exporting to CSV Including csv in Mail body

Please see Export and Mail example.

All parameters on command line

@mail To="someone@somewhere.com" 
Subject="The subject" 
Body="The body"  
MailServerHost="smtp.gmail.com" 
MailServerPort="587" 
MailServerUser="first.last@gmail.com" 
MailServerPassword="password" 
MailServerSecurity="STARTTLS";

Expanding variables in the Body

The body of the mail can contain references to place holders with values supplied by DbVisualizer during execution of the script in which @mail is executed. The values are inserted using the syntax: {{place holder}}

Example:

@mail Body="Hi, Number of records exported where {{dbvis-current.rowCount}} ......"

The above example shows how we are referring the property dbvis-current.rowCount. dbvis-current refers to the current total result.

Following is a list of accessible variables.

Variable/property	Description
dbvis-last properties. Properties referring the result of the command executed just before the @mail command
{{dbvis-last.statusCode}}	The result status code
{{dbvis-last.isError}}	"true" if the last command failed
{{dbvis-last.executionMetrics.elapsed}}	The execution time
{{dbvis-last.executionMetrics.begin}}	Execution start time
dbvis-current properties. These refers the script execution up to but not including the @mail command in which they are used
{{dbvis-current.executionCount}}	Number of statements/commands executed
{{dbvis-current.schemaChanged}}	"true" if the schema has changed
{{dbvis-current.catalogChanged}}	"true" if the catalog has changed
{{dbvis-current.updateCount}}	The number of updated records
{{dbvis-current.numberOfResultSetsFetched}}	The number of result sets fetched
{{dbvis-current.rowCount}}	The number of records returned
{{dbvis-current.emptyResultSetsCount}}	The number of empty result sets fetched
{{dbvis-current.successCount}}	The number of statements/commands successfully executed
{{dbvis-current.errorCount}}	The number of statements/commands failed
{{dbvis-current.warningCount}}	The number of statements/commands with result warning
{{dbvis-current.stoppedOnErrors}}	"true" if the execution of the script was stopped on errors
{{dbvis-current.stoppedOnSQLWarning}}	"true" if the execution of the script was stopped because of SQL Warnings
{{dbvis-current.stoppedOnNoRows}}	"true" if the execution of the script was stopped because of result returned no rows
{{dbvis-current.executorWasInterrupted}}	"true" if the execution of the script was stopped due to user interrupt
File attachment related variables
{{FileSource}}	Replaced in runtime with the content of the file FileSource is referring to

The use of variables is illustrated in the following example.

Export and mail Example

@log "logfile.txt";

@export on;
@export set filename="g_actors.csv" Format="CSV";
SELECT * FROM ACTOR WHERE last_name LIKE 'G%';
@export off;

@export on;
@export set filename="f_contry.csv" Format="CSV" CsvIncludeColumnHeader="false";
SELECT country FROM COUNTRY WHERE country LIKE 'F%';
@export off;

@mail Subject="Last report" To="first.last@somewhere.com" MailServerAccount="gmail" 
Body="Hi, 
The total export took: {{dbvis-current.commandElapsedExecTime}} sec and {{dbvis-current.rowCount}} records where exported.

Actors with a last name starting with 'G':
{{FileSource}} 

Countries starting with 'F':
{{FileSource1}}

The log file is attached.
"
FileSource="g_actors.csv" FileSource_AttachType="Embed"
FileSource1="f_contry.csv" FileSource1_AttachType="Embed"
FileSource2="logfile.txt";

<- Previous@export - Export query result Next ->@import - Importing data