How to Save Data Using JSON Parameters in SaveToDB

How to Save Data Using JSON Parameters in SaveToDB

This section explains how to save data with dynamic columns using stored procedures that accept JSON parameters.

Built-in JSON Parameters

There are five built-in parameters you can use:

  • @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 retrieves row values as an array.

  • @json_values_f2 retrieves row values as an object.

  • @json_changes_f1 and @json_changes_f2 capture all changes, with @json_changes_f1 as arrays and @json_changes_f2 as objects.

  • @json_changes_f3 behaves like @json_changes_f2, but always returns the complete set of values, even for empty columns.

Note that "f1", "f2", and "f3" are simply format indexes.

Additionally, the following built-in parameters may be useful:

  • @json_columns (or @JsonColumns): Retrieves actual column names.
  • @table_name (or @TableName): Retrieves the name of the select query object.
  • @edit_action (or @EditAction): Retrieves the operation type ("INSERT", "UPDATE", "DELETE", or "MERGE").

Configuration

You can assign procedures for saving changes in three ways:

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

Example of xls.objects Table

IDTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_OBJECTUPDATE_OBJECTDELETE_OBJECT
 s02view_cashbookVIEWs02.view_cashbook_inserts02.view_cashbook_updates02.view_cashbook_delete
 s02usp_cashbookPROCEDUREs02.usp_cashbook_update

Example of QueryList View

IDTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDUREPROCEDURE_TYPE
 s02view_cashbookVIEWs02.view_cashbook_inserts02.view_cashbook_updates02.view_cashbook_delete
 s02usp_cashbookPROCEDUREs02.usp_cashbook_update

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

You can use this technique even without the SaveToDB Framework.

The second configuration row contains a single update procedure. Use this for procedures with the json_changes parameters or when the same procedure is used 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 server-side, building JSON values from the parameters of the POST, PUT, and DELETE commands.

Samples

Below are detailed samples for each case.

Sample for @json_values_f1

Here are sample 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. Note 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.cashbook WHERE id = @id;

END
GO

Sample for @json_values_f2

Here are sample 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}';

Unlike @json_values_f1, @json_values_f2 retrieves 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 assume the underlying table structure is known.

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

Here's 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 accept the target table name and the edit action.

You can create a wrapper procedure like this or use 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

Since the same procedure handles insert, update, and delete operations, you only need to define a single update procedure. Leave the insert and delete fields of the configuration empty.

Here are sample 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}';

Note that the procedure receives the name of the view used to select data, not the target table name. Therefore, you need to use a wrapper procedure or SQL code to call it with the correct target table name.

Sample for @json_changes_f1

Here’s a sample command using 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, resulting in a single procedure call. This can significantly improve overall performance.

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

Here’s 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’s a sample command using 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}]
                }
            }
        }';

Unlike @json_changes_f1, @json_changes_f2 retrieves values as objects.

Here’s 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 assume the underlying table structure is known.

You can create completely generic procedures to save changes.

Here’s 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 accept the target table name. You also need to define parameters for primary columns used in delete operations.

You can create a wrapper procedure like this or use 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

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.