We use cookies to enhance your experience while on our website, serve personalized content, provide social media features and to optimize our traffic. By continuing to browse the site you are agreeing to our use of cookies.
Find out more here
.
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!
Hello,
I don't have experience with running such query for MSSQL Server.
But what I can say is that you are not managing/handling authorization properly.
The Platform API expects a Bearer token (Authorization header).
Basic authentication is not supported with Platform API access.
So you'll need to get a token first - in your case, using an OAuth Client Credentials flow (no user involved).
That part indeed leverages basic authentication.
You then need to use the retrieved token in your Platform API requests (sending it as Bearer token in Authorization header).
