SQL Server

Emit JSON Array to StringBuilder

See more JSON Examples

Demonstrates how to write a JSON array to a Chilkat StringBuilder object. 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

    DECLARE @a int
    EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @a OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @a, 'Load', @success OUT, '[ 1,2,3,4 ]'

    -- We could get the JSON array as a string...
    DECLARE @s nvarchar(4000)
    EXEC sp_OAMethod @a, 'Emit', @s OUT

    PRINT @s

    -- But what if the JSON array contains a huge amount of data,
    -- and we don't want to marshal it all from Chilkat's internal
    -- native code to a string in our programming language?

    -- You can instead emit to a Chilkat StringBuilder
    DECLARE @sb int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT

    EXEC sp_OAMethod @a, 'EmitSb', @success OUT, @sb

    -- Perhaps the StringBuilder object can be passed to other methods..
    -- Chilkat typically provides methods where a potentially large string
    -- can be passed in via a StringBuilder object..

    -- Or perhaps you wish to save to a file..
    EXEC sp_OAMethod @sb, 'WriteFile', @success OUT, 'c:/temp/qa_output/a.json', 'utf-8', 0

    EXEC @hr = sp_OADestroy @a
    EXEC @hr = sp_OADestroy @sb


END
GO