(SQL Server) Read/Write JSON with Binary Data such as JPEG Files

Demonstrates how binary files could be stored in JSON in base64 format. Creates JSON containing the contents of a JPG file, and then reads the JSON to extract the JPEG image.

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
    -- First load a small JPG file..
    DECLARE @bd int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bd OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @bd, 'LoadFile', @success OUT, 'qa_data/jpg/starfish20.jpg'
    -- Assume success, but your code should check for success..

    -- Create JSON containing the binary data in base64 format.
    DECLARE @json1 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json1 OUT

    EXEC sp_OAMethod @json1, 'UpdateBd', @success OUT, 'starfish', 'base64', @bd

    DECLARE @jsonStr nvarchar(4000)
    EXEC sp_OAMethod @json1, 'Emit', @jsonStr OUT

    PRINT @jsonStr

    -- Here's the output:
    -- {"starfish":"/9j/4AAQSkZJRgA ... cN2iuLFsCEbDGxQkI6RO/n//2Q=="}

    -- Let's create a new JSON object, load it with the above JSON, and extract the JPG image..
    DECLARE @json2 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json2 OUT

    EXEC sp_OAMethod @json2, 'Load', @success OUT, @jsonStr

    -- Get the binary bytes.
    DECLARE @bd2 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bd2 OUT

    EXEC sp_OAMethod @json2, 'BytesOf', @success OUT, 'starfish', 'base64', @bd2

    -- Save to a file.
    EXEC sp_OAMethod @bd2, 'WriteFile', @success OUT, 'qa_output/starfish20.jpg'


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @bd
    EXEC @hr = sp_OADestroy @json1
    EXEC @hr = sp_OADestroy @json2
    EXEC @hr = sp_OADestroy @bd2


END
GO