[SQL Server] Interroger un service web via Transact SQL Exemple 1

  • Imprimer

-- =============================================

-- Author:        Zouhaier KHARROUBI

-- Create date: 28/07/2017

-- Description:   Interroger les services WEB via Transactsql

-- Exemples de paramètres à passer au script

--Exemple 1 :

--SELECT @Url = 'http://localhost/service.asmx'

--SELECT @HttpMethod = 'soap'

--SELECT @ParametresValeurs = 'login=monlogin&password=monmotdepasse&domain=mektaba.info'

--SELECT @SoapAction = 'Authenticate'

--Exemple 2 :

--SELECT @Url = 'http://www.webservicex.com/stockquote.asmx/GetQuote'

--SELECT @HttpMethod = 'get'

--SELECT @ParametresValeurs = 'symbol=MSFT'

--SELECT @SoapAction = 'Authenticate'

-- =============================================

DECLARE @Url                       VARCHAR(1024),

            @HttpMethod             VARCHAR(10),

            @ParametresValeurs      VARCHAR(1024),   -- parametre1=valeur&parametre2=valeur

            @SoapAction             VARCHAR(1024) = null

BEGIN

SET NOCOUNT ON;

IF @HttpMethod in('get','GET') and len(@ParametresValeurs) > 0

BEGIN

   SELECT @Url = @Url + '?' + @ParametresValeurs

END

DECLARE                 @obj             INT

                        ,@Reponse         VARCHAR(8000)

                        ,@ReponseXml      XML

                        ,@status          VARCHAR(50)

                        ,@statusText      VARCHAR(1024)

                        ,@method          VARCHAR(10) =(CASE WHEN @HttpMethod in('soap','SOAP') THEN 'POST' ELSE @HttpMethod END)

EXEC sp_OACreate'MSXML2.XMLHTTP', @obj OUT

EXEC sp_OAMethod@obj, 'Open', null, @method, @Url, false

IF @HttpMethod in('get','GET')

      BEGIN

            EXEC sp_OAMethod@obj, 'send'

      END

ELSE

      IF @HttpMethod in('post','POST')

            BEGIN

                  EXEC sp_OAMethod@obj, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded'

                  EXEC sp_OAMethod@obj, 'send', null, @ParametresValeurs

            END

      ELSE

            IF @HttpMethod IN('soap','SOAP')

            BEGIN

                  IF @SoapAction is null

                        RAISERROR('@SoapAction is null', 10, 1)

                  DECLARE @host VARCHAR(1024) = @Url

                  IF @host like 'http://%'>

                        SELECT @host = right(@host, len(@host) - 7)

                  ELSE IF @host like 'https://%'>

                        SELECT @host = RIGHT(@host, len(@host) - 8)

                  IF charindex(':', @host) > 0 and charindex(':', @host) < charindex('/', @host)

                        SELECT @host = LEFT(@host, charindex(':', @host) - 1)

                  ELSE

                        SELECT @host = LEFT(@host, charindex('/', @host) - 1)

                  DECLARE @envelope VARCHAR(8000) = '<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><{action} xmlns="http://tempuri.org/">{params}</{action}></soap:Body></soap:Envelope>'

                  DECLARE @params VARCHAR(8000) = ''

                  WHILE LEN(@ParametresValeurs) > 0

                  BEGIN

                        DECLARE @parametre VARCHAR(256),

                                   @valeur VARCHAR(256)

                        IF charindex('&', @ParametresValeurs) > 0

                        BEGIN

                             SELECT @parametre = left(@ParametresValeurs, charindex('&', @ParametresValeurs) - 1)

                             SELECT @valeur = RIGHT(@parametre, len(@parametre) - charindex('=', @parametre))

                             SELECT @parametre = left(@parametre, charindex('=', @parametre) - 1)

                             SELECT @params = @params + '<' + @parametre + '>' + @valeur + '</'+ @parametre + '>'

                             SELECT @ParametresValeurs = right(@ParametresValeurs, LEN(@ParametresValeurs) - LEN(@parametre + '=' + @valeur + '&'))

                        END

                        ELSE

                        BEGIN

                             SELECT @valeur = RIGHT(@ParametresValeurs, len(@ParametresValeurs) - charindex('=', @ParametresValeurs))

                             SELECT @parametre = left(@ParametresValeurs, charindex('=', @ParametresValeurs) - 1)

                             SELECT @params = @params + '<' + @parametre + '>' + @valeur + '</'+ @parametre + '>'

                             SELECT @ParametresValeurs = NULL

                        END

                  END

                  SELECT @envelope = replace(@envelope, '{action}', @SoapAction)

                  SELECT @envelope = replace(@envelope, '{params}', @params)

                  SELECT @SoapAction = 'http://tempuri.org/' + @SoapAction

                  PRINT @host

                  PRINT @SoapAction

                  PRINT @envelope

                  EXEC sp_OAMethod@obj, 'setRequestHeader', null, 'Content-Type', 'text/xml; charset=utf-8'

                  EXEC sp_OAMethod@obj, 'setRequestHeader', null, 'Host', @host

                  EXEC sp_OAMethod@obj, 'setRequestHeader', null, 'SOAPAction', @SoapAction

                  EXEC sp_OAMethod@obj, 'send', null, @envelope

            END

EXEC sp_OAGetProperty@obj, 'responseText', @Reponse out

EXEC sp_OADestroy@obj

SELECT @status as [status], @statusText as [statusText], @Reponse as [response]

END

GO