Change Handlers Using Stored Procedures

You can use stored procedures to validate user input and save data changes to a database when cell values change.

Define these handlers in the xls.handlers table for all columns or just the required ones.

Here’s a sample handler for all columns:

You can skip this configuration if the handler procedure name ends with the _change suffix, as shown in the example.

Here’s a sample of handlers for specific columns:

Implementation Details

SaveToDB and DBEdit execute the specified handler stored procedures directly.

DBGate makes change handler procedures accessible via POST commands and includes the procedure in the table or column metadata.

ODataDB creates an ActionImport for each handler procedure and adds annotations to EntityTypes or EntityType columns.

The SaveToDB add-in reads metadata from DBGate and ODataDB to execute these handlers.

Stored Procedure Parameters

Stored procedures can use parameters populated with values according to these rules:

1. Values from data columns with matching names, like @id and @name, when the selected data includes the id and name columns.
2. Values from data query parameters with matching names, like @account_id, when the select query includes the @account_id parameter.
3. Special context values like @cell_name or @cell_value.
4. Values from Excel named cells, such as @customer_id for the named cell customer_id (only applicable for the SaveToDB Add-In).

The most useful context parameters are:

• @column_name: the name of the changed cell's column.
• @cell_value: the new string value of the cell.
• @cell_number_value: the new numeric value of the cell.
• @cell_datetime_value: the new datetime value of the cell.

For a complete list of Context Parameters, refer to the documentation.

You can use @cell_number_value and @cell_datetime_value to retrieve typed values.

You can also detect incorrect input values for such columns if the typed value is NULL while the string value is not.

In some cases, data column names may contain characters unsuitable for parameter names, such as spaces in "customer name."

In these cases, replace prohibited characters with their XML-encoded forms. For example, use "customer_x0020_name."

See Parameter Name Conventions for more details.

Sample Change Handlers Using Stored Procedures

Below are sample stored procedures for each supported database platform.

Use these examples to resolve parameter names and updated table columns.

SQL Server | MySQL | PostgreSQL | Oracle | Snowflake

Sample Change Handler for Microsoft SQL Server

CREATE PROCEDURE [s02].[usp_cashbook3_change]
    @column_name nvarchar(255),
    @cell_value nvarchar(255) = NULL,
    @cell_number_value money = NULL,
    @cell_datetime_value date = NULL,
    @id int = NULL
AS
BEGIN

SET NOCOUNT ON;

IF @column_name = 'debit'
    UPDATE s02.cashbook SET debit = @cell_number_value WHERE id = @id;
ELSE IF @column_name = 'credit'
    UPDATE s02.cashbook SET credit = @cell_number_value WHERE id = @id;
ELSE IF @column_name = 'item_id'
    UPDATE s02.cashbook SET item_id = @cell_number_value WHERE id = @id;
ELSE IF @column_name = 'company_id'
    UPDATE s02.cashbook SET company_id = @cell_number_value WHERE id = @id;
ELSE IF @column_name = 'account_id'
    UPDATE s02.cashbook SET account_id = @cell_number_value WHERE id = @id;
ELSE IF @column_name = 'date'
    UPDATE s02.cashbook SET [date] = @cell_datetime_value WHERE id = @id;

END

Sample Change Handler for MySQL

CREATE PROCEDURE s02.usp_cashbook3_change (
    column_name varchar(128),
    cell_value varchar(255),
    cell_number_value varchar(255),
    cell_datetime_value varchar(255),
    id int
)
BEGIN

IF column_name = 'date' THEN
    UPDATE s02.cashbook t SET date = cell_datetime_value WHERE t.id = id;
ELSEIF column_name = 'account_id' THEN
    UPDATE s02.cashbook t SET account_id = CAST(cell_number_value AS unsigned) WHERE t.id = id;
ELSEIF column_name = 'item_id' THEN
    UPDATE s02.cashbook t SET item_id = CAST(cell_number_value AS unsigned) WHERE t.id = id;
ELSEIF column_name = 'company_id' THEN
    UPDATE s02.cashbook t SET company_id = CAST(cell_number_value AS unsigned) WHERE t.id = id;
ELSEIF column_name = 'debit' THEN
    UPDATE s02.cashbook t SET debit = cell_number_value WHERE t.id = id;
ELSEIF column_name = 'credit' THEN
    UPDATE s02.cashbook t SET credit = cell_number_value WHERE t.id = id;
END IF;

END
//

Sample Change Handler for PostgreSQL

CREATE OR REPLACE FUNCTION s02.usp_cashbook3_change (
    column_name varchar,
    cell_value varchar,
    cell_number_value double precision,
    cell_datetime_value date,
    id integer
)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN

IF column_name = 'id' THEN
    RAISE EXCEPTION 'Do not change the id column';
    RETURN;
ELSIF column_name = 'date' THEN
    IF cell_datetime_value IS NULL AND cell_value IS NOT NULL THEN
        RAISE EXCEPTION 'Date requires a date value';
        RETURN;
    END IF;
    UPDATE s02.cashbook p SET date = cell_datetime_value WHERE p.id = usp_cashbook3_change.id;
ELSIF column_name = 'debit' THEN
    IF cell_number_value IS NULL AND cell_value IS NOT NULL THEN
        RAISE EXCEPTION 'Debit requires a number value';
        RETURN;
    END IF;
    UPDATE s02.cashbook p SET debit = cell_number_value WHERE p.id = usp_cashbook3_change.id;
ELSIF column_name = 'credit_id' THEN
    IF cell_number_value IS NULL AND cell_value IS NOT NULL THEN
        RAISE EXCEPTION 'Credit requires a number value';
        RETURN;
    END IF;
    UPDATE s02.cashbook p SET credit_id = cell_number_value WHERE p.id = usp_cashbook3_change.id;
ELSIF column_name = 'account_id' THEN
    UPDATE s02.cashbook p SET account_id = CAST(cell_number_value AS integer) WHERE p.id = usp_cashbook3_change.id;
ELSIF column_name = 'company_id' THEN
    UPDATE s02.cashbook p SET company_id = CAST(cell_number_value AS integer) WHERE p.id = usp_cashbook3_change.id;
ELSIF column_name = 'item_id' THEN
    UPDATE s02.cashbook p SET item_id = CAST(cell_number_value AS integer) WHERE p.id = usp_cashbook3_change.id;
ELSE
    RAISE NOTICE 'The cashbook table does not contain the % column', column_name;
    RETURN;
END IF;

IF NOT FOUND THEN
    RAISE NOTICE 'The record with the id % not found', id;
    RETURN;
END IF;

END
$$;

Sample Change Handler for Oracle Database

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK3_CHANGE (
    COLUMN_NAME VARCHAR2,
    CELL_VALUE VARCHAR2,
    CELL_NUMBER_VALUE DOUBLE PRECISION,
    CELL_DATETIME_VALUE DATE,
    ID NUMBER
)
AS
BEGIN

IF COLUMN_NAME = 'DATE' THEN
    UPDATE S02.CASHBOOK P SET "DATE" = CELL_DATETIME_VALUE WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSIF COLUMN_NAME = 'DEBIT' THEN
    UPDATE S02.CASHBOOK P SET DEBIT = CELL_NUMBER_VALUE WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSIF COLUMN_NAME = 'CREDIT' THEN
    UPDATE S02.CASHBOOK P SET CREDIT = CELL_NUMBER_VALUE WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSIF COLUMN_NAME = 'ACCOUNT_ID' THEN
    UPDATE S02.CASHBOOK P SET ACCOUNT_ID = CAST(CELL_NUMBER_VALUE AS NUMBER) WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSIF COLUMN_NAME = 'COMPANY_ID' THEN
    UPDATE S02.CASHBOOK P SET COMPANY_ID = CAST(CELL_NUMBER_VALUE AS NUMBER) WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSIF COLUMN_NAME = 'ITEM_ID' THEN
    UPDATE S02.CASHBOOK P SET ITEM_ID = CAST(CELL_NUMBER_VALUE AS NUMBER) WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
END IF;

END;
/

Sample Change Handler for Snowflake

Snowflake supports stored procedures written in JavaScript. The usage scenario is the same.