FOUNDATIONPublished

Embedded SQL in AdvPL

BeginSql Alias <cAlias> ... EndSql

Write readable SQL queries directly in AdvPL source code using precompiler substitutions, branch and logical-deletion filters, and declarative type conversion.

SQLEmbedded SQLDBAccessTopConnQueryDatabaseBeginSqlEndSql

01 · OVERVIEW

Overview

Embedded SQL lets you write the SELECT directly between BeginSql and EndSql. During compilation and execution, special expressions delimited by % are transformed to integrate the query with Protheus and DBAccess. Documented resources include %table, %xfilial, %exp, %notDel, %Order, column ... as Date/Logic/Numeric, and %noparser%. By default the query is processed by ChangeQuery(); the opened alias must be closed when processing is complete.

02 · SYNTAX

Syntax

BeginSql Alias <cAlias> ... EndSql

Parameters

%table:ALIAS%

SubstitutionOptional

Resolves the physical Protheus table name for the specified alias.

%xfilial:ALIAS%

SubstitutionOptional

Resolves the current branch value appropriate for the specified table.

%exp:expression%

ExpressionOptional

Inserts the value of a compatible AdvPL variable or expression into the query.

%notDel%

SubstitutionOptional

Generates the logical-deletion condition based on D_E_L_E_T_.

%Order:ALIAS%

SubstitutionOptional

Converts an AdvPL index expression into SQL ordering syntax.

column <field> as <type>

ConversionOptional

Declares Date, Logic, or Numeric result fields for conversion to AdvPL types.

%noparser%

ControlOptional

Prevents the query from being processed by ChangeQuery() before it is sent to the database. Use only for a clear technical reason.

Return value

Opens the query cursor in the alias specified by BeginSql. The result set should be traversed as a query WorkArea and closed with DbCloseArea() when it is no longer needed.

03 · PRACTICAL EXAMPLE

Basic query with table, branch and logical deletion

#Include "TOTVS.ch"

User Function ExEmbedded()
    Local cAlias := GetNextAlias()
    Local cCode  := "000001"

    // Embedded SQL avoids concatenating text fragments to build the SELECT.
    // A1_COD means “Code”, A1_NOME means “Name”, and A1_FILIAL means “Branch”.
    BeginSql Alias cAlias
        SELECT A1_COD, A1_NOME
          FROM %table:SA1% SA1
         WHERE A1_FILIAL = %xfilial:SA1%
           AND A1_COD    = %exp:cCode%
           AND SA1.%notDel%
    EndSql

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

    // The cursor opened by BeginSql must be closed when processing ends.
    (cAlias)->(DbCloseArea())
Return

Expected result

The query resolves the physical table, branch, code value, and logical-deletion filter without manually building an SQL string.

04 · PRACTICAL EXAMPLE

Declarative date-field conversion

#Include "TOTVS.ch"

User Function ExEmbeddedDate()
    Local cAlias  := GetNextAlias()
    Local cPrefix := "NF"

    // E2_EMISSAO means “Issue date”, E2_PREFIXO means “Prefix”,
    // E2_NUM means “Number”, and E2_FILIAL means “Branch”.
    BeginSql Alias cAlias
        column E2_EMISSAO as Date
        SELECT E2_PREFIXO, E2_NUM, E2_EMISSAO
          FROM %table:SE2% SE2
         WHERE E2_FILIAL  = %xfilial:SE2%
           AND E2_PREFIXO = %exp:cPrefix%
           AND SE2.%notDel%
    EndSql

    While !(cAlias)->(Eof())
        // E2_EMISSAO is already exposed as an AdvPL date value.
        ConOut((cAlias)->E2_PREFIXO + " - " + DToC((cAlias)->E2_EMISSAO))
        (cAlias)->(DbSkip())
    EndDo

    (cAlias)->(DbCloseArea())
Return

Expected result

The column declaration replaces later manual handling of the returned field type.

05 · PRACTICAL EXAMPLE

Inspecting the executed query with GetLastQuery()

#Include "TOTVS.ch"

User Function ExLastQuery()
    Local cAlias := GetNextAlias()
    Local aInfo  := {}

    // A1_COD means “Code”, A1_NOME means “Name”, and A1_FILIAL means “Branch”.
    BeginSql Alias cAlias
        SELECT A1_COD, A1_NOME
          FROM %table:SA1% SA1
         WHERE A1_FILIAL = %xfilial:SA1%
           AND SA1.%notDel%
    EndSql

    // GetLastQuery() provides information about the query that was actually opened.
    aInfo := GetLastQuery()
    ConOut(aInfo[2]) // Executed SQL

    (cAlias)->(DbCloseArea())
Return

Expected result

GetLastQuery()[2] exposes the executed SQL string; the complete return also includes the alias, conversion metadata, parser flag, and cursor-opening time.

BEST PRACTICES

Use %table:ALIAS% instead of hard-coding the physical table name in SQL.
Use %xfilial:ALIAS% and %notDel% when the query requires current-branch and logical-deletion filtering.
Use %exp:variable% to insert AdvPL values into Embedded SQL and avoid string concatenation when embedded syntax already covers the case.
Use column declarations for result fields that must be converted to Date, Logic, or Numeric.
Always close the alias opened by BeginSql when processing is complete.
Use GetLastQuery() after opening the cursor when you need to inspect the executed query and cursor-opening time.
When the query or replacement values must contain the ? character, evaluate FWPreparedStatement/FWExecStatement as recommended by the official documentation.

COMMON PITFALLS

Do not place functions directly inside the Embedded SQL block when the syntax requires a precomputed expression; store the value in a variable before BeginSql.
The ? character is reserved by Embedded SQL processing and may cause nonconforming behavior when used in the query or replacement values.
Do not use %noparser% merely for convenience; it disables the standard ChangeQuery() processing.
Breakpoints inside the BeginSql/EndSql block do not behave like ordinary AdvPL breakpoints; place them before or after the block.
If compilation reports an error at EndSql, also check indentation, spaces, and tabs before the instruction according to the target environment documentation.
Using an alias that is already open may cause an execution error.

Embedded SQL in AdvPL

Overview

Syntax

Parameters

Return value

Basic query with table, branch and logical deletion

Declarative date-field conversion

Inspecting the executed query with GetLastQuery()

Related content

Comments