GetNextAlias()FunçãoRequired
Generates a temporary result-set alias and prevents collisions.
FOUNDATIONPublished
Developing queries in Protheus
cQuery → ChangeQuery() → TCQUERY / TCGenQry() → WorkArea → DbCloseArea()Build portable and efficient SQL queries in Protheus with DBAccess, dynamic aliases and correct result-set handling.
Overview
DBAccess connects Protheus to supported SQL databases and exposes ISAM and native SQL access. For data sets, a well-designed query usually reduces client-side scans, processing and network traffic. Queries may be assembled as strings or written with Embedded SQL; both reach DBAccess.
Syntax
cQuery → ChangeQuery() → TCQUERY / TCGenQry() → WorkArea → DbCloseArea()Parameters
RetSqlName()FunçãoRequired
Maps a Protheus logical alias to its physical database table name.
ChangeQuery()FunçãoRequired
Adapts SQL syntax to supported databases.
TcSetField()FunçãoOptional
Adjusts non-character result fields to expected AdvPL types.
SqlOrder()FunçãoOptional
Converts an AdvPL index expression for SQL ORDER BY.
xFilial()FunçãoOptional
Supplies the correct branch value for a table filter.
DToS()FunçãoOptional
Produces YYYYMMDD for date comparisons when appropriate.
Portable basic query
#Include "TOTVS.ch"
#Include "TopConn.ch"
// See the Portuguese example; localize only user-facing text.Expected result
Uses the physical table name, branch and deletion filters, compatibility conversion, dynamic alias and explicit cleanup.
Recno query for positioning the real table
cQuery := "SELECT R_E_C_N_O_ RECNO "
cQuery += "FROM " + RetSqlName("SA1") + " SA1 "
cQuery += "WHERE A1_FILIAL = '" + xFilial("SA1") + "' "
cQuery += "AND D_E_L_E_T_ = ' '"
cQuery := ChangeQuery(cQuery)
TCQUERY (cQuery) ALIAS (cAlias) NEW
While !(cAlias)->(Eof())
SA1->(DbGoTo((cAlias)->RECNO))
// Leia ou trate o registro real já posicionado.
(cAlias)->(DbSkip())
EndDo
(cAlias)->(DbCloseArea())Expected result
The result set identifies rows and DbGoTo() positions the physical table through R_E_C_N_O_.
- 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.
- 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.