(SQL Server) Refinitiv World-Check One - Get Top Level Groups

See more Refinitiv Examples

Sends a signed GET request to get the top level groups.

Note: This example requires Chilkat v9.5.0.89 or later.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- Create the following JSON:
    -- 
    -- {
    --   "keyId": "aaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee",
    --   "hmacKey": "xxxxzI3vi58xxxCBxxx1+P/d8tGxx7KuLqN/KMPNxxxxekhj8/bx83+1YQSUxxxxYyv939ceY06GvhYRKmxxxx==",
    --   "algorithm": "hmac-sha256",
    --   "headers": [
    --     "(request-target)",
    --     "host",
    --     "date"
    --   ]
    -- }

    -- Substitute your actual API key and API secret in place of "api-key" and "api-secret"
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'keyId', 'api-key'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'hmacKey', 'api-secret'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'algorithm', 'hmac-sha256'
    -- Indicate the names of the headers to be included in the signature.
    -- "(request-target)" is not actually a header name, but is a special name for HTTP signatures.
    -- Copy the following three lines of code exactly as-is.  
    -- Do not replace "host", "date", or "(request-target)" with values.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'headers[0]', '(request-target)'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'headers[1]', 'host'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'headers[2]', 'date'

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    -- Setting the AuthSignature property causes the following header to be computed and added:
    -- Authorization: Signature keyId="...", algorithm="hmac-sha256", headers="(request-target) host date", signature="..."
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    EXEC sp_OASetProperty @http, 'AuthSignature', @sTmp0

    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Cache-Control', 'no-cache'

    -- Chilkat will auto-add the Date header because it's needed by the HTTP Signature.

    DECLARE @responseJson nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @responseJson OUT, 'https://api-worldcheck.refinitiv.com/v2/groups'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @http
        RETURN
      END


    PRINT @responseJson

    PRINT '----'
    -- A 200 status code indicates success.

    EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
    PRINT 'Status code = ' + @iTmp0

    -- Successful JSON looks like this:
    -- [ 
    --   {
    --   "id": "...",
    --   "name": "Company Name",
    --   "parentId": null,
    --   "hasChildren": false,
    --   "status": "ACTIVE",
    --   "children": []
    --   }
    -- ]

    -- Use this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    DECLARE @jarr int
    -- Use "Chilkat_9_5_0.JsonArray" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @jarr OUT

    EXEC sp_OAMethod @jarr, 'Load', @success OUT, @responseJson

    DECLARE @jsonObj int

    DECLARE @id nvarchar(4000)

    DECLARE @name nvarchar(4000)

    DECLARE @parentId nvarchar(4000)

    DECLARE @hasChildren int

    DECLARE @status nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAGetProperty @jarr, 'Size', @count_i OUT
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OAMethod @jarr, 'ObjectAt', @jsonObj OUT, @i
        EXEC sp_OAMethod @jsonObj, 'StringOf', @id OUT, 'id'
        EXEC sp_OAMethod @jsonObj, 'StringOf', @name OUT, 'name'
        EXEC sp_OAMethod @jsonObj, 'StringOf', @parentId OUT, 'parentId'
        EXEC sp_OAMethod @jsonObj, 'BoolOf', @hasChildren OUT, 'hasChildren'
        EXEC sp_OAMethod @jsonObj, 'StringOf', @status OUT, 'status'
        SELECT @j = 0
        EXEC sp_OAMethod @jsonObj, 'SizeOfArray', @count_j OUT, 'children'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jsonObj, 'J', @j
            SELECT @j = @j + 1
          END
        EXEC @hr = sp_OADestroy @jsonObj

        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @jarr


END
GO