SQLSERVER调用WebAPI和WebService
----MSSQL调用API接口函数CREATE/ALTER PROCEDURE .
@ApiUrl VARCHAR(200), --api请求地址
@BodyJsom varchar(max),
@ResponseText NVARCHAR(4000) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
Declare @Object as Int
Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'POST',@ApiUrl,'false'
Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/json'
Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Accept','S3YiFeiAPI'
Exec sp_OAMethod @Object, 'send', NULL, @BodyJsom --发送数据
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
--EXEC sp_OAGetErrorInfo @Object --异常输出
Exec sp_OADestroy @Object-- 释放对象
END
---------MSSQL调用函数
declare @ApiUrl as varchar(200)
set @ApiUrl = 'http://192.168.56.122:9023/YiFeiAPI?ApiName=GetSQL&ApiType=GET'
DECLARE @BodyJsom varchar(max)
set @BodyJsom ='{ "xFields": "MB001,MB002,MB064", "xDBTabel": "DEMO..INVMB", "xWhere": "MB001 IN (''21001'',''21002'')", "xGroup": "MB001,MB002,MB064", "xOrder": "MB001 DESC"}'
DECLARE@ResponseText NVARCHAR(4000)
Exec S3DB.. @ApiUrl,@BodyJsom,@ResponseText OUTPUT
select @ResponseText
开启组件
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
declare @ServiceUrl as varchar(1000)
set @ServiceUrl = 'http://172.16.170.192:803/api/wardRound/GetInfo'
DECLARE @data varchar(max);
--发送数据
set @data='{"ID": 67,"UserID": 10}'
Declare @Object as Int
Declare @ResponseText ASvarchar(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/json;charset=UTF-8'
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
DECLARE @sUrl NVARCHAR(100)=N'http://127.0.0.1/WebService/Dye.asmx?op=ColorSystem'
DECLARE @sSoapContent NVARCHAR(2000)=N''
SELECT @sSoapContent=N'<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<ColorSystem xmlns="http://tempuri.org/">
<iCompanyId>1</iCompanyId>
<sColorNo>2</sColorNo>
</ColorSystem>
</soap12:Body>
</soap12:Envelope>'
DECLARE @sResult NVARCHAR(MAX)
SELECT @sResult=dbo.fnpbWebServiceInvoke(@sUrl,N'',N'',@sSoapContent).value(N'(/Result)','NVARCHAR(MAX)')
SELECT @sResult=REPLACE(@sResult,N'"',N'')
SELECT @sResult=REPLACE(@sResult,N'{',N'')
SELECT @sResult=REPLACE(@sResult,N'}',N'')
SELECT @sResult=REPLACE(@sResult,N'[',N'')
SELECT @sResult=REPLACE(@sResult,N']',N'')
Create PROCEDURE .
-- Add the parameters for the stored procedure here
@ApiUrl VARCHAR(200), --api请求地址
@RequestType VARCHAR(5),--请求类型:POST,GET
@ResponseText NVARCHAR(4000) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @data varchar(8000);
--发送数据
set @data=''
Declare @Object as Int
Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT
Exec sp_OAMethod @Object, 'open', NULL, @RequestType,@ApiUrl,'false'
IF @RequestType='POST'
BEGIN
Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/x-www-form-urlencoded'
END
Exec sp_OAMethod @Object, 'send', NULL, @data --发送数据
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
--EXEC sp_OAGetErrorInfo @Object --异常输出
--Select@ResponseText as ResponseText
--Select * from .(@ResponseText)
Exec sp_OADestroy @Object
END
https://www.cnblogs.com/wangdongying/p/16903215.html
页:
[1]