Solved

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

Posted on 2004-10-07
4
645 Views
Last Modified: 2008-01-09
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!
0
Comment
Question by:webago
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
Melih SARICA earned 500 total points
ID: 12256957
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
 

Author Comment

by:webago
ID: 12259749
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
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 12274004
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

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now