(SQL Server) Must-Match Patterns

You may restrict the spider to only follow links that match any one of a set of "must-match" wildcard patterns. The AddMustMatchPattern can be called repeatedly to add must-match patterns.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @spider int
    -- Use "Chilkat_9_5_0.Spider" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Spider', @spider OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- --------------------------------------------------------------------
    -- Note: The URLs in this example are no longer valid.
    -- You should replace the URLs with URLs from a site of your
    -- own choosing -- preferably your own site if testing.
    -- (Google's Directory no longer exists.)
    -- --------------------------------------------------------------------

    -- First, we'll get the outbound links for a page in the
    -- Google directory.  Then we'll add some must-match
    -- and then re-fetch, to see it work...

    EXEC sp_OAMethod @spider, 'Initialize', NULL, 'directory.google.com'
    EXEC sp_OAMethod @spider, 'AddUnspidered', NULL, 'http://directory.google.com/Top/Recreation/Outdoors/Hiking/Backpacking/'

    DECLARE @success int

    EXEC sp_OAMethod @spider, 'CrawlNext', @success OUT

    -- Display the outbound links
    DECLARE @i int

    DECLARE @url nvarchar(4000)

    EXEC sp_OAGetProperty @spider, 'NumOutboundLinks', @iTmp0 OUT
    SELECT @i = 0
    WHILE @i <= @iTmp0 - 1
      BEGIN
        EXEC sp_OAMethod @spider, 'GetOutboundLink', @sTmp0 OUT, @i
        PRINT @sTmp0
        SELECT @i = @i + 1
      END

    -- The output:
    -- http://www.backpacker.com
    -- http://www.cmc.org
    -- http://www.backpacking.net
    -- http://www.thebackpacker.com/
    -- http://www.rei.com/online/store/LearnShareArticlesList?categoryId=Camping
    -- http://www.trailspace.com/
    -- http://www.catskillhikes.com/
    -- http://gorp.away.com/gorp/location/asia/nepal/favpicks.htm
    -- http://www.backpackinglight.com/cgi-bin/backpackinglight/index.html
    -- http://www.yetizone.com/
    -- http://www.backpackingfun.com
    -- http://www.freezerbagcooking.com/
    -- http://www.spadout.com/backpacking/
    -- http://sierrabackpacker.com
    -- http://www.abovecalifornia.com/
    -- http://www.personal.psu.edu/faculty/r/p/rpc1/bbb/
    -- http://www.thebackpackersguide.com
    -- http://www.journeywest.com/WB/index.html
    -- http://www.johann-sandra.com/backpackdir.htm
    -- http://www.geocities.com/amytys/
    -- http://www.cloudwalkersbasecamp.com
    -- http://www.netbackpacking.com
    -- http://members.tripod.com/~stooges/
    -- http://www.thebackpackingsite.com
    -- http://www.thruhikers.com/
    -- http://www.redcompservices.com/AT/
    -- http://members.aol.com/CMorHiker/backpack
    -- http://mywebpages.comcast.net/midwestpacker/
    -- http://www.midwesthiker.com/
    -- http://www.WeBackpack.com
    -- http://www.michiganhiker.com
    -- http://www.host33.com/backpack/
    -- http://www.wilderness-backpacking.com
    -- http://www.thetravelmonkey.net
    -- http://dmoz.org/cgi-bin/add.cgi?where=Recreation/Outdoors/Hiking/Backpacking
    -- http://dmoz.org/about.html
    -- http://dmoz.org/cgi-bin/apply.cgi?where=Recreation/Outdoors/Hiking/Backpacking
    -- http://dmoz.org
    -- http://dmoz.org/profiles/cdog.html
    -- http://dmoz.org/profiles/justinwp.html

    -- Do it again, but this time with avoid patterns.
    EXEC sp_OAMethod @spider, 'Initialize', NULL, 'directory.google.com'
    EXEC sp_OAMethod @spider, 'AddUnspidered', NULL, 'http://directory.google.com/Top/Recreation/Outdoors/Hiking/Backpacking/'

    -- Add some must-match patterns:
    EXEC sp_OAMethod @spider, 'AddMustMatchPattern', NULL, '*.com/*'
    EXEC sp_OAMethod @spider, 'AddMustMatchPattern', NULL, '*.net/*'

    -- Add some avoid-patterns:
    EXEC sp_OAMethod @spider, 'AddAvoidOutboundLinkPattern', NULL, '*.mypages.*'
    EXEC sp_OAMethod @spider, 'AddAvoidOutboundLinkPattern', NULL, '*.personal.*'
    EXEC sp_OAMethod @spider, 'AddAvoidOutboundLinkPattern', NULL, '*.comcast.*'
    EXEC sp_OAMethod @spider, 'AddAvoidOutboundLinkPattern', NULL, '*.aol.*'
    EXEC sp_OAMethod @spider, 'AddAvoidOutboundLinkPattern', NULL, '*~*'

    EXEC sp_OAMethod @spider, 'CrawlNext', @success OUT


    PRINT '-----------------------'

    -- Display the outbound links
    EXEC sp_OAGetProperty @spider, 'NumOutboundLinks', @iTmp0 OUT
    SELECT @i = 0
    WHILE @i <= @iTmp0 - 1
      BEGIN
        EXEC sp_OAMethod @spider, 'GetOutboundLink', @sTmp0 OUT, @i
        PRINT @sTmp0
        SELECT @i = @i + 1
      END

    -- Output:
    -- http://www.thebackpacker.com/
    -- http://www.rei.com/online/store/LearnShareArticlesList?categoryId=Camping
    -- http://www.trailspace.com/
    -- http://www.catskillhikes.com/
    -- http://gorp.away.com/gorp/location/asia/nepal/favpicks.htm
    -- http://www.backpackinglight.com/cgi-bin/backpackinglight/index.html
    -- http://www.yetizone.com/
    -- http://www.freezerbagcooking.com/
    -- http://www.spadout.com/backpacking/
    -- http://www.abovecalifornia.com/
    -- http://www.journeywest.com/WB/index.html
    -- http://www.johann-sandra.com/backpackdir.htm
    -- http://www.geocities.com/amytys/
    -- http://www.thruhikers.com/
    -- http://www.redcompservices.com/AT/
    -- http://www.midwesthiker.com/
    -- http://www.host33.com/backpack

    EXEC @hr = sp_OADestroy @spider


END
GO