Consultas SQL em AdvPL: query em string, Embedded SQL e FWExecStatement

Toda consulta em AdvPL termina do mesmo jeito: um alias aberto com o resultado, percorrido registro a registro, e fechado no fim. O que muda entre as três formas é como a query chega ao banco — e quanto trabalho sobra para você no caminho. Este é o oitavo texto da série; o anterior trata de GetArea e RestArea.

O que toda query precisa ter

Três coisas, qualquer que seja a forma:

• RetSqlName("SA1") devolve o nome físico da tabela na empresa em uso. Escrever o nome à mão quebra o fonte na primeira empresa diferente.
• xFilial("SA1") devolve a filial certa para aquela tabela, respeitando se ela é compartilhada ou exclusiva.
• D_E_L_E_T_ = ' ' — o Protheus não apaga registro, marca. Sem esse filtro a consulta traz o que o usuário excluiu.

E o nome do alias sai de GetNextAlias(), que devolve um nome livre na thread:

Local cAlias := GetNextAlias()

Alias fixo colide assim que a rotina é chamada duas vezes na mesma pilha — o mesmo tipo de problema que o escopo de variáveis resolve para variável.

Forma 1: a query em string

Você monta o texto, passa pelo ChangeQuery() — que adapta a sintaxe ao banco em uso — e abre com MPSysOpenQuery():

Local cAlias := GetNextAlias()
Local cQuery := ""

cQuery := "SELECT A1_COD, A1_LOJA, A1_NOME, A1_ULTCOM "
cQuery += "  FROM " + RetSqlName("SA1")
cQuery += " WHERE A1_FILIAL = '" + xFilial("SA1") + "'"
cQuery += "   AND A1_EST = 'SP'"
cQuery += "   AND D_E_L_E_T_ = ' '"
cQuery := ChangeQuery(cQuery)

MPSysOpenQuery(cQuery, cAlias, {{"A1_ULTCOM", "D", 8, 0}})

O terceiro parâmetro é opcional: um array no formato {campo, tipo, tamanho, decimais} para os campos que precisam de conversão. Ele aparece montado a partir do DbStruct() da tabela em rotinas como F677QryAprSol() e LoadFK1(). Sem ele, data volta como texto AAAAMMDD.

Em fonte antigo você vai achar as duas formas que vieram antes:

TCQuery cQuery New Alias (cAlias)                          // comando, exige TopConn.ch
DbUseArea(.T., "TOPCONN", TcGenQry(,, cQuery), cAlias) // AF12CHPMIL(), na rotina ATFA012

Nelas a conversão de data é manual, campo a campo, com TCSetField() logo depois de abrir:

TCSetField(cAlias, "A1_ULTCOM", "D", 8, 0)

Forma 2: Embedded SQL

O BeginSql... EndSql escreve o SQL direto no fonte, e o pré-compilador troca as expressões marcadas por %:

Local cAlias := GetNextAlias()
Local cUF    := "SP"

BeginSql Alias cAlias
    COLUMN A1_ULTCOM AS DATE
    SELECT A1_COD, A1_LOJA, A1_NOME, A1_ULTCOM
      FROM %table:SA1% SA1
     WHERE SA1.A1_FILIAL = %xfilial:SA1%
       AND SA1.A1_EST = %Exp:cUF%
       AND SA1.%notDel%
     ORDER BY A1_COD, A1_LOJA
EndSql

O padrão usa exatamente esse conjunto em AF005VERIF(), e o COLUMN... AS DATE em FinLOnl07(). A vantagem é legibilidade e a sintaxe neutra de banco: você não escreve TOP, nem concatenação, nem função de nulo de um SGBD específico.

Forma 3: FWExecStatement, com parâmetros

É a forma que os fontes recentes do padrão preferem. A query leva ? no lugar de cada valor, e cada valor entra por um método tipado:

Local cQuery := ""
Local cAlias := ""
Local nPar   := 1
Local oQuery := Nil

cQuery := "SELECT A1_COD, A1_LOJA, A1_NOME, A1_ULTCOM"
cQuery += "  FROM " + RetSqlName("SA1")
cQuery += " WHERE A1_FILIAL = ?"
cQuery += "   AND A1_EST = ?"
cQuery += "   AND D_E_L_E_T_ = ?"
cQuery := ChangeQuery(cQuery)

oQuery := FWExecStatement():New(cQuery)
oQuery:SetString(nPar++, xFilial("SA1"))
oQuery:SetString(nPar++, cUF)
oQuery:SetString(nPar++, Space(1))

cAlias := oQuery:OpenAlias(GetNextAlias())

O valor nunca vira parte do texto da query, então a aspa digitada pelo usuário é só um caractere — ela não fecha uma string SQL e não abre espaço para injeção. Esse é o motivo de usar. A sequência acima é a mesma do padrão em ATF012SD1(): New, uma chamada SetString por ?, OpenAlias(GetNextAlias()).

Existem também SetNumeric, para número; SetDate, para data; e SetIn, que resolve de uma vez a lista inteira de uma cláusula IN (?) — sem ele, você monta a lista concatenando ? um por um e perde a vantagem de escrever a query uma única vez. Há ainda SetUnsafe, que insere o texto sem tratamento — serve para nome de tabela, nunca para valor digitado, porque aí a proteção contra aspa desaparece de novo. FWPreparedStatement existe com a mesma ideia, mas devolve a query pronta em vez de abrir o alias — útil quando o SQL final vai ser reaproveitado em outro lugar, como dentro de um INSERT... SELECT.

Percorrer e fechar

Com o alias aberto, o resto é igual nas três formas — um While sobre o alias, como visto em condições e laços:

/*/{Protheus.doc} PGV001
Lista os clientes ativos de uma UF.
@author Pragiva
@type user function
/*/
User Function PGV001(cUF)

    Local cAlias := ""
    Local nTotal := 0

    cAlias := PGVAbre(cUF)   // qualquer uma das três formas

    While !(cAlias)->(EoF())
        ConOut((cAlias)->A1_COD + "/" + (cAlias)->A1_LOJA + " " + ;
               DToC((cAlias)->A1_ULTCOM))
        nTotal++
        (cAlias)->(DbSkip())
    EndDo

    (cAlias)->(DbCloseArea())

Return nTotal

Dois esquecimentos custam caro aqui. Sem o DbSkip(), o laço nunca sai do primeiro registro e a thread fica presa até alguém derrubá-la. Sem o DbCloseArea(), cada chamada deixa um alias e um cursor abertos no banco; numa rotina que roda em lote, isso acaba em erro de limite de áreas de trabalho, longe do ponto que causou.

Repare também no prefixo (cAlias)->. Sem ele, EoF() e DbSkip() agem na área corrente, que pode ser outra tabela — é a mesma razão de existir do GetArea e RestArea.

Só leitura

Tudo isto é para SELECT. Incluir, alterar ou excluir registro por UPDATE direto pula validação, gatilho, ponto de entrada e log do padrão. Para gravar, o caminho é RecLock ou rotina automática — assunto do próximo texto, RecLock ou ExecAuto. E quando o resultado precisa ser guardado e reprocessado, a série fecha com FWTemporaryTable.

Antes de embarcar a query no fonte, rode no banco e confira campos e volume:

SELECT TOP 20 A1_COD, A1_LOJA, A1_NOME, A1_ULTCOM
  FROM SA1010
 WHERE A1_EST = 'SP'
   AND A1_MSBLQL <> '1'
   AND D_E_L_E_T_ = ' '
 ORDER BY A1_COD, A1_LOJA

Se o nome de um campo não bate, o erro aparece aqui, em segundos — e não no log do AppServer, na tela do usuário.

Se uma consulta sua está lenta ou trazendo registro que não devia, fale com a gente.