(SQL Server) Using Pre-defined JSON Templates

Demonstrates how to predefine a JSON template, and then use it to emit JSON with variable substitutions.

Note: This example requires Chilkat v9.5.0.67 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)
    -- One way to create JSON is to do it in a straightforward manner:
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    DECLARE @success int
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'id', '0001'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'type', 'donut'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'name', 'Cake'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'image.url', 'images/0001.jpg'
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'image.width', 200
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'image.height', 200
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'thumbnail.url', 'images/thumbnails/0001.jpg'
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'thumbnail.width', 32
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'thumbnail.height', 32
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- The JSON created by the above code:

    -- 	{ 
    -- 	  "id": "0001",
    -- 	  "type": "donut",
    -- 	  "name": "Cake",
    -- 	  "image": { 
    -- 	    "url": "images/0001.jpg",
    -- 	    "width": 200,
    -- 	    "height": 200
    -- 	  },
    -- 	  "thumbnail": { 
    -- 	    "url": "images/thumbnails/0001.jpg",
    -- 	    "width": 32,
    -- 	    "height": 32
    -- 	  }
    -- 	}

    -- An alternative is to predefine a template, and then use it to emit with variable substitutions.
    -- For example:

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

    EXEC sp_OAMethod @jsonTemplate, 'UpdateString', @success OUT, 'id', '{$id}'
    EXEC sp_OAMethod @jsonTemplate, 'UpdateString', @success OUT, 'type', 'donut'
    EXEC sp_OAMethod @jsonTemplate, 'UpdateString', @success OUT, 'name', '{$name}'
    EXEC sp_OAMethod @jsonTemplate, 'UpdateString', @success OUT, 'image.url', '{$imageUrl}'
    -- The "i." indicates that it's an integer variable.
    EXEC sp_OAMethod @jsonTemplate, 'UpdateString', @success OUT, 'image.width', '{$i.imageWidth}'
    EXEC sp_OAMethod @jsonTemplate, 'UpdateString', @success OUT, 'image.height', '{$i.imageHeight}'
    EXEC sp_OAMethod @jsonTemplate, 'UpdateString', @success OUT, 'thumbnail.url', '{$thumbUrl}'
    EXEC sp_OAMethod @jsonTemplate, 'UpdateString', @success OUT, 'thumbnail.width', '{$i.thumbWidth}'
    EXEC sp_OAMethod @jsonTemplate, 'UpdateString', @success OUT, 'thumbnail.height', '{$i.thumbHeight}'
    -- Give this template a name.
    EXEC sp_OAMethod @jsonTemplate, 'Predefine', @success OUT, 'donut'

    -- --------------------------------------------------------------------------
    -- OK, the template is defined.  Defining a template can be done once
    -- at the start of your program, and you can discard the jsonTemplate object (it
    -- doesn't need to stick around..)

    -- Now we can create instances of the JSON object by name:
    DECLARE @jsonDonut int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonDonut OUT

    EXEC sp_OASetProperty @jsonDonut, 'EmitCompact', 0
    EXEC sp_OAMethod @jsonDonut, 'LoadPredefined', @success OUT, 'donut'
    EXEC sp_OAMethod @jsonDonut, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- The output is this:

    -- 	{ 
    -- 	  "id": "{$id}",
    -- 	  "type": "donut",
    -- 	  "name": "{$name}",
    -- 	  "image": { 
    -- 	    "url": "{$imageUrl}",
    -- 	    "width": "{$i.imageWidth}",
    -- 	    "height": "{$i.imageHeight}"
    -- 	  },
    -- 	  "thumbnail": { 
    -- 	    "url": "{$thumbUrl}",
    -- 	    "width": "{$i.thumbWidth}",
    -- 	    "height": "{$i.thumbHeight}"
    -- 	  }
    -- 	}

    -- Finally, we can substitute variables like this:
    DECLARE @donutValues int
    -- Use "Chilkat_9_5_0.Hashtable" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Hashtable', @donutValues OUT

    EXEC sp_OAMethod @donutValues, 'AddStr', @success OUT, 'id', '0001'
    EXEC sp_OAMethod @donutValues, 'AddStr', @success OUT, 'name', 'Cake'
    EXEC sp_OAMethod @donutValues, 'AddStr', @success OUT, 'imageUrl', 'images/0001.jpg'
    EXEC sp_OAMethod @donutValues, 'AddInt', @success OUT, 'imageWidth', 200
    EXEC sp_OAMethod @donutValues, 'AddInt', @success OUT, 'imageHeight', 200
    EXEC sp_OAMethod @donutValues, 'AddStr', @success OUT, 'thumbUrl', 'images/thumbnails/0001.jpg'
    EXEC sp_OAMethod @donutValues, 'AddInt', @success OUT, 'thumbWidth', 32
    EXEC sp_OAMethod @donutValues, 'AddInt', @success OUT, 'thumbHeight', 32

    -- Emit with variable substitutions:
    DECLARE @omitEmpty int
    SELECT @omitEmpty = 1
    EXEC sp_OAMethod @jsonDonut, 'EmitWithSubs', @sTmp0 OUT, @donutValues, @omitEmpty
    PRINT @sTmp0

    -- Output:

    -- 	{ 
    -- 	  "id": "0001",
    -- 	  "type": "donut",
    -- 	  "name": "Cake",
    -- 	  "image": { 
    -- 	    "url": "images/0001.jpg",
    -- 	    "width": 200,
    -- 	    "height": 200
    -- 	  },
    -- 	  "thumbnail": { 
    -- 	    "url": "images/thumbnails/0001.jpg",
    -- 	    "width": 32,
    -- 	    "height": 32
    -- 	  }
    -- 	}

    -- Change some of the values:
    EXEC sp_OAMethod @donutValues, 'AddStr', @success OUT, 'id', '0002'
    EXEC sp_OAMethod @donutValues, 'AddStr', @success OUT, 'imageUrl', 'images/0002.jpg'
    EXEC sp_OAMethod @donutValues, 'AddStr', @success OUT, 'thumbUrl', 'images/thumbnails/0002.jpg'

    EXEC sp_OAMethod @jsonDonut, 'EmitWithSubs', @sTmp0 OUT, @donutValues, @omitEmpty
    PRINT @sTmp0

    -- Output:

    -- 	{ 
    -- 	  "id": "0002",
    -- 	  "type": "donut",
    -- 	  "name": "Cake",
    -- 	  "image": { 
    -- 	    "url": "images/0002.jpg",
    -- 	    "width": 200,
    -- 	    "height": 200
    -- 	  },
    -- 	  "thumbnail": { 
    -- 	    "url": "images/thumbnails/0002.jpg",
    -- 	    "width": 32,
    -- 	    "height": 32
    -- 	  }
    -- 	}

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @jsonTemplate
    EXEC @hr = sp_OADestroy @jsonDonut
    EXEC @hr = sp_OADestroy @donutValues


END
GO