Saving Data Using JSON Parameters
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:
- Using the
xls.objects
table - Using query list views
- Using procedure name suffixes
Example of 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 |
Example of 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 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
- 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 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