sqlserver 调用http接口

USE [DBMonitor]

GO

/****** Object: StoredProcedure [dbo].[P_POST_HttpRequestData] Script Date: 2024/7/12 14:20:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

php:

$oriContent = file_get_contents('php://input');

$info=json_decode($oriContent,true);

echo $info['smtpemailto'];

*/

ALTER PROCEDURE [dbo].[P_POST_HttpRequestData]

(

@URL NVARCHAR(max) ,

@DATA VARCHAR(MAX) ,--{"userName":"rest","password":"123456"} 发送post的数据 接收方file_get_contents('php://input')获取json数据

@REQ_H_ACCEPT VARCHAR(256) ,--application/json

@REQ_H_CONTENT_TYPE VARCHAR(256)--application/json

)

AS

BEGIN

DECLARE @object INT ,

@returnStatus INT ,

@returnText VARCHAR(5000) ,

@errMsg VARCHAR(2000) ,

@httpStatus VARCHAR(20);

BEGIN try

/* 初始化 */

EXEC @returnStatus = sp_OACreate 'Msxml2.ServerXMLHTTP.3.0',

@object OUT;

IF @returnStatus <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;

RETURN ('初始化对象失败，' + @errMsg + ISNULL(@returnText,''));

END;

/*创建链接*/

EXEC @returnStatus= sp_OAMethod @object, 'open', NULL, 'post', @URL,

'false';

IF @returnStatus <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;

RETURN -1--('创建连接失败，' + @errMsg + ISNULL(@returnText, ''));

END;

EXEC @returnStatus= sp_OAMethod @object, 'setRequestHeader', NULL,

'Accept', @REQ_H_ACCEPT;

EXEC @returnStatus= sp_OAMethod @object, 'setRequestHeader', NULL,

'Content-Type', @REQ_H_CONTENT_TYPE;

EXEC @returnStatus= sp_OAMethod @object, 'setRequestHeader', NULL,

'Content-Length', '1000000';

/*发起请求*/

EXEC @returnStatus= sp_OAMethod @object, 'send', NULL, @DATA;

IF @returnStatus <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;

select @errMsg + ISNULL(@returnText, '')

RETURN -11--('发起请求失败，' + @errMsg + ISNULL(@returnText, ''));

END;

/*获取HTTP状态代码*/

EXEC @returnStatus = sp_OAGetProperty @object, 'Status',

@httpStatus OUT;

IF @returnStatus <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;

RETURN -12-- ('获取HTTP状态代码失败，' + @errMsg + ISNULL(@returnText, ''));

END;

IF @httpStatus <> 200

BEGIN

RETURN -13--('访问错误，HTTP状态代码:' + @httpStatus);

END;

/*获取返回信息*/

EXEC @returnStatus= sp_OAGetProperty @object, 'responseText',

@returnText OUT;

IF @returnStatus <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;

RETURN -1--('获取返回信息失败，' + @errMsg + ISNULL(@returnText, ''));

END;

END TRY

BEGIN CATCH

SELECT ERROR_MESSAGE(),ERROR_LINE()

END CATCH

--SELECT @returnText;

END;