Saving Data Using SQL

You can use SQL commands for INSERT, UPDATE, and DELETE operations to save data changes to a database.

Here’s a sample for the xls.objects table:

TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_OBJECT	UPDATE_OBJECT	DELETE_OBJECT
s02	view_cashbook	VIEW		<INSERT SQL>	<UPDATE SQL>	<DELETE SQL>
s02	usp_cashbook	PROCEDURE		<INSERT SQL>	<UPDATE SQL>	<DELETE SQL>
s02	code_cashbook	CODE	<SELECT SQL>	<INSERT SQL>	<UPDATE SQL>	<DELETE SQL>

Here’s a sample for the QueryList view:

TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_PROCEDURE	UPDATE_PROCEDURE	DELETE_PROCEDURE
s02	view_cashbook	VIEW		<INSERT SQL>	<UPDATE SQL>	<DELETE SQL>
s02	usp_cashbook	PROCEDURE		<INSERT SQL>	<UPDATE SQL>	<DELETE SQL>
s02	code_cashbook	CODE	<SELECT SQL>	<INSERT SQL>	<UPDATE SQL>	<DELETE SQL>

Below are code samples.

Implementation Details

SaveToDB and DBEdit execute the specified SQL commands for insert, update, and delete operations.

DBGate runs the code server-side based on POST, PUT, and DELETE commands.

ODataDB creates EntitySets for views and FunctionImports, along with new EntitySets for stored procedures and SQL codes.

SQL Command Parameters

SQL commands can use parameters populated with values according to these rules:

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

SaveToDB 10+, DBEdit, DBGate, and ODataDB also support parameters with the source_ prefix, which retrieve source values of the loaded data before changes.

If data column names contain characters unsuitable for parameter names, such as spaces in "customer name," replace prohibited characters with their XML-encoded forms. For example, use "customer_x0020_name".

Refer to Parameter Name Conventions for more details.

Also, see Context Parameters.

Sample SQL Commands

Here are sample SQL commands to select data and save changes for each supported database platform.

Note that SQL Server and SQLite use parameters prefixed with @, while others use a colon (:).

Sample SQL Commands for Microsoft SQL Server

Select code:

SELECT
    t.id,
    CAST(t.[date] AS datetime) AS [date],
    t.account_id,
    t.item_id,
    t.company_id,
    t.debit,
    t.credit
FROM
    s02.cashbook t
WHERE
    COALESCE(@account_id, t.account_id, -1) = COALESCE(t.account_id, -1)
    AND COALESCE(@item_id, t.item_id, -1) = COALESCE(t.item_id, -1)
    AND COALESCE(@company_id, t.company_id, -1) = COALESCE(t.company_id, -1)

Insert code:

INSERT INTO s02.cashbook
    ([date], account_id, item_id, company_id, debit, credit)
VALUES
    (@date, @account_id, @item_id, @company_id, @debit, @credit)

Update code:

UPDATE s02.cashbook
SET
    [date] = @date,
    account_id = @account_id,
    item_id = @item_id,
    company_id = @company_id,
    debit = @debit,
    credit = @credit
WHERE
    id = @id

Delete code:

DELETE FROM s02.cashbook WHERE id = @id

Sample SQL Commands for MySQL and PostgreSQL

Select code:

SELECT
    t.id,
    t.date,
    t.account_id,
    t.item_id,
    t.company_id,
    t.debit,
    t.credit
FROM
    s02.cashbook t
WHERE
    COALESCE(:account_id, t.account_id, -1) = COALESCE(t.account_id, -1)
    AND COALESCE(:item_id, t.item_id, -1) = COALESCE(t.item_id, -1)
    AND COALESCE(:company_id, t.company_id, -1) = COALESCE(t.company_id, -1)

Insert code:

INSERT INTO s02.cashbook
    ([date], account_id, item_id, company_id, debit, credit)
VALUES
    (:date, :account_id, :item_id, :company_id, :debit, :credit)

Update code:

UPDATE s02.cashbook
SET
    [date] = :date,
    account_id = :account_id,
    item_id = :item_id,
    company_id = :company_id,
    debit = :debit,
    credit = :credit
WHERE
    id = :id

Delete code:

DELETE FROM s02.cashbook WHERE id = :id

Sample SQL Commands for Oracle Database and Snowflake

Select code:

SELECT
    t.ID,
    t."DATE",
    t.ACCOUNT_ID,
    t.ITEM_ID,
    t.COMPANY_ID,
    t.DEBIT,
    t.CREDIT
FROM
    S02.CASHBOOK t
WHERE
    COALESCE(:ACCOUNT_ID, t.ACCOUNT_ID, -1) = COALESCE(t.ACCOUNT_ID, -1)
    AND COALESCE(:ITEM_ID, t.ITEM_ID, -1) = COALESCE(t.ITEM_ID, -1)
    AND COALESCE(:COMPANY_ID, t.COMPANY_ID, -1) = COALESCE(t.COMPANY_ID, -1)

Insert code:

INSERT INTO S02.CASHBOOK
    ([DATE], ACCOUNT_ID, ITEM_ID, COMPANY_ID, DEBIT, CREDIT)
VALUES
    (:DATE, :ACCOUNT_ID, :ITEM_ID, :COMPANY_ID, :DEBIT, :CREDIT)

Update code:

UPDATE S02.CASHBOOK
SET
    "DATE" = :DATE,
    ACCOUNT_ID = :ACCOUNT_ID,
    ITEM_ID = :ITEM_ID,
    COMPANY_ID = :COMPANY_ID,
    DEBIT = :DEBIT,
    CREDIT = :CREDIT
WHERE
    ID = :ID

Delete code:

DELETE FROM S02.CASHBOOK WHERE ID = :ID

Sample SQL Commands for SQLite

Select code:

SELECT
    t.id,
    t.date,
    t.account_id,
    t.item_id,
    t.company_id,
    t.debit,
    t.credit
FROM
    cashbook t
WHERE
    COALESCE(@account_id, t.account_id, -1) = COALESCE(t.account_id, -1)
    AND COALESCE(@item_id, t.item_id, -1) = COALESCE(t.item_id, -1)
    AND COALESCE(@company_id, t.company_id, -1) = COALESCE(t.company_id, -1)

Insert code:

INSERT INTO cashbook
    (date, account_id, item_id, company_id, debit, credit)
VALUES
    (@date, @account_id, @item_id, @company_id, @debit, @credit)

Update code:

UPDATE s02.cashbook
SET
    date = @date,
    account_id = @account_id,
    item_id = @item_id,
    company_id = @company_id,
    debit = @debit,
    credit = @credit
WHERE
    id = @id

Delete code:

DELETE FROM s02.cashbook WHERE id = @id