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.
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 'firstname.lastname@example.org',--receiver 'email@example.com',--cc 'firstname.lastname@example.org,email@example.com',--bcc with two addresses specified 'firstname.lastname@example.org',--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;
Options Specific to Mail Servers
|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 STARTING_OFFSET option from a table that contains the last offset, as inselect lastOffset as STARTING_OFFSET from TEST.committedOffset.For more details, see the topic, Using Options Query in Integrating Kafka at https://docs.sqlstream.com/integrating-sqlstream/kafka/.|
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 USERNAMEemail@example.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