Sign In

Sending Email

ParaSQL has the ability to send email notifications (outbound email) including attachments. Your email can be in plain text or HTML format. The attachment(s) can come from any Document Field in your database.

Typically applications send email when certain events occurs (e.g. a status changes). Email is sent by adding a row to the parasql_email_out table with the contents of the email. Attachments can optionally be added to the email by adding rows to the parasql_email_out_attachment table.

Most often email is sent from a Database Trigger because it can easily capture and test for any status change, but email can also be sent from a Stored Procedure or Scheduled Event.

Sending A Plain Text Email

To send a plain text email, simply add a row to the parasql_email_out table as follows:

INSERT INTO parasql_email_out (to_address, subject, body) VALUES ('joe@anydomain.com', 'My subject line', 'Body of the email here');

Sending An HTML Email

To send an HTML formatted email, simply add a row to the parasql_email_out table as follows:

INSERT INTO parasql_email_out (html_format, to_address, subject, body) VALUES (true, 'joe@anydomain.com', 'My subject line', '<p>Body of the email here</p>');

Sending An Email with Attachments

To send an email with attachment(s), simply add a row to the parasql_email_out_attachment table that contains a reference to the document field you want to attach to the email:

DECLARE email_id BIGINT;
DECLARE doc_to_send VARCHAR(255); -- ParaSQL Document Fields are declared as VARCHAR(255)

INSERT INTO parasql_email_out (to_address, subject, body) VALUES ('joe@anydomain.com', 'My subject line', 'Body of the email here');

-- get the primary key of the parasql_email_out row we just inserted
SELECT last_insert_id() INTO email_id;

-- get the value of some Document Field
SELECT MyDocField INTO doc_to_send FROM SomeTable WHERE SomeTable_ID = 1234;

-- add the doc as email attachment
INSERT INTO parasql_email_out_attachment (email_out_id, attachment) VALUES (email_id, doc_to_send);

Columns In parasql_email_out Table

Column NameDatatypeComments
idBIGINT NOT NULL AUTO_INCREMENTPrimary Key
statusENUM('UNSENT','SENT','ERROR') NOT NULL DEFAULT 'UNSENT'UNSENT, SENT, or ERROR
subjectVARCHAR(255) NOT NULLEmail subject
bodyMEDIUMTEXT NOT NULLEmail body as plain text or HTML
html_formatBOOLEAN NOT NULL DEFAULT FALSESet to TRUE if body is in HTML format
to_address VARCHAR(255) NOT NULLComma or semicolon separated list of addresses
cc_address VARCHAR(255)Comma or semicolon separated list of addresses
bcc_addressVARCHAR(255)Comma or semicolon separated list of addresses
replyto_addressVARCHAR(255)Single address only
from_addressVARCHAR(64) NOT NULL DEFAULT 'no-reply@parasql.com'Restricted to approved senders
scheduled_onDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP()Time email was added to table (server time zone)
delay_untilDATETIMESend immediately if null (server time zone)
sent_onDATETIMETime email was actually sent (UTC)
user_idVARCHAR(64) NOT NULL DEFAULT USER()User ID of sender
attachment_content MEDIUMTEXTContent for simple TEXT attachment (not used by SendGrid)
attachment_filenameVARCHAR(128)Filename for simple TEXT attachment (not used by SendGrid)
unsubscribe_groupsVARCHAR(128)semicolon separated list of SendGrid Unsubscribe Group IDs
enable_trackingBOOLEAN NOT NULL DEFAULT FALSEenable SendGrid click tracking and open tracking
substitutionsVARCHAR(512)Email substitutions as key=value;key=value; (only used by SendGrid)
transportENUM('Google','SendGrid')
error_messageVARCHAR(512)error message text if send error

Columns In parasql_email_out_attachment Table

Column NameDatatypeComments
idBIGINT NOT NULL AUTO_INCREMENTPrimary Key
email_out_idBIGINT NOT NULL REFERENCES parasql_email_out (id) ON DELETE CASCADEReferences parasql_email_out.id
attachmentVARCHAR(255) NOT NULL COMMENT 'GCSFILE'ParaSQL Document Field

Columns In parasql_email_personalizations Table (SendGrid only)

This table enables sending personalized email to a large number of recipients using a single email body (typically for email marketing). You can think of a single personalization row as a single envelope in a mass mailing (it contains the address info and substitutions needed to customize each person's name or other info in the email template).

Column NameDatatypeComments
idBIGINT NOT NULL AUTO_INCREMENTPrimary Key
email_out_idBIGINT NOT NULL FOREIGN KEY (email_out_id) REFERENCES parasql_email_out (id) ON DELETE CASCADEReferences parasql_email_out.id
to_addressVARCHAR(255) NOT NULLComma or semicolon separated list of addresses, but typically only one in a personalization
cc_addressVARCHAR(255)Comma or semicolon separated list of addresses, but typically none in a personalization
bcc_addressVARCHAR(255)Comma or semicolon separated list of addresses, but typically none in a personalization
subjectVARCHAR(255)Personalized subject; can also customize email subject with substitutions
substitutionsVARCHAR(512)Email substitutions as key=value;key=value; such as %first_name%=Bob;%account_no%=1234;

Sending Email From anybody@yourcompany.com (SendGrid only)

By default ParaSQL uses Google to send email. When sending email via Google the only valid from_address is no-reply@parasql.com or the email address of the currently signed in Google Account user. This is too restrictive for many use cases including invoicing, marketing, etc.

ParaSQL can optionally send email via your SendGrid account (www.sendgrid.com). This will allow you to send email from anybody@yourcompany.com without restriction. After setting up your SendGrid account (be sure to visit SendGrid > Settings > Sender Authentication to authenticate your domain) you can provide a SendGrid API Key to ParaSQL for sending email. All outgoing email will be signed by your domain and sent with TLS security.

Using Full Email Addresses (SendGrid only)

When sending email via SendGrid you may format any email address as simply joe.blow@somedomain.com or optionally use the complete name and address format like this: Joe Blow <joe.blow@somedomain.com>

Email Substitutions (SendGrid only)

When sending email via SendGrid you may specify custom values to be substituted into your email body or subject line. The substitution values are specified via the parasql_email_out.substitutions column or the parasql_email_personalizations.substitutions column.

Say you had an email body like this:

Hi %first_name%,
Your customer number is %customer_id%.
Thanks!

You could then specify substitutions to be:

%first_name%=Bob;%customer_id%=12345;

And the email sent would look like this:

Hi Bob,
Your customer number is 12345.
Thanks!

There are also global substitution tags that you can use in your email body for things like Unsubscribe links. For example, the <%asm_global_unsubscribe_raw_url%> tag will be replaced with the SendGrid global unsubscribe URL, and the <%asm_group_unsubscribe_raw_url%> tag will be replaced with the SendGrid group unsubscribe URL. Use the second tag with the parasql_email_out.unsubscribe_groups column to specify which unsubscribe group the email belongs to.

Diagnosing Problems

To diagnose email problems, review the error_message column of the parasql_email_out table by issuing a command like the following via Tools > SQL Console...:

SELECT * FROM parasql_email_out WHERE error_message IS NOT NULL ORDER BY id DESC LIMIT 25

The most common error is specifying an invalid from_address. If sending via Google (the default) the from_address must be either no-reply@parasql.com or the email address of the currently signed in Google Account user. If sending via SendGrid the from_address must be somebody@yourdomain.com. You may of course create a view on this hidden table to make it accessible to your applications.