(SQL Server) Load .crl, Convert to XML, Get Revoked Serial Numbers and Dates

Load a binary .crl file (Certificate Revocation List), converts to XML, and then gets the revoked certificate serial numbers and revocation dates.

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

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

    DECLARE @success int
    EXEC sp_OAMethod @bdCrl, 'LoadFile', @success OUT, 'qa_data/crl/ca1.crl'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load CRL file.'
        EXEC @hr = sp_OADestroy @bdCrl
        RETURN
      END

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

    EXEC sp_OAMethod @asn, 'LoadBd', @success OUT, @bdCrl
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @asn, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @bdCrl
        EXEC @hr = sp_OADestroy @asn
        RETURN
      END

    -- Convert ASN.1 to XML and load into xml and re-emit for pretty printing..
    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_OAMethod @asn, 'AsnToXml', @sTmp0 OUT
    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @sTmp0
    EXEC sp_OAMethod @xml, 'SaveXml', @success OUT, 'qa_output/crl.xml'

    -- Use this online tool to generate parsing code from CRL XML: 
    -- Generate Parsing Code from XML

    -- Here's code to parse the XML.  This code was generated by the above tool,
    -- and then we modified it by removing unneeded code and changing some names
    DECLARE @i int

    DECLARE @count_i int

    DECLARE @tagPath nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @k int

    DECLARE @count_k int

    DECLARE @revokedCertSerialHex nvarchar(4000)

    DECLARE @dateRevoked nvarchar(4000)

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

    SELECT @i = 0
    EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @count_i OUT, 'sequence'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @xml, 'I', @i

        SELECT @j = 0
        EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @count_j OUT, 'sequence[i]|sequence'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @xml, 'J', @j

            SELECT @k = 0
            EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @count_k OUT, 'sequence[i]|sequence[j]|sequence'
            WHILE @k < @count_k
              BEGIN
                EXEC sp_OASetProperty @xml, 'K', @k

                -- Get the revoked certificate's serial number in uppercase hex.
                EXEC sp_OAMethod @xml, 'GetChildContent', @revokedCertSerialHex OUT, 'sequence[i]|sequence[j]|sequence[k]|int'

                PRINT 'serial number: ' + @revokedCertSerialHex

                -- Get the date/time revoked.  It will be a string formatted as "YYMMDDhhmmssZ", such as "181023093028Z"
                EXEC sp_OAMethod @xml, 'GetChildContent', @dateRevoked OUT, 'sequence[i]|sequence[j]|sequence[k]|utctime'

                -- Starting in Chilkat v9.5.0.77, date/time strings formatted as YYMMDDhhmmssZ can be parsed as follows:
                EXEC sp_OAMethod @dt, 'SetFromTimestamp', @success OUT, @dateRevoked

                EXEC sp_OAMethod @dt, 'GetAsRfc822', @sTmp0 OUT, 0
                PRINT 'date revoked: ' + @sTmp0

                SELECT @k = @k + 1
              END
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @bdCrl
    EXEC @hr = sp_OADestroy @asn
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @dt


END
GO