SQL Server

VoiceBase -- Retrieve JSON Transcript

See more VoiceBase Examples

Retrieves a JSON transcript for a media file. SQL Server

-- 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 HTTP API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- Insert your Bearer token here:
    DECLARE @accessToken nvarchar(4000)
    SELECT @accessToken = 'VOICEBASE_TOKEN'

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Add the access (bearer) token to the request, which is a header
    -- having the following format:
    -- Authorization: Bearer <userAccessToken>
    DECLARE @sbAuth int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbAuth OUT

    DECLARE @success int
    EXEC sp_OAMethod @sbAuth, 'Append', @success OUT, 'Bearer '
    EXEC sp_OAMethod @sbAuth, 'Append', @success OUT, @accessToken
    EXEC sp_OAMethod @sbAuth, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Authorization', @sTmp0

    DECLARE @sbUrl int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbUrl OUT

    EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, 'https://apis.voicebase.com/v2-beta/media/$MEDIA_ID/transcripts/latest'
    DECLARE @replaceCount int
    EXEC sp_OAMethod @sbUrl, 'Replace', @replaceCount OUT, '$MEDIA_ID', 'f9b9bb88-d52c-4960-bcef-d516a9f85594'

    DECLARE @strJson nvarchar(4000)
    EXEC sp_OAMethod @sbUrl, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'QuickGetStr', @strJson OUT, @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 @http
        EXEC @hr = sp_OADestroy @sbAuth
        EXEC @hr = sp_OADestroy @sbUrl
        RETURN
      END

    -- The response should be JSON, even if an error.
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'Load', @success OUT, @strJson
    EXEC sp_OASetProperty @json, 'EmitCompact', 0


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

    EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbAuth
        EXEC @hr = sp_OADestroy @sbUrl
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- See the sample JSON response below..

    -- Iterate over the words..
    DECLARE @numWords int
    EXEC sp_OAMethod @json, 'SizeOfArray', @numWords OUT, 'transcripts.latest.words'
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numWords
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'transcripts.latest.words[i].w'
        PRINT @sTmp0
        SELECT @i = @i + 1
      END

    PRINT 'Success.'

    -- A sample JSON response:

    -- { 
    --   "_links": { 
    --     "self": { 
    --       "href": "/v2-beta/media/f9b9bb88-d52c-4960-bcef-d516a9f85594/transcripts/latest"
    --     }
    --   },
    --   "transcripts": { 
    --     "latest": { 
    --       "revision": "b25e81dc-ae3e-4f9d-8008-1d56a283c17f",
    --       "engine": "standard",
    --       "confidence": 2.196210728898151,
    --       "words": [
    --         { 
    --           "p": 0,
    --           "s": 830,
    --           "c": 0.14,
    --           "e": 870,
    --           "w": "You"
    --         },
    --         { 
    --           "p": 1,
    --           "s": 1860,
    --           "c": 0.432,
    --           "e": 1920,
    --           "w": "know"
    --         },
    --         { 
    --           "p": 2,
    --           "s": 1930,
    --           "c": 0.288,
    --           "e": 2250,
    --           "w": "that's"
    --         },
    --         { 
    --           "p": 3,
    --           "s": 2250,
    --           "c": 0.923,
    --           "e": 2300,
    --           "w": "a"
    --         },
    -- ...

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbAuth
    EXEC @hr = sp_OADestroy @sbUrl
    EXEC @hr = sp_OADestroy @json


END
GO