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

• mrpolecat

  SSCertifiable

  Points: 7014

  More actions

  September 24, 2016 at 5:32 pm

  #1902986

  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

  September 25, 2016 at 2:47 pm

  #1903001

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