SQL通过SP_OA调用外部HTTP
DECLARE @Object int;
DECLARE @HR int;
DECLARE @Property nvarchar(255);
DECLARE @Return nvarchar(255);
DECLARE @Source nvarchar(255), @Desc nvarchar(255);
DECLARE @httpStatus int;
DECLARE @response varchar(8000);
--创建 OLE 对象的实例
EXEC @HR = sp_OACreate N'MSXML2.XMLHTTP.6.0',@Object OUT;
IF @HR 0
BEGIN
EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT;
RAISERROR('Error Creating COM Component 0x%x, %s, %s',16,1, @HR, @Source, @Desc)
GOTO END_ROUTINE
END
BEGIN
--Open
EXEC @HR = sp_OAMethod @Object,N'open',Null,'GET','http://localhost:1728/HttpServer/submit.aspx',FALSE;
IF @HR 0
BEGIN
EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT;
RAISERROR('Open 0x%x, %s, %s',16,1, @HR, @Source, @Desc)
GOTO CLEANUP
END
--setRequestHeader
EXEC @HR = sp_OAMethod @Object,N'setRequestHeader',Null,'Content-Type','text/xml';
IF @HR 0
BEGIN
EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT;
RAISERROR('setRequestHeader 0x%x, %s, %s',16,1, @HR, @Source, @Desc)
GOTO CLEANUP
END
--send
EXEC @HR = sp_OAMethod @Object,N'send',Null,'';
IF @HR 0
BEGIN
EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT;
RAISERROR('send 0x%x, %s, %s',16,1, @HR, @Source, @Desc)
GOTO CLEANUP
END
--readyState
EXEC @HR = sp_OAGetProperty @Object,'readyState', @httpStatus OUT;
IF @HR 0
BEGIN
EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT;
RAISERROR('readyState 0x%x, %s, %s',16,1, @HR, @Source, @Desc)
GOTO CLEANUP
END
--verify status
IF @httpStatus 4
BEGIN
RAISERROR('readyState http status bad', 16,1)
GOTO CLEANUP
END
--status
EXEC @HR = sp_OAGetProperty @Object,'status', @httpStatus OUT;
IF @HR 0
BEGIN
EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT;
RAISERROR('getstatus 0x%x, %s, %s',16,1, @HR, @Source, @Desc)
GOTO CLEANUP
END
--verify status
IF @httpStatus 200
BEGIN
Print Cast(@httpStatus As varchar)
RAISERROR('Open http status bad', 16,1)
GOTO CLEANUP
END
--responseText
EXEC @HR = sp_OAGetProperty @Object, 'responseText', @response OUT
IF @HR 0
BEGIN
EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT;
RAISERROR('responseText 0x%x, %s, %s',16,1, @HR, @Source, @Desc)
GOTO CLEANUP
END
Print @response
END
CLEANUP:
BEGIN
EXEC @HR = sp_OADestroy @Object;
IF @HR 0
BEGIN
EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT;
SELECT HR = convert(varbinary(4),@HR),Source=@Source,Description=@Desc;
END
END
END_ROUTINE:
RETURN;
GO
RSS订阅
499
