d02540311 2017-04-07 09:12 采纳率: 0%
浏览 1439

如何在数据库中模拟http请求网页短信接口

USE [Test]
GO

/****** Object: StoredProcedure [dbo].[UXP_WS_SendShortMsg_20170406] Script Date: 04/07/2017 17:04:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*

sql发送手机短信接口

ex:
declare @re int
execute dbo.UXP_WS_SendShortMsg '1307','验证码为:5892344',@re output
select @re
*/

CREATE proc [dbo].UXP_WS_SendShortMsg_20170406,
@msg nvarchar(4000),
@return int output
)
as
declare @return_desc varchar(100)

DECLARE @obj INT

DECLARE @url nVARCHAR(4000)

DECLARE @url_aliyun nVARCHAR(4000)--阿里云短信接口

DECLARE @response VARCHAR(5000)

declare @flowuserid varchar(30)
declare @uid nvarchar(30)
declare @pwd nvarchar(30)
set @uid='temul'
set @pwd='termupsd'

----阿里云 http://.88:9800/WS.asmx/SendMessage?TelCode=15919424&messageCenter={"carleadinfo":"aaa","usecarempinfo":"bbb"}&TemplateCode=110

--print @chno

set @url ='http://.88:9800/NewWS.asmx/SendMessage?'+@msg
EXEC sp_OACreate 'MSXML2.ServerXMLHttp.6.0',@obj out
print @obj

EXEC sp_OAMethod @obj,'Open',null,'get',@url,false

EXEC sp_OAMethod @obj,'send'

EXEC sp_OAGetProperty @obj,'responseText',@response out

--SELECT @response [response]

EXEC sp_OADestroy @obj

declare @i int,@j int
declare @sourceC varchar(20),@placeC Varchar(30)
while charindex('非法字符是',@response,1)<>0
begin
set @i=charindex('是:',@response,1)
set @i=@i+1
set @sourcec=rtrim(substring(@response,@i+1,len(@response)-@i-9))
set @placec=left(@sourcec,1) + ' ' + substring(@sourcec,2,len(@sourcec)-1)
set @msg=replace(@msg,@sourcec,@placec)

--将非法字符间中间强行插入空格符,再次发送
set @url ='http://4.88:9800/NewWS.asmx/SendMessage?'+@msg
EXEC sp_OACreate 'MSXML2.ServerXMLHttp.6.0',@obj out 

EXEC sp_OAMethod @obj,'Open',null,'GET',@url,false      
EXEC sp_OAMethod @obj,'send'      
EXEC sp_OAGetProperty @obj,'responseText',@response out          
EXEC sp_OADestroy @obj 

end

IF CHARINDEX('success',@response,1)<>0 --SCM返回成功信息
    begin
        set @return=1
        SET @return_desc='Success'
    end

ELSE --
BEGIN
set @return=0
print @response
SET @return_desc='出错1,' + @response
end
print @return_desc

RETURN

GO

  • 写回答

1条回答

  • zqbnqsdsmd 2017-04-08 15:43
    关注
    评论

报告相同问题?

悬赏问题

  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)