(SQL Server) Add File Attachments to a PDF

See more PDF Signatures Examples

Demonstrates how to attach files to a PDF. This is also known as embedding a file within a PDF.

Note: This example requires Chilkat v9.5.0.97 or greater.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @pdf int
    -- Use "Chilkat_9_5_0.Pdf" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Pdf', @pdf OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- We'll be adding attachments to this PDF file..
    -- Note: We are loading the PDF file into memory.  The PDF file is not kept open at this point.
    DECLARE @success int
    EXEC sp_OAMethod @pdf, 'LoadFile', @success OUT, 'qa_data/pdf/helloWorld.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        RETURN
      END

    -- Note: The ability to attach files to a PDF was added in Chilkat v9.5.0.97

    -- Build JSON to provide information about the files to be embedded in the PDF.
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    DECLARE @i int
    SELECT @i = 0

    -- We can attach multiple files in one operation.

    -- Here we specify the local relative file path of the 1st file to be embedded.
    EXEC sp_OASetProperty @json, 'I', @i
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'files[i].description', 'Hello World'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'files[i].localFilePath', 'qa_data/xml/helloWorld.xml'
    SELECT @i = @i + 1

    -- Specify the 2nd file to be attached.
    EXEC sp_OASetProperty @json, 'I', @i
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'files[i].description', 'Image of starfish'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'files[i].localFilePath', 'qa_data/jpg/starfish.jpg'
    -- You can explicitly specify the subType (i.e. MIME type) of the file.
    -- Otherwise Chilkat will use the default MIME type based on the filename extension.
    -- If no default MIME type exists, then "application/octet-stream" is used.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'files[i].subType', 'image/jpeg'
    SELECT @i = @i + 1

    -- You can alternatively provide the file data from base64 encoded data rather than from a local file.
    EXEC sp_OASetProperty @json, 'I', @i
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'files[i].description', 'Hello World from Data'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'files[i].fileData', 'SGVsbG8gV29ybGQsIEhlbGxvIFdvcmxkLCBIZWxsbyBXb3JsZCwgSGVsbG8gV29ybGQsIEhlbGxvIFdvcmxk'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'files[i].embeddedFilename', 'abc.txt'
    SELECT @i = @i + 1

    -- By default, the filename used within the PDF is the filename part of the local file path.
    -- You can change it by specifying the embeddedFilename.
    EXEC sp_OASetProperty @json, 'I', @i
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'files[i].description', 'Invoice'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'files[i].localFilePath', 'qa_data/xml/invoice.xml'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'files[i].embeddedFilename', 'invoice_1234.xml'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'files[i].subType', 'application/xml'

    -- The above JSON provides instructions for attaching 4 files to the PDF.
    -- Let's attach the files..
    -- It is perfectly acceptable to write over the PDF file that was loaded,
    -- but in this example we'll write the PDF with attachments to a new PDF file.
    EXEC sp_OAMethod @pdf, 'AddEmbeddedFiles', @success OUT, @json, 'qa_output/helloWorld_withAttachments.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- You can alternatively write the PDF file with attachments to a Chilkat BinData object..
    DECLARE @bd int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT

    EXEC sp_OAMethod @pdf, 'AddEmbeddedFilesBd', @success OUT, @json, @bd
    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 @bd
        RETURN
      END
    -- Then you can do what you want with the BinData, which contains the binary image of the PDF with attachments.


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @pdf
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @bd


END
GO