Saving Data Using SQL

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

Here is a sample for the xls.objects table:

Here is a sample for the QueryList view:

You will find code samples below.

Implementation Details

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

DBGate executes the code on the server-side according to POST, PUT, and DELETE commands.

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

SQL Command Parameters

SQL commands 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. values from Excel named cells like @customer_id for named cell customer_id (the SaveToDB Add-In only);
4. special context values like @rownum or @transaction_id.

SaveToDB 10+, DBEdit, DBGate, and ODataDB also allow using parameters with the source_ prefix that get source values of the loaded data before changes.

In some cases, data columns can have names that are not suitable 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.

See also Context Parameters.

Sample SQL Commands

Below you will find sample SQL commands to select data and save changes for each supported database platform.

Note that SQL Server, SQL Server Compact, and SQLite use parameters with the @ character while others are with the colon.

SQL Server | MySQL | PostgreSQL | Oracle | DB2 | NuoDB | Snowflake | SQL CE | SQLite

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, IBM DB2, NuoDB, 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 SQL Server Compact and 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