Deep End with JSON and HTTP

Question

Deep End with JSON and HTTP

charles 50750

SSC Veteran

Points: 202
More actions
September 23, 2016 at 6:33 pm

#400770

So, I am a dabbler, and have been asked to help out at work.
We have an API that delivers a JSON file. I need to be able to populate a table in SQL with the JSON data through a sp if possible
The URL is http://servername/api/rest/22,23,24?date=2016-09-23
and the output is
[{"agentId":22,"firstName":"AGENT1","lastName":"AGNET","scheduleAdherence":98.0,"scheduleConformity":100.0},{"agentId":23,"firstName":"AGENT2","lastName":"AGNET2","scheduleAdherence":98.0,"scheduleConformity":99.0},{"agentId":24,"firstName":"AGENT3","lastName":"AGNET3","scheduleAdherence":97.0,"scheduleConformity":102.0}]
I have tried the following but my response is always blank
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Declare @Body as varchar(8000);
Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'post','http://servername/api/rest/22,23,24?date=2016-09-23', 'false'
Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
Exec sp_OAMethod @Object, 'send', null, @body
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Select @ResponseText [response]
Exec sp_OADestroy @Object

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

You are setting the method to post but appears you should be doing a get. POST is should only used when you are passing a payload back to the API to do an insert. GET for request and PUT to update.

charles 50750 SSC Veteran Points: 202 More actions · Answer 2

Thanks, that done it for me with another TEST url ,I must have something wrong with my API or JSON format