MSSQL 调用接口，仅分享

MSSQL 调用接口，仅分享

MSSQL2016+支持JSON

------------------- 开启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
---------------------------------------------------------------

测试接口为金蝶云免密用户密码<第三方登录加密接口>
仅分享，不建议使用SQL调用接口

select [dbo].[fn_API]('https://127.0.0.1.ik3cloud.com/K3Cloud/Kingdee.BOS.WebApi.ServicesStub.AuthService.LoginByAppSecret.common.kdsvc','{"format": 1,"useragent": "ApiClient","rid": "956657478","parameters": "[\"20190902195133\",\"Administrator\",\"207606_5f1B6yHv0qA+W24KVazK5L0Y4CLo\",\"bcddc079ca594fc1800cf40e53d1a\",2052]","timestamp": "\/Date(1602656942830+0800)\/","v": "1.0"}')

create FUNCTION [dbo].[fn_API](@URL VARCHAR(500), @DATA VARCHAR(5000))
RETURNS VARCHAR(8000)
AS      
BEGINDECLARE
@REQ_H_ACCEPT VARCHAR(256),
@REQ_H_CONTENT_TYPE VARCHAR(256),
@Cookie VARCHAR(256)set @REQ_H_ACCEPT='application/json'
set @REQ_H_CONTENT_TYPE='application/json'
set @Cookie=''
DECLARE 
@object int,
@selectStatus int,
@selectText varchar(8000),
@errMsg varchar(2000),
@httpStatus varchar(20)
;/* 初始化 */  EXEC @selectStatus = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0',@object OUT;  IF @selectStatus <> 0  BEGIN  
EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @selectText OUT;
select @selectText=('初始化对象失败#，' + @errMsg + ISNULL(@selectText,''));  END   
/*创建链接*/
EXEC @selectStatus= SP_OAMethod @object,'open',NULL,'post',@URL,'false';IF @selectStatus <> 0
BEGIN
EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @selectText OUT;
select @selectText=('创建连接失败#，' + @errMsg + ISNULL(@selectText, ''));
ENDEXEC @selectStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Accept',@REQ_H_ACCEPT;
EXEC @selectStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Content-Type',@REQ_H_CONTENT_TYPE;
EXEC @selectStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Cookie-Container',@Cookie;EXEC @selectStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Content-Length','1000000';/*发起请求*/
EXEC @selectStatus= SP_OAMethod @object,'send',NULL,@DATA;
IF @selectStatus <> 0 
BEGIN 
EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @selectText OUT;
select @selectText=('发起请求失败#，' + @errMsg + ISNULL(@selectText, ''));
END/*获取HTTP状态代码*/
EXEC @selectStatus = SP_OAGetProperty @Object, 'Status', @httpStatus OUT;IF @selectStatus <> 0BEGINEXEC sp_OAGetErrorInfo @Object, @errMsg OUT, @selectText OUT;select @selectText=('获取HTTP状态代码失败#，' + @errMsg + ISNULL(@selectText, ''));ENDIF @httpStatus <> 200BEGINselect @selectText=('访问失败#，HTTP状态代码:' + @httpStatus);END/*获取返回信息*/
EXEC @selectStatus= SP_OAGetProperty @object,'responseText',@selectText OUT;IF @selectStatus <> 0 
BEGIN 
EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @selectText OUT;
select @selectText=('获取返回信息失败#，' + @errMsg + ISNULL(@selectText, ''));
END
RETURN @selectText
END

执行结果

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！