SQL server2008 调用HTTP接口

最新推荐文章于 2024-05-25 18:18:53 发布

转载最新推荐文章于 2024-05-25 18:18:53 发布 · 2.8k 阅读

5 ·

CC 4.0 BY-SA版权

原文链接：https://blog.youkuaiyun.com/caijunfen/article/details/73915123

SQLserver 专栏收录该内容

47 篇文章

订阅专栏

本文详细介绍了如何在SqlServer中配置OleAutomationProcedures参数以启用HTTPPOST请求功能，并提供了一个具体的函数FN_HTTP_POST的创建及使用示例，演示了如何通过此函数发送POST请求并接收响应。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

------------------- 开启Sql Server 通讯配置 -------------------
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures';
GO
---------------------------------------------------------------

-- HTTP POST 请求
-- [测试]

SELECT dbo.FN_HTTP_POST('http://172.16.0.143:8091/api/hd/UpdateOrderStatus','{"userName":"rest","password":"123456"}','application/json','application/json');

SELECT dbo.FN_HTTP_POST('http://172.16.0.143:8091/api/hd/PostTest','{"userName":"rest","password":"123456"}','application/json','application/json');

-- DROP FUNCTION FN_HTTP_POST;
CREATE FUNCTION FN_HTTP_POST(@URL VARCHAR(256),
@DATA VARCHAR(8000),
@REQ_H_ACCEPT VARCHAR(256),
@REQ_H_CONTENT_TYPE VARCHAR(256)
)
RETURNS VARCHAR(5000)
AS
BEGIN

DECLARE
@object int,
@returnStatus int,
@returnText varchar(5000),
@errMsg varchar(2000),
@httpStatus varchar(20)
;

/* 初始化 */
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 ('创建连接失败，' + @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;
RETURN ('发起请求失败，' + @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 ('获取HTTP状态代码失败，' + @errMsg + ISNULL(@returnText, ''));
END

IF @httpStatus <> 200
BEGIN
RETURN ('访问错误，HTTP状态代码:' + @httpStatus);
END
/*获取返回信息*/
EXEC @returnStatus= SP_OAGetProperty @object,'responseText',@returnText OUT;

IF @returnStatus <> 0
BEGIN
EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;
RETURN ('获取返回信息失败，' + @errMsg + ISNULL(@returnText, ''));
END

RETURN @returnText;
END
;

转自:https://blog.youkuaiyun.com/caijunfen/article/details/73915123