SQL Server

Firebase JSON Put and Patch

See more JSON Examples

Demonstrates how to apply Firebase put and patch events to a JSON database. 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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @json1 nvarchar(4000)
    SELECT @json1 = '{"a": 1, "b": 2}'

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

    -- Use Firebase delimiters for JSON paths.
    EXEC sp_OASetProperty @json, 'DelimiterChar', '/'

    DECLARE @success int
    EXEC sp_OAMethod @json, 'Load', @success OUT, @json1
    EXEC sp_OAMethod @json, 'FirebasePut', @success OUT, '/c', '{"foo": true, "bar": false}'
    -- Output should be: {"a":1,"b":2,"c":{"foo":true,"bar":false}}

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT '1) ' + @sTmp0

    EXEC sp_OAMethod @json, 'FirebasePut', @success OUT, '/c', '"hello world"'
    -- Output should be: {"a":1,"b":2,"c":"hello world"}

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT '2) ' + @sTmp0

    EXEC sp_OAMethod @json, 'FirebasePut', @success OUT, '/c', '{"foo": "abc", "bar": 123}'
    -- Output should be: {"a":1,"b":2,"c":{"foo":"abc","bar":123}}

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT '3) ' + @sTmp0

    -- Back to the original..
    EXEC sp_OAMethod @json, 'FirebasePut', @success OUT, '/', '{"a": 1, "b": 2}'

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT '4) ' + @sTmp0

    EXEC sp_OAMethod @json, 'FirebasePut', @success OUT, '/c', '{"foo": true, "bar": false}'
    EXEC sp_OAMethod @json, 'FirebasePatch', @success OUT, '/c', '{"foo": 3, "baz": 4}'
    -- Output should be: {"a":1,"b":2,"c":{"foo":3,"bar":false,"baz":4}}

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT '5) ' + @sTmp0

    EXEC sp_OAMethod @json, 'FirebasePatch', @success OUT, '/c', '{"foo": "abc123", "baz": {"foo": true, "bar": false}, "bax": {"foo": 200, "bar": 400} }'
    -- Output should be: {"a":1,"b":2,"c":{"foo":"abc123","bar":false,"baz":{"foo":true,"bar":false},"bax":{"foo":200,"bar":400}}}

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT '6) ' + @sTmp0

    EXEC @hr = sp_OADestroy @json


END
GO