Hi Guys! I'm having a hard time trying to set a connection to Genesys API using MSSQL Server. Below is the code:
DECLARE @Object int
DECLARE @ret int
DECLARE @ResponseText varchar(max)
DECLARE @json table(json_Table nvarchar(max))
DECLARE @body varchar(max)
DECLARE @Apilink varchar(max)
DECLARE @AuthHeader nvarchar(max)
DECLARE @contentType nvarchar(64)
DECLARE @clientID nvarchar(100)
DECLARE @clientSecret nvarchar(100)
DECLARE @stringText varchar(500)
DECLARE @encode varchar(500)
set @clientID='XXXXX'
set @clientSecret='XXXXX'
set @stringText = @clientID+':'+@clientSecret
set @encode = (SELECT CAST(@stringText as varbinary(max)) FOR XML PATH(''), BINARY BASE64)
set @authHeader = 'BASIC '+@encode
--set @contentType = 'application/x-www-form-urlencoded'
set @contentType = 'application/json'
set @Apilink = 'https://api.mypurecloud.com/api/v2/workforcemanagement/businessunits/XXXX'
--Exec @ret = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
Exec @ret = sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec @ret = sp_OAMethod @Object,'open', null, 'get',@Apilink,'false'
Exec sp_OAMethod @Object,'setRequestHeader',null,'Authentication',@authHeader
Exec sp_OAMethod @Object,'setRequestHeader',null,'Content-Type',@contentType
Exec sp_OAMethod @Object,'send'--,null, @body
--Exec sp_OAMethod @Object,'responseText',@ResponseText output
Insert into @json(json_Table) Exec sp_OAGetProperty @Object, 'responseText'
select * from @json
I tried to use MSXML2.ServerXMLHTTP.6.0 parameter for the object but I keep receiveing the error: "No authentication bearer token specified in authorization header." But I do not intend to use the auth Bearer method, that's why I used the basic encoding (base64) code.
When I use MSXML2.XMLHTTP parameter I get NULL value for the @json table.
Does anyone know how I fix it? Is there another way of retrieving data and exporting it to a SQL Server table?
Thanks in advance!