Email Notifications

You can use AppSynergy's email notification features to send email from your triggers or other PL/SQL code. In order to send email you must first create a SendGrid account and then link AppSynergy to it.

Linking AppSynergy to SendGrid

Follow these steps:

Create a SendGrid account - You can do so here.
Link Your Domain - Using their control panel, link your domain and verify it via Settings > Sender Authentication > Domain Authentication.
Create an API Key - Using their control panel, create an API key via Settings > API Key. Be sure to grant Mail Send privileges to the API key.
Link AppSynergy to your API Key - In AppSynergy go to My Apps then click on Account Settings. There you can enter your SendGrid API key and preferred default From email address.

Example 1 - Text Email

To send an email insert a row into the parasql_email_out table:

INSERT INTO parasql_email_out (from_address, to_address, subject, body)
VALUES (parasql_user_email(), 'joe@mydomain.com', 'My subject', 'Hello World');

In the example above the parasql_user_email() function returns the email address of the currently logged in user.

Example 2 - HTML Email

Similar to the example above but with html_format set to TRUE:

INSERT INTO parasql_email_out (from_address, to_address, subject, body, html_format)
VALUES (parasql_user_email(), 'joe@mydomain.com', 'My subject', '<h1>Body of HTML email here</h1>', TRUE);

Example 3 - Email with Attachment

To send an email with a document field attachment add a row to the parasql_email_out_attachment table as follows:

BEGIN

  DECLARE v_attachment VARCHAR(512);

  -- get document for attachment
  SELECT MyDocumentField 
    INTO v_attachment 
  FROM MyTable 
  WHERE MyTable_ID = 1234;

  -- create the email
  INSERT INTO parasql_email_out (from_address, to_address, subject, body) 
  VALUES (parasql_user_email(), 'joe@mydomain.com', 'My subject', 'My body...');

  -- add the attachment
  INSERT INTO parasql_email_out_attachment (email_out_id, attachment) 
  VALUES (last_insert_id(), v_attachment);

END

Email Related Tables

The following tables are used for sending and managing email. You can query them for diagnostic purposes.

parasql_email_out

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; joe.blow@somedomain.com or Joe Blow <joe.blow@somedomain.com>
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	Restricted to approved senders. Typically set via the parasql_user_email() function or hard coded to something like noreply@mydomain.com
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.
user_id	VARCHAR(64) NOT NULL DEFAULT USER()	User ID of sender.
unsubscribe_groups	VARCHAR(128)	DEPRECATED. SendGrid only. Semicolon separated list of SendGrid Unsubscribe Group IDs.
enable_tracking	BOOLEAN NOT NULL DEFAULT FALSE	DEPRECATED. SendGrid only. Enable SendGrid click tracking and open tracking.
substitutions	VARCHAR(512)	DEPRECATED. 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.

parasql_email_out_attachment

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