Embedded SQL

Objetivo

O objetivo do Embedded SQL é facilitar a escrita e leitura de queries. Para isso, foi definida uma sintaxe para que se possa escrever a query diretamente no código AdvPL, sem a necessidade de ficar concatenando pedaços de string para compor a string final.

A utilização do Embedded SQL divide-se em:

• Compilação de código-fonte.
• Execução de código-fonte.
  
  

Para compilar um código-fonte com o código escrito no formato Embedded, deve-se utilizar o produto Microsiga Protheus, com build igual ou superior a 7.00.050721P, em um ambiente com repositório para TOTVS | DBAccess (RPODB=TOP). A utilização deste recurso também depende da atualização da LIB.

Exemplo básico - Código-fonte atual

cQuery:= ' SELECT SE2.E2_PREFIXO,SE2.E2_NUM ' 
cQuery += ' FROM '+RetSqlTable('SE2')+' SE2,'+ RetSqlTable('QEK') + ' QEK ' 
cQuery += ' WHERE SE2.E2_FILIAL= '+xfilial('SE2')+' AND ' 
cQuery += ' SE2.E2_PREFIXO <> ''+cPrefixo+'' AND ' 
cQuery += " SE2.D_E_L_E_T_ = ' ' " 
cQuery += ' ORDER BY '+RetSqlOrder('SE2') 

dbUseArea(.T.,'TOPCONN',TcGenQry(,,cQuery),'E2TEMP',.T.,.T.)

TCSetField('E2TEMP','E2_EMISSAO','D',8,0)

Exemplo básico - Utilizando Embedded SQL

BeginSql alias 'E2TEMP' 
	column E2_EMISSAO as Date
	SELECT 
		SE2.E2_PREFIXO,
		SE2.E2_NUM 
	FROM 
		%table:SE2% SE2,%table:QEK% QEK 
	WHERE 
		SE2.E2_FILIAL= %xfilial:SE2% AND 
		SE2.E2_PREFIXO<> %exp:cPrefixo% AND 
		SE2.%notDel% ORDER BY %Order:SE2% 
EndSql

Características operacionais - Sintaxe

• O bloco de código onde será escrito o comando SELECT, deve sempre ser iniciado com BeginSQL Alias e finalizado com EndSQL.
• Partes do código-fonte que devem ser substituídos aparecem entre os sinais de % (porcentagem). Essas expressões possuem tratamentos especiais em momento de execução.
• Qualquer instrução colocada entre BeginSQL ... EndSQL, que não seja uma expressão %...%, será inserida na query que será enviada para o banco de dados, de forma literal.
• A partir da lib label (20180820) os campos do tipo 'M' (memo) são retornados na query. Para isto é necessário verificar a versão do DBAccess em uso (melhoria suportada a partir da Build  20180222)
• Variáveis, expressões e funções aparecem iniciando com %exp:%.
• Em column deve-se especificar campos da query que são do tipo de dado data, lógico ou numérico (DATE, LOGIC, NUMBER). Esta linha é trocada por chamadas à função TCSetField().
• %noparser% indica que a query não deve passar pela função ChangeQuery() antes de ser enviada ao banco de dados. Caso não especificado, o padrão é a string da query ser passada automaticamente pela função ChangeQuery().
• %table:% é substituída por RetSqlName().
• %temp-table:%cNomeDaTabela a variável cNomeDaTabela  é concatenada na query.
• %notDel% é substituída por D_E_L_E_T_=' '.
• %Order:% é substituída por SqlOrder (->(IndexKey())).
• Em ChangeQuery(),  é recomendado o uso da clausula Where, ainda que  1 = 1. Com a criação da regra de Acessos para visualização a empresa/un/filial logado, a query será alterada para incluir o filtro de acesso. A não inclusão prévia da clausula Where, associado ao uso de instruções como GroupBy, resultará em erro de execução.

Há 3 opções para o %Order:

1º Opção:

2º Opção:

3º Opção:

Limitações

• Não é permitido incluir funções no meio do código embedded. Se precisar, o valor deve ser guardado em uma variável antes do início do BeginSQL.
• Não é possível informar um array no comando 'As Numeric(nVar1,nVar2)'
• O uso do caractere "*" (asterisco) em uma Query, não pode ser o primeiro caractere de uma linha do código, mesmo que esta linha esteja entre o BEGINSQL e o ENDSQL, pois o pré-compilador do AdvPL entende que uma linha iniciada com o caractere  "*" é uma linha de comentário, ignorando o * e as demais instruções dessa linha. 

Exemplo

tam_cp := GetE2ValorSize() 
BeginSql alias 'E2TEMP' 
	column E2_EMISSAO as Date, 
	E2_VALOR as Numeric(tam_cp,2) . . . 
EndSql

Erros de compilação

Caso seja utilizado algum argumento inválido para especificar as colunas ou erros de sintaxe nas expressões que serão transformadas para montagem da query, a compilação do código-fonte é interrompida com a ocorrência Syntax Error, informando a linha em que a primeira ocorrência foi encontrada.

EndSQL (Error C2001 Syntax error:)
Caso a ocorrência de compilação aponte diretamente para a linha do código-fonte, em que está escrita a instrução EndSQL, verifique se existe algum espaço em branco ou tabulação, a partir do início da linha, antes da instrução EndSQL. A versão atual deste ambiente não suporta esta declaração, exigindo que a instrução EndSQL seja alinhada à esquerda do código-fonte, sem espaço ou tabulações.

Erros de execução

• Query Argument Error: Alias [XXX] already in use.
Caso a instrução BeginSQL especifique um alias que já esteja aberto (em uso), a aplicação é abortada com ocorrência de erro fatal, informando em XXX o alias utilizado.

• Query Argument Error: Invalid Value Type [X]
Caso alguma expressão informada na query, através da tag %exp:...%, retorne um valor do tipo diferente de C (Caracter), D (Data), N (Numérico) ou L (Lógico), a aplicação é abortada com ocorrência de erro, em que o tipo do argumento inesperado é apresentando em [X].

• Type Mismach on +
Esta ocorrência, se reproduzida, informará na pilha de chamadas o número da linha do código-fonte correspondente à instrução EndSQL. Essa ocorrência ocorre caso alguma função intermediária do engine do Embedded SQL, excluíndo-se as funções especificadas na query com a sintaxe %exp:...%, retornar um conteúdo não caractere que será acrescentado na query. Esta ocorrência é mais dificil de localizar, sendo útil nestes casos a análise do arquivo temporário gerado pelo TOTVS | Development Studio, no momento da compilação.

• Help NOFUNCW - Função __EXECSQL
Caso um código-fonte Embedded SQL seja executado em um repositório que não tenha sido atualizado ou que não seja um repositório para o ambiente TOTVS | DBAccess (RPODB=TOP), a aplicação exibirá essa ocorrência, indicando que a função interna de execução da query não está presente no ambiente. Verifique se a LIB está atualizada e se o RPO, em uso, pertence a um ambiente TOTVS | DBAccess.

Característica operacionais - Depuração

Dada a montagem da query, não é possível depurar o bloco do código/codeblock compreendido entre as intruções BeginSQL e EndSQL, não sendo considerados pontos de parada de depuração (BreakPoints), caso colocados neste intervalo do código. A colocação de pontos de parada deve ser realizada antes ou depois desse bloco.

Função auxiliar - GetLastQuery()
Após a abertura do cursor, no alias especificado, a função GetLastQuery() retorna um array, com 5 elementos, onde estão disponíveis as seguintes informações sobre a query executada.

[1] cAlias - Alias usado para abrir o cursor.
[2] cQuery - Query executada.
[3] aCampos - Array de campos com critério de conversão especificados.
[4] lNoParser - Caso verdadeiro (.T.), não foi utilizada a função ChangeQuery() na string original.
[5] nTimeSpend - Tempo, em segundos, utilizado para abertura do cursor.

Exemplo mais completo

AdvPL

BeginSql alias 'E2TEMP' 
    column E2_EMISSAO as Date, 
    E2_VALOR as Numeric(tam_cp,2) 
    column QEK_SKLDOC As Logical 
  SELECT 
    SE2.E2_PREFIXO,
    SE2.E2_NUM, 
    SE2.E2_FORNECE, 
    SE2.E2_LOJA,
    SE2.E2_VALOR, 
    SE2.D_E_L_E_T_ DEL1, 
    QEK.D_E_L_E_T_ DEL2 , 
    QEK.QEK_SKLDOC, 
    SE2.R_E_C_N_O_ SE2RECNO 
  FROM 
    %table:SE2% SE2,
    %table:qeK% QEK 
  WHERE 
    SE2.E2_FILIAL= %xfilial:SE2% AND 
    qek.%notDel% and 
    SE2.E2_PREFIXO<> %exp:cPrefixo% AND 
    SE2.E2_NUM<> %exp:(cAlias)->M0_CODIGO% AND 
    SE2.E2_NUM<>45 AND 
    SE2.E2_FORNECE=%exp:Space(Len(SE2->E2_FORNECE))% AND 
    SE2.E2_EMISSAO<>%exp:MV_PAR06% AND 
    SE2.E2_LOJA<>%exp:MV_PAR05% AND 
    SE2.E2_VALOR<>%exp:MV_PAR04% AND 
    qek.QEK_SKLDOC<>%exp:MV_PAR03% And 
    SE2.%notDel% 
  ORDER BY 
      %Order:SE2,1% 
EndSql

Código-fonte gerado pelo pré-compilador (PPO)

Embedded SQL - Boas Práticas

A formação aplicada no embedded SQL segue a formatação horizontal padrão e por ser um comando, deve seguir a capitulação heterogénea:

// EmbeddedSQLQuery.prw

#INCLUDE "TOTVS.CH"

Function EmbeddedSQLQuery()
BeginSQL Alias cAliasTrb
	SELECT R_E_C_N_O_ RECNOSN1
		FROM %Table:SN1%
		WHERE N1_FILIAL = %XFilial:SN1% AND
			N1_CBASE >= %Exp:MV_PAR01% AND
			N1_CBASE <= %Exp:MV_PAR02% AND
			N1_ITEM >= %Exp:MV_PAR03% AND
			N1_ITEM <= %Exp:MV_PAR04% AND
			N1_GRUPO >= %Exp:MV_PAR05% AND
			N1_GRUPO <= %Exp:MV_PAR06% AND
			N1_AQUISIC >= %Exp:MV_PAR07% AND
			N1_AQUISIC <= %Exp:MV_PAR08% AND
			%Exp:cWhere% AND
			%NotDel%

EndSQL

Return

Limitações

O Embedded SQL trabalha em dois momentos, na pré-compilação e em tempo de execução. Durante esses processamentos, diversos valores da query são substituídos por uma interrogação (?), logo esse é um caractere reservado do Embedded SQL e utilizá-lo tanto na query quanto nos valores, pode levar a cenários de não conformidade.

Para situações que sua query ou seus valores de substituição presentes dentro dos percentuais possuem o caractere de interrogação, recomendamos o uso das seguintes classes:

• FWPreparedStatement - FWPreparedStatement - Frameworksp - TDN (totvs.com)
• FWExecStatement - FWExecStatement - Frameworksp - TDN (totvs.com)