Script to perform http(s) post

/*
This is a procedure, not an udf because of the exec insert at the end
The exec insert is needed because the response can exceed 8000 characters

Normally, one should call methods like this :
exec@rc    = master.dbo.sp_OAMethod @http, 'Open', NULL, 'POST' ,'www.d-trix.com/default.asp', 0

But this does not work with WinHttp.WinHttpRequest.5.1 and gives different errors
So we call methods like this
exec@rc    = master.dbo.sp_OAMethod @http, 'Open("POST","www.d-trix.com/default.asp",0)'

if a (temp) table with the name #usp_httppost exists, the procedure will
insert the result into that table.
if not, the procedure will return a resultset with the results
*/create procedure dbo.usp_httppost
(
@URL varchar(512) = '?',
@post varchar(4000) = '',
@WebLogin varchar(128) = null,
@WebPassword varchar(128) = null,
@ProxyLogin varchar(128) = null,
@ProxyPassword varchar(128) = null
)
/*
returns @usp_httppost table
(
return_status bit null, -- 0 is OK, 1 is error
error_msg varchar(4096) null,
HTTP_Status varchar(30) null,
HTTP_AllResponseHeaders text null,
HTTP_ResponseText text null
)
*/as
begin
if isnull(@URL,'?') = '?'
begin
print 'Name        : usp_httppost'
print 'Server      : SQLserver 2000 '
print 'Description : Post data as coming from an HTML FORM with METHOD=POST'
print '              to an URL and retrieve the result.'
print 'Parameters  : @URL           : the url to use ( like https://www.d-trix.com )'
print '              @post          : the data to post'
print '              @WebLogin      : (optional) The Username for the webserver'
print '              @WebPassword   : (optional) The password for the webserver'
print '              @ProxyLogin    : (optional) The Username for the proxyserver'
print '              @ProxyPassword : (optional) The password for the proxyserver'
print 'Notes       : . The data to be posted should be like param1=val1&par2=val2'
print '              . if a table called #usp_httppost exists, the result is stored'
print '                into that table. If not, the procedure returns a resultset.'
print '              . The procedure also sets the option for winHTTP to ignore all'
print '                ssl errors. You can comment this for security.'
print '              . The procedure tries to use master.dbo.fn_hexadecimal to convert the'
print '                sp_OA error to a hex string. If the UDF does not exists, it uses it''s'
print '                own code to do the convertion.'
print 'Date        : 2005-01-19'
print 'Author      : Bert De Haes ( bertdehaes@scarlet.be )'
print 'History     : '
print 'Example     : exec dbo.usp_httppost ''http://www.sqlservercentral.com/search/turbo.asp'',''searchstring=ftp&btnG= Go &cArticles=on&cForums=on&cScripts=on&sitesearch=http://www.sqlservercentral.com'''

return 2
end

set nocount on

declare@http int, -- the objecttoken for WinHttp.WinHttpRequest.5.1
@rc int, -- the return code from sp_OA procedures
@src varchar(255), -- the source of an error
@desc varchar(512), -- the desciption of an error
@Doing varchar(4000), -- What are we doing when calling a sp_OA proc
@TableExisted bit, -- Did the temp table exists yes(1) or no(0)
@HexError varchar(15) -- @rc converted to hexadecimal

-- init

set @rc = 0

if object_id('tempdb..#usp_httppost') is null
begin
-- The temp table #usp_httppost does not exists
set @TableExisted = 0
create table #usp_httppost
(
return_status bit null, -- 0 is OK, 1 is error
error_msg varchar(4096) null,
HTTP_Status varchar(30) null,
HTTP_AllResponseHeaders text null,
HTTP_ResponseText text null
)
end
else
begin
set @TableExisted = 1
truncate table #usp_httppost
end
 
-- Insert a default record

insert#usp_httppost ( return_status, error_msg )
values( 1 , 'Unknown error' )

-- create a table to store output from different sp_OA calls

create table #tempresult
(
HTTP_Status varchar(30) null,
HTTP_AllResponseHeaders text null,
HTTP_ResponseText text null
)

-- create the 'WinHttp.WinHttpRequest.5.1' object

set@Doing= 'Create WinHttp.WinHttpRequest.5.1 object.'
exec@rc= master.dbo.sp_OACreate 'WinHttp.WinHttpRequest.5.1', @http output 
if @rc <> 0 goto Error

-- open the url on the server

set@Doing = 'Open("POST" , "' + @URL + '", 0 )'
exec@rc    = master.dbo.sp_OAMethod @http, @Doing
if @rc <> 0 goto Error

-- set ignore ssl error
set@Doing = 'Option' -- = 0x3300
exec@rc    = master.dbo.sp_OASetProperty  @http,@Doing,13056,4
if @rc <> 0 goto Error

-- set the SetRequestHeader

set @Doing = 'SetRequestHeader("Content-Type","application/x-www-form-urlencoded")'
exec@rc    = master.dbo.sp_OAMethod @http,@Doing
if @rc <> 0 goto Error

if @WebLogin is not null AND @WebPassword is not null
begin
-- Set the Credentials for the Webserver

set@Doing = 'SetCredentials("' + @WebLogin + '","' + @WebPassword + '",0)'
exec@rc    = master.dbo.sp_OAMethod @http, @Doing
if @rc <> 0 goto Error
end

if @ProxyLogin is not null AND @ProxyPassword is not null
begin
-- Set the Credentials for the Proxy

set@Doing = 'SetCredentials("' + @ProxyLogin + '","' + @ProxyPassword + '",1)'
exec@rc    = master.dbo.sp_OAMethod @http, @Doing
if @rc <> 0 goto Error
end

-- send the info

set @Doing = 'Send("' + @post + '")'
exec@rc    = master.dbo.sp_OAMethod @http,@Doing
if @rc <> 0 goto Error

-- Get the HTTP_Status

set@Doing = 'Status'
truncate table#tempresult
insert#tempresult (HTTP_Status)
exec@rc = sp_OAMethod @http,@Doing 
if @rc <> 0 goto Error

update#usp_httppost
setHTTP_Status = #tempresult.HTTP_Status
from#tempresult
where#tempresult.HTTP_Status is not null

-- Get the ResponseHeaders

set@Doing = 'GetAllResponseHeaders'
truncate table#tempresult
insert#tempresult (HTTP_AllResponseHeaders)
exec@rc = sp_OAMethod @http,@Doing 
if @rc <> 0 goto Error

update#usp_httppost
setHTTP_AllResponseHeaders = #tempresult.HTTP_AllResponseHeaders
from#tempresult
where#tempresult.HTTP_AllResponseHeaders is not null

-- retrieve the ResponseText

set@Doing = 'ResponseText'
truncate table#tempresult
insert#tempresult (HTTP_ResponseText)
exec@rc = sp_OAMethod @http,@Doing 
if @rc <> 0 goto Error

update#usp_httppost
setHTTP_ResponseText = #tempresult.HTTP_ResponseText
from#tempresult
where#tempresult.HTTP_ResponseText is not null

-- Everything went well

update #usp_httppost
set return_status = 0,error_msg = 'DONE'

-- if we get here the normal way, don't do error
GOTO Cleanup

Error:

-- Get error information

if @http is not null
begin
exec sp_OAGetErrorInfo @http, @src OUT, @desc OUT
end
else
begin
set @src = '?'
set @desc = '?'
end

if object_id('master.dbo.fn_hexadecimal') is null
begin
-- code for fn_hexadecimal here ...
declare@i int,
@length int,
@hexstring char(16),
@byte tinyint


set @HexError = '0x'
set @i = 1
set @length = DATALENGTH(cast (@rc as varbinary))
set @hexstring = '0123456789ABCDEF'

while @i <= @length
begin
set @byte = convert(tinyint, substring(cast (@rc as varbinary),@i,1))
set @HexError = @HexError
      + substring(@hexstring, floor(@byte/16)+1, 1)
              + substring(@hexstring, (@byte % 16)+1, 1)
set @i = @i + 1
end
end
else
begin
set @HexError = master.dbo.fn_hexadecimal(@rc)
end

update #usp_httppost
setreturn_status = 1,
error_msg = 
'Error ['+ ISNULL( @HexError,'' ) +
'], While ['+ ISNULL( @Doing, '' ) + 
'], Source ['+ ISNULL( @src  , '' ) + 
'], Description ['+ ISNULL( @desc, '' ) + ']'

-- Destroy created object(s)

Cleanup:
if @http is not null
begin
exec@rc= master.dbo.sp_OADestroy @http
set @http = null
if @rc <> 0 goto Error
end

-- Give the result back to the caller

Result:
if @TableExisted = 0
select * from #usp_httppost

return 0
end