Getting Data from SQL Queries

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:

IDTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_OBJECTUPDATE_OBJECTDELETE_OBJECT
s02code_cashbookCODESELECT * FROM s02.cashbook

Here is a sample for the query list view:

IDTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDUREPROCEDURE_TYPE
s02code_cashbookCODESELECT * 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 Server | MySQL | PostgreSQL | Oracle | DB2 | NuoDB | Snowflake | SQL CE | SQLite

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)