SQL Server
SQL Server
VoiceBase -- Compound Expression Search
See more VoiceBase Examples
Demonstrates how to do a VoiceBase compound expression search. See VoiceBase Search for more details about Search.Chilkat SQL Server Downloads
-- 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)
DECLARE @success int
SELECT @success = 0
-- 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
DECLARE @req int
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
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbAuth OUT
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 @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpSReq', @success OUT, 'apis.voicebase.com', 443, 1, @req, @resp
IF @success = 0
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
EXEC @hr = sp_OADestroy @resp
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
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
-- See the sample JSON response below..
-- Iterate over the JSON like this:
DECLARE @dt int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt OUT
DECLARE @dtObj int
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @dtObj 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
EXEC sp_OAMethod @dt, 'ToDtObj', NULL, @localTime, @dtObj
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
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 @resp
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @dtObj
END
GO