eC-Messenger may access external databases through JDBC.

JDBC is a Java based standard for accessing databases. eC-Messenger can access all databases, which have a JDBC driver available (which should be most relational databases; none relational databases, e.g. Lotus Notes, may offer special solutions).

In eC-Messenger, a periodic Task will be configured that accesses that external database. This Task contains a sequence of SQL statements (in any order) that export from eC-Messenger (e.g. Tracking Data, Recipient Attributes or Unsubscriptions) or import (e.g. new Recipients). Each statement has a (freely choosable) name.

For exports, the Statements contain placeholders which are filled in by the exporting process. The placeholders follow the regular eC-M placeholder logic.

For imports ("load" in the example below), the placeholders {fromRow},{numRows} are used in order to chunk the input data. This is especially important if huge datasets are to be processed. There is a configuration chunksize related to this (set up by eCircle).

In order to map the table fields to the attribute names required by eC-Messenger, SQL aliases may be used (as illustrated in the "load" query below for the email field.).

Example SQL Statements and Sequence#

This example assumes a table users in the remote database, which has (possibly among other fields) a key field e which contains the email address (which is always the primary key for eC-M). The Task is configured, to execute the SQL sequence "bounce,unsubscribed,load,subscribed", which can be read as "delete the invalid users from the external database first, the load the users form there into eC-M, then add the eC-M subscribers to that database".

  bounce: delete from users where e=<%user.Email%>;
  unsubscribed: delete from users where e=<%user.Email%>;
  load: select e as email from users limit {fromRow},{numRows};
  subscribed: insert into users (e) values (<%user.Email%>);

Configuration Scenarios#

eC-M is build to allow direct access to a remote database. This is most flexible, as it allows eC-M to trigger the execution.

Sometimes this may not be possible, e.g. because of security restriction on the customer side (e.g. because the inbound port must be opened in the firewall or the database table access can not be secured on some databases). In this cases eCircle offers a "bridging" database (MySQL), that can be used by the customer. In such a scenario, timing must be observed, as the eC-M will be configured to poll the bridge table at configured intervalls.

Tables can be created as appropriate by the customer or by eCircle.

For monitoring purposes, it is useful to maintain a small logging table, that records start and end times for each access. For eC-M, another query may be prepended and appended to the statement set shown above, like this:

 insert into log(dt, action) values (now(), "Start Import");

Required Information / Conf#

(This is a part of the complete configuration, done by eCircle personnel).

JDBC Driver

The driver jar file. This can be obtained from the database vendor. For most of the commonly used databases (Oracle, Postgres, MySQL), we do have drivers. Some Vendors (like Microsoft, for MS-SQL Server) require a license. In case we do not own a license, the driver must be provided to us from the Customer.

DB Host

The Database Server, an IP or a Host name. For the "bridge" scenario, eCircle has this information.

DB Port

The TCP Port to access the database. For the "bridge" scenario, eCircle has this information.

enc

The encoding of Text used in the remote database. For the "bridge" scenario, this will be UTF-8.

Import Settings

Other settings related to the import mode are sync_type and action (see eC-M Handbook). The system name and the recipient group to be imported to must be given too.