Solved

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

Posted on 2004-10-07
4
640 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 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

12 Experts available now in Live!

Get 1:1 Help Now