1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
|
DECLARE @URI varchar(2000) = 'http://.../...php'
DECLARE @methodName varchar(50) = 'POST'
DECLARE @requestBody varchar(8000) = 'type=6&module=2&id=101&title=Titre Notif&message=Message Notif&externalRef=8'
DECLARE @UserName nvarchar(100) = 'UserName '
DECLARE @Password nvarchar(100) = 'Password '
DECLARE @responseText nvarchar(max) = ''
SET NOCOUNT ON
SET @responseText = 'FAILED'
DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255)
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
-- open the destination URI with Specified method
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false'
IF @hResult <> 0
BEGIN
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
-- set request headers - Content-Type
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded'
IF @hResult <> 0
BEGIN
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 request headers - Authorization
DECLARE @CrendentialEncoded VARCHAR(500) = @UserName + ':' + @Password
SELECT @CrendentialEncoded = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'VARCHAR(MAX)')
FROM ( SELECT CAST(@CrendentialEncoded AS VARBINARY(MAX)) AS bin) AS bin_sql_server_temp;
SET @CrendentialEncoded = 'Basic ' + @CrendentialEncoded
PRINT @CrendentialEncoded
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Authorization', @CrendentialEncoded
IF @hResult <> 0
BEGIN
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 @returnval nvarchar(max)
-- 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
goto destroy
return
END
-- Get status text
declare @statusText varchar(1000), @status varchar(1000)
exec sp_OAGetProperty @objectID, 'StatusText', @statusText out
exec sp_OAGetProperty @objectID, 'Status', @status out
select @status as Status, @statusText as StatusText, @methodName as MethodName
-- Get response text
EXEC @hResult = sp_OAGetProperty @objectID, 'responseText',@responseText OUTPUT
--Exec @hResult = sp_OAMethod @objectID, 'responseText', @responseText OUTPUT
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'ResponseText failed',
MedthodName = @methodName
goto destroy
return
END
Select @ResponseText [ResponseText]
destroy:
exec sp_OADestroy @objectID
SET NOCOUNT OFF
GO |
Partager