Query Builder

Protheus Query Builder

Overview

Build correct, safe, and optimized SQL queries for Protheus ERP tables. Protheus has unique database conventions — mandatory soft-delete filters, multi-branch filtering, Hungarian notation for fields, data dictionary-driven schemas, and specific index patterns — that every query must respect. This skill generates queries that follow these conventions and helps choose between Workarea access and Embedded SQL.

When to Use

Use this skill when:

• Writing SQL queries against Protheus tables (SA1, SD1, SF2, etc.)
• Building parameterized SQL queries (FWExecStatement, TCSqlExec) in AdvPL/TLPP
• Optimizing existing queries for Protheus-specific patterns
• Deciding between Workarea access and Embedded SQL
• Ensuring mandatory filters are not missing
• Generating safe queries that prevent SQL injection

Bundled Reference Files

This skill uses progressive disclosure. The SKILL.md body covers conventions, decision logic, the checklist, and anti-patterns. Detailed code templates and cross-database reference tables are in the references/ directory — read them on demand based on the scenario:

Also refer to references/sonarqube-rules-reference.md for the complete SonarQube rules reference shared across skills.

Protheus Database Conventions

Table Naming

Protheus table aliases follow the pattern XXN where XX is the module prefix and N is a sequence:

Physical Table Names

The physical table name in the database appends the company code:

Use RetSQLName("SA1") to get the correct physical table name dynamically.

Field Naming

Fields follow the pattern XX_FIELD where XX matches the table alias prefix:

Mandatory System Fields

Every query against Protheus tables MUST include these filters:

-- ALWAYS include both filters
SELECT A1_COD, A1_NOME
FROM SA1010 SA1
WHERE SA1.D_E_L_E_T_ = ' '
  AND SA1.A1_FILIAL = '01'

Warning: Omitting D_E_L_E_T_ will return deleted records. Omitting the branch filter will return records from all branches, which is usually incorrect and a security risk.

Query Patterns

This skill supports five query patterns. Read references/query-patterns-and-examples.md for the full code templates.

Important: Pattern 4 (TCSqlExec) bypasses data dictionary validations and triggers. Prefer Workarea RecLock/MsUnlock for standard CRUD operations.

Workarea vs. Embedded SQL Decision Matrix

Rule of thumb: Use Workarea for CRUD operations on single records. Use Embedded SQL for reporting, aggregation, and complex joins.

Index Awareness (SIX Dictionary)

Protheus indexes are defined in the SIX table. The first index of each table (order 1) is typically the primary key.

Common Index Patterns

Using Indexes in Embedded SQL

For SQL Server, use the %nolock% DBAccess macro on read queries. This macro translates to WITH (NOLOCK) on MSSQL and is silently ignored on PostgreSQL/Oracle (MVCC), making it safe to use in cross-database code:

SELECT A1_COD, A1_NOME
FROM SA1010 SA1 WITH (%nolock%)
WHERE SA1.D_E_L_E_T_ = ' '
  AND SA1.A1_FILIAL = '01'
  AND SA1.A1_COD = '000001'
  AND SA1.A1_LOJA = '01'

Tip: Always order WHERE clauses to match the index key expression order for the query optimizer.

SQL Injection Prevention

Never concatenate user input directly into SQL strings. Use FWExecStatement to parameterize all dynamic values.

The safe pattern:

1. Build the SQL with ? placeholders for every dynamic value (filter, branch, deletion flag).
2. Wrap it in ChangeQuery() for cross-database compatibility.
3. Instantiate with FWExecStatement():New(cQuery) (or FWPreparedStatement on libs older than 20211116).
4. Bind values 1..N with SetString(), SetNumeric(), SetDate(), SetBoolean(), SetIn().
5. Execute:
   • SELECT returning a cursor → cAlias := oStatement:OpenAlias([cAlias][, cLifeTime, cTimeout])
   • SELECT returning a single scalar → xValue := oStatement:ExecScalar(cColumn[, cLifeTime, cTimeout])
   • INSERT / UPDATE / DELETE → TCSqlExec(oStatement:GetFixQuery())
6. Always close opened aliases ((cAlias)->(DBCloseArea())) and call oStatement:Destroy().

For LIKE clauses, build the % wildcard on the AdvPL side ("%" + cSearch + "%") and bind the whole string as a single ? parameter — this avoids cross-DB concat operator differences.

Do not use SetUnsafe() with user input. It bypasses bind safety and reintroduces SQL injection risk; reserve it for identifiers built from constants.

Cache (optional): OpenAlias / ExecScalar accept cLifeTime and cTimeout (seconds, as character) to reuse a cached result from DBAccess for hot, repeatedly executed queries.

Read references/query-patterns-and-examples.md for complete safe/unsafe code examples, the full method reference, and LIKE-clause examples.

Common Anti-Patterns

Query Building Checklist

Mandatory

• D_E_L_E_T_ = ' ' included for every table in the query
• Branch filter (XX_FILIAL) included for every table
• Physical table name obtained via RetSQLName("XXX")
• User-provided values parameterized with FWExecStatement (preferred over raw TCQuery)
• Result alias name generated with GetNextAlias() and opened via FWExecStatement:OpenAlias()
• Opened alias closed with DBCloseArea() in all code paths (including errors) and FWExecStatement:Destroy() is called

Performance

• Only required fields listed (no SELECT *)
• WHERE clause order matches index key expression
• %nolock% hint used for SQL Server read queries
• Pagination used for large result sets
• JOINs reference indexed columns

Safety

• No macro-execution (&cExpr) in SQL strings
• No hardcoded company/branch codes
• Error handling wraps the query execution (Try-Catch)
• Workarea area saved and restored with GetArea()/RestArea()
• No IIF() in query construction or expressions — use If/Else/EndIf or SQL CASE WHEN
• No CREATE PROCEDURE in source — use SPManager
• GetMV()/ExistBlock() results cached before use in loops

Refer to references/sonarqube-rules-reference.md for the complete SonarQube rules reference.

Cross-Database Compatibility

Protheus supports PostgreSQL, MSSQL Server, and Oracle. All generated SQL must work across all three, or use framework tools to handle differences:

• ChangeQuery(cQuery) — translates SQL syntax to the active database dialect (TOP → LIMIT, etc.). BeginSQL/EndSQL calls it automatically.
• TCGetDB() — returns "MSSQL", "ORACLE", or "POSTGRES" for DB-specific branching when ChangeQuery() is insufficient.
• DBAccess macros — %nolock%, %notDel%, %table:XXX%, %Order:XXX% — translated per database in SQL strings.
• Best practice: Always use ANSI-compatible SQL (COALESCE, CASE WHEN, CONCAT, FETCH FIRST) or rely on ChangeQuery().

Read references/cross-database-compatibility.md for full code examples, the DBAccess macros table, and the cross-database function equivalents reference (MSSQL vs PostgreSQL vs Oracle).

Troubleshooting

• Missing D_E_L_E_T_ filter: All Protheus queries must include D_E_L_E_T_ = ' ' (or equivalent %notDel%) to exclude logically deleted records. Omitting this returns deleted rows.
• Wrong physical table name: Use RetSQLName('alias') to get the physical table name. Hardcoded names like SA1010 will break across environments with different company codes.
• Index not being used: Ensure the WHERE clause column order matches the SIX index key expression. Use %nolock% hint for SQL Server read-only queries to avoid lock contention.
• Branch/company filter missing: Use FWxFilial('alias') for the branch filter. Hardcoding branch codes causes cross-branch data leakage.
• SQL injection via macro-execution: Never use &(cExpr) to build SQL strings. Use FWExecStatement to parameterize all dynamic values in queries.