Usina BRAdvPL Guide
Sign in
← All topics
FOUNDATIONPublished

Developing queries in Protheus

cQuery → ChangeQuery() → TCQUERY / Embedded SQL | FWPreparedStatement / FWExecStatement → WorkArea

Build portable and efficient SQL queries in Protheus and use parameterized statements with FWPreparedStatement and FWExecStatement when values should remain separate from SQL text.

SQLDBAccessTopConnQueryPerformanceDatabaseFWExecStatementFWPreparedStatementSetInParameterized SQL
01 · OVERVIEW

Overview

DBAccess remains the access layer for supported SQL databases, while RetSqlName(), GetNextAlias(), ChangeQuery(), TCQUERY, and Embedded SQL remain relevant. For queries with changing values, FWPreparedStatement organizes statement parameters and FWExecStatement can execute the query and open the result as an alias. Official TOTVS documentation also states that SetIn(), inherited by FWExecStatement from FWPreparedStatement, is used with SQL IN clauses.

02 · SYNTAX

Syntax

cQuery → ChangeQuery() → TCQUERY / Embedded SQL | FWPreparedStatement / FWExecStatement → WorkArea

Parameters

GetNextAlias()
FunctionOptional

Generates a temporary alias for the result set and avoids collisions with already open work areas.

RetSqlName()
FunctionOptional

Converts the logical Protheus alias into the physical table name in the database.

ChangeQuery()
FunctionOptional

Adapts the SQL statement for compatibility with supported databases.

TcSetField()
FunctionOptional

Adjusts non-character result fields to the expected AdvPL types.

SqlOrder()
FunctionOptional

Converts an AdvPL index expression for use in an ORDER BY clause.

xFilial()
FunctionOptional

Returns the appropriate branch value for the specified table filter.

DToS()
FunctionOptional

Converts a date to YYYYMMDD when that format is required while building the query.

Methods

FWPreparedStatementBase class for preparing an SQL statement with parameters kept separate from their values.FWPreparedStatement():New( <cQuery> )
FWExecStatementStatement used to execute a parameterized query and open its result as an alias.FWExecStatement():New( <cQuery> )
SetIn()Fills a parameter associated with an SQL IN clause. FWExecStatement inherits this method from FWPreparedStatement.oStatement:SetIn( <nParameter>, <aValues> )
03 · PRACTICAL EXAMPLE

Portable basic query

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

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

    // RetSqlName() resolves the physical table name in the database.
    cQuery := "SELECT A1_COD, A1_NOME FROM " + RetSqlName("SA1")
    cQuery += " WHERE A1_FILIAL = '" + xFilial("SA1") + "'"
    cQuery += " AND D_E_L_E_T_ = ' '"

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

    TCQuery cQuery New Alias (cAlias)

    While !(cAlias)->(Eof())
        // A1_NOME means “Name”; keep the actual Protheus field identifier unchanged.
        ConOut((cAlias)->A1_COD + " - " + (cAlias)->A1_NOME)
        (cAlias)->(DbSkip())
    EndDo

    // Always close the WorkArea opened for the result set.
    (cAlias)->(DbCloseArea())
Return
Expected result

The query uses the physical table name, branch filter, logical-deletion filter, dynamic alias, and explicit cleanup.

04 · PRACTICAL EXAMPLE

Recno query to position the real table

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

    // R_E_C_N_O_ identifies the physical record represented by the result row.
    cQuery := "SELECT R_E_C_N_O_ RECNO, A1_COD, A1_NOME FROM " + RetSqlName("SA1")
    cQuery += " WHERE A1_FILIAL = '" + xFilial("SA1") + "'"
    cQuery += " AND D_E_L_E_T_ = ' '"
    cQuery := ChangeQuery(cQuery)

    TCQuery cQuery New Alias (cAlias)

    While !(cAlias)->(Eof())
        DbSelectArea("SA1")
        SA1->(DbGoTo((cAlias)->RECNO))
        // After DbGoTo(), SA1 is positioned on the real record. A1_NOME means “Name”.
        ConOut(SA1->A1_COD + " - " + SA1->A1_NOME)
        (cAlias)->(DbSkip())
    EndDo

    (cAlias)->(DbCloseArea())
Return
Expected result

The result set identifies records and DbGoTo() positions the real table using R_E_C_N_O_.

05 · PRACTICAL EXAMPLE

Parameterized query with FWExecStatement

User Function ExFWExec()
    Local cQuery := ""
    Local cAlias := ""
    Local oStmt

    // The ? placeholder keeps the value outside the SQL text.
    cQuery := "SELECT A1_COD, A1_NOME FROM " + RetSqlName("SA1")
    cQuery += " WHERE A1_FILIAL = ? AND D_E_L_E_T_ = ' '"
    cQuery := ChangeQuery(cQuery)

    oStmt := FWExecStatement():New(cQuery)
    oStmt:SetString(1, xFilial("SA1"))
    cAlias := oStmt:OpenAlias()

    While !(cAlias)->(Eof())
        // A1_NOME means “Name”; keep the real Protheus identifier unchanged.
        ConOut((cAlias)->A1_COD + " - " + (cAlias)->A1_NOME)
        (cAlias)->(DbSkip())
    EndDo

    (cAlias)->(DbCloseArea())
    oStmt:Destroy()
Return
Expected result

The query parameterizes the branch, opens the result as an alias, and explicitly releases resources.

06 · PRACTICAL EXAMPLE

IN clause with SetIn()

User Function ExSetIn()
    Local cQuery := ""
    Local cAlias := ""
    Local aCustomers := {"000010", "000020", "000030"}
    Local oStmt

    // The second placeholder represents the list used by the IN clause.
    cQuery := "SELECT A1_COD, A1_NOME FROM " + RetSqlName("SA1")
    cQuery += " WHERE A1_FILIAL = ? AND A1_COD IN (?) AND D_E_L_E_T_ = ' '"
    cQuery := ChangeQuery(cQuery)

    oStmt := FWExecStatement():New(cQuery)
    oStmt:SetString(1, xFilial("SA1"))
    oStmt:SetIn(2, aCustomers)
    cAlias := oStmt:OpenAlias()

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

    (cAlias)->(DbCloseArea())
    oStmt:Destroy()
Return
Expected result

SetIn() associates an array with the IN-clause placeholder.

BEST PRACTICES
  • Select only required columns; avoid SELECT *.
  • Use GetNextAlias() instead of fixed aliases.
  • Use RetSqlName() and pass the statement through ChangeQuery().
  • Filter each branch field with xFilial() for that table.
  • Exclude logically deleted rows in every participating table.
  • Prefer ANSI JOIN syntax.
  • Use aggregate functions to reduce returned rows when appropriate.
  • Always close the WorkArea with DbCloseArea().
  • Treat query performance as the combined result of AdvPL code, DBAccess, the DBMS, infrastructure and data volume; measure slow queries and resource use before changing indexes or hardware.
  • Document custom indexes and revalidate them after Protheus upgrades.
  • Define archiving and retention with business, legal, backup and recovery requirements.
  • Resolve physical tables with RetSqlName() and branch context with xFilial(); do not infer suffixes from company or branch numbers.
  • Keep database structures aligned with the Protheus data dictionary and use TOTVS-supported mechanisms for structural changes.
  • Confirm actual result types: traditional dictionary dates and empty values may use character, spaces or zero instead of SQL NULL.
  • Prefer parameterization when variable values can be kept separate from the SQL structure.
  • Keep RetSqlName(), xFilial(), and D_E_L_E_T_ handling appropriate to the table and query purpose.
  • Close the alias returned by OpenAlias() and destroy the statement after processing.
COMMON PITFALLS
  • DBAccess control fields represent logical records and deletions.
  • Do not assume a query result supports the same navigation as an ISAM table.
  • Database-specific SQL may fail elsewhere.
  • Non-aggregated fields must be compatible with GROUP BY.
  • Use Recno queries only when real-table positioning is required.
  • Never concatenate untrusted input directly into SQL.
  • Do not disable or remove standard Protheus indexes without formal TOTVS guidance, DBA review, testing and a rollback plan.
  • Do not apply FILLFACTOR, index rebuild or statistics changes globally; measure each workload.
  • The referenced article reports practitioner experience with SQL Server in a historical Protheus context. It is not official TOTVS documentation and is not automatically portable to other databases or releases.
  • Do not create database fields, tables, constraints or indexes without assessing the data dictionary, DBAccess compatibility and the official upgrade process.
  • Partitioning, compression, replication, clustering and In-Memory support depend on current DBMS, Protheus and DBAccess versions. Statements from 2012 are historical context, not current product rules.
  • Do not treat FWExecStatement as an automatic replacement for every query; choose the technique according to context, portability, and parameterization needs.
  • The position passed to SetString(), SetIn(), and similar methods must match the placeholder order in the statement.
  • Avoid concatenating external or user-provided values directly into SQL text when parameterization is available.

Related content

CommandTCQUERY →FoundationProtheus include files →FunctionDToS →CommandWHILE ... ENDDO →FunctionDbSelectArea →FoundationEmbedded SQL in AdvPL →FoundationD_E_L_E_T_ field and logical deletion in Protheus →Technical governanceUsing internal Protheus resources →Historical sourceProtheus Programming Manual (2001) →
REFERENCES
  1. TOTVS. Developing queries in Protheus. TDN.
  2. TOTVS. Embedded SQL. TDN.
  3. TOTVS. TCQUERY command. TDN.
  4. LIMA, Fabrício. 5 reasons for Protheus (TOTVS) users to hire a SQL Server DBA. Blog, published Dec. 14, 2013, with later updates. Complementary practitioner reference; not official TOTVS documentation.
  5. INOWE, Marcel. Tips on the Protheus (TOTVS) database. 4SQLServer, Sept. 12, 2012. Historical practitioner reference focused on SQL Server; comments on the original post record later corrections and version changes.
  6. MICROSIGA. ADVPL Programming — Query usage. Official historical ADVPL X SQL Programming documentation, Aug. 27, 2006. "Protheus particularities" section.
  7. MICROSIGA. Programming Manual. Collaborative document, file dated July 11, 2001. Historical reference.
  8. TOTVS. FWExecStatement. TDN.
  9. TOTVS. FWPreparedStatement. TDN.
  10. TOTVS. Cross Segmentos — ADVPL — FwExecStatement — SetIn. Central de Atendimento.
Status
Published
Page created on
Last reviewed on
Original language
Portuguese
Reviewed by
Usina.BR
0 approved comment(s)

Comments

There are no approved comments yet.

Sign in with Google or Microsoft to comment.

Powered by Usina Docs · Alpha

© 2026 Usina.BR. All rights reserved. TOTVS, Protheus, Microsiga and their respective logos are trademarks of their owners. AdvPL Guide is independent and uses limited, attributed excerpts justified by educational purposes. See the Terms of Use. Terms of Use