如何在数据库中模拟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个回答

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问