Integrating Mail Servers

You can send emails from s-Server using the Extensible Common Data adapter or agent. To do so, you set up a server object with information on connecting to the SMTP server. At minimum, you need to specify a host for your SMTP server and a user name for this host. You can also specify a password, port, and connection security (these all default to none). See Options for Writing to Mail Servers below.

Once you have set up the server object, you set up a foreign stream that uses the server object. The foreign stream contains information on the message you will send through the SMTP server. Here, you specify a subject, receiver, and body for the message. You can also specify CC, BCC, and REPLY-TO for the message.

SENDER, RECEIVER, SUBJECT, CC, BCC, REPLY_TO can all be specified as options in either the server or foreign stream. Specifying them in the server lets you reuse these options for any foreign stream that uses the server. See Writing to Mail Servers Using SQL below.

You can also specify SENDER, RECEIVER, SUBJECT, CC, BCC, and REPLY_TO as column names in the foreign stream. This lets you insert values for these columns from another stream.

s-Server will write any other columns for the foreign stream into the body of the message.

To write mail from a remote location, you can use the ECD agent. See Implementing the ECD Agent for Mail Servers below.

Writing to Mail Servers Using SQL

To send email from s-Server, you create a foreign stream in SQL that references the prebuilt server MAIL_SERVER. This object contains specific information for the mail to be sent. You will likely want to specify one or more of the following as either foreign stream options or special columns: SENDER, RECEIVER, SUBJECT, CC, BCC, REPLY_TO.

For more information on creating foreign streams, see the topic CREATE FOREIGN STREAM in the Streaming SQL Reference Guide.

You will also need to specify a formatter for the foreign stream. Usually, you can use CSV for this setting. Specifying "formatter" as a foreign stream option tells s-Server that this foreign stream writes data. See Output Formats for Writing in this guide for more details.

Streams, like most SQL objects (but unlike data wrappers and servers), must be created within a schema. The following code first creates a schema called mail in which to run the rest of the sample code below, then creates a foreign stream named outbox.

CREATE OR REPLACE SCHEMA mail;
SET SCHEMA 'mail';

CREATE OR REPLACE FOREIGN STREAM outbox
(SUBJECT VARCHAR(400), --special column: subject used in the header when sending mail
RECEIVER VARCHAR(400), --special column: address used in the "To" header when sending mail
CC VARCHAR(400), --special column: address used in the "CC" header when sending mail
BCC VARCHAR(400), --special column: address used in the "BCC" header when sending mail
REPLY_TO VARCHAR(400), --special column: address used in the "Reply-To" header when sending the email.
BODY VARCHAR(400)) --body of the message. Can be any column name.  
                   --Any additional columns will also be added to the body of the message.
SERVER MAIL_SERVER
OPTIONS
(formatter 'CSV');

To test this configuration, you can use code along the following lines:

insert into mail.outbox values(
'testing mail write',--subject
  'receiver@guavus.com',--receiver
  'cc@guavus.com',--cc
  'bcc@guavus.com,bcc2@guavus.com',--bcc with two addresses specified
  'reply-to@guavus.com',--reply_to
  'Checking in to see if mail write works'--body of message
 );

In most cases, you will want to set up a pump that writes data to mail.outbox. Pumps are INSERT macros that continually pass data from one point in a streaming pipeline to the other. See the topic CREATE PUMP in the s-Server Streaming SQL Reference Guide for more details.

You do so with code along the following lines:

CREATE OR REPLACE SCHEMA pumps;
SET SCHEMA 'pumps';

CREATE OR REPLACE PUMP "writerPump" STOPPED AS
--We recommend creating pumps as stopped
--then using ALTER PUMP "Pumps"."writerPump" START to start it
insert into mail.outbox --schema and name for foreign stream defined above
SELECT STREAM * FROM "MyStream";
--stream from which to insert data, where "MyStream" is a currently existing stream

To start writing data, use the following code:

ALTER PUMP "Pumps"."writerPump" START;

Foreign Stream Options for Mail Servers

Option Definition
USERNAME Required. User name for the SMTP server defined in HOST.
HOST Required. Host name for the SMTP server.
PASSWORD Optional. Password to use for the SMTP server defined in HOST. s-Server uses this option with USERNAME when authenticating to the SMTP server. If this option is empty, s-Server will not attempt authentication. Defaults to none.
PORT Optional. Port for SMTP server defined by HOST. Defaults to none.
CONNECTION_SECURITY Optional. Security used to connect to SMTP server. SSL, STARTTLS or NONE. Defaults to NONE.
SENDER Optional. Used in the “Sender” header when sending the email. Can also be specified as a special column in the foreign stream.
RECEIVER Optional. Address used in the “To” header when sending the email. Can also be specified as a special column in the foreign stream. You can specify multiple addresses separated by commas.
SUBJECT Optional. Subject for email. Can also be specified as a special column in the foreign stream.
REPLY_TO Optional. Address used in the "Reply-To" header when sending the email. Can also be specified as a special column in the foreign stream.
CC Optional. Address used in the “CC” header when sending the email. Can also be specified as a special column in the foreign stream. You can specify multiple addresses separated by commas.
BCC Optional. Address used in the “BCC” header when sending the email. Can also be specified as a special column in the foreign stream. You can specify multiple addresses separated by commas.
TIMEOUT Optional. Socket read timeout value in milliseconds. Defaults to 30000.
CONN_TIMEOUT Optional. Socket connection timeout value in milliseconds. Defaults to 30000.
FORMAT_CHARSET_KEY Optional. Charset for formatting mail. Defaults to UTF-8. See https://docs.oracle.com/javase/8/docs/api/java/nio/charset/StandardCharsets.html
LOCALHOST Optional. Used to set the mail.smtp.localhost option in the JavaMail API. See https://javaee.github.io/javamail/docs/api/com/sun/mail/smtp/package-summary.html for more information. It is generally best to leave this blank.
OPTIONS_QUERY Optional. Lets you query a table to update adapter options at runtime. You can use this, for example, to set the PORT option using select PORT from TEST.mail_options. For more details see the topic Using the Options Query Property.

Implementing the ECD Agent for Mail Servers

The ECD agent takes similar options, but these options need to be formatted in a properties file along the lines of the following. These properties correspond to those defined for the adapter above.

Note: Before using the ECD agent, you need to create a source stream for it. In the below example, you would need to create the foreign stream outbox in a schema called mail.

# Mail server configuration
USERNAME=myuser@myserver.com
HOST=mail.myserver.com
PASSWORD=mypassword!
HOST=mail.myserver.com
PORT=587
CONNECTION_SECURITY=STARTTLS
# Schema and name of origin stream
SCHEMA_NAME=mail
TABLE_NAME=outbox
#columns
ROWTYPE=RECORDTYPE(VARCHAR(400) SUBJECT, VARCHAR(400) RECEIVER, VARCHAR(400) CC, VARCHAR(400) BCC, VARCHAR(400) REPLY_TO, VARCHAR(400) BODY))
FORMATTER=CSV