如何通过将HTTP POST与T-SQL和OLE自动化过程结合使用来发送参数 [英] How do I send Parameters by using HTTP POST with T-SQL and OLE Automation Procedures

问题描述

我正在使用OLE自动化过程将HTTP POST请求发送到SOAP-Webservice并处理返回的数据.通过使用下面的代码，这对我来说效果很好.现在，我需要将POST参数传递到Web服务. 知道我该怎么做吗?

I'm using the OLE Automation Procedures to send a HTTP POST Request to a SOAP-Webservice and process the returned data. This works fine for me by using the code snipped below. Now I need to pass a POST-parameter over to the webservice. Any idea how i could do that?

参数:searchstring
值:V34432221

Parameter: searchstring
Value: V34432221

DECLARE @XMLResponse xml
DECLARE @obj INT
DECLARE @ValorDeRegreso INT
DECLARE @sUrl NVARCHAR(200)
DECLARE @response VARCHAR(MAX)
DECLARE @hr INT
DECLARE @src NVARCHAR(255)
DECLARE @desc NVARCHAR(255)
SET @sUrl = 'http://server/Webservice/Service1.asmx/Suche'
EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
EXEC sp_OAMethod @obj, 'Open', NULL, 'POST', @sUrl, false
EXEC sp_OAMethod @obj, 'send'
EXEC sp_OAGetProperty @obj, 'responseText', @XMLResponse OUT
--process @XMLResponse...

推荐答案

EXEC sp_OAMethod @ obj，'send'，NULL，@ StringRequest完美地工作. 这是我的代码，如果有帮助的话:

EXEC sp_OAMethod @obj, 'send', NULL, @StringRequest works perfectly. This is my code, if it can be helpfull :

Declare @Objet as Int
Declare @Reponse as Varchar(8000)
Declare @url VARCHAR(3000)
Declare @Parametres VARCHAR(3000)
Declare @Erreur int
Declare @MessageErreur varchar(255)
Declare @MessageErreurAvecNumero varchar(255)
Declare @Statut int

SET @url = 'http://localhost/MonWebservice/MaPage.asmx/maFonction'
SET @Parametres = 'theIDMachin=' + cast('268029' as varchar)
SET @Parametres = @Parametres + '&theTypeMachin=' + cast('1' as varchar)
SET @Parametres = @Parametres + '&theBooleen=False'
SET @Parametres = @Parametres + '&theStatut=' + cast('0' as varchar)

exec @Erreur = sp_OACreate 'MSXML2.ServerXMLHttp', @Objet OUT;
if @Erreur <> 0 begin set @MessageErreur = 'sp_OACreate MSXML2.ServerXMLHttp.3.0 failed' goto fin end

exec @Erreur = sp_OAMethod @Objet, 'open', NULL, 'POST', @url, false            
if @Erreur <> 0 begin set @MessageErreur = 'sp_OAMethod Open failed' goto fin end

exec @Erreur = sp_OAMethod @Objet, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
if @Erreur <> 0 begin set @MessageErreur = 'sp_OAMethod setRequestHeader failed' goto fin end

exec @Erreur = sp_OAMethod @Objet, 'send', NULL, @Parametres
if @Erreur <> 0 begin set @MessageErreur = 'sp_OAMethod Send failed' goto fin end

exec @Erreur = sp_OAGetProperty @Objet, 'status', @Statut OUT
if @Erreur <> 0 begin set @MessageErreur = 'sp_OAGetProperty read status failed' goto fin end

exec @Erreur = sp_OAMethod @Objet, 'responseText', @Reponse OUTPUT
if @Erreur <> 0 begin set @MessageErreur = 'sp_OAMethod read response failed' goto fin end

select @Reponse

exec sp_OADestroy @Objet
return

fin:
print @MessageErreur
exec sp_OADestroy @Objet
SET @MessageErreurAvecNumero = 'Erreur : ' + cast(@Erreur as varchar) + ' - ' + >@MessageErreur
Raiserror(@MessageErreurAvecNumero, 16, 1)
return

这篇关于如何通过将HTTP POST与T-SQL和OLE自动化过程结合使用来发送参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！