SQL Server

Read/Write JSON with Binary Data such as JPEG Files

See more JSON Examples

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. 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
    DECLARE @success int
    SELECT @success = 0

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

    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.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.JsonObject', @json2 OUT

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

    -- Get the binary bytes.
    DECLARE @bd2 int
    EXEC @hr = sp_OACreate 'Chilkat.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