SQL Server

Using Pre-defined JSON Templates

See more JSON Examples

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.

SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    --  One way to create JSON is to do it in a straightforward manner:
    DECLARE @json int
    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
    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
    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
    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