COMMANDPublished

TCQUERY

TCQUERY <cSqlExpr> ALIAS <cAlias> [NEW]

Executes an SQL query through the TOPCONN RDD and exposes the result in an AdvPL work area.

DatabaseSQLTopConnQueryAliasCommands

01 · OVERVIEW

Overview

TCQUERY executes an SQL expression against a relational database through the TOPCONN RDD and opens the result under the supplied alias. During compilation it is translated into DbUseArea() and TCGenQry() calls. NEW should be used to open a new work area and avoid closing the current area and any table already open in it.

02 · SYNTAX

Syntax

TCQUERY <cSqlExpr> ALIAS <cAlias> [NEW]

Parameters

cSqlExpr

CaractereRequired

Constant or variable SQL expression executed through TOPCONN.

ALIAS cAlias

CaractereRequired

Name of the work area where the query result is opened. It may be a literal or expression.

NEW

CláusulaOptional

Opens the query in a new WorkArea. Official documentation recommends always using it to avoid closing the current area.

03 · PRACTICAL EXAMPLE

Query with a dynamic alias

#Include "TOTVS.ch"
#Include "TopConn.ch"

User Function ExTCQuery()
    Local cAlias := GetNextAlias()
    Local cQuery := ""

    cQuery := "SELECT A1_COD, A1_NOME "
    cQuery += "FROM " + RetSqlName("SA1") + " "
    cQuery += "WHERE D_E_L_E_T_ = ' ' "
    cQuery += "ORDER BY A1_COD"
    cQuery := ChangeQuery(cQuery)

    TCQUERY (cQuery) ALIAS (cAlias) NEW

    While !(cAlias)->(Eof())
        ConOut((cAlias)->A1_COD + " - " + ;
            (cAlias)->A1_NOME)
        (cAlias)->(DbSkip())
    EndDo

    (cAlias)->(DbCloseArea())
Return

Expected result

The query uses SA1’s physical name, receives compatibility adjustments, opens a new WorkArea and closes the alias when finished.

04 · PRACTICAL EXAMPLE

Conceptual command equivalence

#Include "TopConn.ch"

// Forma recomendada pela legibilidade:
TCQUERY (cQuery) ALIAS QRY NEW
QRY->(DbCloseArea())

// Internamente, o comando combina operações equivalentes a:
// DbUseArea(.T., "TOPCONN", ;
//     TCGenQry(,, cQuery), "QRY", .T., .T.)

Expected result

TCQUERY simplifies opening with DbUseArea() and TCGenQry() while keeping source intent clear.

BEST PRACTICES

Include TopConn.ch for the command; TOTVS.ch may remain as the source general include.
Use NEW for every new query unless there is a carefully controlled technical reason not to.
Use GetNextAlias() when the routine may coexist with other aliases or run more than once.
Use RetSqlName() for Protheus physical table names and filter deleted records where applicable.
Pass SQL through ChangeQuery() when portability across supported databases is required.
Explicitly close the alias when finished, preferably in a flow that also handles errors.
Date or numeric fields may require type adjustment after opening; consult TCSetField() for the result and database.

COMMON PITFALLS

Without NEW, TCQUERY uses the current WorkArea and may close an already open table.
Fixed aliases may collide with other routines; generate one when necessary.
Do not directly concatenate user, request or integration input into SQL; validate it and use safe mechanisms available in the context.
Database-specific SQL reduces portability; review functions, concatenation, limits and conversions.
SELECT * increases traffic and coupling; request only required fields.
Forgetting DbCloseArea() leaves resources and a work area occupied.
A TOPCONN query is normally read-only; do not assume it can be edited like a regular table.