To facilitate your invocation of SQLMessenger's information push functionality in your database, SQLMessenger provides the "External Interface" feature. After you enable the "External Interface Function" on the data source, SQLMessenger will create a table named "sqlmessenger_intf" on the data source. You can invoke SQLMessenger's email or Slack message sending function by inserting data into this interface table. Alternatively, you can also start a configured task in SQLMessenger via this interface.
Enable the "External Interface" feature on the data source
Allow sending emails from the interface table on this data source |
If this option is selected, you can send emails through the interface table on this data source. The SQL statements will look like the following:
INSERT INTO sqlmessenger_intf
|
Allow triggering tasks from the interface table on this data source |
If this option is selected, you can start tasks in SQLMessenger through the interface table on this data source. The SQL statements will look like the following:
INSERT INTO sqlmessenger_intf
|
| Select Tasks | If the "Allow triggering tasks from the interface table on this data source" is selected, you can select which tasks can be started from the interface table on this data source. |
| Allow sending messages to Slack from the interface table in this data source |
If this option is selected, you can start tasks in SQLMessenger through the interface table on this data source. The SQL statements will look like the following:
INSERT INTO sqlmessenger_intf
Tips: In the send_to field, you can enter the member's Member ID, or member's email address, or a channel name (preceded by a "#" for channel names). Note: If you enter an email address or a channel name, SQLMessenger will look up the email address or channel name in the locally cached Slack member list to obtain their Member ID. Therefore, when there are changes to the workspace member information, you need to promptly import the updated member list into the SQLMessenger system. Click here for more details. |
| Select the apps to send message to Slack | Specify which Slack apps this data source can use to send messages. It is allowed by default to use any Slack App or Webhook in the system. |
| Scan the interface table every xx second(s) | Set the interval in seconds for reading new requests from the interface tables. The default is to read every 60 seconds. |
2 The Workflow of The External Interface
The workflow of the external interface function of SQLMessenger is as follows:
1) If the external interface function is enabled, the system will periodically read unprocessed requests (with the state field value of "A") from the interface table (sqlmessenger_intf) and update the "state" field of the requests to "D" (processing).
2) The system performs corresponding actions based on the type of interface request (determined by the "oper_type" field), such as sending emails or initiating a task.
3) After the system completes processing, it writes back the processing results to the interface table, updating the "state" field to "C" (processed successfully), "E" (processing error), or "F" (processing failure). After a certain period, the system moves processed records to the archive table (sqlmessenger_intf_h).
Tips: If the processing status of a request is "E" (processing error), the system will retry processing later. If the processing status is "F" (processing failure), no further retry attempts will be made.
The workflow of the External Interface
If the "External Interface" feature is enabled, the system will create the following interface tables on the corresponding data source:
sqlmessenger_intf |
Used to store user's requests. |
sqlmessenger_intf_h |
Used to store the processed records. Once a request in sqlmessenger_intf is completed, the system will archive the processed request record into the sqlmessenger_intf_h table. |
Tips: In Oracle database, SQLMessenger will also create a sequence named "seq_sqlmessenger_intf_id" on the data source. Additionally, an INSERT trigger will be created on the SQLMESSENGER_INTF table to generate a new record's REC_ID upon record insertion.
The structure of SQLMESSENGER_INTF:
REC_ID |
BIGINT |
The unique ID of the record. It is the primary key. In an Oracle database, the trigger insert_sqlmessenger_intf_id fetches the value from seq_sqlmessenger_intf_id. Other databases use an auto-increment field for automatic numbering. |
OPER_TYPE |
CHAR(1) |
The type of operation to be performed. M: Send an email. T: Start a specified task. Default is M (send an email). |
TASK_ID |
INT |
If OPER_TYPE is "T"(start a specified task), the task ID to be started needs to be specified here. The task ID can be found in the Task Manager.
Find the Task ID on the Task Manager's task list Tips: Before starting a task through the external interface table, you need to add the task to the "Tasks allowed to be started from this data source" list in the "External Interface" configuration of the data source.
Add tasks to the "Tasks allowed to be started from this data source" list |
SUBJECT |
VARCHAR(500) |
If OPER_TYPE is "M" (to send an email), you need to specify the subject of the email to be sent here. |
BODY |
VARCHAR |
If OPER_TYPE is "M" (to send an email), specify the body content of the email here. The maximum length is 2000 for Informix and 4000 for other databases. |
BODY_TYPE |
CHAR(4) |
If OPER_TYPE="M" (send an email), specify the type of the email body here. It can be "HTML" (where the content in the BODY is treated as HTML code) or "TEXT" (where the content in the BODY is treated as plain text). The default is "TEXT". |
SEND_TO |
VARCHAR |
If OPER_TYPE is "M" (send an email), specify the recipients of the email here, separated by commas for multiple recipients. For example: user01@sqlmessenger.com,user02@sqlmessenger.com or "User1<user01@sqlmessenger.com>,User2<user02@sqlmessenger.com>" If OPER_TYPE is "S" (send a message via Slack API), specify the recipients of the message here, separated by commas for multiple recipients. For example: U014J079A4F,info@sqlmessenger.com,#team |
COPY_TO |
VARCHAR |
If OPER_TYPE is "M" (send an email), you can specify the recipients of the email in the CC field here, separated by commas for multiple recipients. For example: user01@sqlmessenger.com,user02@sqlmessenger.com or "User1<user01@sqlmessenger.com>,User2<user02@sqlmessenger.com>" |
TASK_PARAMS |
VARCHAR |
If OPER_TYPE is "T" (start a specified task), you can set the variable values to be passed to the task here. The format is "parameter_name=parameter_value;", separated by ";" for multiple parameters. The maximum length is 2000 for Informix and 4000 for other databases. Tips: The task needs to have the option "Allow passing variable values from external interface table" selected in the task configuration for it to accept the value of this variable from the external interface. Otherwise, an error of "Not allowed to pass this variable from the external interface" will occur during task execution.
Allow passing variable values from external interface table |
EXP_TIME |
DATETIME |
Specify the expiration time for the request record here. If no expiration time is specified, the default expiration is 24 hours. Request records that expire will no longer be processed. |
USE_MAIL_ACCT |
VARCHAR |
If OPER_TYPE is "M", you can specify here which sending account to use for this email, with multiple email accounts separated by commas. If not specified, SQLMessenger will randomly select a sending account to send the email. When specifying the sending email account, you can use the '*' symbol to indicate any account. For example: acct1@test.com,acct2@test.com,* This indicates a preference to use acct1 for sending emails. If sending with acct1 fails, it will attempt to send with acct2. If sending with acct2 also fails, it will use any available account (including acct1 and acct2) for sending. |
SLACK_APP_NAME |
VARCHAR |
If OPER_TYPE is "S", specify which Slack app to use for sending this message. Tips: The Slack App Name must be the name of an App or Webhook already configured in SQLMessenger, case-insensitive. |
CREATE_TIME |
DATETIME |
The creation time of the request record. Automatically filled in by the system. |
STATE |
CHAR(1) |
The status of the request record. A: Unprocessed (default state); D: Taken by SQLMessenger; C: Executed successfully; E: Execution error; F: Execution failed, X: Deleted. Default is A. Tips: If the processing status of a request is "E" (processing error), the system will retry processing later. If the processing status is "F" (processing failure), no further retry attempts will be made. |
STATE_TIME |
DATETIME |
The state time of the request record. Automatically filled in by the system. |
MAIL_ID |
BIGINT |
The email ID generated by SQLMessenger for the request. SQLMessenger fills this field after the request has been successfully processed. |
TASK_INST_ID |
BIGINT |
If OPER_TYPE is "T" (start a specified task), SQLMessenger will fill this field with the generated task instance ID after processing is complete. |
RESULT_MESSAGE |
VARCHAR |
Description of the processing result. Filled in by SQLMessenger. |
SYSTEM_ID |
VARCHAR |
The SQLMessenger system ID that processed this request, filled in by SQLMessenger. |
DATA_SOURCE_ID |
INT |
The data source ID that processed this request, filled in by SQLMessenger. |
OPTIONS |
VARCAHR |
(This field is not used) |
4 External Interface Usage Cases
1) Sending text email through the external interface:
INSERT INTO sqlmessenger_intf
(subject,--The subject of the email
body,--The body of the email
send_to --Recipient's email address
)
VALUES ('Test Mail',
'Hi! This is a test mail.',
'user01@sqlmessenger.com,user01@sqlmessenger.com');
2) Sending HTML email through the external interface:
INSERT INTO sqlmessenger_intf
(subject,--The subject of the email
body,--The body of the email
send_to,--Recipient's email address
body_type --The format type of the body. Fill in 'HTML' when sending HTML emails
)
VALUES ('Test Mail',
'Hi! This is a test mail. <span style="color:red;">The content here is displayed in red font</span>',
'user01@sqlmessenger.com',
'HTML');
HTML email
3) Start a task with a task ID of 123 through an external interface table:
INSERT INTO easysqlmail_intf
(oper_type,--Operation type. Fill in "T" when starting a task
task_id --Task ID to start
)
VALUES ('T',
123);
4) Start a task and pass in variables through the external interface table:
INSERT INTO easysqlmail_intf
(oper_type,--Operation type. Fill in "T" when starting a task
task_id,--Task ID to start
task_params --List of variables to pass to the task
)
VALUES ('T',
123,
'UserName=Andy;Month=202401');
5) Send a message to Slack workspace members via the Slack API
INSERT INTO sqlmessenger_intf
(oper_type,
slack_app_name,
send_to,
body)
VALUES ( 'S',--Operation type. "S" indicates to send a message to Slack workspace members via Slack API.
'SQLMessenger(User Token)',--Specify which Slack app to use for sending this message.
'U014J079A4F,info@sqlmessenger.com,#team',--The Slack workspace members who will receive this message. Separate multiple members with a comma (",").
'This is a test message' --The content of the message.
);
Tips: In the send_to field, you can enter the member's Member ID, or member's email address, or a channel name (preceded by a "#" for channel names).
Note: If you enter an email address or a channel name, SQLMessenger will look up the email address or channel name in the locally cached Slack member list to obtain their Member ID. Therefore, when there are changes to the workspace member information, you need to promptly import the updated member list into the SQLMessenger system. Click here for more details.