Sending Email
Often it is necessary to send an email notification when something changes in your database.
For example, when an order status changes from InProcess to Shipped you might want to notify the customer.
You can send an email from your database when something changes with just a few lines of trigger code.
The email can be anything from a simple plain text email to a highly formatted HTML email with document attachments.
Sending A Plain Text Email
To send a plain text email, simply add a row to the parasql_email_out table:
INSERT INTO parasql_email_out (to_address, subject, body)
VALUES ('somebody@somedomain.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 with html_format set to TRUE:
INSERT INTO parasql_email_out (to_address, subject, body, html_format)
VALUES ('somebody@somedomain.com', 'My subject line', '<h1>Body of HTML email here</h1>', TRUE);
Sending An Email with Attachments
To send an email with a document field attachment, simply add a row to the parasql_email_out_attachment table
that contains a reference to the document field.
BEGIN
DECLARE v_email_id BIGINT;
DECLARE v_doc_attachment VARCHAR(255); -- Document Fields are declared as VARCHAR(255)
-- create the email
INSERT INTO parasql_email_out (to_address, subject, body)
VALUES ('somebody@somedomain.com', 'My subject line', 'Body of the email here');
-- get the primary key of the email we just created
SELECT last_insert_id() INTO v_email_id;
-- get the document we want to attach
SELECT MyDocField INTO v_doc_attachment
FROM MyTable
WHERE MyTable_ID = 1234;
-- add the attachment to the email
INSERT INTO parasql_email_out_attachment (email_out_id, attachment)
VALUES (v_email_id, v_doc_attachment);
END
The parasql_email_out Table
Column Name | Datatype | Comments |
id | BIGINT NOT NULL AUTO_INCREMENT | Primary Key |
status | ENUM('UNSENT','SENT','ERROR') NOT NULL DEFAULT 'UNSENT' | UNSENT, SENT, or ERROR |
subject | VARCHAR(255) NOT NULL | Email subject. |
body | MEDIUMTEXT NOT NULL | Email body as plain text or HTML. |
html_format | BOOLEAN NOT NULL DEFAULT FALSE | Set to TRUE if body is in HTML format. |
to_address | VARCHAR(255) NOT NULL | Comma or semicolon separated list of addresses. |
cc_address | VARCHAR(255) | Comma or semicolon separated list of addresses. |
bcc_address | VARCHAR(255) | Comma or semicolon separated list of addresses. |
replyto_address | VARCHAR(255) | Single address only. |
from_address | VARCHAR(64) NOT NULL DEFAULT 'no-reply@parasql.com' | Restricted to approved senders unless using SendGrid.
If using SendGrid please provide support with your SendGrid API Key and your preferred default from address. |
scheduled_on | DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP() | Time email was added to table (server time zone). |
delay_until | DATETIME | Send immediately if null (server time zone). |
sent_on | DATETIME | Time email was actually sent (UTC). |
user_id | VARCHAR(64) NOT NULL DEFAULT USER() | User ID of sender. |
attachment_content | MEDIUMTEXT | DEPRECATED. Content for simple TEXT attachment (not used by SendGrid). |
attachment_filename | VARCHAR(128) | DEPRECATED. Filename for simple TEXT attachment (not used by SendGrid). |
unsubscribe_groups | VARCHAR(128) | SendGrid only. Semicolon separated list of SendGrid Unsubscribe Group IDs. |
enable_tracking | BOOLEAN NOT NULL DEFAULT FALSE | SendGrid only. Enable SendGrid click tracking and open tracking. |
substitutions | VARCHAR(512) | SendGrid only. Email substitutions as key=value;key=value; string. |
transport | ENUM('Google','SendGrid') | Automatically managed by AppSynergy; do not change this value. |
error_message | VARCHAR(512) | Error message text if send error. Automatically managed by AppSynergy; do not change this value. |
The parasql_email_out_attachment Table
Column Name | Datatype | Comments |
id | BIGINT NOT NULL AUTO_INCREMENT | Primary Key |
email_out_id | BIGINT NOT NULL REFERENCES parasql_email_out (id) ON DELETE CASCADE | References parasql_email_out.id |
attachment | VARCHAR(255) NOT NULL COMMENT 'GCSFILE' | contents of a Document Field, Image Field or Signature Field |
The 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 Name | Datatype | Comments |
id | BIGINT NOT NULL AUTO_INCREMENT | Primary Key |
email_out_id | BIGINT NOT NULL FOREIGN KEY (email_out_id) REFERENCES parasql_email_out (id) ON DELETE CASCADE | References parasql_email_out.id |
to_address | VARCHAR(255) NOT NULL | Comma or semicolon separated list of addresses, but typically only one in a personalization |
cc_address | VARCHAR(255) | Comma or semicolon separated list of addresses, but typically none in a personalization |
bcc_address | VARCHAR(255) | Comma or semicolon separated list of addresses, but typically none in a personalization |
subject | VARCHAR(255) | Personalized subject; can also customize email subject with substitutions |
substitutions | VARCHAR(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 your account 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.
You can optionally send email via your linked 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 support 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.