Skip to content

ClrHttpRequest-v3

Latest
Latest
Compare
Choose a tag to compare
@EitanBlumin EitanBlumin released this 27 Aug 10:44
· 3 commits to master since this release
v3.0.0
88f87f1
This commit was created on GitHub.com and signed with GitHub’s verified signature. The key has expired.
GPG key ID: 4AEE18F83AFDEB23
Expired
Learn about vigilant mode.

SQL Server CLR function for running REST methods over HTTP.

This project is a fork of the project initially published By Eilert Hjelmeseth, 2018/10/11 here:
http://www.sqlservercentral.com/articles/SQLCLR/177834/

Eilert's GitHub project: https://github.com/eilerth/sqlclr-http-request

My version extends the project by adding the following:

  • Usage of TLS1.2 security protocol (nowadays a global standard).
  • Two new authentication methods:
    • Authorization-Basic-Credentials (Basic authorization using Base64 credentials)
    • Authorization-Network-Credentials (creates a new NetworkCredential object and assigns it to the Credentials property of the request)
  • Addition of a proper PreDeployment script which takes care of CLR assembly signing without requiring the TRUSTWORTHY database setting.
  • Added UTF8 encoding support instead of ASCII.
  • Added support for case-insensitive headers.

These changes allow the SQL Server function to work with advanced services such as Zendesk.
For example:

-- Credentials info: Username (email address) must be followed by /token when using API key
DECLARE @credentials NVARCHAR(4000) = 'agent@company_domain.com/token:api_token_key_here'
DECLARE @headers NVARCHAR(4000) = '<Headers><Header Name="Content-Type">application/json</Header><Header Name="Authorization-Basic-Credentials">' + @credentials + '</Header></Headers>'

-- Global Zendesk Settings:
DECLARE @zendesk_address NVARCHAR(400) = 'https://your_subdomain_here.zendesk.com'

-- Look for existing tickets based on @RequesterEmail:
SET @uri = @zendesk_address + '/api/v2/search.json?query=type:ticket status<solved requester:' + @RequesterEmail

DECLARE @tickets NVARCHAR(MAX), @ticket NVARCHAR(MAX)

-- This is where the magic happens:
SET @tickets = [dbo].[clr_http_request]
        (
            'GET',
            @uri,
            NULL,
            @headers,
            300000,
            0,
            0
        ).value('/Response[1]/Body[1]', 'NVARCHAR(MAX)')

-- check if ticket exists based on @Subject:
SELECT @ticket = [value]
FROM OPENJSON(@tickets, '$.results')
WHERE JSON_VALUE([value], '$.subject') = @Subject
AND JSON_VALUE([value], '$.status') IN ('new', 'open', 'pending')

SELECT uri = JSON_VALUE(@ticket, '$.url'), submitter = JSON_VALUE(@ticket, '$.submitter_id')

For more use cases visit here: https://github.com/EitanBlumin/ClrHttpRequest/blob/master/UseCases.md

For more info on using the Zendesk API, visit here: https://developer.zendesk.com/rest_api/docs/core/introduction

Assets 6
Loading