ECD Adapter and Agent: Writing to Mail Servers

<< Click to Display Table of Contents >>

Navigation:  Integrating Blaze with Other Systems > Writing Data Out of s-Server > Writing to Other Destinations >

ECD Adapter and Agent: Writing to Mail Servers

Previous pageReturn to chapter overviewNext page

Writing to a mail server with the ECD Adapter and Agent (ECDA) is relatively straightforward.

Note: Because of the nature of streaming data, you will need to set up a pump in order to move rows continually from an s-Server stream to another stream, file, Kafka topic, RDBMS table or other location. Pumps are INSERT macros that continually pass data from one point in a streaming pipeline to the other. A model for setting up a pump is provided below. See the topic CREATE PUMP in the s-Server Streaming SQL Reference Guide for more details.

Options Specific to Mail Servers

All adapter or agent implementations involve configuring options. For adapters, you configure and launch the adapter in SQL, using either server or foreign stream/table options. For agents, you configure such options using a properties file and launch the agent at the command line. Many of the options for the ECD adapter and agent are common to all I/O systems.

Options specific to mail servers appear below. .

Field

Definition

USERNAME

Username for authentication on mail server.

PASSWORD

Password for authentication on mail server.

MBOXDIR

Subdirectory, if any, on mail server.

HOST

Location of the mail server.

PORT

Mail server port.

FOLDER

Folder on mail server.

PROTOCOL

IMAP, IMAPS, IMAP4-SSL, POP3, SSL-POP, SMTP, SSMTP, MSTOR

Implementing the ECD Adapter and Agent for Mail Servers

You configure the ECD Adapter and Agent to read or write over mail servers using server options and foreign table/stream options. You reference the library com.sqlstream.aspen.namespace.common.MailColumnSet, as in the following code:

CREATE OR REPLACE SERVER "MailServer" TYPE 'MAIL'

FOREIGN DATA WRAPPER ECDA;

 

Next, you create a foreign stream object. This object contains connection information for the socket, such as format type, port, character encoding, whether or not to write a header, and the host name. Streams, like most SQL objects (but unlike data wrappers and servers), should be created within a schema. The following code first creates a schema in which to run the rest of the sample code below, then creates a foreign stream named "MailWriterStream."

CREATE OR REPLACE SCHEMA "MAILWRITER"

SET SCHEMA 'MAILWRITER'

CREATE OR REPLACE FOREIGN STREAM "FileWriterStream"

(subject VARCHAR(40)   OPTIONS (header 'subject'), -- this column is the Subject: header

 recipient VARCHAR(40) OPTIONS (header 'to'),      -- this column, is the To: header

 body VARCHAR(40)      OPTIONS (body ''))          -- this column is the message body

--Columns for the new stream

SERVER "MailWriterServer"

OPTIONS

(character_encoding 'UTF-8');

 

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 "FileWriterSchema"."FileWriterStream"

SELECT STREAM * FROM "MyStream";

--where "MyStream" is a currently existing stream

 

To start writing data, use the following code:

ALTER PUMP "Pumps"."writerPump" START;

 

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 "FILEWRITERSTREAM."

# Mail server configuration

USERNAME=MYUSER

PASSWORD=MUMBLE

CHARACTER_ENCODING=UTF-8

HOST=LOCALHOST

PROTOCOL=IMAP

PORT=143

# Schema, name, and parameter signature of origin stream

SCHEMA_NAME='MAILWRITER'

TABLE_NAME=FILEWRITERSTREAM

ROWTYPE=RECORDTYPE(VARCHAR(2040) id, VARCHAR(2040) reported_at, VARCHAR(2040) shift_no, VARCHAR(2040) trip_no, VARCHAR(2040) route_variant_id)

 

Special Columns Generated by ECDA for Mail Servers (Input Only)

The ECD Adapter and Agent generates several special columns when parsing data from mail servers. You can declare these columns to make them part of a foreign stream or table.

Special Column

Type

Meaning

MAIL_SERVER

VARCHAR(4096)

The mail server that sent this email.

MAIL_PORT

INTEGER

The port on which this email was received.

MAIL_FOLDER

VARCHAR(256)

The directory into which this message was saved.

MESSAGE_SENDER

VARCHAR(256)

The sender of this email.

MESSAGE_RECEIVER

VARCHAR(4096)

The receiver of this email.

SUBJECT

VARCHAR(4096)

The subject of this email.