Getting Data from SQL Queries

Database developers can define named SQL queries in the xls.objects table or the query list views.

Users can access these objects by selecting the query list in the connection wizard of SaveToDB and DBEdit, or by using a query list URL in DBGate and ODataDB.

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

ID	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_OBJECT	UPDATE_OBJECT	DELETE_OBJECT
	s02	code_cashbook	CODE	SELECT * FROM s02.cashbook

Here’s a sample from the query list view:

ID	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_PROCEDURE	UPDATE_PROCEDURE	DELETE_PROCEDURE	PROCEDURE_TYPE
	s02	code_cashbook	CODE	SELECT * FROM s02.cashbook

Objects defined via SQL are similar to stored procedures.

You can save changes, use change handlers, configure value lists, and translate names, columns, and parameters.

Additionally, you can use these named objects like stored procedures in all configuration tables.

Note that SQL objects support SELECT, EXEC, GRANT, REVOKE, and other SQL commands. However, to retrieve data, use only SELECT and EXEC commands.

Implementation Details

SaveToDB and DBEdit prepare and send SQL commands to a database server.

ODataDB creates FunctionImports, while DBGate generates APIs similar to stored procedures.

SQL Samples for Getting Data

Below are samples for each supported database platform.

Note that SQL Server and SQLite use parameters prefixed with the @ character, while other platforms use a colon (:).

SQL Samples for Microsoft SQL Server

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)

SQL Samples for MySQL and PostgreSQL

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)

SQL Samples for Oracle Database and Snowflake

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)

SQL Samples for SQLite

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)