@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>";

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 Embed Note that it is not supported to specify inline attachments and message body in the same email. Since an inline attachment and body text can not be specified at the same time, DbVisualizer adds support to include text file content in the Body of the email from multiple files. By setting the `AttachType="Embed"` parameter 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.

Mail server connection parameters

Parameter	Default	Values
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 Use 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 around the values. I.e., `=""true""` is correct. Using `="true"` will render an error.
MailServerAccount		Refers to the name of a mail server account defined in Tools → Tool Properties and in the General / Mail Server Account category. 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, all of them are sent as attachments.
Having Body specified and AttachType="Inline" is not allowed. The file will then be attached.
AttachType="Inline, Embed" is not valid. The 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 the FileSource_ContentType. Use this to specify a MIME content type representing the file being attached. E.g.

FileSource_ContentType="image/png"
FileSource_ContentType="text/html"
FileSource_ContentType="application/vnd.ms-excel"

For embedded files, check the next chapter for information on 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 (placeholders) any files with AttachType="Embed" can be specified which when the email is sent is replaced with the file content. In addition, there is 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 MailServerXXX 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.

Adding a new Mail Server Account

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

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

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 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 placeholders 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 variables.

Variables keeping a result for the last executed statement

These holds data for the last executed statement before the ´@mail` command.

Variable/property	Description
{{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.

Variables keeping script execution result

These holds data up to but not including the @mail command in which they are used.

Variable/property	Description

{{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