场景是对方发布了一个http的post接口,需要从Sql server数据直接访问这个接口地址,请求接口返回的数据。请求参数是json格式的。oracle数据库楼主做过,但是sqlserver数据库没做过,上网搜了一些资料,然后在生产库测试了下,有数据返回,但没对返回的数据进行解析,有空在分享解析接口返回数据的脚本。本文先分享请求接口的函数。另外,可以尝试将函数写成存储过程。本文也把猜想的存储过程脚本分享出来,但没做具体的调试,不知是否成功,仅供参考,读者有意研究的话可以尝试自行调试,如果可以麻烦,跟帖分享出来。谢谢!
函数脚本如下
-- HTTP POST 请求
-- [测试]
-- SELECT dbo.FN_HTTP_POST('http://ip:port/xxx/xxxx/barinfo','{"data":{"barcode":"01xxxxx03"}}','application/json','application/json; charset=UTF-8');
CREATE FUNCTION FN_HTTP_POST(
@URL VARCHAR(256),
@DATA VARCHAR(4000),
@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;
存储过程脚本(未经过验证,仅供参考,不保证能用)
CREATE PROCEDURE [dbo].[usp_Util_HttpPost]
@Url NVARCHAR(4000),
@PostData NVARCHAR(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ErrMsg VARCHAR(5000)
DECLARE @token INT,@result INT,@returnTextErr VARCHAR(200),@HttpStatus VARCHAR(200)
EXECUTE @result = sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @token OUT
IF @result <> 0
BEGIN
EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT
SET @ErrMsg = '初始化对象失败,' + @ErrMsg + ISNULL(@returnTextErr, '')
RAISERROR(@ErrMsg,16,-1)
END
EXECUTE @result = sp_OAMethod @token, 'open', NULL, 'POST', @Url,'false'
IF @result <> 0
BEGIN
EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT
SET @ErrMsg = '创建连接失败,' + @ErrMsg + ISNULL(@returnTextErr, '')
RAISERROR(@ErrMsg,16,-1)
END
EXECUTE @result = sp_OAMethod @token, 'setRequestHeader', NULL,'Content-Type', 'application/json; charset=UTF-8'
IF @result <> 0
BEGIN
EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT
SET @ErrMsg = '设置RequestHeader属性失败,' + @ErrMsg + ISNULL(@returnTextErr, '')
RAISERROR(@ErrMsg,16,-1)
END
EXECUTE @result = sp_OAMethod @token,'send', NULL, @PostData--发送请求
IF @result <> 0
BEGIN
EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT
SET @ErrMsg = '发送请求头失败,' + @ErrMsg + ISNULL(@returnTextErr, '')
RAISERROR(@ErrMsg,16,-1)
END
EXECUTE @result = sys.sp_OAGetProperty @token, 'Status', @HttpStatus OUT
IF @result <> 0
BEGIN
EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT
SET @ErrMsg = '读取[Status]属性值失败,' + @ErrMsg + ISNULL(@returnTextErr,'')
RAISERROR(@ErrMsg,16,-1)
END
IF @HttpStatus <> 200
BEGIN
SET @ErrMsg ='访问错误,http状态代码,'+@HttpStatus+''
RAISERROR(@ErrMsg,16,1)
END
IF @result <> 0
BEGIN
EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT
SET @ErrMsg = '获取回复报文失败:' + ISNULL(@ErrMsg, '') + ','+ ISNULL(@returnTextErr, '')
RAISERROR(@ErrMsg,16,-1)
END
EXECUTE @result = sp_OADestroy @token
IF @result <> 0
BEGIN
EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT
SET @ErrMsg = '释放资源对象,' + @ErrMsg + ISNULL(@returnTextErr, '')
RAISERROR(@ErrMsg,16,-1)
END
END