SQL Server
PDF Signature Appearance Text Substitution Keywords

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 SQL Server Downloads

SQL Server
-- 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)
    DECLARE @success int
    SELECT @success = 0

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

    DECLARE @pdf int
    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.
    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
    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
    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
    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:

    -- (image:https://example-code.com/images/pdf_signature_text_replacements.jpg/endImage)

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


END
GO