Using SQLMessenger External Interface

1. What is the External Interface

To facilitate invoking SQLMessenger’s message delivery and report distribution capabilities from your database, SQLMessenger provides the External Interface feature. After enabling this function on a data source, the system automatically creates the interface table sqlmessenger_intf within the database linked to the specified data source. Inserting data into this table lets you send emails, Slack messages, or reports, or trigger preconfigured tasks in SQLMessenger.

Enable the External Interface feature on the data source

Enable the External Interface feature on the data source

Enable sending emails from the interface table on this data source	When enabled, this option permits sending emails through the interface table for this data source. Example SQL statements are as follows: INSERT INTO sqlmessenger_intf (send_to, subject, body) VALUES ('test@sqlmessenger.com',--Recipient Address 'Test mail',--Email subject 'This is a test mail.' --Email Body );
Enable triggering tasks from the interface table on this data source	When enabled, this option allows you to trigger tasks in SQLMessenger through the interface table for this data source. Example SQL statements are as follows: INSERT INTO sqlmessenger_intf (oper_type, task_id) VALUES ( 'T',--Operation type. "T" indicates to start a task. 123 --Task ID to start );
Select Tasks	If Allow triggering tasks via the interface table on this data source is enabled, you can select which tasks can be triggered from the interface table for this data source.
Enable sending messages to Slack from the interface table on this data source	When enabled, this option allows you to send messages to Slack via the interface table for this data source. Example SQL statements are as follows: INSERT INTO sqlmessenger_intf (oper_type, slack_app_name, send_to, body) VALUES ( 'S',--Operation type. Operation type: "S" = send message to Slack 'SQLMessenger(User Token)',--Slack app name to use 'U014J079A4F,info@sqlmessenger.com,#team',--Recipients: Member ID / email / #channel. Separate multiple members with a comma (","). 'This is a test message' --Message content ); Tips: In the send_to field, you can specify a Member ID, a member email address, or a channel name (prefix with #). Note: When using an email address or channel name, SQLMessenger resolves it to a Member ID from the locally cached Slack member list. If workspace member information changes, re-import the updated member list into SQLMessenger. Click here for details.
Choose Slack apps for sending messages	Specify which Slack apps this data source can use to send messages. By default, all Slack apps and webhooks in the system are allowed.
Scan the interface table every xx seconds	Set the interval (in seconds) for polling new requests from the interface table. The default interval is 60 seconds.

2. External Interface Workflow

The workflow of SQLMessenger’s External Interface function is as follows:

If the External Interface is enabled, the system periodically reads unprocessed requests (state = 'A') from the interface table (sqlmessenger_intf) and updates their state to 'D' (processing).

The system executes the corresponding action based on the request type (oper_type), such as sending emails or triggering a task.

After processing, the system writes the result back to the interface table, updating the state to 'C' (success), 'E' (error, retryable), or 'F' (failure, non‑retryable). Processed records are later moved to the archive table (sqlmessenger_intf_h).

Tips: If a request’s status is 'E' (processing error), the system will retry it later. If the status is 'F' (processing failure), no retries will be performed.

The Workflow of the External Interface

3. External Interface Table

If the External Interface feature is enabled, the system creates the following interface tables for the corresponding data source:

sqlmessenger_intf	Stores user-submitted operation requests including email delivery, task triggering and Slack message sending. The system regularly fetches and processes these requests.
sqlmessenger_intf_h	Stores all processed historical records. Completed requests from sqlmessenger_intf will be archived into this table automatically.

Tips: In Oracle databases, SQLMessenger also creates a sequence named seq_sqlmessenger_intf_id in the data source. In addition, a BEFORE INSERT trigger is created on the SQLMESSENGER_INTF table to automatically generate REC_ID values for new records.

The structure of SQLMESSENGER_INTF:

REC_ID	BIGINT	Unique record ID and primary key. In Oracle databases, the insert_sqlmessenger_intf_id trigger retrieves values from seq_sqlmessenger_intf_id. Other databases use an auto-increment column for automatic numbering.
OPER_TYPE	CHAR(1)	Type of operation. M: Send an email. T: Start a specified task. S: Send a message using Slack API. Default is M (send an email).
TASK_ID	INT	If OPER_TYPE = 'T', specify the ID of the task to be triggered. The task ID can be found in Task Manager. Find the task ID in the Task Manager Task Manager Tips: Before triggering tasks via the external interface table, add the task to the Tasks allowed to be started from this data source list in the data source's External Interface configuration. Add tasks to the "Tasks allowed to be started from this data source" list Data Source Management
SUBJECT	VARCHAR(500)	If OPER_TYPE = 'M' (send email), specify the email subject.
BODY	VARCHAR	If OPER_TYPE = 'M' (send an email), specify the email body. Maximum length is 2000 for Informix and 4000 for other databases.
BODY_TYPE	CHAR(4)	If OPER_TYPE = 'M' (send an email), specifies the email body type. Allowed values: HTML: BODY is treated as HTML TEXT: BODY is treated as plain text. Default: TEXT.
SEND_TO	VARCHAR	Recipients of the operation. If OPER_TYPE = 'M' (send an email), specifies the email recipients. Multiple recipients are separated by commas. Examples: user01@sqlmessenger.com,user02@sqlmessenger.com or "User1<user01@sqlmessenger.com>,User2<user02@sqlmessenger.com>" If OPER_TYPE = 'S' (send a message via Slack API), specifies the message recipients, including Slack user IDs, email addresses, or channels. Multiple recipients are separated by commas. Example: U014J079A4F,info@sqlmessenger.com,#team
COPY_TO	VARCHAR	CC recipients. If OPER_TYPE = 'M' (send an email), specifies the CC recipients of the email. Multiple recipients are separated by commas. Examples: user01@sqlmessenger.com,user02@sqlmessenger.com or User1<user01@sqlmessenger.com>,User2<user02@sqlmessenger.com>
BCC	VARCHAR	BCC recipients for email messages. Applicable when OPER_TYPE = 'M' (send an email). Multiple recipients are separated by commas. Example formats: user01@sqlmessenger.com,user02@sqlmessenger.com User1<user01@sqlmessenger.com>,User2<user02@sqlmessenger.com>
TASK_PARAMS	VARCHAR	Parameters passed to the task. Applicable when OPER_TYPE = 'T' (start a specified task). Specifies variable values to be passed to the task. Format: parameter_name=parameter_value;parameter_name=parameter_value; Multiple parameters are separated by semicolons (;). Maximum length is 2000 for Informix and 4000 for other databases. Note: The "Allow variable value to be passed in from external interface tablesW" option in the task variable configuration must be enabled to allow parameters to be passed from this field. Otherwise, task execution will fail with the error: "Not allowed to pass this variable from the external interface." Allow passing variable values from external interface table Using Variables in Tasks
EXP_TIME	DATETIME	Set expiration time of the request record. Default validity period is 24 hours if unspecified. Expired records will not be processed.
USE_MAIL_ACCT	VARCHAR	Specifies the sending email account used when OPER_TYPE = 'M'. Multiple accounts can be specified, separated by commas. If not specified, SQLMessenger will randomly select an available sending account. The '' symbol can be used to indicate any available account. Example: acct1@test.com,acct2@test.com, This indicates a preferred sending order: 1. Use acct1 first. 2. If sending with acct1 fails, use acct2. 3. If both acct1 and acct2 fail, use any available account (including acct1 and acct2).
SLACK_APP_NAME	VARCHAR	Specifies the Slack app used for sending messages when OPER_TYPE = 'S'. Note: The value must match the name of a Slack App or Webhook configured in SQLMessenger. Matching is case-insensitive.
CREATE_TIME	DATETIME	Timestamp when the request record is created. Automatically populated by the system.
STATE	CHAR(1)	Status of the request record. A: Unprocessed (default) D: Being processed by SQLMessenger C: Executed successfully E: Execution error (will be retried later) F: Execution failed (no further retries) X: Deleted Default: A Note: If the status is 'E' (execution error), the system will retry processing later. If the status is 'F' (execution failed), no further retry attempts will be made.
STATE_TIME	DATETIME	Timestamp when the status of the request record is updated. Automatically set by the system.
MAIL_ID	BIGINT	Email ID generated by SQLMessenger for the request. This field is populated after successful processing.
TASK_INST_ID	BIGINT	Task instance ID generated by SQLMessenger. Applicable when OPER_TYPE = 'T' (start a specified task). This field is populated after processing is complete.
RESULT_MESSAGE	VARCHAR	Message describing the processing result. This field is automatically populated by SQLMessenger.
SYSTEM_ID	VARCHAR	System ID that processed the request. Automatically populated by SQLMessenger.
DATA_SOURCE_ID	INT	ID of the data source used for processing the request. This field is automatically populated by SQLMessenger.
OPTIONS	VARCHAR	Reserved field. Currently not used.

4. External Interface Use Cases

4.1 Send plain text email via external interface

INSERT INTO sqlmessenger_intf
            (subject,-- Email subject
             body,-- Email content
             send_to -- Recipient email addresses
)
VALUES      ('Test Mail',
             'Hi! This is a test mail.',
             'user01@sqlmessenger.com,user01@sqlmessenger.com');

4.2 Send HTML email via external interface

INSERT INTO sqlmessenger_intf
            (subject,-- Email subject
             body,-- Email content
             send_to,-- Recipient email address
             copy_to,-- CC email address
             bcc,-- BCC email address
             body_type -- Body format; use 'HTML' for HTML emails
)
VALUES      ('Test Mail',
'<p>This is a test HTML email. <span style="color:red;">The font color here is red.</span></p>',
'user102@test.com',
'user103@test.com',
'user104@test.com',
'HTML');

HTML Email

4.3 Start task with ID 123 via external interface table

INSERT INTO easysqlmail_intf
            (oper_type,-- Operation type; use 'T' to start a task
             task_id -- ID of the task to be started
)
VALUES      ('T',
             123);

4.4 Start task and pass variables via external interface table

INSERT INTO easysqlmail_intf
            (oper_type,-- Operation type; use 'T' to start a task
             task_id,-- ID of the task to be started
             task_params -- Variables passed to the task (format: key=value;key=value)
)
VALUES      ('T',
             123,
             'UserName=Andy;Month=202401');

4.5 Send messages to Slack workspace members via Slack API

INSERT INTO sqlmessenger_intf
            (oper_type,
             slack_app_name,
             send_to,
             body)
VALUES      ( 'S',-- Operation type; 'S' = Send message to Slack via API
             'SQLMessenger(User Token)',-- Slack app used for sending messages
             'U014J079A4F,info@sqlmessenger.com,#team',-- Recipients (member ID/email/channel); separate with commas
              'This is a test message' -- Message content
);

Tips: Enter member ID, email address or channel name (prefixed with #) in the send_to field.

Note: The system will match input content against cached Slack data to get corresponding member ID. Please update member list timely once workspace information changes. Click here for details.

SQLMessenger User Manual