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.