如何从SQL服务器发出HTTP请求？

I wanted to send an HTTP request from SQL server to Tomcat server. I have installed SQL server 2012 express and non .NET application in Tomcat server. I have gone through this like Make a HTTP request from SQL server

我想从SQL服务器向Tomcat服务器发送HTTP请求。我在Tomcat服务器上安装了SQL Server 2012 express和非.NET应用程序。我已经完成了这一点，比如从SQL服务器发出HTTP请求

As it says in the above article, "The COM object WinHttp.WinHttpRequest.5.1 must be installed on the server, some typical variations are WinHttp.WinHttpRequest.5". I have downloaded winhttp.zip from the winhttp download link, found winhttp.dll in the zip folder and pasted it in C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2\MSSQL\Binn as suggested in this msdn link.

正如在上面的文章中所说，“必须在服务器上安装COM对象WinHttp.WinHttpRequest.5.1，一些典型的变体是WinHttp.WinHttpRequest.5”。我从winhttp下载链接下载了winhttp.zip，在zip文件夹中找到了winhttp.dll，并按照此msdn链接中的建议将其粘贴到C：\ Program Files \ Microsoft SQL Server \ MSSQL11.MSSQLSERVER2 \ MSSQL \ Binn中。

Following that same advice, I have executed following line in SSMS:

遵循同样的建议，我在SSMS中执行了以下行：

sp_addextendedproc 'GetHttp',
 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2\MSSQL\Binn\winhttp.dll';

I also executed the following code in SSMS as said in "Make an HTTP request from SQL server link":

我还在SSMS中执行了以下代码，如“从SQL服务器链接发出HTTP请求”中所述：

Alter function GetHttp
(
@url varchar(8000)      
)
returns varchar(8000)
as
BEGIN
DECLARE @win int 
DECLARE @hr  int 
DECLARE @text varchar(8000)

EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win,'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 

RETURN @text
END

Then I get the error

然后我得到了错误

Msg 2010, Level 16, State 1, Procedure GetHttp, Line 2
Cannot perform alter on 'GetHttp' because it is an incompatible object type.

Msg 2010，Level 16，State 1，Procedure GetHttp，Line 2无法对'GetHttp'执行alter，因为它是一个不兼容的对象类型。

I do not know how to call the function to send the HTTP request. I assume it is something like this GetHttp('http://www.google.co.in/').

我不知道如何调用该函数来发送HTTP请求。我认为它就像这个GetHttp（'http://www.google.co.in/'）。

What am I missing?

我错过了什么？

4 个解决方案

#1

I got another answer as well. I created procedure like follows

我也得到了另一个答案。我创建了如下的程序

CREATE procedure HTTP_Request( @sUrl varchar(200))
As


Declare
@obj int
,@hr int
,@msg varchar(255)


 exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
if @hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
failed', 16,1) return end


exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false
if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end


exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type',
'application/x-www-form-urlencoded'
if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed' goto
eh end


exec @hr = sp_OAMethod @obj, send, NULL, ''
if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end

 exec @hr = sp_OADestroy @obj
return
eh:
exec @hr = sp_OADestroy @obj
Raiserror(@msg, 16, 1)
return
GO

I called the stored procedure with url

我用url调用了存储过程

USE [master]
GO

 DECLARE    @return_value int

EXEC    @return_value = [dbo].[HTTP_Request]
    @sUrl = N'url'

SELECT  'Return Value' = @return_value

GO

Thank you guys to make me work this.

谢谢你们让我这样做。

#2

I got answer by powershell. What I did is open powershell in sql server then I did execute following code in powershell.

我得到了powershell的回答。我所做的是在sql server中打开powershell然后我在powershell中执行了以下代码。

$http_Request= New-Object system.Net.WebClient;
$Result = $http_Request.downloadString("url")

#3

That really helped me @niren.

这真的帮助了我@niren。

Thought I'd post my amendment that puts it in scalar function and allows you to get the service response. Only downside is scalar funcs can't raiserrors so there's something to think about catching elsewhere.

以为我会发布我的修正案，将其置于标量函数中并允许您获得服务响应。唯一的缺点是标量函数不能反复出现，因此有必要考虑去其他地方。

CREATE function [dbo].[fn_HttpPOST]
(
    @sUrl varchar(8000)      
)
returns varchar(8000)
as
BEGIN
    DECLARE @obj int 
    DECLARE @hr  int 
    DECLARE @msg varchar(8000)    


    exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
    if @hr <> 0 begin set @Msg = 'sp_OACreate MSXML2.ServerXMLHttp.3.0 failed' return @Msg end

    exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false
    if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end

    exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
    if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed' goto eh end

    exec @hr = sp_OAMethod @obj, send, NULL, ''
    if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end

     EXEC @hr=sp_OAGetProperty @Obj,'ResponseText',@msg OUTPUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Obj

    exec @hr = sp_OADestroy @obj

    RETURN @msg

    eh:
    exec @hr = sp_OADestroy @obj
    return @msg
END

#4

Made this monster for my own needs

根据自己的需要制作这个怪物

CREATE PROCEDURE [dbo].[RequestHttpWebService]
    @Url varchar(1024),
    @HttpMethod varchar(10),
    @ParamsValues varchar(1024),    -- param1=value&param2=value
    @SoapAction varchar(1024) = null
AS
BEGIN
    SET NOCOUNT ON;

    if @HttpMethod in ('get','GET') and len(@ParamsValues) > 0
    begin
        set @Url = @Url + '?' + @ParamsValues
    end

    declare @obj int
        ,@response varchar(8000)
        ,@responseXml xml
        ,@status varchar(50)
        ,@statusText varchar(1024)
        ,@method varchar(10) = (case when @HttpMethod in ('soap','SOAP') then 'POST' else @HttpMethod end)

    exec sp_OACreate 'MSXML2.ServerXMLHttp', @obj out
    exec sp_OAMethod @obj, 'Open', null, @method, @Url, false

    if @HttpMethod in ('get','GET')
    begin
        exec sp_OAMethod @obj, 'send'
    end
    else if @HttpMethod in ('post','POST')
    begin
        exec sp_OAMethod @obj, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded'
        exec sp_OAMethod @obj, 'send', null, @ParamsValues
    end
    else if @HttpMethod in ('soap','SOAP')
    begin
        if @SoapAction is null
            raiserror('@SoapAction is null', 10, 1)

        declare @host varchar(1024) = @Url
        if @host like 'http://%'
            set @host = right(@host, len(@host) - 7)
        else if @host like 'https://%'
            set @host = right(@host, len(@host) - 8)

        if charindex(':', @host) > 0 and charindex(':', @host) < charindex('/', @host)
            set @host = left(@host, charindex(':', @host) - 1)
        else 
            set @host = left(@host, charindex('/', @host) - 1)

        declare @envelope varchar(8000) = '<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><{action} xmlns="http://tempuri.org/">{params}</{action}></soap:Body></soap:Envelope>'
        declare @params varchar(8000) = '' 

        WHILE LEN(@ParamsValues) > 0
        BEGIN
            declare @param varchar(256),
                    @value varchar(256)

            IF charindex('&', @ParamsValues) > 0
            BEGIN

                SET @param = left(@ParamsValues, charindex('&', @ParamsValues) - 1)
                set @value = RIGHT(@param, len(@param) - charindex('=', @param))
                set @param = left(@param, charindex('=', @param) - 1)
                set @params = @params + '<' + @param + '>' + @value + '</'+ @param + '>'
                SET @ParamsValues = right(@ParamsValues, LEN(@ParamsValues) - LEN(@param + '=' + @value + '&'))
            END
            ELSE
            BEGIN
                set @value = RIGHT(@ParamsValues, len(@ParamsValues) - charindex('=', @ParamsValues))
                set @param = left(@ParamsValues, charindex('=', @ParamsValues) - 1)

                set @params = @params + '<' + @param + '>' + @value + '</'+ @param + '>'
                SET @ParamsValues = NULL
            END
        END

        set @envelope = replace(@envelope, '{action}', @SoapAction)
        set @envelope = replace(@envelope, '{params}', @params)

        set @SoapAction = 'http://tempuri.org/' + @SoapAction

        print @host
        print @SoapAction
        print @envelope

        exec sp_OAMethod @obj, 'setRequestHeader', null, 'Content-Type', 'text/xml; charset=utf-8'
        exec sp_OAMethod @obj, 'setRequestHeader', null, 'Host', @host
        exec sp_OAMethod @obj, 'setRequestHeader', null, 'SOAPAction', @SoapAction
        exec sp_OAMethod @obj, 'send', null, @envelope
    end

    exec sp_OAGetProperty @obj, 'responseText', @response out
    exec sp_OADestroy @obj

    select @status as [status], @statusText as [statusText], @response as [response]

END

GO