Saving Data Using JSON Parameters

This topic shows how to save data with dynamic columns using stored procedures with JSON parameters.

There are five such built-in parameters:

@json_values_f1 (or @JsonValuesF1)
@json_values_f2 (or @JsonValuesF2)
@json_changes_f1 (or @JsonChangesF1)
@json_changes_f2 (or @JsonChangesF2)
@json_changes_f3 (or @JsonChangesF3)

@json_values_f1 and @json_values_f2 get row values, @json_values_f1 as an array, and @json_values_f2 as an object.

@json_changes_f1 and @json_changes_f2 get all changes, @json_changes_f1 as arrays and @json_values_f2 as objects.

@json_changes_f3 is the same as @json_changes_f2, but it always gets the complete set of values even for empty columns.

For your notes, "f1", "f2", and "f3" are simply format indexes.

Also, you can find the following built-in parameters useful:

@json_columns (or @JsonColumns) gets actual column names.
@table_name (or @TableName) gets the name of the select query object.
@edit_action (or @EditAction) gets the "INSERT", "UPDATE", "DELETE", or "MERGE" value depending on the operation.

Configuration

There are three ways to assign procedures used to save changes:

Using the xls.objects table
Using the query list views
Using procedure name suffixes

Here is a sample for the xls.objects table:

ID	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_OBJECT	UPDATE_OBJECT	DELETE_OBJECT
	s02	view_cashbook	VIEW		s02.view_cashbook_insert	s02.view_cashbook_update	s02.view_cashbook_delete
	s02	usp_cashbook	PROCEDURE			s02.usp_cashbook_update

Here is a sample for the QueryList view:

ID	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_PROCEDURE	UPDATE_PROCEDURE	DELETE_PROCEDURE	PROCEDURE_TYPE
	s02	view_cashbook	VIEW		s02.view_cashbook_insert	s02.view_cashbook_update	s02.view_cashbook_delete
	s02	usp_cashbook	PROCEDURE			s02.usp_cashbook_update

All the configurations above are optional as SaveToDB products link procedures automatically by the _insert, _update, and _delete suffixes.

You can use this technique even without installed SaveToDB Framework.

The second configuration rows contain a single update procedure. Use this for procedures with the json_changes parameters or when you use the same procedure for all operations (see below).

Implementation Details

SaveToDB and DBEdit call the specified stored procedures with the JSON parameters.

DBGate and ODataDB execute the procedures on the server-side, building JSON values using parameters of the POST, PUT, and DELETE commands.

Samples

Below you will find detailed samples for each case.

Sample for @json_values_f1
Sample for @json_values_f2
Using Generic Row Update Procedures
Sample for @json_changes_f1
Sample for @json_changes_f2
Using Generic Table Update Procedures

Sample for @json_values_f1

Here is a sample of generated commands for insert, update, and delete procedures:

EXEC [s24].[view_cashbook_json_values_f1_delete] @id = 21;

EXEC [s24].[view_cashbook_json_values_f1_update]
    @json_columns = N'["id","date","account_id","item_id","company_id","debit","credit"]'
    , @json_values_f1 = N'[1,"2022-01-10",1,1,1,200000,null]';

EXEC [s24].[view_cashbook_json_values_f1_insert]
    @json_columns = N'["id","date","account_id","item_id","company_id","debit","credit"]'
    , @json_values_f1 = N'[21,"2022-03-31",1,2,8,null,100000]';

Here are sample procedures for Microsoft SQL Server that update the underlying table using JSON values.

Pay attention that the delete procedure uses a regular parameter @id.

CREATE PROCEDURE [s24].[view_cashbook_json_values_f1_insert]
    @json_columns nvarchar(max)
    , @json_values_f1 nvarchar(max)
AS
BEGIN

SET @json_values_f1 = '[' + @json_values_f1 + ']'    -- Fix for OPENJSON top-level array

INSERT INTO s24.cashbook
    ([date], account_id, item_id, company_id, debit, credit)
SELECT
    t2.[date], t2.account_id, t2.item_id, t2.company_id, t2.debit, t2.credit
FROM
    OPENJSON(@json_values_f1) WITH (
        [id] int '$[0]'
        , [date] date '$[1]'
        , [account_id] int '$[2]'
        , [item_id] int '$[3]'
        , [company_id] int '$[4]'
        , [debit] float '$[5]'
        , [credit] float '$[6]'
    ) t2;

END
GO

CREATE PROCEDURE [s24].[view_cashbook_json_values_f1_update]
    @json_columns nvarchar(max)
    , @json_values_f1 nvarchar(max)
AS
BEGIN

SET @json_values_f1 = '[' + @json_values_f1 + ']'  -- Fix for OPENJSON top-level array

UPDATE s24.cashbook
SET
    [date] = t2.date
    , account_id = t2.account_id
    , item_id = t2.item_id
    , company_id = t2.company_id
    , debit = t2.debit
    , credit = t2.credit
FROM
    s24.cashbook t
    INNER JOIN OPENJSON(@json_values_f1) WITH (
        [id] int '$[0]'
        , [date] date '$[1]'
        , [account_id] int '$[2]'
        , [item_id] int '$[3]'
        , [company_id] int '$[4]'
        , [debit] float '$[5]'
        , [credit] float '$[6]'
    ) t2 ON t2.id = t.id

END
GO

CREATE PROCEDURE [s24].[view_cashbook_json_values_f1_delete]
    @id int = NULL
AS
BEGIN

DELETE FROM s24.json_test WHERE id = @id

END
GO

Sample for @json_values_f2

Here is a sample of generated commands for insert, update, and delete procedures:

EXEC [s24].[view_cashbook_json_values_f2_delete] @id = 21;

EXEC [s24].[view_cashbook_json_values_f2_update]
    @json_values_f2 = N'{"id":1,"date":"2022-01-10","account_id":1,"item_id":1,"company_id":1,"debit":200000,"credit":null}';

EXEC [s24].[view_cashbook_json_values_f2_insert]
    @json_values_f2 = N'{"id":21,"date":"2022-03-31","account_id":1,"item_id":2,"company_id":8,"debit":null,"credit":100000}';

Contrary to @json_values_f1, @json_values_f2 gets values as objects.

Here are sample procedures for Microsoft SQL Server that update the underlying table using JSON values.

CREATE PROCEDURE [s24].[view_cashbook_json_values_f2_insert]
    @json_values_f2 nvarchar(max)
AS
BEGIN

INSERT INTO s24.cashbook
    ([date], account_id, item_id, company_id, debit, credit)
SELECT
    t2.[date], t2.account_id, t2.item_id, t2.company_id, t2.debit, t2.credit
FROM
    OPENJSON(@json_values_f2) WITH (
        [id] int '$."id"'
        , [date] date '$."date"'
        , [account_id] int '$."account_id"'
        , [item_id] int '$."item_id"'
        , [company_id] int '$."company_id"'
        , [debit] float '$."debit"'
        , [credit] float '$."credit"'
    ) t2

END
GO

CREATE PROCEDURE [s24].[view_cashbook_json_values_f2_update]
    @json_values_f2 nvarchar(max)
AS
BEGIN

UPDATE s24.cashbook
SET
    [date] = t2.date
    , account_id = t2.account_id
    , item_id = t2.item_id
    , company_id = t2.company_id
    , debit = t2.debit
    , credit = t2.credit
FROM
    s24.cashbook t
    INNER JOIN OPENJSON(@json_values_f2) WITH (
        [id] int '$."id"'
        , [date] date '$."date"'
        , [account_id] int '$."account_id"'
        , [item_id] int '$."item_id"'
        , [company_id] int '$."company_id"'
        , [debit] float '$."debit"'
        , [credit] float '$."credit"'
    ) t2 ON t2.id = t.id

END
GO

CREATE PROCEDURE [s24].[view_cashbook_json_values_f2_delete]
    @id int = NULL
AS
BEGIN

DELETE FROM s24.cashbook WHERE id = @id

END
GO

Using Generic Row Update Procedures

The examples above show a simple case when the underlying table structure is known.

As the @json_values_f2 parameter gets values for all columns, you can create completely generic procedures to save changes.

Here are is a sample of such a procedure for Microsoft SQL Server:

https://dbgate.savetodb.com/api/mssql-241/en-us/s24.xl_update_generic_row/$definition

In addition to row values, the procedure must get the target table name and the edit action.

You can create a wrapper procedure like the following or use an SQL code in the UPDATE_OBJECT field of the configuration table to call it.

CREATE PROCEDURE [s24].[view_cashbook_json_generic_row_update]
    @id int = NULL
    , @table_name nvarchar(255) = NULL
    , @edit_action nvarchar(6) = NULL
    , @json_values_f2 nvarchar(max) = NULL
AS
BEGIN

EXEC s24.xl_update_generic_row @id, '[s24].[cashbook]', @edit_action, @json_values_f2

END

As the same procedure processes insert, update, and delete operations, only define a single update procedure. Leave the insert and delete fields of the configuration with empty values.

Here is a sample of generated commands:

EXEC [s24].[view_cashbook_json_generic_row_update]
    @id = 21
    , @table_name = N'[s24].[view_cashbook_json_generic_row]'
    , @edit_action = N'DELETE'
    , @json_values_f2 = N'{"id":21,"date":"2022-03-31","account_id":1,"item_id":2,"company_id":8,"debit":null,"credit":100000}';

EXEC [s24].[view_cashbook_json_generic_row_update]
    @id = 1
    , @table_name = N'[s24].[view_cashbook_json_generic_row]'
    , @edit_action = N'UPDATE'
    , @json_values_f2 = N'{"id":1,"date":"2022-01-10","account_id":1,"item_id":1,"company_id":1,"debit":200000,"credit":null}';

EXEC [s24].[view_cashbook_json_generic_row_update]
    @id = 21
    , @table_name = N'[s24].[view_cashbook_json_generic_row]'
    , @edit_action = N'INSERT'
    , @json_values_f2 = N'{"id":21,"date":"2022-03-31","account_id":1,"item_id":2,"company_id":8,"debit":null,"credit":100000}';

Pay attention that the procedure gets the name of the view used to select data but not the target table name.

That's why you need to use a wrapper procedure or an SQL code to call it with the right target table name.

Sample for @json_changes_f1

Here is a sample of generated commands with the @json_changes_f1 parameter:

EXEC [s24].[view_cashbook_json_changes_f1_update]
    @id = NULL
    , @json_changes_f1 = N'{
        "table_name":"[s24].[view_cashbook_json_changes_f1]",
        "actions":{
            "insert":{
                "action":"insert"
                , "columns":["id","date","account_id","item_id","company_id","debit","credit"]
                , "rows":[[21,"2022-03-31",1,2,8,null,100000]]
                }
            ,"update":{
                "action":"update"
                , "columns":["id","date","account_id","item_id","company_id","debit","credit"]
                , "rows":[[1,"2022-01-10",1,1,1,200000,null]]
                }
            ,"delete":{
                "action":"delete"
                , "columns":["id"]
                , "rows":[[21]]
                }
            }
        }';

SaveToDB and DBEdit generate a single command for all changes and make a single procedure call.

This can improve the entire performance dramatically.

DBGate and ODataDB create a regular API for such objects, get regular POST, PUT, and DELETE commands (in a single batch), and call procedures with JSON parameters on the server-side.

Here is a sample SQL Server procedure that uses JSON array values to update the underlying table.

CREATE PROCEDURE [s24].[view_cashbook_json_changes_f1_update]
    @id int = NULL
    , @json_changes_f1 nvarchar(max) = NULL
AS
BEGIN
SET NOCOUNT ON

DECLARE @insert nvarchar(max),  @update nvarchar(max), @delete nvarchar(max)

SELECT
    @insert = t2.[insert]
    , @update = t2.[update]
    , @delete = t2.[delete]
FROM
    OPENJSON(@json_changes_f1) WITH (
        actions nvarchar(max) AS json
    ) t1
    CROSS APPLY OPENJSON(t1.actions) WITH (
        [insert] nvarchar(max) '$.insert' AS json
        , [update] nvarchar(max) '$.update' AS json
        , [delete] nvarchar(max) '$.delete' AS json
    ) t2

IF @insert IS NOT NULL
INSERT INTO s24.cashbook
    ([date], account_id, item_id, company_id, debit, credit)
SELECT
    t2.[date], t2.account_id, t2.item_id, t2.company_id, t2.debit, t2.credit
FROM
    OPENJSON(@insert) WITH (
        [rows] nvarchar(max) '$.rows' AS json
    ) t1
    CROSS APPLY OPENJSON(t1.[rows]) WITH (
        [id] int '$[0]'
        , [date] date '$[1]'
        , [account_id] int '$[2]'
        , [item_id] int '$[3]'
        , [company_id] int '$[4]'
        , [debit] float '$[5]'
        , [credit] float '$[6]'
    ) t2;

IF @update IS NOT NULL
UPDATE s24.cashbook
SET
    [date] = t2.[date]
    , account_id = t2.account_id
    , item_id = t2.item_id
    , company_id = t2.company_id
    , debit = t2.debit
    , credit = t2.credit
FROM
    s24.cashbook t
    INNER JOIN (
        SELECT
            t2.id AS id
            , t2.[date] AS [date]
            , t2.account_id AS account_id
            , t2.item_id AS item_id
            , t2.company_id AS company_id
            , t2.debit AS debit
            , t2.credit AS credit
        FROM
            OPENJSON(@update) WITH (
                [rows] nvarchar(max) '$.rows' AS json
            ) t1
            CROSS APPLY OPENJSON(t1.[rows]) WITH (
                [id] int '$[0]'
                , [date] date '$[1]'
                , [account_id] int '$[2]'
                , [item_id] int '$[3]'
                , [company_id] int '$[4]'
                , [debit] float '$[5]'
                , [credit] float '$[6]'
            ) t2
    ) t2 ON t2.id = t.id

IF @delete IS NOT NULL
DELETE FROM s24.cashbook
FROM
    s24.cashbook t
    INNER JOIN (
        SELECT
            t2.[id] AS [id]
        FROM
            OPENJSON(@delete) WITH (
                [rows] nvarchar(max) '$.rows' AS json
            ) t1
            CROSS APPLY OPENJSON(t1.[rows]) WITH (
                [id] int '$[0]'
            ) t2
    ) t2 ON t2.[id] = t.[id]

END

Sample for @json_changes_f2

Here is a sample of generated commands with the @json_changes_f2 parameter:

EXEC [s24].[view_cashbook_json_changes_f2_update]
     @id = NULL
     , @json_changes_f2 = N'{
        "table_name":"[s24].[view_cashbook_json_changes_f2]"
        , "actions":{
            "insert":{
                "action":"insert"
                , "columns":["id","date","account_id","item_id","company_id","debit","credit"]
                , "rows":[{"id":21,"date":"2022-03-31","account_id":1,"item_id":2,"company_id":8,"debit":null,"credit":100000}]
                }
            ,"update":{
                "action":"update"
                , "columns":["id","date","account_id","item_id","company_id","debit","credit"],
                , "rows":[{"id":1,"date":"2022-01-10","account_id":1,"item_id":1,"company_id":1,"debit":200000,"credit":null}]
                }
            , "delete":{
                "action":"delete"
                , "columns":["id"]
                , "rows":[{"id":21}]
                }
            }
        }';

Contrary to @json_changes_f1, @json_changes_f3 gets values as objects.

Here is a sample SQL Server procedure that uses JSON object values to update the underlying table.

CREATE PROCEDURE [s24].[view_cashbook_json_changes_f2_update]
    @id int = NULL
    , @json_changes_f2 nvarchar(max) = NULL
AS
BEGIN
SET NOCOUNT ON

DECLARE @insert nvarchar(max),  @update nvarchar(max), @delete nvarchar(max)

SELECT
    @insert = t2.[insert]
    , @update = t2.[update]
    , @delete = t2.[delete]
FROM
    OPENJSON(@json_changes_f2) WITH (
        actions nvarchar(max) AS json
    ) t1
    CROSS APPLY OPENJSON(t1.actions) WITH (
        [insert] nvarchar(max) '$.insert' AS json
        , [update] nvarchar(max) '$.update' AS json
        , [delete] nvarchar(max) '$.delete' AS json
    ) t2

IF @insert IS NOT NULL
INSERT INTO s24.cashbook
    ([date], account_id, item_id, company_id, debit, credit)
SELECT
    t2.[date], t2.account_id, t2.item_id, t2.company_id, t2.debit, t2.credit
FROM
    OPENJSON(@insert) WITH (
        [rows] nvarchar(max) '$.rows' AS json
    ) t1
    CROSS APPLY OPENJSON(t1.[rows]) WITH (
        [id] int '$."id"'
        , [date] date '$."date"'
        , [account_id] int '$."account_id"'
        , [item_id] int '$."item_id"'
        , [company_id] int '$."company_id"'
        , [debit] float '$."debit"'
        , [credit] float '$."credit"'
    ) t2;

IF @update IS NOT NULL
UPDATE s24.cashbook
SET
    [date] = t2.[date]
    , account_id = t2.account_id
    , item_id = t2.item_id
    , company_id = t2.company_id
    , debit = t2.debit
    , credit = t2.credit
FROM
    s24.cashbook t
    INNER JOIN (
        SELECT
            t2.id AS id
            , t2.[date] AS [date]
            , t2.account_id AS account_id
            , t2.item_id AS item_id
            , t2.company_id AS company_id
            , t2.debit AS debit
            , t2.credit AS credit
        FROM
            OPENJSON(@update) WITH (
                [rows] nvarchar(max) '$.rows' AS json
            ) t1
            CROSS APPLY OPENJSON(t1.[rows]) WITH (
                [id] int '$."id"'
                , [date] date '$."date"'
                , [account_id] int '$."account_id"'
                , [item_id] int '$."item_id"'
                , [company_id] int '$."company_id"'
                , [debit] float '$."debit"'
                , [credit] float '$."credit"'
            ) t2
    ) t2 ON t2.id = t.id;

IF @delete IS NOT NULL
DELETE FROM s24.cashbook
FROM
    s24.cashbook t
    INNER JOIN (
        SELECT
            t2.[id] AS [id]
        FROM
            OPENJSON(@delete) WITH (
                [rows] nvarchar(max) '$.rows' AS json
            ) t1
            CROSS APPLY OPENJSON(t1.[rows]) WITH (
                [id] int '$."id"'
            ) t2
    ) t2 ON t2.id = t.id

END

Using Generic Table Update Procedures

The examples above show the cases when the underlying table structure is known.

You can create completely generic procedures to save changes.

Here are is a sample of such a procedure for Microsoft SQL Server:

https://dbgate.savetodb.com/api/mssql-241/en-us/s24.xl_update_generic_table/$definition

In addition to changed values, the procedure must get the target table name.

Also, you have to define parameters for primary columns passed for delete operations.

You can create a wrapper procedure like the following or use an SQL code in the UPDATE_OBJECT field of the configuration table to call it.

CREATE PROCEDURE [s24].[view_cashbook_json_generic_table_update]
    @id int = NULL
    , @table_name nvarchar(255) = NULL
    , @json_changes_f2 nvarchar(max) = NULL
AS
BEGIN

EXEC s24.xl_update_generic_table '[s24].[cashbook]', @json_changes_f2

END