(SQL Server) PDF Signature Appearance Text Substitution Keywords

See more PDF Signatures Examples

This example demonstrates the text substitution keywords that can be used in the PDF signature's appearance text.

Note: This example requires Chilkat v9.5.0.85 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

    -- Load a PDF to be signed.
    DECLARE @success int
    EXEC sp_OAMethod @pdf, 'LoadFile', @success OUT, 'qa_data/pdf/empty.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        RETURN
      END

    -- Options for signing are specified in JSON.
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    -- In most cases, the signingCertificateV2 and signingTime attributes are required.
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'signingCertificateV2', 1
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'signingTime', 1

    -- Put the signature on page 1, top left
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'page', 1
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.y', 'top'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.x', 'left'

    -- Use a font scale of 7.0
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.fontScale', '7.0'

    -- The appearance of the PDF signature can contain any number of text lines.
    -- Chilkat has defined a number of special keywords that can be used in any text line.
    -- Each keyword is replaced with the actual value.
    -- For example, "cert_cn" is replaced with the certificate subject's common name (CN).

    -- Here we are creating a signature with many lines of text, to demonstrate each
    -- keyword.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[0]', 'Date/time in PDF format: current_dt'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[1]', 'Current Local date/time: current_datetime'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[2]', 'GMT Timestamp: current_timestamp_gmt'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[3]', 'Local Timestamp: current_timestamp_local'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[4]', 'GMT RFC822 date/time: current_rfc822_dt_gmt'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[5]', 'Local RFC822 date/time: current_rfc822_dt_local'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[6]', 'Cert DN (Distinguished Name): cert_dn'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[7]', 'Cert Common Name: cert_cn'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[8]', 'Cert Organizational ID (2.5.4.97): cert_org_id'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[9]', 'Cert Email: cert_email'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[10]', 'Cert Organization: cert_organization'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[11]', 'Cert Organziational Unit: cert_ou'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[12]', 'Cert State: cert_state'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[13]', 'Cert Country: cert_country'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[14]', 'Cert Locality: cert_locality'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[15]', 'Cert Serial Number (hex): cert_serial_hex'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[16]', 'Cert Serial Number (decimal): cert_serial_dec'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[17]', 'Cert Thumbprint: cert_thumbprint'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[18]', 'Cert Issuer Common Name: cert_issuer_cn'

    DECLARE @jpgData int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @jpgData OUT

    EXEC sp_OAMethod @jpgData, 'LoadFile', @success OUT, 'qa_data/jpg/seal1.jpg'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load the JPG image.'
        EXEC @hr = sp_OADestroy @pdf
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jpgData
        RETURN
      END
    EXEC sp_OAMethod @pdf, 'SetSignatureJpeg', @success OUT, @jpgData
    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 @jpgData
        RETURN
      END

    -- Specify that we want to display the JPG image in the center with 33% opacity.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.image', 'custom-jpg'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.imagePlacement', 'center'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.imageOpacity', '33'

    -- Load the signing certificate. (Use your own certificate.)
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/myPdfSigningCert.pfx', 'pfx_password'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jpgData
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Tell the pdf object to use the certificate for signing.
    EXEC sp_OAMethod @pdf, 'SetSigningCert', @success OUT, @cert
    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 @jpgData
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    EXEC sp_OAMethod @pdf, 'SignPdf', @success OUT, @json, 'qa_output/hello_signed.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 @jpgData
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END


    PRINT 'The PDF has been successfully cryptographically signed.'

    -- The appearance of the signature appears in Adobe Acrobat as shown here:

    

    EXEC @hr = sp_OADestroy @pdf
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @jpgData
    EXEC @hr = sp_OADestroy @cert


END
GO