How can I pass a body from stored procedure to a REST service?

Question

I want to be able to POST a json message to a REST service. I am able to do a GET call by using examples from google-search

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', 
    --Your Web Service Url (invoked)    'false'

Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Select @ResponseText

Exec sp_OADestroy @Object

I have tried different variations of the following:

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255) 
declare @Body as varchar(8000) = 
'{
    "Subsystem": 1,
    "Exception": "",
    "Message": "I have done what you asked",
    "Time": "2014-06-09T11:16:35",
    "Attribute": { "Number of attempts": "0" }
}' 

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'post', 
    'https://thecorrecturl:446/api/handelse/', 'false'
Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'

declare @len int
set @len = len(@body)
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Length', @len
Exec sp_OAMethod @Object, 'setRequestBody', null, 'Body', @body
EXEC sp_OAMethod @Object, 'send', null
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Select @ResponseText
Exec sp_OADestroy @Object

The ResponseText I get back is the omnious "{"Message":"An error has occurred."}"

Can anyone point me in the right direction to do this or just give the correct way to do it ;)

I have used the GET call to my url and got the correct handelse from the service.

Regards Caroline

Beffrey

Did you notice that false all the way on the right? after the "your web service URL" comment. does that trigger the issue? – Parliamentary

If I change from false to true I get a NULL response instead but still the same error on the server side (from the IIS log POST /api/handelse/ - 446 - XXXXXXXXXXX 500 0 0 203) – Beffrey

have you tried running the calls from management studio to see what the error is? just saw your edit - good thing you found the issue – Parliamentary

Answer 1

Caroline's solution:

A colleague of mine stumbled on the solution

Instead of using

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;

we used

Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;

The final result that posted a handelse to our service looks like

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Declare @Body as varchar(8000) = 
'{
    "Subsystem": 1,
    "Exception": "",
    "Message": "I have done what you asked",
    "Time": "2014-06-09T11:16:35",
    "Attribute": { "Number of attempts": "0" }
}'  

Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
EXEC  sp_OAMethod @Object, 'open', NULL, 'post','https://thecorrecturl:446/api/handelse/', '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

Exec sp_OADestroy @Object

Hope this helps someone else

Regards Caroline

Grout

Answer 2

Brian query works fine. I you want to have API key add the following line

declare @apikey as nvarchar(32) = 'your api key';     
..
/*after content-type*/
exec sp_OAMethod @object, 'setRequestHeader', null, 'ApiKey', @apikey
...

Infelicity

Answer 3

Brian's answer is essentially what you asked for, although I would also add some debugging, resulting in something like this:

DECLARE @hResult INT;
DECLARE @source nvarchar(255), @desc nvarchar(255)
....
EXEC @hResult = sp_OAMethod @Object, 'responseText', @return OUTPUT
IF @hResult <> 0
BEGIN
    EXEC sp_OAGetErrorInfo @Object, @source OUT, @desc OUT
    SELECT hResult = convert(varbinary(4), @hResult),
           source = @source,
           description = @desc,
           FailPoint = 'ResponseText failed'
    goto destroy
    return
END

You can do this in each step of sp_OAMethod calls, so you know where it failed if it does.

However, please bear in mind sp_OAMethod 'responseText' has a limitation, it can't process an output larger than varchar(8000)

Also, since you mentioned you are working with JSON, the answer should be processed along these lines:

SELECT * FROM OPENJSON(@return, '$.response') 
WITH (
    Item nvarchar(255) '$.item',
    Description nvarchar(255) '$.description',
    ...
)

With this you should be able to read the JSON response, and have each field as a column that you can later work with in a better manner.

Please note you probably have to set an additional 'Authorization' header for most APIs.

Merlon