(SQL Server) Adobe Analytics Reporting API (1.4)

Demonstrates a simple POST of JSON to the Adobe Analytics Reporting API (v1.4)

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 requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.
    DECLARE @success int

    -- In this example, replace "rsid" with your report suite id, and update the URL to use the correct endpoint
    DECLARE @url nvarchar(4000)
    SELECT @url = 'https://api.omniture.com/admin/1.4/rest/?method=Report.Queue'

    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

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'reportDescription.reportSuiteID', 'rsid'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'reportDescription.dateGranularity', 'hour'

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

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

    EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT
    DECLARE @timecreated nvarchar(4000)
    EXEC sp_OAMethod @dt, 'GetAsTimestamp', @timecreated OUT, 0

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

    DECLARE @nonce nvarchar(4000)
    EXEC sp_OAMethod @prng, 'GenRandom', @nonce OUT, 12, 'hex'

    DECLARE @secret nvarchar(4000)
    SELECT @secret = 'SECRET'

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

    EXEC sp_OAMethod @sb, 'Append', @success OUT, @nonce
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @timecreated
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @secret

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

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha1'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
    DECLARE @digest nvarchar(4000)
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'HashStringENC', @digest OUT, @sTmp0

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

    EXEC sp_OAMethod @sbNonce, 'Append', @success OUT, @nonce
    EXEC sp_OAMethod @sbNonce, 'Encode', @success OUT, 'base64'

    EXEC sp_OAMethod @sb, 'Clear', NULL
    EXEC sp_OAMethod @sb, 'Append', @success OUT, 'UsernameToken Username="USERNAME", PasswordDigest="'
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @digest
    EXEC sp_OAMethod @sb, 'Append', @success OUT, '", Nonce="'
    EXEC sp_OAMethod @sbNonce, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @sTmp0
    EXEC sp_OAMethod @sb, 'Append', @success OUT, '", Created="'
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @timecreated
    EXEC sp_OAMethod @sb, 'Append', @success OUT, '"'

    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'X-WSSE', @sTmp0

    DECLARE @resp int
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'PostJson2', @resp OUT, @url, 'text/json', @sTmp0
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @sbNonce
        RETURN
      END


    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Http Status code: ' + @iTmp0

    PRINT 'JSON response:'
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @resp


    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @dt
    EXEC @hr = sp_OADestroy @prng
    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @sbNonce


END
GO