Change Handlers Using Stored Procedures

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

You can define such handlers in the xls.handlers table for all columns or only the required columns.

Here is a sample of a handler for all columns:

You can omit this configuration if the handler procedure has a handled object name with the _change suffix like in the sample.

Here is a sample of the handlers for the selected columns:

Implementation Details

SaveToDB and DBEdit execute the specified handler stored procedures themselves.

DBGate makes change handler procedures available via the POST commands and adds 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 such handlers.

Stored Procedure Parameters

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

1. values from data columns with the same name like @id and @name when the selected data has the id and name columns;
2. values from data query parameters with the same name like @account_id when the select query has the @account_id parameter;
3. special context values like @cell_name or @cell_value.
4. values from Excel named cells like @customer_id for named cell customer_id (the SaveToDB Add-In only);

The most useful context parameters are:

• @column_name gets a changed cell column name.
• @cell_value gets a new cell string value.
• @cell_number_value gets a new cell number value.
• @cell_datetime_value gets a new cell datetime value.

See also a complete list of Context Parameters.

You can use the @cell_number_value and @cell_datetime_value parameters to get typed values.

Also, you can detect incorrect input values for such columns if the typed value is NULL while the string value is not NULL.

In some cases, data columns can have names that are unsuitable for parameter names, like space in the "customer name" column name.

In this case, you can replace prohibited characters with the XML-encoded form. For, example "customer_x0020_name".

See Parameter Name Conventions for details.

Sample Change Handlers Using Stored Procedures

Below you will find sample stored procedures for each supported database platform.

Use the suggested solutions to resolve names of parameters and updated table columns.

SQL Server | MySQL | PostgreSQL | Oracle | DB2 | NuoDB | 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 IBM DB2

--#SET TERMINATOR %%

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK3_CHANGE (
    COLUMN_NAME VARCHAR(255)
    , CELL_VALUE VARCHAR(255)
    , CELL_NUMBER_VALUE DOUBLE
    , CELL_DATETIME_VALUE DATE
    , ID INTEGER
    )
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    COMMIT ON RETURN YES
    LANGUAGE SQL
P1: BEGIN

IF COLUMN_NAME = 'DATE' THEN
    UPDATE S02.CASHBOOK P SET DATE = CELL_DATETIME_VALUE WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSEIF COLUMN_NAME = 'DEBIT' THEN
    UPDATE S02.CASHBOOK P SET DEBIT = CELL_NUMBER_VALUE WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSEIF COLUMN_NAME = 'CREDIT' THEN
    UPDATE S02.CASHBOOK P SET CREDIT = CELL_NUMBER_VALUE WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSEIF 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;
ELSEIF 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;
ELSEIF 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;
END IF;

END P1
%%

--#SET TERMINATOR ;

Sample Change Handler for NuoDB

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK3_CHANGE (
    COLUMN_NAME VARCHAR(255)
    , CELL_VALUE VARCHAR(255)
    , CELL_NUMBER_VALUE DOUBLE
    , CELL_DATETIME_VALUE DATE
    , ID INTEGER
    )
AS

VAR ID1 INTEGER = ID;

IF (COLUMN_NAME = 'DATE')
    UPDATE S02.CASHBOOK SET DATE = CELL_DATETIME_VALUE WHERE ID = ID1;
    RETURN;
END_IF;

IF (COLUMN_NAME = 'DEBIT')
    UPDATE S02.CASHBOOK SET DEBIT = CELL_NUMBER_VALUE WHERE ID = ID1;
    RETURN;
END_IF;

IF (COLUMN_NAME = 'CREDIT')
    UPDATE S02.CASHBOOK SET CREDIT = CELL_NUMBER_VALUE WHERE ID = ID1;
    RETURN;
END_IF;

IF (COLUMN_NAME = 'ACCOUNT_ID')
    UPDATE S02.CASHBOOK SET ACCOUNT_ID = CAST(CELL_NUMBER_VALUE AS INTEGER) WHERE ID = ID1;
    RETURN;
END_IF;

IF (COLUMN_NAME = 'COMPANY_ID')
    UPDATE S02.CASHBOOK SET COMPANY_ID = CAST(CELL_NUMBER_VALUE AS INTEGER) WHERE ID = ID1;
    RETURN;
END_IF;

IF (COLUMN_NAME = 'ITEM_ID')
    UPDATE S02.CASHBOOK SET ITEM_ID = CAST(CELL_NUMBER_VALUE AS INTEGER) WHERE ID = ID1;
    RETURN;
END_IF;

END_PROCEDURE
@@

Sample Change Handler for Snowflake

Snowflake supports stored procedures written in JavaScript.

The usage scenario is the same.