关于Smartbi配置Sql Server数据源调用HTTP请求

关于Smartbi配置Sql Server数据源调用HTTP请求

	要求利用存储过程调用http接口，并返回报表！笔记
	1、开启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

	2、HTTP POST 请求函数

CREATE function [dbo].[fn_http_post](
@URL varchar(256),
@DATA varchar(2000),
@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
exec @returnStatus = SP_OAGetProperty @object, 'Status', @httpStatus OUT;
if @returnStatus <> 0
BEGIN
exec sp_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;
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

	3、存储过程入口

CREATE PROCEDURE [dbo].[oa_zyy_getTime]
@StartTime VARCHAR(20),
@EndTime VARCHAR(20)
AS
declare
@code VARCHAR(5),
@msg VARCHAR(50)
BEGIN
select @code=dbo.fn_http_post('http://localhost:8080/select?StartTime='+
@StartTime+'&EndTime='+@EndTime,'','application/json','application/json');
if @code='1'
	BEGIN
		select * from oa_zyy_journalRpt;
	end
else 
	BEGIN
		select @msg='获取日报失败！';
	end
END

	4、接口Controller

package com.kq.controller;

import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.kq.service.OAZYYService;

/**
*@author 
*@date 2020-06-05 13:52:14
*/
@RestController
@RequestMapping(method=RequestMethod.POST,produces = "application/json;charset=UTF-8")
public class OAZYYController {

	@Autowired
	private OAZYYService oas;
	@RequestMapping(value="/select")
	public String select(@RequestParam Map<String, String>request,HttpServletResponse response) {
		System.out.println(request.get("StartTime")+request.get("EndTime"));
		return oas.journalRpt(request);
	}
}