COMMANDPublished

TCQUERY

TCQUERY <cSqlExpr> ALIAS <cAlias> [NEW]

Executes a SELECT query through the TOPCONN RDD and opens the result in an AdvPL WorkArea.

DatabaseSQLTOPCONNQueryAliasWorkAreaDQL

01 · OVERVIEW

Overview

TCQUERY opens the result of a read-only SQL query under an AdvPL alias through the TOPCONN RDD. At compile time, the command is translated into operations based on DbUseArea() and TCGenQry(). NEW should be preferred to create a new WorkArea and avoid unintentionally closing the current area. The resulting cursor is intended for sequential reading and should not be treated as an editable ISAM table.

02 · SYNTAX

Syntax

TCQUERY <cSqlExpr> ALIAS <cAlias> [NEW]

Parameters

cSqlExpr

CharacterRequired

Character expression, constant, or variable containing a SELECT query to be executed through TOPCONN.

ALIAS cAlias

CharacterRequired

Alias name under which the result set will be opened. It may be a literal or an expression.

NEW

ClauseOptional

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

Return value

Does not directly return a value. The query result becomes available under the supplied alias as a read cursor.

03 · PRACTICAL EXAMPLE

Portable query with a dynamic alias

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

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

    // A1_COD means “Code”, A1_NOME means “Name”, and A1_FILIAL means “Branch”.
    // D_E_L_E_T_ is the technical field used to control logical deletion.
    cQuery := "SELECT A1_COD, A1_NOME "
    cQuery += "FROM " + RetSqlName("SA1") + " "
    cQuery += "WHERE A1_FILIAL = '" + xFilial("SA1") + "' "
    cQuery += "AND D_E_L_E_T_ = ' ' "
    cQuery += "ORDER BY A1_COD"

    // ChangeQuery() adapts the statement to supported databases when required.
    cQuery := ChangeQuery(cQuery)

    // NEW preserves the WorkArea that was current before this query.
    TCQUERY (cQuery) ALIAS (cAlias) NEW

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

    // The query alias occupies a WorkArea and should be closed when finished.
    (cAlias)->(DbCloseArea())
Return

Expected result

The query uses SA1’s physical table name, filters the branch and logical deletion, opens a dynamic alias in a new WorkArea, and releases the cursor at the end.

04 · PRACTICAL EXAMPLE

What the command abstracts

#Include "TopConn.ch"

// More readable form for opening the query:
TCQUERY (cQuery) ALIAS QRY NEW
QRY->(DbCloseArea())

// Conceptually, TCQUERY combines TOPCONN opening with TCGenQry().
// Internal implementation may evolve; use the documented command syntax.

Expected result

TCQUERY directly expresses the intent to open a query as a WorkArea without making application code depend on opening details.

BEST PRACTICES

Include TopConn.ch to make the command available; TOTVS.ch may remain the general include for the source file.
Use NEW unless there is a specific and controlled technical reason to reuse the current WorkArea.
Prefer GetNextAlias() when the routine may coexist with other aliases or execute more than once.
Use RetSqlName() for physical Protheus table names, xFilial() where applicable, and filter D_E_L_E_T_ on tables with logical deletion.
Use ChangeQuery() when the statement needs adaptation to supported databases.
Select only required fields and explicitly close the alias after processing.
When external values need parameterization, consider FWPreparedStatement/FWExecStatement instead of concatenating them directly into SQL.

COMMON PITFALLS

Without NEW, opening may reuse the current WorkArea and close a table already open there.
TCQUERY is intended for read queries beginning with SELECT. For DML/DDL, use the appropriate mechanism such as TCSqlExec(), according to official documentation.
A query cursor is not a regular ISAM table: do not rely on editing, DbSkip(-1), DbGoBottom(), or LastRec() to represent the number of rows.
DbGoTop() on a query cursor may close and reopen the cursor, submitting the query to the database again.
Fixed aliases may collide with areas opened by other routines.
Database-specific SQL reduces portability.
Do not directly concatenate untrusted input into SQL when parameterization is available.