Stavo guardando alcuni esempi su come chiamare un servizio Web da SQL Server. E tutti mostrano codice molto illeggibile, che immagino sarebbe un incubo da mantenere. Quindi, la domanda principale è perché scegliere questo approccio rispetto a un programma standalone che può fare lo stesso.
Solo per esempio uno dei modi per chiamare un servizio web da T-SQL:
CREATE PROCEDURE[dbo].[cm_genericHTTPRequest]
{
@URI varchar(2000) = '',
@methodName varchar(50) = '',
@requestBody varchar(max) = '',
@SoapAction varchar(255),
@UserName nvarchar(100), -- Domain\UserName or UserName
@Password nvarchar(100),
@responseText varchar(8000) output
)
as
begin
--print 'starting the generic call'
IF @methodName = ''
BEGIN
select FailPoint = 'Method Name must be set'
return
END
set @responseText = 'FAILED'
DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255)
--print 'Creating MSXML2.ServerXMLHTTP'
EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'Create failed',
MedthodName = @methodName
goto destroy
return
END
--print 'open the destination URI with Specified method'
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false'--, @UserName, @Password
IF @hResult <> 0
BEGIN
--print 'error openiNg the destination URI with Specified method'
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'Open failed',
MedthodName = @methodName
goto destroy
return
END
--print 'set request headers'
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'application/soap+xml; charset=utf-8'
IF @hResult <> 0
BEGIN
-- print 'error setting request header'
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'SetRequestHeader failed',
MedthodName = @methodName
goto destroy
return
END
---- set soap action
--print 'set soap action '
--EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction
--IF @hResult <> 0
--BEGIN
-- print 'error setting soap action '
-- EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
-- SELECT hResult = convert(varbinary(4), @hResult),
-- source = @source,
-- description = @desc,
-- FailPoint = 'SetRequestHeader failed',
-- MedthodName = @methodName
-- goto destroy
-- return
--END
declare @len int
set @len = len(@requestBody)
--print 'send request body'
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len
IF @hResult <> 0
BEGIN
--print 'sending request body failed'
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'SetRequestHeader failed',
MedthodName = @methodName
goto destroy
return
END
--print 'send the request'
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'Send failed',
MedthodName = @methodName
--print 'error sending the request '
goto destroy
return
END
declare @statusText varchar(1000), @status varchar(1000)
--print 'Get status text'
exec sp_OAGetProperty @objectID, 'StatusText', @statusText out
exec sp_OAGetProperty @objectID, 'Status', @status out
-- select @status, @statusText, @methodName
exec sp_OAGetProperty @objectID, 'responseText', @responseText out
--print convert(varchar(10),@hresult)
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
--print 'error getting response text'
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'ResponseText failed',
MedthodName = @methodName
goto destroy
return
END
destroy:
exec sp_OADestroy @objectID
--print 'destroying'