Getting Data from SQL Queries

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

Users can connect to such objects by selecting the query list in the connection wizard in SaveToDB and DBEdit, or a query list URL in DBGate and ODataDB.

Here is a sample for 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 is a sample for 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, translate names, columns, and parameters.

Moreover, you can use such named objects similar to stored procedures in all configuration tables.

Note that SQL objects allow using SELECT, EXEC, GRANT, REVOKE, and other SQL commands. However, to get data, use SELECT and EXEC commands only.

Implementation Details

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

ODataDB creates FunctionImports. DBGate creates APIs similar to stored procedures.

SQL Samples for Getting Data

Below you will find samples 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 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, IBM DB2, NuoDB, 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 SQL Server Compact and 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)