SQL Server Requires Chilkat v11.7.0+

Search for Text in a PDF

See more PDF Text Extraction Examples

Demonstrates the "search" operation, which finds every occurrence of a string in a PDF and returns each match with its page number and the text around it. Matching ignores case unless caseSensitive is true. SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @sTmp1 nvarchar(4000)
    DECLARE @sTmp2 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  This example requires the Chilkat API to have been previously unlocked.
    --  See Global Unlock Sample for sample code.

    --  The three-page PDF used in this example is available at search_context.pdf

    DECLARE @pdf int
    EXEC @hr = sp_OACreate 'Chilkat.Pdf', @pdf OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @pdf, 'LoadFile', @success OUT, 'qa_data/pdf/search_context.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        RETURN
      END

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    DECLARE @jsonOut int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonOut OUT

    --  The string to find.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'query', 'chilkat'

    --  The number of characters returned before and after each match.  The default is 40.
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'context', 30

    EXEC sp_OAMethod @pdf, 'ExtractText', @success OUT, 'search', @json, @jsonOut
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonOut
        RETURN
      END

    --  "count" is the number of matches.  Each match has the page, the matched text as it appears
    --  in the PDF, and the text before and after it.

    EXEC sp_OAMethod @jsonOut, 'IntOf', @iTmp0 OUT, 'count'
    PRINT 'Matches: ' + @iTmp0

    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jsonOut, 'SizeOfArray', @count_i OUT, 'matches'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jsonOut, 'I', @i

        EXEC sp_OAMethod @jsonOut, 'IntOf', @iTmp0 OUT, 'matches[i].page'

        EXEC sp_OAMethod @jsonOut, 'StringOf', @sTmp0 OUT, 'matches[i].before'

        EXEC sp_OAMethod @jsonOut, 'StringOf', @sTmp1 OUT, 'matches[i].text'

        EXEC sp_OAMethod @jsonOut, 'StringOf', @sTmp2 OUT, 'matches[i].after'

        PRINT 'page ' + @iTmp0 + ': ...' + @sTmp0 + '[' + @sTmp1 + ']' + @sTmp2 + '...'
        SELECT @i = @i + 1
      END

    --  The search ignores case by default.  Set caseSensitive to find only the exact spelling.
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'caseSensitive', 1
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'query', 'Chilkat'
    EXEC sp_OAMethod @pdf, 'ExtractText', @success OUT, 'search', @json, @jsonOut
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonOut
        RETURN
      END

    EXEC sp_OAMethod @jsonOut, 'IntOf', @iTmp0 OUT, 'count'
    PRINT 'Case-sensitive matches for Chilkat: ' + @iTmp0

    --  Sample output:

    --  Matches: 5
    --  page 1: ...[Chilkat] River Field Notes The Chilkat...
    --  page 1: ...Chilkat River Field Notes The [Chilkat] River runs for about fifty mi...
    --  page 2: ...DAY ONE: UPPER [CHILKAT] FLATS Clear and cold, minus e...
    --  page 2: ...he river is sometimes spelled [chilkat] in older records, and the val...
    --  page 3: ...y because the fish run on the [Chilkat] was thinning. Two tagged bird...
    --  Case-sensitive matches for Chilkat: 3

    EXEC @hr = sp_OADestroy @pdf
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @jsonOut


END
GO