(SQL Server) Combine Timestamp Reply and Content File into a TimeStampData

Demonstrates how to combine a timestamp reply (RFC 3161) with a data file (any type of file) to create a TimeStampData structure (RFC 5544).

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.

    -- Also see this example:
    -- Send a TimeStamp Request to a TimeStamp Authority and get the Response

    -- Load a timestamp reply file
    DECLARE @bdTsr int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdTsr OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @bdTsr, 'LoadFile', @success OUT, 'qa_data/tsd/sample.tsr'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load timestamp reply file.'
        EXEC @hr = sp_OADestroy @bdTsr
        RETURN
      END

    -- Load the tsr into an ASN.1 object.
    DECLARE @asnTsr int
    -- Use "Chilkat_9_5_0.Asn" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Asn', @asnTsr OUT

    EXEC sp_OAMethod @bdTsr, 'GetEncoded', @sTmp0 OUT, 'base64'
    EXEC sp_OAMethod @asnTsr, 'LoadEncoded', @success OUT, @sTmp0, 'base64'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @asnTsr, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @bdTsr
        EXEC @hr = sp_OADestroy @asnTsr
        RETURN
      END

    -- Get the timestamp reply as XML.
    DECLARE @xmlTsr int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlTsr OUT

    EXEC sp_OAMethod @asnTsr, 'AsnToXml', @sTmp0 OUT
    EXEC sp_OAMethod @xmlTsr, 'LoadXml', @success OUT, @sTmp0

    -- The timestamp reply XML begins like this:
    -- We'll want to remove the 1st "sequence" subtree.

    --     <?xml version="1.0" encoding="utf-8"?>
    --     <sequence>
    --         <sequence>       <---- Remove this sub-tree.
    --             <int>00</int>
    --             <sequence>
    --                 <utf8>Operation Okay</utf8>
    --             </sequence>
    --         </sequence>
    --         <sequence>   
    --             <oid>1.2.840.113549.1.7.2</oid>
    --             <contextSpecific tag="0" constructed="1">
    --             ...

    -- Remove the 1st sub-tree..
    EXEC sp_OAMethod @xmlTsr, 'RemoveChildByIndex', NULL, 0

    -- In this example, the data file we're combining with the timestamp into a timestampData is a .p7m.
    -- However, it can be any type of file (text or binary), it doesn't matter..
    DECLARE @bdContent int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdContent OUT

    EXEC sp_OAMethod @bdContent, 'LoadFile', @success OUT, 'qa_data/tsd/sample.p7m'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load content file.'
        EXEC @hr = sp_OADestroy @bdTsr
        EXEC @hr = sp_OADestroy @asnTsr
        EXEC @hr = sp_OADestroy @xmlTsr
        EXEC @hr = sp_OADestroy @bdContent
        RETURN
      END

    -- Begin building the TimeStampData.  
    -- We'll build as XML and then convert to ASN.1
    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OASetProperty @xml, 'Tag', 'sequence'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'oid', '1.2.840.113549.1.9.16.1.31'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'contextSpecific', 1, 'tag', '0'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'contextSpecific', 1, 'constructed', '1'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'contextSpecific|sequence|int', '01'
    EXEC sp_OAMethod @bdContent, 'GetEncoded', @sTmp0 OUT, 'base64'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'contextSpecific|sequence|octets', @sTmp0
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'contextSpecific|sequence|contextSpecific', 1, 'tag', '0'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'contextSpecific|sequence|contextSpecific', 1, 'constructed', '1'

    DECLARE @xContext int
    EXEC sp_OAMethod @xml, 'GetChildWithTag', @xContext OUT, 'contextSpecific|sequence|contextSpecific'
    EXEC sp_OAMethod @xContext, 'AddChildTree', @success OUT, @xmlTsr
    EXEC @hr = sp_OADestroy @xContext

    -- Convert the XML to ASN.1
    DECLARE @tsd int
    -- Use "Chilkat_9_5_0.Asn" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Asn', @tsd OUT

    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @tsd, 'LoadAsnXml', @success OUT, @sTmp0

    -- Write the timestamped data to a file.
    EXEC sp_OAMethod @tsd, 'WriteBinaryDer', @success OUT, 'qa_output/sample.tsd'

    -- Alternatively, get the tsd ASN.1 as base64..
    DECLARE @tsdBase64 nvarchar(4000)
    EXEC sp_OAMethod @tsd, 'GetEncodedDer', @tsdBase64 OUT, 'base64_mime'

    PRINT @tsdBase64

    EXEC @hr = sp_OADestroy @bdTsr
    EXEC @hr = sp_OADestroy @asnTsr
    EXEC @hr = sp_OADestroy @xmlTsr
    EXEC @hr = sp_OADestroy @bdContent
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @tsd


END
GO