关于Smartbi配置SqlServer数据源调用HTTP请求cshonroute的博客-

关于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('https://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); } }