Solved

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

Posted on 2004-10-07
4
675 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

691 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