Sign In

ParaSQL Inbound & Outbound Mail

Overview

ParaSQL has the ability to send email notifications (Outbound Mail) and receive email into the database (Inbound Mail).

Typically ParaSQL applications send email notifications when a certain event occurs (e.g. a status or value changes). The email being sent can be simple or complex, in basic text format or include fancy HTML formatting. You can also send documents stored in your database as email attachments.

In addition you can receive email into your database by sending email to db12345@parasql-app.appspotmail.com where db12345 is your ParaSQL account number. The mail is received into your database with formatting and file attachments intact for easy use within applications.

Outbound Mail (Sending Email Notifications)

To send an email, simply insert a row into the parasql_email_out table. The columns are as follows:

Column NameDatatypeComments
idBIGINT NOT NULL AUTO_INCREMENTPrimary Key
subjectVARCHAR(255) NOT NULLEmail subject
bodyMEDIUMTEXTEmail body as plain text or HTML
html_formatBOOLEANSet to TRUE if body is in HTML format
to_address VARCHAR(255) NOT NULLSemicolon separated list of addresses
cc_address VARCHAR(255)Semicolon separated list of addresses
bcc_addressVARCHAR(255)Semicolon separated list of addresses
replyto_addressVARCHAR(255)Semicolon separated list of addresses
from_addressVARCHAR(64) NOT NULL DEFAULT 'no-reply@parasql.com'Restricted to approved senders
statusVARCHAR(255) NOT NULL DEFAULT 'UNSENT'UNSENT, SENT, or ERROR with message
scheduled_onDATETIME NOT NULL DEFAULT now()Time email was added to table
delay_untilDATETIMESend immediately if null
sent_onDATETIMETime email was actually sent
user_idVARCHAR(64) NOT NULL DEFAULT current_user()User ID of sender
attachment_content MEDIUMTEXTContent for simple TEXT attachment
attachment_filenameVARCHAR(128)Filename for simple TEXT attachment

To add an attachment to an email, simply insert a row into the parasql_email_out_attachment table. The columns are as follows:

Column NameDatatypeComments
idBIGINT NOT NULL AUTO_INCREMENTPrimary Key
email_out_idBIGINT NOT NULLReferences parasql_email_out.id
attachmentVARCHAR(255) GCSFILEParaSQL Document Field

Typically outbound mail notifications are sent via a Database Trigger after a new row is added or an existing row is updated with a new status or value. The following example sends a simple email after a new customer Order row is added:

BEGIN

    -- send email
    INSERT INTO parasql_email_out (to_address, replyto_address, subject, body)
       VALUES (NEW.Customer_Email_Address,'billing@mycompany.com','Thank you for your order.','Body of the message here...');

END

The following is an example of how to add an attachment to that email:

BEGIN

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

    -- send email
    INSERT INTO parasql_email_out (to_address, replyto_address, subject, body)
       VALUES (NEW.Customer_Email_Address,'billing@mycompany.com','Thank you for your order.','Body of the message here...');

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

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

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

END

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

SELECT * FROM parasql_email_out WHERE status != 'SENT' ORDER BY id DESC

Inbound Mail (Sending Email To Your Database)

You can receive email into your database by sending email to db12345@parasql-app.appspotmail.com where db12345 is your ParaSQL account number. The mail is received into your database with formatting and file attachments intact for easy use within applications. This feature is configurable and must be enabled for your account.

The mail is stored in three tables (that will be visible in ParaSQL once your account is enabled):

Typical use cases include CRM and Customer Service type applications; the end user would typically CC or BCC db12345@parasql-app.appspotmail.com in correspondence to keep a copy in the database that can be associated by matching email addresses. Outbound mail can also use this address as a ReplyTo address.