(SQL Server) VoiceBase -- Compound Expression Search

Demonstrates how to do a VoiceBase compound expression search. See VoiceBase Search for more details about Search.

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
    DECLARE @iTmp1 int
    DECLARE @iTmp2 int
    DECLARE @iTmp3 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
    -- 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 @req int
    -- Use "Chilkat_9_5_0.HttpRequest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT

    EXEC sp_OASetProperty @req, 'HttpVerb', 'GET'
    EXEC sp_OASetProperty @req, 'Path', '/v2-beta/media'

    -- Add the access (bearer) token to the request, which is a header
    -- having the following format:
    -- Authorization: Bearer <userAccessToken>
    DECLARE @sbAuth int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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 @req, 'AddHeader', NULL, 'Authorization', @sTmp0

    -- Search for media containing the terms any of the terms "test", "number", or "three"
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'query', '"test" OR "number" OR "three"'

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'SynchronousRequest', @resp OUT, 'apis.voicebase.com', 443, 1, @req
    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 @req
        EXEC @hr = sp_OADestroy @sbAuth
        RETURN
      END

    -- Examine the response status code and body.

    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Response status code = ' + @iTmp0

    -- The response should be JSON, even if an error.
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    -- A successful response will have a status code = 200
    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN

        PRINT 'Failed.'
      END
    ELSE
      BEGIN

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'mediaId'
        PRINT 'mediaId: ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, '_links.self.href'
        PRINT 'href: ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'status'
        PRINT 'status: ' + @sTmp0

        PRINT 'Success.'
      END

    EXEC @hr = sp_OADestroy @resp

    -- See the sample JSON response below..

    -- Iterate over the JSON like this:
    DECLARE @dt int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt OUT

    DECLARE @mediaCount int
    EXEC sp_OAMethod @json, 'SizeOfArray', @mediaCount OUT, 'media'
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @mediaCount
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i



        PRINT '-- ' + @i + ' --'

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'media[i].mediaId'
        PRINT '  mediaId: ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'media[i].status'
        PRINT '  status: ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'media[i].metadata.contentType'
        PRINT '  contentType: ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'media[i].metadata.length.milliseconds'
        PRINT '  milliseconds: ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'media[i].metadata.length.descriptive'
        PRINT '  descriptive: ' + @sTmp0
        DECLARE @dateCreated nvarchar(4000)
        EXEC sp_OAMethod @json, 'StringOf', @dateCreated OUT, 'media[i].dateCreated'
        EXEC sp_OAMethod @dt, 'SetFromTimestamp', @success OUT, @dateCreated

        DECLARE @localTime int
        SELECT @localTime = 1
        DECLARE @dtObj int
        EXEC sp_OAMethod @dt, 'GetDtObj', @dtObj OUT, @localTime

        EXEC sp_OAGetProperty @dtObj, 'Month', @iTmp0 OUT

        EXEC sp_OAGetProperty @dtObj, 'Day', @iTmp1 OUT

        EXEC sp_OAGetProperty @dtObj, 'Hour', @iTmp2 OUT

        EXEC sp_OAGetProperty @dtObj, 'Minute', @iTmp3 OUT
        PRINT '  ' + @iTmp0 + '/' + @iTmp1 + '  ' + @iTmp2 + ':' + @iTmp3
        EXEC @hr = sp_OADestroy @dtObj

        SELECT @i = @i + 1
      END

    PRINT 'Finished.'

    -- A sample JSON response:

    -- { 
    --   "_links": { 
    --     "self": { 
    --       "href": "/v2-beta/media"
    --     }
    --   },
    --   "media": [
    --     { 
    --       "mediaId": "26063536-FFFF-4020-93ba-0878112d834b",
    --       "status": "finished",
    --       "metadata": { 
    --         "contentType": "audio/x-wav",
    --         "length": { 
    --           "milliseconds": 85141,
    --           "descriptive": "85.0 sec"
    --         }
    --       },
    --       "dateCreated": "2017-01-19T16:49:32.000Z"
    --     },
    --     { 
    --       "mediaId": "8163fbbc-FFFF-4794-aa95-045420bb321d",
    --       "status": "finished",
    --       "metadata": { 
    --         "contentType": "audio/x-wav",
    --         "length": { 
    --           "milliseconds": 65342,
    --           "descriptive": "65.0 sec"
    --         }
    --       },
    --       "dateCreated": "2017-01-19T20:08:49.000Z"
    --     },
    -- ...
    -- ...
    --     { 
    --       "mediaId": "b01e27be-FFFF-4b62-8802-6dc66a75c4d3",
    --       "status": "finished",
    --       "metadata": { 
    --         "contentType": "audio/x-wav",
    --         "length": { 
    --           "milliseconds": 11581,
    --           "descriptive": "11.0 sec"
    --         }
    --       },
    --       "dateCreated": "2017-02-06T20:55:43.000Z"
    --     }
    --   ]
    -- }

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @req
    EXEC @hr = sp_OADestroy @sbAuth
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @dt


END
GO