(SQL Server) RSAP Union API - Get Members Status

Demonstrates how to use an OAuth2 access token for the RSAP Union API. Calls the endpoint to get the statuses of all union members.

For more information, see https://app.swaggerhub.com/apis-docs/pderas/RSAP/2.0.1#/Members/getAllUnionMemberStatuses

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
    -- 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 @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- Load the access token previously obtained by this example:  RSAP Union OAuth2
    DECLARE @jToken int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jToken OUT

    EXEC sp_OAMethod @jToken, 'LoadFile', @success OUT, 'qa_data/tokens/rsapToken.json'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load access token JSON.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jToken
        RETURN
      END

    -- Adds the "Authorization: Bearer ACCESS_TOKEN" header.
    EXEC sp_OAMethod @jToken, 'StringOf', @sTmp0 OUT, 'access_token'
    EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0

    -- For authentication, assuming both the client cert and access token are needed???
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/certs_and_keys/union_client_certificate.crt'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jToken
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

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

    EXEC sp_OAMethod @privKey, 'LoadAnyFormatFile', @success OUT, 'qa_data/certs_and_keys/union_client_certificate.nopass.key', ''
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jToken
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    -- Associate the private key with the cert.
    -- This will fail if the private key is not actually the correct one that corresponds to the public key stored within the cert.
    EXEC sp_OAMethod @cert, 'SetPrivateKey', @success OUT, @privKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jToken
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    -- Tell HTTP to use the cert for client TLS certificate authentication.
    EXEC sp_OAMethod @http, 'SetSslClientCert', @success OUT, @cert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jToken
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

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

    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://api-test.rsap.ca/members/status', @sbResponseBody
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jToken
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

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

    EXEC sp_OAMethod @jResp, 'LoadSb', @success OUT, @sbResponseBody
    EXEC sp_OASetProperty @jResp, 'EmitCompact', 0


    PRINT 'Response Body:'
    EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @http, 'LastStatus', @respStatusCode OUT

    PRINT 'Response Status Code = ' + @respStatusCode
    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @http, 'LastHeader', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jToken
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @sbResponseBody
        EXEC @hr = sp_OADestroy @jResp
        RETURN
      END

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @jToken
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @privKey
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jResp


END
GO