SQL Server Requires Chilkat v11.7.0+

Get Every Occurrence of a Label in a PDF

See more PDF Text Extraction Examples

Demonstrates the "labelOccurrence" option. When the same label appears more than once (for example "Total:" on each of several receipts), "first" (the default) uses the first occurrence, "all" returns every occurrence with its page, and a number selects one occurrence. SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 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 PDF used in this example (three receipts, one per page) is available at three_receipts.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/three_receipts.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

    --  Each of the three pages is a receipt with a "Total:" line.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'label', 'Total'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'labelValue', 'right'

    --  By default only the first occurrence (in reading order) is used.
    EXEC sp_OAMethod @pdf, 'ExtractText', @success OUT, 'text', @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, 'StringOf', @sTmp0 OUT, 'text'
    PRINT 'First total: ' + @sTmp0

    --  labelOccurrence "all" returns every occurrence.  The "text" member holds the values joined
    --  by line breaks, and labelMatches has one entry per occurrence with its page.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'labelOccurrence', 'all'
    EXEC sp_OAMethod @pdf, 'ExtractText', @success OUT, 'text', @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

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

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

        EXEC sp_OAMethod @jsonOut, 'StringOf', @sTmp0 OUT, 'labelMatches[i].text'
        PRINT 'Page ' + @iTmp0 + ' total: ' + @sTmp0
        SELECT @i = @i + 1
      END

    --  A number selects one occurrence: 2 is the second.
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'labelOccurrence', 2
    EXEC sp_OAMethod @pdf, 'ExtractText', @success OUT, 'text', @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, 'StringOf', @sTmp0 OUT, 'text'
    PRINT 'Second total: ' + @sTmp0

    --  The occurrences are counted over the selected pages, so combining "pages" with the label
    --  is another way to pick one receipt:
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'labelOccurrence', 'first'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'pages', '3'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'label', 'Receipt No.'
    EXEC sp_OAMethod @pdf, 'ExtractText', @success OUT, 'text', @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, 'StringOf', @sTmp0 OUT, 'text'
    PRINT 'Receipt number on page 3: ' + @sTmp0

    --  Sample output:

    --  First total: $19.86
    --  Page 1 total: $19.86
    --  Page 2 total: $17.34
    --  Page 3 total: $23.62
    --  Second total: $17.34
    --  Receipt number on page 3: 1003

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


END
GO