SQL Server Requires Chilkat v11.7.0+

Replace the Value After a Label in a PDF (Re-issue an Invoice)

See more PDF Text Replacement Examples

Demonstrates replacing the values that follow labels such as "Invoice Number:" on a PDF that has no placeholders -- an ordinary invoice. Each entry of the "labels" array names a label as it appears on the page and the new value; the value replaces the rest of the label's line. Label matching ignores case, extra spaces and a trailing colon. The replaced text is read back with ExtractText to confirm it. SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @sTmp1 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 is available at invoice_labels.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/invoice_labels.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        RETURN
      END

    --  The invoice has lines such as "Invoice Number:   INV-2026-0412" and "Total Due:  $1,234.53".
    --  Each label's new value replaces whatever follows the label on its line.
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'labels[0].label', 'Invoice Number:'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'labels[0].value', 'INV-2026-0500'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'labels[1].label', 'Invoice Date:'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'labels[1].value', 'April 2, 2026'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'labels[2].label', 'customer'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'labels[2].value', 'Contoso Ltd'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'labels[3].label', 'Total Due:'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'labels[3].value', '$1,999.99'

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

    EXEC sp_OAMethod @pdf, 'TextReplace', @success OUT, 'apply', @json, @jsonOut
    IF @success = 0
      BEGIN

        EXEC sp_OAMethod @jsonOut, 'Emit', @sTmp0 OUT
        PRINT 'Not applied: ' + @sTmp0
        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
      END

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

        EXEC sp_OAMethod @jsonOut, 'StringOf', @sTmp0 OUT, 'replacements[i].label'

        EXEC sp_OAMethod @jsonOut, 'StringOf', @sTmp1 OUT, 'replacements[i].pages'
        PRINT 'Replaced the value of "' + @sTmp0 + '" on page ' + @sTmp1
        SELECT @i = @i + 1
      END

    EXEC sp_OAMethod @pdf, 'WriteFile', @success OUT, 'qa_output/invoice_reissued.pdf'
    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

    --  Read the new values back with ExtractText's label option.
    DECLARE @jsonRead int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonRead OUT

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

    EXEC sp_OAMethod @jsonRead, 'UpdateString', @success OUT, 'label', 'Total Due'
    EXEC sp_OAMethod @jsonRead, 'UpdateString', @success OUT, 'labelValue', 'right'
    EXEC sp_OAMethod @pdf, 'ExtractText', @success OUT, 'text', @jsonRead, @jsonText
    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
        EXEC @hr = sp_OADestroy @jsonRead
        EXEC @hr = sp_OADestroy @jsonText
        RETURN
      END

    EXEC sp_OAMethod @jsonText, 'StringOf', @sTmp0 OUT, 'text'
    PRINT 'Total Due is now: ' + @sTmp0

    --  Sample output:

    --  Replaced the value of "Invoice Number:" on page 1
    --  Replaced the value of "Invoice Date:" on page 1
    --  Replaced the value of "customer" on page 1
    --  Replaced the value of "Total Due:" on page 1
    --  Total Due is now: $1,999.99

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


END
GO