(SQL Server) Convert any File to Base64 (and back)

Demonstrates how to get the contents of any file as a base64 string, and then write it back.

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
    DECLARE @bd int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- This example will load a PDF and return it as a base64 string.
    DECLARE @success int
    EXEC sp_OAMethod @bd, 'LoadFile', @success OUT, 'qa_data/pdf/helloWorld.pdf'
    IF @success <> 1
      BEGIN

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

    DECLARE @b64Str nvarchar(4000)
    EXEC sp_OAMethod @bd, 'GetEncoded', @b64Str OUT, 'base64'

    PRINT @b64Str

    -- Now write the base64 string back to the binary PDF file:
    DECLARE @bd2 int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd2 OUT

    EXEC sp_OAMethod @bd2, 'AppendEncoded', @success OUT, @b64Str, 'base64'
    EXEC sp_OAMethod @bd2, 'WriteFile', @success OUT, 'qa_output/helloWorld.pdf'

    EXEC @hr = sp_OADestroy @bd
    EXEC @hr = sp_OADestroy @bd2


END
GO