Using MSXML2.ServerXMLHTTP within Stored Procedure To Grab Source of HTML Page and save to table

  • smhunt

    Mr or Mrs. 500

    Points: 576

    I'm trying to use 'MSXML2.ServerXMLHTTP' though extended store procedures to retrieve the html source of parsed asp pages to be stored into a database for later use in a mail que system

    When I execute the following code in Query Analyzer the  @vResponseText variable is returned NULL

    DECLARE

     @vPointer INT,

     @vResponseText VARCHAR(8000),

     @vStatus INT,

     @vStatusText VARCHAR(200)

    EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @vPointer OUTPUT

    EXEC sp_OAMethod @vPointer, 'open', NULL, 'GET', 'http://tlcpet.com/default.asp'

    EXEC sp_OAMethod @vPointer, 'send'

    EXEC sp_OAMethod @vPointer, 'responseText', @vResponseText OUTPUT

    EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT

    EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT

    EXEC sp_OADestroy @vPointer

    Select @vStatus, @vStatusText, @vResponseText

    However, when i execute the following code (omiiting the output variable and selecting 'responseText' directly) I get the source of the page as intended (Not NULL)

    DECLARE

     @vPointer INT,

     @vResponseText VARCHAR(8000),

     @vStatus INT,

     @vStatusText VARCHAR(200)

    EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @vPointer OUTPUT

    EXEC sp_OAMethod @vPointer, 'open', NULL, 'GET', 'http://tlcpet.com/default.asp'

    EXEC sp_OAMethod @vPointer, 'send'

    EXEC sp_OAMethod @vPointer, 'responseText' --, @vResponseText OUTPUT

    EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT

    EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT

    EXEC sp_OADestroy @vPointer

    Select @vStatus, @vStatusText, @vResponseText

    I need to be able to 'responseText'  into the OUTPUT variable @vResponseText so that I can then insert it into a table

    Can someone see where I'm going wrong? XMLHttp IS properly installed and functioning (the latter example and many other applications on this server use the object succesfully)

     

    Thank in advance, I'm stumped on this

  • Brian Hinken

    SSC Journeyman

    Points: 86

    EXEC sp_OAGetProperty @vPointer, 'responseText', @vResponseText OUTPUT

    Do the same for 'Status' and 'StatusText'

  • smhunt

    Mr or Mrs. 500

    Points: 576

    Hi there thanks for the suggestion, but no success....

    Have you tried the code you suggested in query analyzer on you machine and got it to work?

    In my orginal example Do the same for 'Status' and 'StatusText' work properly using EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT

    Its only the response Text line that doesnt work

    I get a NULL value returned for responseText both ways (using sp_OAMethod and sp_OAGetProperty)

    intrestingly I also sucessfully get a correct value 'Status' and 'StatusText' using using sp_OAMethod or sp_OAGetProperty

    any further ideas on this one?

  • ignas

    SSC Journeyman

    Points: 83

    Had the same problem with the following code >>

    Funny thing is that if I use @response varchar(MAX) instead of @response varchar(8000) it will fail and tohing will be returned

    Suggestion to play with a variable space in your own code there might be some lmitations ...

    this one works for me

    CREATE procedure HTTP_POST( @sUrl varchar(200), @response varchar(8000) out, @error varchar(100) out)

    As

    Declare

    @obj int

    ,@hr int

    ,@status int

    ,@msg varchar(255)

    exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT

    -- exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT

    if @hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0

    failed', 16,1) return end

    exec @hr = sp_OAMethod @obj, 'open', NULL, 'GET', @sUrl, false

    if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end

    exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type',

    'application/x-www-form-urlencoded'

    if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed' goto

    eh end

    exec @hr = sp_OAMethod @obj, send, NULL, ''

    if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end

    exec @hr = sp_OAGetProperty @obj, 'status', @status OUT

    if @hr <>0 begin set @msg = 'sp_OAMethod read status failed' goto

    eh

    end

    if @status <> 200 begin set @msg = 'sp_OAMethod http status ' +

    str(@status) goto eh end

    exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT

    if @hr <>0 begin set @msg = 'sp_OAMethod read response failed' goto

    eh end

    exec @hr = sp_OADestroy @obj

    return

    eh:

    exec @hr = sp_OADestroy @obj

    set @error = @msg

    return

    GO

  • jonathan.smith

    SSC Journeyman

    Points: 85

    Many Thanks, this solution saved my bacon!!!

  • sandeep1k.mishra

    Valued Member

    Points: 59

    Thanks it's working fine

    :-):-):-):-):-):-)

  • francesco.mantovani

    Right there with Babe

    Points: 789

    14 years have passed, it's time to update the reply.
    Also, this query can:

    1) catch the error
    2) put the REST call response into a string
    3) parse the JSON and put it into a table
    4) fix the problem with the NVARCHAR(MAX) and it's now storing till 2GB of size

    Declare @Object as Int;
    DECLARE @hr int
    Declare @json as table(Json_Table nvarchar(max))

    Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
    Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',
          'http://overpass-api.de/api/interpreter?data=[out:json];area[name=%22Auckland%22]-%3E.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;', --Your Web Service Url (invoked)
          'false'
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
    Exec @hr=sp_OAMethod @Object, 'send'
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
    Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

    INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
    -- select the JSON string
    select * from @json
    -- Parse the JSON string
    SELECT * FROM OPENJSON((select * from @json), N'$.elements')
    WITH ( 
      [type] nvarchar(max) N'$.type' ,
      [id] nvarchar(max) N'$.id',
      [lat] nvarchar(max) N'$.lat',
      [lon] nvarchar(max) N'$.lon',
      [amenity] nvarchar(max) N'$.tags.amenity',
      [name] nvarchar(max) N'$.tags.name' 
    )
    EXEC sp_OADestroy @Object

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply