SQL Stored Procedure using HTTP Object to Request File and Download locally

I have a database of the remote location of over 500 images that I would like to download.
(Ex. http://www.mygolfgear.com/products/iStourplusequilizer.jpg)

I am writing a stored procedure that uses an http object to download the images locally.

For the purposes of answer in the question, let's name the stored procedure sp_download_images
The file name being download is @filename
The local destination for the image is @filelocal
The remote http location for the image is @filehttp

I have written the cursor to get the image names, but I need assistance in coding the creation of the http object and invoking the object to download the file from the remote http location to the local destination.

Update:

Here is the ASP Equivalent of what I am trying to do in SQL.  I think all that needs to be done is use SP_OACreate and SP_OAMethod, but I am unsure of the method arguments for SP_OAMethods


      sSource = http://www.mygolfgear.com/products/iStourplusequilizer.jpg      sDest = C:\temp\iStourplusequilizer.jpg


      set oHTTP = Server.CreateObject("Microsoft.XMLHTTP")

      oHTTP.open "GET", sSource, False
      oHTTP.send

      
      set oStream = server.createobject("adodb.stream")
      Const adTypeBinary = 1
      Const adSaveCreateNotExist = 1
      Const adSaveCreateOverWrite = 2
      oStream.type = adTypeBinary
      oStream.open
      oStream.write oHTTP.responseBody
      oStream.savetofile sDest, adSaveCreateOverWrite
      
      
      set oStream = nothing
      set oHTTP = nothing


Help!
webagoAsked:
Who is Participating?
 
Melih SARICAConnect With a Mentor IT ManagerCommented:
Here is an solution,

It works fine if u wanna see response text. but cant make it write responsetext into the stream. Donno why ..

u try it..

-----
--Download HTTP by Melh SARICA
create Proc dbo._DownloadHTTP(
           @src varchar(400),
           @trg varchar(400)
)
as

declare @ohttp int
declare @ostream int
declare @ResponseBody binary
declare @result int

declare @adTypeBinary int
declare @adSaveCreateNotExist int
declare @adSaveCreateOverWrite int


set @adTypeBinary = 1
set @adSaveCreateNotExist = 1
set @adSaveCreateOverWrite = 2



set @src =N'http://www.microsoft.com'
set @trg =N'C:\temp\a.html'

exec @RESULT = SP_OACreate 'Microsoft.XMLHTTP', @ohttp out
  if @result = 0
      begin
            Print 'Http Object             : Created'
            exec @RESULT = sp_Oamethod @ohttp, 'OPEN',null, 'GET',@src,False
            if @result = 0 print 'Http Open             : Ok  Addr : ' + @src
            exec @RESULT = sp_Oamethod @ohttp, 'SEND'
            if @result = 0 print 'Http Send             : Ok'
            exec @RESULT = sp_Oamethod @ohttp,'RESPONSETEXT',@ResponseBody out
            if @result = 0 print 'Http Response             : Ok'      

            exec @RESULT = SP_OACreate 'AdoDb.Stream', @ostream out
            if @Result = 0
                  begin
                        Print 'Stream Object             : Created'
                        exec @RESULT = sp_OaSetProperty @ostream, 'TYPE',@adTypebinary
                        if @result = 0 print 'Stream Type Set            : Ok  Type : ' + str(@adTypebinary,1)
                        exec @RESULT = sp_Oamethod @ostream, 'OPEN'
                        if @result = 0 print 'Stream Open             : Ok'
                        exec @RESULT = sp_Oamethod @ostream,'write',@ResponseBody
                        if @result = 0
                              print 'Stream Write             : Ok'                                          
                        else
                              print 'Stream Write             : Error'                                                                  
                        exec @RESULT = sp_Oamethod @ostream,'SaveToFile',@trg,@adSaveCreateOverWrite
                        if @result = 0
                              print 'Stream Saved to File      : Ok  File : '+      @trg
                        else
                                 print 'Stream Saved to File      :   Error : '+      str(@result,15)

                  end

            EXEC sp_OADestroy @ostream
            EXEC sp_OADestroy @ohttp
           end

go
      
But i advice u to do it via DTS object. It would Works Fine...

How to do it wth DTS.

-- Table Creation script
Use NorthWind
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_Downloadimages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_Downloadimages]
GO

CREATE TABLE [dbo].[_Downloadimages] (
      [srchttp] [varchar] (400) COLLATE SQL_Latin1_General_CP1254_CI_AS NOT NULL ,
      [trgfile] [varchar] (400) COLLATE SQL_Latin1_General_CP1254_CI_AS NOT NULL ,
      [processed] [tinyint] NOT NULL
) ON [PRIMARY]
GO

-- DTS Script

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
dim iRs
dim mCOnn
dim mstream
dim mHttp
dim mData
set mconn = createobject("adodb.Connection")
set iRS = createobject("AdoDb.Recordset")
set mhttp =CreateObject("Microsoft.XMLHTTP")
mconn.open "Driver=SQL Server;server=localhost;Trusted COnnection=True;"
irs.open "select * from northwind.._Downloadimages where processed=0",mconn,1,3,1
set mstream = createObject("Adodb.Stream")
mstream.type= 1
while not irs.eof
  mstream.open
  mhttp.open "GET" , irs("srcHttp"),false
  mhttp.send
  mData = mhttp.Responsebody
  mstream.write mdata
  mstream.savetofile irs("trgfile"),2
  mstream.close
 irs.movenext
wend
irs.close
mconn.close
set mhttp= nothing
set mstream = nothing
set irs = nothing
set mconn = nothing


      Main = DTSTaskExecResult_Success
End Function


Melih SARICA
0
 
webagoAuthor Commented:
Melih,

Coding is exactly what I was looking for.

For some reason the sp_OAMethod  ostream 'Write' is getting an error.
Can you think of a reason?

Where in the open is it setting the argument for @trg  as the file to open.
0
 
Melih SARICAIT ManagerCommented:
Well if i must say the truth.. I dont know.. What is teh problem.. Maybe an Adressing Problem or A Bug not sure..

For that reason i did send u an activex script. for a DTS package..

Create an Activex DTS Object. Shedule it with a job.. And then Delete Job Shcedule and Run The Job When Ever U want with an SQL Command..

Command like msdb..sp_Start_Job '<JOB Name>'

0
All Courses

From novice to tech pro — start learning today.