SQL Server

Decode Base64 Image String (GIF, JPG, etc.) and Save to File

I have an XML response that includes a base 64 encoded image string (UPS label). It's a huge string and I need to save it as a gif file on my users pc.

This example shows how to do it..

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

    DECLARE @bd int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- I created an extra-small GIF image for this example..
    EXEC sp_OAMethod @bd, 'LoadFile', @success OUT, 'qa_data/gif/xsmall.gif'
    IF @success <> 1
      BEGIN

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

    -- Show GIF bytes as base64:
    EXEC sp_OAMethod @bd, 'GetEncoded', @sTmp0 OUT, 'base64'
    PRINT @sTmp0

    -- This particular GIF in base64 is this:
    -- R0lGODlhBQAFAMQAAAAAAP////z8/PHx8evr6+jo6OHh4d7e3sPDw8LCwpqamo2NjWxsbGRkZFpaWk1NTUtLS0hISCwsLCQkJP///wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAEAABQALAAAAAAFAAUAAAUTYCAYQUBAU1lI0TIgztE8jFImIQA7

    -- Let's begin with an XML string:
    DECLARE @x nvarchar(4000)
    SELECT @x = '<abc><def>R0lGODlhBQAFAMQAAAAAAP////z8/PHx8evr6+jo6OHh4d7e3sPDw8LCwpqamo2NjWxsbGRkZFpaWk1NTUtLS0hISCwsLCQkJP///wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAEAABQALAAAAAAFAAUAAAUTYCAYQUBAU1lI0TIgztE8jFImIQA7</def></abc>'

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @x

    -- Get the base64 string:
    DECLARE @gifBase64 nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @gifBase64 OUT, 'def'


    PRINT @gifBase64

    -- Load bd with the base64 decoded bytes.  
    EXEC sp_OAMethod @bd, 'Clear', @success OUT
    EXEC sp_OAMethod @bd, 'AppendEncoded', @success OUT, @gifBase64, 'base64'

    -- Save to a GIF file.
    EXEC sp_OAMethod @bd, 'WriteFile', @success OUT, 'qa_output/xsmall.gif'

    EXEC @hr = sp_OADestroy @bd
    EXEC @hr = sp_OADestroy @xml


END
GO