(SQL Server) JSON AppendArrayCopy Example

Demonstrates the AppendArrayCopy function.

Note: This example requires Chilkat v9.5.0.82 or above.

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)
    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

    DECLARE @success int
    EXEC sp_OAMethod @json, 'Load', @success OUT, '{ "name": "John", "marbles": 100 }'

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

    EXEC sp_OAMethod @jarr, 'Load', @success OUT, '[ 1, 2, 3, "apple", "orange", [ 10, 20, 30 ], { "name": "Alfred"} ]'

    -- Copy the contents of jar to a new array member named "stuff".
    EXEC sp_OAMethod @json, 'AppendArrayCopy', @success OUT, 'stuff', @jarr

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0
    -- Expected output is:  {"name":"John","marbles":100,"stuff":[1,2,3,"apple","orange",[10,20,30],{"name":"Alfred"}]}

    -- Note: The contents of the array were copied.
    -- If we modify the contents of jarr, it does not modify the array contained within "json" because it is a separate copy.
    -- For example:
    EXEC sp_OAMethod @jarr, 'AddIntAt', @success OUT, -1, 2020
    EXEC sp_OAMethod @jarr, 'Emit', @sTmp0 OUT
    PRINT @sTmp0
    -- Expected output: [1,2,3,"apple","orange",[10,20,30],{"name":"Alfred"},2020]

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0
    -- Remains unchanged: {"name":"John","marbles":100,"stuff":[1,2,3,"apple","orange",[10,20,30],{"name":"Alfred"}]}

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @jarr


END
GO