如何将存储过程中的主体传递给REST服务？

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

我希望能够将json消息发送到REST服务。我可以使用google-search中的示例进行GET调用

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."}"

我得到的ResponseText是有意义的“{”消息“:”发生了错误。“}”

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.

我已经使用GET调用我的网址并从服务中获得了正确的handelse。

Regards Caroline

1 个解决方案

#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

向我们的服务发布handelse的最终结果如下

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