External Web API Invokation

chm.HttpInvokeJson (UDF)

This UDF is used to invoke any external API. It receives an ApiRequest object in JSON format (as string), invokes the external API and returns the result as an ApiResponse object in JSON format (as string).

      CREATE FUNCTION [chm].[HttpInvokeJson](@request NVARCHAR (MAX) NULL)
RETURNS NVARCHAR(MAX)
    

Example:

      declare @request nvarchar(max) = N'
{
  "url": "https://reqres.in/api/users/2",
  "method": "GET"
}
'
declare @response nvarchar(max)


set @response = chm.HttpInvokeJson(@request)

print @response
    

The example prints a result like the following output:

      { "Text": "{\"data\":{\"id\":2,\"email\":\"janet.weaver@reqres.in\",\"first_name\":\"Janet\",\"last_name\":\"Weaver\",\"avatar\":\"https://reqres.in/img/faces/2-image.jpg\"},\"support\":{\"url\":\"https://reqres.in/#support-heading\",\"text\":\"To keep ReqRes free, contributions towards server costs are appreciated!\"}}", "StatusCode": "OK", "StatusDescription": "OK", "Headers": {"Connection": "keep-alive", "Access-Control-Allow-Origin": "*", "CF-Cache-Status": "HIT", "Age": "5519", "Expect-CT": "max-age=604800, report-uri=\"https://report-uri.cloudflare.com/cdn-cgi/beacon/expect-ct\"", "Report-To": "{\"endpoints\":[{\"url\":\"https:\\/\\/a.nel.cloudflare.com\\/report\\/v3?s=jvDjse%2Fj1iNnUd42PdX8uBzk%2BL3Hier66XoKkfWzDIBn%2Bo5ksGADTI0UsxPrhooDSfBJOHKKkNEFNVwBlhZmo9knR0SS0hTcFVAJ3q%2Fd3FSkXCesBNQTU664Sw%3D%3D\"}],\"group\":\"cf-nel\",\"max_age\":604800}", "NEL": "{\"success_fraction\":0,\"report_to\":\"cf-nel\",\"max_age\":604800}", "CF-RAY": "71b51e1498b6904e-FRA", "Accept-Ranges": "bytes", "Content-Length": "280", "Cache-Control": "max-age=14400", "Content-Type": "application/json; charset=utf-8", "Date": "Tue, 14 Jun 2022 18:26:59 GMT", "ETag": "W/\"118-pbdwwFo9SKNhD3Lx5iHJyngpq00\"", "Server": "cloudflare", "Via": "1.1 vegur", "X-Powered-By": "Express"}, "ContentType": "application/json; charset=utf-8", "ContentEncoding": "", "ContentLength": 280, "CharacterSet": "utf-8", "Method": "GET", "LastModified": "2022-06-14T22:56:59Z", "ProtocolVersion": { "Major": 1, "Minor": 1, "Build": -1, "Revision": -1, "MajorRevision": -1, "MinorRevision": -1 }, "Server": "cloudflare", "SupportsHeaders": true, "IsFromCache": false, "ResponseUri": "https://reqres.in/api/users/2", "IsMutuallyAuthenticated": false }
    

ApiRequest and ApiResponse and their properties are described here.

chm.HttpInvoke (SPROC)

This sproc can be used to invoke any external API. It works like HttpInvokeJson. It just gets details of request as separate parameters and returns the result as a record.

      CREATE PROCEDURE [chm].[HttpInvoke]
(
    @url            NVARCHAR(500),
    @method         NVARCHAR(20),
    @contentType    NVARCHAR(200),
    @form           NVARCHAR(MAX),
    @querystring    NVARCHAR(2000),
    @data           NVARCHAR(MAX),
    @body           VARBINARY(MAX),
    @headers        NVARCHAR(MAX),
    @cookies        NVARCHAR(MAX),
    @files          NVARCHAR(MAX),
    @options        NVARCHAR(MAX)
)
    

Parameters:

• @url: External API's URL
• @method: Http method used to invoke API.
• @contentType: Content-Type header value.
• @form: List of form values to be sent in non-GET request in Http/Form or JSON format

Example:

      name=John%20Doe&city=New%20York&age=24

{ "name": "John Doe", "city": "New York", "age": 24 }
    

• @querystring: List of querstring values in URL or JSON format.

Example:

      order-by=title&order-dir=asc

{ "order-by": "title", "order-dir": "asc" }
    

• @data: Request body as a string. It is used to send textual body content like JSON.
• @body: Request body as a byte array. It is used to send binary data. It has more precedence over @data. If used, @data will be ignored.
• @headers: Request headers in key:value pair separated by newline (\n) characters or in JSON format.

Example:

      user-agent: chameleon 1.0
authentication: Bearer ahsdgajshdg

or

{ "user-agent": "chameleon 1.0", "authentication": "Bearer ahsdgajshdg" }
    

• @cookies: Request cookies in JSON format as a lis of ApiCookie objects. e.g. [{"name": "cookie1", "value": "cookie1-value"}, {"name": "cookie2", "value": "cookie2-value"}].
• @files: List of files to be posted to the target external API in JSON format. Each file should have a name and its content should be specified in Base64 format. e.g. { "file1.jpg": "jsdhkajsdhkajsdh==" }
• @options: Custom options to customize Api invocation as a ApiInvokeOptions in JSON format.

Example:

      { "Timeout": 30000 }
    

Example:

      exec chm.HttpInvoke 'https://reqres.in/api/users/2','GET','','','','',null,'','','',''
    

After execution of the above example, the following result will be returned:

chm.HttpGet (SPROC)

This helper SPROC simpilifies invoking external APIs using GET method. Its result is the same format as HttpInvoke SPROC, i.e. a ApiResponse record.

      CREATE PROCEDURE [chm].[HttpGet]
(
    @url        NVARCHAR(500),
    @query      NVARCHAR(MAX),
    @headers    NVARCHAR(MAX),
    @cookies    NVARCHAR(MAX),
    @options    NVARCHAR(MAX)
)
    

Parameters are the same as those in HttpInvoke SPROC.

Example:

      exec chm.HttpGet 'https://reqres.in/api/users/2','','','',''
    

The result is the same as the example given in HttpInvoke SPROC.

chm.HttpPost (SPROC)

This helper SPROC simpilifies invoking external APIs using POST method. Its result is the same format as HttpInvoke SPROC, i.e. a ApiResponse record.

      CREATE PROCEDURE [chm].[HttpPost]
(
    @url        NVARCHAR(500),
    @query      NVARCHAR(MAX),
    @data       NVARCHAR(MAX),
    @headers    NVARCHAR(MAX),
    @cookies    NVARCHAR(MAX),
    @options    NVARCHAR(MAX)
)
    

Parameters are the same as those in HttpInvoke SPROC, with this point that @data parameter is like @form parameter in HttpInvoke SPROC.

Example:

      exec chm.HttpPost 'https://reqres.in/api/users','',N'
{
    "name": "morpheus",
    "job": "leader"
}','',''
    

chm.HttpPut (SPROC)

This helper SPROC simpilifies invoking external APIs using POST method. Its result is the same format as HttpInvoke SPROC, i.e. a ApiResponse record.

      CREATE PROCEDURE [chm].[HttpPost]
(
    @url        NVARCHAR(500),
    @query      NVARCHAR(MAX),
    @data       NVARCHAR(MAX),
    @headers    NVARCHAR(MAX),
    @cookies    NVARCHAR(MAX),
    @options    NVARCHAR(MAX)
)
    

Parameters are the same as those in HttpInvoke SPROC, with this point that @data parameter is like @form parameter in HttpInvoke SPROC.

Example:

      exec chm.HttpPut 'https://reqres.in/api/users/2','',N'
{
    "name": "morpheus",
    "job": "leader"
}','',''
    

chm.HttpPatch (SPROC)

This helper SPROC simpilifies invoking external APIs using POST method. Its result is the same format as HttpInvoke SPROC, i.e. a ApiResponse record.

      CREATE PROCEDURE [chm].[HttpPatch]
(
    @url        NVARCHAR(500),
    @query      NVARCHAR(MAX),
    @data       NVARCHAR(MAX),
    @headers    NVARCHAR(MAX),
    @cookies    NVARCHAR(MAX),
    @options    NVARCHAR(MAX)
)
    

Parameters are the same as those in HttpInvoke SPROC, with this point that @data parameter is like @form parameter in HttpInvoke SPROC.

Example:

      exec chm.HttpPatch 'https://reqres.in/api/users/2','',N'
{
    "name": "morpheus",
    "job": "leader"
}','',''
    

chm.HttpDelete (SPROC)

This helper SPROC simpilifies invoking external APIs using POST method. Its result is the same format as HttpInvoke SPROC, i.e. a ApiResponse record.

      CREATE PROCEDURE [chm].[HttpDelete]
(
    @url        NVARCHAR(500),
    @query      NVARCHAR(MAX),
    @data       NVARCHAR(MAX),
    @headers    NVARCHAR(MAX),
    @cookies    NVARCHAR(MAX),
    @options    NVARCHAR(MAX)
)
    

Parameters are the same as those in HttpInvoke SPROC, with this point that @data parameter is like @form parameter in HttpInvoke SPROC.

Example:

      exec chm.HttpDelete 'https://reqres.in/api/users/2','','','',''