How to send parameters using HTTP POST with T-SQL and OLE automation procedures

Question

I use OLE automation routines to send an HTTP POST request to a SOAP-Webservice and process the returned data. This works fine for me using the code below. Now I need to pass the POST parameter to the web service. Any idea how I could do this?

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

Answer 1

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

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

Answer 2

Maybe this works:

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)
DECLARE @StringRequest NVARCHAR(500)
SET @StringRequest = 'searchstring=V34432221'
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, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC sp_OAMethod @obj, 'send', NULL, @StringRequest
EXEC sp_OAGetProperty @obj, 'responseText', @XMLResponse OUT
--process @XMLResponse...