Using HTTP Command in Tasks

 

1 What is HTTP Command

The HTTP command allows you to call API interfaces on remote servers via HTTP (or HTTPS) protocols within tasks. For example, you can use the HTTP command to invoke various APIs provided by Google or Microsoft and store the results in your database.

HTTP Command config dialog

2 HTTP Command Configuration

2.1 Creating an HTTP Command

Step1: In the "Task Commands" tab of the task configuration dialog, click "New Command" and select the "Create an HTTP command" option. Click "OK".

Step2: In the command configuration dialog:

Enter the command name.

Select the Execution Environment and Timing for the command.

If connecting to a remote HTTP host via an HTTP proxy, choose the proxy server to use.

Using Task Commands

2.2 Configuring HTTP Request

You can configure the HTTP request details in the "SQL for HTTP Request" tab. The configuration includes:

URL: The HTTP request URL.

Headers: HTTP headers.

Request Method: HTTP Method (GET or POST).

Content: Request payload.

SQLMessenger uses SQL statements to construct the parameters for the HTTP request. You can flexibly combine the HTTP request parameters using SQL queries. The SQL statement used to construct the HTTP request must return a field named "_http_request_". SQLMessenger will use the value of this field as the HTTP request parameter.

SQLMessenger takes the value of the "_http_request_" field from the first row of the result set returned by the SQL statement as the content of the HTTP request.

The value of "_http_request_" should be a multi-line text, where each line starts with the type of the line, followed by a field separator ":" and then the data of the line. For example, the following SQL statement constructs an HTTP request to call Slack's Web API to obtain a Slack file upload URL.

SELECT '
url : https://slack.com/api/files.getUploadURLExternal
method : get
header : Authorization=Bearer xxx
field : filename=report.xlsx
field : length=188621
' AS _http_request_ ;

The syntax of _http_request_

Line Type	Description	Required	Syntax	Example
url	Sets the HTTP request URL.	Yes	url   :   https://xxx or http://xxx	url   :   https://slack.com/api/files.getUploadURLExternal
method	The method for setting the HTTP request. Only POST and GET methods are supported. If this type is not specified, the HTTP request will default to using the GET method.	No	method   :   get or post	method   :   post
header	Set the header options for the HTTP request. The header type can appear multiple times in a request, with one per line. The syntax after the type separator is as follows: Header Name   =   Header Value For example: Content-Type   =   application/x-www-form-urlencoded;charset:utf-8	No	header   :   HeaderName=HeaderValue	header   :   Content-Type=application/x-www-form-urlencoded header   :   Sec-Ch-Ua-Platform=Windows header   :   XToken=
field	Set the content of the data fields to be submitted via the HTTP request. The field type can appear multiple times in a request, with one per line. The syntax after the type separator is as follows: Field Name   =   Field Value For example: UserName   =   Andy Field values are typically single-line text. If the field value contains line breaks, enclose the value in double quotes ("), for example: UserName   =   "Andy Jack Kerr" If the field value contains both line breaks and double quotes, in addition to enclosing the value in double quotes, you must also use the database's replace method to replace double quotes with two consecutive double quotes before generating the HTTP request content in the SQL statement. The resulting string should look like this: UserName   =   " ""Andy"" ""Jack"" ""Kerr"" "	No	field   :   FieldName=Value	field   :   UserName=Andy field   :   age=35 field   :   city=New York
file	Set the file information to be submitted via the HTTP request. The file type can appear multiple times in a request, with one per line. This type can only be used in HTTP requests that are submitted using the "POST" method. The syntax after the type separator is as follows: Field Name   =   Field Value(which can be a file name or a FILE_UUID). For example: report_file_name   =   d:\temp\report.xlsx Note: When the command is executed, the corresponding file will be uploaded to the HTTP server via the URL you have set. The Content-Type of the file is "multipart/form-data".	No	file   :   FieldName=FileName or FILE_UUID	file   :   report_file_name=d:\temp\report.xlsx

Submit Request Data via "_body_" Field

Some Web APIs require the caller to submit a JSON or XML document as a parameter. When calling these Web APIs, you can use a separate field named "_body_" in the SQL to store the data to be submitted. For example, the following SQL constructs an HTTP request to call the Slack Web API to send a file message to a specified channel:

SELECT '
url:https://slack.com/api/files.completeUploadExternal
header:Content-type=application/json; charset=utf-8
header:Authorization=Bearer XXXXXXX
method:post
' AS _http_request_,
'
{
"channel_id":"C014HV8XXXX",
"files":[{"id":"F018923111", "title":"Sales Report(Nov 2024)"}]
}
' AS _body_

In this example, the value of the "_body_" field will be sent as a parameter in the POST request to the Web API.

Note:

The "_body_" field can only be used with the POST method; it is not valid for GET methods, and the HTTP command will fail.

When using the "_body_" field to store the data to be submitted, the "_http_request_" field should not contain "field" and "file" lines.

2.3 Processing HTTP Call Results

You can configure SQL statements in the "SQL for HTTP Response Processing" tab to handle the results of the HTTP command, such as parsing and processing the data returned by the Web API.

Config "SQL for HTTP Response Processing"

When processing the HTTP call results, you should first check if the HTTP return code is valid. For example, you need to use the following SQL statement to check if the HTTP return code is 200; if it is not, the task should be aborted:

SELECT 'Error. HTTP code:' || #[@@HTTPResponseCode]# AS _sqlmessenger_error_
WHERE #[@@HTTPResponseCode]#::int4 <> 200

For specific HTTP codes that require aborting the task, refer to the official documentation of the Web API you are calling.

In the "SQL for HTTP Response Processing" section, you can use the following system variables to retrieve the content returned after the HTTP call:

@@HTTPResponseBody	Retrieves the body content returned after the HTTP call, such as JSON data, XML data, text, HTML code, etc. If the HTTP command downloads a file, this variable will return the file's FILE_UUID.
@@GetHTTPResponseHeader(HeaderName)	Retrieves the value of the HTTP response header returned by the server after the last execution of the HTTP command. The parameter is the name of the header to retrieve, such as "Content-Type". If no HeaderName is specified, SQLMessenger will return a JSON object containing all HTTP response headers, formatted as follows: { "name":"header name xxx", "value":"header value xxx" }
@@HTTPCommandInstId	Retrieves the ID of the command instance generated by the last execution of the HTTP command. The command instance ID is a long integer.
@@HTTPResponseCode	Retrieves the HTTP response code returned by the server after the last execution of the HTTP command. The HTTP response code is an integer, such as 200 or 302.

In the "SQL for HTTP Response Processing" section, you can:

Trigger an Exception and Abort the Task:If you encounter an error while processing data, you can use the "_sqlmessenger_error_" field in the SQL statement to trigger an exception and abort the task. For example, the following statement aborts the task and logs the error message from the "error" field in the task log if the "ok" field in the JSON data from the HTTP call equals "false":

SELECT data->>'error' AS _sqlmessenger_error_
FROM (
SELECT #[@@HTTPResponseBody]#::jsonb AS data
) a WHERE data->>'ok' = 'false';

Construct and Initiate a New HTTP Request: If you want to initiate a new HTTP request in the "SQL for HTTP Response Processing" section, you can construct the HTTP request according to the rules outlined in Section 2.2 Configuring HTTP Request. SQLMessenger will initiate a new HTTP call as specified, and once the call is completed, it will execute the statements in "SQL for HTTP Response Processing" again to process the results. If the last SQL statement does not contain the "_http_request_" field, or if it contains the "_http_request_" field but returns no records, SQLMessenger will terminate the HTTP command and proceed to execute other task commands.

Example: Using HTTP command to call Slack's Web API to Read Slack Conversation List.

In this example:

If the "ok" field in the JSON data equals "false", the task will be aborted.

If the "next_cursor" field in the JSON data is not empty, the next page is retrieved until the "next_cursor" field is empty.

-- If the "ok" field in the JSON data equals "false", abort the task and log the error message
SELECT data->>'error' AS _sqlmessenger_error_
FROM (SELECT #[@@HTTPResponseBody]#::jsonb AS data) a
WHERE data->>'ok' = 'false';

-- Data processing statement, saving the retrieved channel data into the t_slack_channels table
INSERT INTO t_slack_channels(task_inst_id,channel_data)
SELECT #[@@TaskInstId]#::int8,channel
FROM (
SELECT jsonb_array_elements(result->'channels') AS channel FROM (
SELECT #[@@HTTPResponseBody]#::jsonb AS result
) a) a;

-- If the next_cursor is not empty, construct a new HTTP request to continue reading the next page until next_cursor is empty
SELECT
'
url:https://slack.com/api/conversations.list?limit=100&cursor=' || (result->'response_metadata'->>'next_cursor')::text || '&types=public_channel,private_channel,mpim,im
header:Authorization=Bearer XXX
header:Content-type=application/x-www-form-urlencoded
' AS _http_request_
FROM (SELECT #[@@HTTPResponseBody]#::jsonb AS result) a WHERE result->'response_metadata'->>'next_cursor' <> '';

2.4 System Variables Related to HTTP Commands

To protect your confidential information (such as your Microsoft Access Token), SQLMessenger provides methods to retrieve encrypted variable values for HTTP commands. These variable values are displayed as "****" in the task logs.

@@GetGlobalVarForHTTPCmd(VarName)	Retrieves the value of an encrypted variable from the Global Variable List. This method can only be used in the SQL statements of an HTTP command. The variable value is displayed as "****" in the task logs.
@@GetEncryptedTempVarForHTTPCmd(VarName)	Retrieves the value of an encrypted variable from the encrypted variable table generated using @@PutEncryptedTempVar method. This method can only be used in the SQL statements of an HTTP command. The variable value is displayed as "****" in the task logs.

Additionally, in HTTP commands, you can also use other system variables provided by SQLMessenger, as well as task variables or query parameters, to construct HTTP requests. The values of these variables will be displayed in plain text in the task logs.

3 Using "Default DataSource" to Handle JSON Data

SQLMessenger uses PostgreSQLto store data. PostgreSQL provides powerful JSON data processing capabilities, which you can access through SQLMessenger's "Default DataSource" (PostgreSQL database).

For example, using the following statements, you can evaluate the results of a Slack Web API call, expand the returned JSON array data, and insert it into another table:

-- If the "ok" field in the JSON data equals "false", abort the task and log the error message
SELECT data->>'error' AS _sqlmessenger_error_
FROM (SELECT #[@@HTTPResponseBody]#::jsonb AS data) a
WHERE data->>'ok' = 'false';

-- Data processing statement, saving the retrieved channel data into t_slack_channels
INSERT INTO t_slack_channels(channel_data)
SELECT channel
FROM (
SELECT jsonb_array_elements(result->'channels') AS channel FROM (
SELECT #[@@HTTPResponseBody]#::jsonb AS result
) a) a;

Using the "Default DataSource" to handle JSON data

For detailed information about PostgreSQL's JSON data processing capabilities, please visit https://www.postgresql.org/docs/14/functions-json.html。

4 Considerations for Using HTTP Commands

(1) Be cautious when using the "Execute the Task Commands of the task and verify the preconditions during the preview process" option during task preview. If your HTTP command calls certain Web APIs to send messages to employees (e.g., Slack's "chat.postMessage" API) or upload files to an HTTP server, selecting the "Execute the Task Commands of the task and verify the preconditions during the preview process" option during task preview may cause those messages to be sent to the HTTP server via the Web API you are calling.

(2) HTTP commands are not affected by the "Do not send emails and messages generated by this task instance" option. If your HTTP command calls certain Web APIs to send messages to employees (e.g., Slack's "chat.postMessage" API) or upload files to an HTTP server, when manually starting these tasks, even if the "Do not send emails and messages generated by this task instance" option is selected, these messages may still be sent to the HTTP server via the Web API you are calling.

Using Task Command, Using Variables in Tasks