首页 SQLServer SQLServer 存储过程调用API接口

SQLServer 存储过程调用API接口

作者:胡同里的砖头 围观群众:273 更新于:2023-12-28

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

--GET
declare @ServiceUrl as varchar(1000)
--set @ServiceUrl = 'http://api.data.rx/api/rs/getdeptstaffstat?city=18&region=0'
set @ServiceUrl = 'http://edu.rx/a/api/getQuesUserNum?region=11&startTime=2018-8-8&endTime=2018-8-25'
DECLARE @data varchar(max);
set @data=''

Declare @Object as Int
Declare @ResponseText AS VARCHAR(8000);
Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',@ServiceUrl,'false'
Exec sp_OAMethod @Object, 'send', NULL, @data --发送数据
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
EXEC sp_OAGetErrorInfo @Object --异常输出
Select @ResponseText
Exec sp_OADestroy @Object
GO

--post
declare @ServiceUrl as varchar(1000)
set @ServiceUrl = 'http://edu.rx/a/api/getQuesUserNum'
DECLARE @data varchar(max);
--发送数据
set @data='region=11&startTime=2018-8-8&endTime=2018-8-25'

Declare @Object as Int
Declare @ResponseText AS varchar(8000) ;
Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'POST',@ServiceUrl,'false'
Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/x-www-form-urlencoded'
Exec sp_OAMethod @Object, 'send', NULL, @data --发送数据
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
EXEC sp_OAGetErrorInfo @Object --异常输出
Select @ResponseText
Exec sp_OADestroy @Object
GO
--该方法是GET请求,返回JSON数据
Declare @Object as Int;
DECLARE @hr int
Declare @Response as table(Json_Table nvarchar(max))

Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',
'http://192.168.102.132:7132/base', --Your Web Service Url (invoked)
'false'
Exec @hr=sp_OAMethod @Object, 'send'
Exec @hr=sp_OAMethod @Object, 'responseText', @Response OUTPUT

INSERT into @Response (Json_Table) exec sp_OAGetProperty @Object, 'responseText'

select * from @Response

EXEC sp_OADestroy @Object



避坑指南:
搞了半天没能成功,最后发现是SSL证书问题,后来把API接口从https改成http之后就能执行成功了

  • 本文标题: SQLServer 存储过程调用API接口
  • 文章分类:【SQLServer】
  • 非特殊说明,本文版权归【胡同里的砖头】个人博客 所有,转载请注明出处.
留言评论
站点声明:
1、本站【胡同里的砖头】个人博客,借鉴网上一些博客模板,取其各优点模块自行拼装开发,本博客开发纯属个人爱好。
2、所有笔记提供给广大用户交流使用,可转载,可复制,纯个人开发所遇问题锦集记录使用
Copyright © huzlblog.com All Rights Reserved. 备案号:苏ICP备2021056683号-8