Desenvolvendo queries no Protheus

bjetivo

Abordar o desenvolvimento de queries utilizando o framework Protheus.

O DBAcess (antigo TopConnect)

O DBAccess é um produto que permite a conexão do ERP Protheus aos diversos bancos de dados SQL suportados.

O DBAcess possui várias funções. Destacamos as mais importantes:

Comunicação com o banco de dados.
Gerenciamento das conexões de estações Protheus x banco da dados.
Tradução dos comandos e funções enviados pela RDD (ISAM) em linguagem SQL.
Envio e tratamento dos comandos SQL nativos ao banco de dados.
Gerenciamento de travas (locks) através do lock manager.

O DBAccess permite aos desenvolvedores trabalhar com banco de dados SQL usando uma metodologia ISAM, exatamente como se estivessem usando um banco ISAM nativo.

No entanto, devido às diferenças intrínsecas ao dois modelos, esta forma de acesso é muito lenta, além de onerar demasiadamente o banco de dados, a rede e o próprio DBAccess.

A recomendação é utilizar sempre que possível a linguagem SQL para acesso aos dados quando se utiliza DBAccess.

A leitura de um conjunto de resultados (result set) obtido através de uma query é muitíssimo mais rápida que a varredura na tabela real usando ISAM.

Além disso, a linguagem SQL permite o uso de funções de agregação, entre outros, que otimizam ainda mais o desempenho.

Implementação no banco de dados

Listamos abaixo algumas características exigidas para a manipulação de tabelas pelo DBAccess.

Campos especiais: as tabelas gerenciadas pelo DBAccess devem possuir campos de controle. Eles são criados automaticamente quando da criação das tabelas. São eles:

Campo	Descrição
R_E_C_N_O_	Número do registro. É a chave primária de todas as tabelas de dados e por consequência único. É alimentado automaticamente quando da inserção de uma linha via TOP. É um inteiro de 4 bytes. Permite compatibilidade ISAM com a função DbGoto().
D_E_L_E_T_	“Flag” de exclusão. Indica a exclusão “lógica” de uma linha ou registro, ou seja, a linha não é mais considerada no Protheus mas permanece no banco de dados. É um caractere de tamanho 1, e os conteúdos permitidos são branco (linha ativa) e asterisco (linha excluída). Criado também por questões de compatibilidade com ISAM.
R_E_C_D_E_L_	Número do registro do item excluído. Este campo recebe o valor de R_E_C_N_O_ quando da exclusão lógica do registro. Este campo é necessário pois caso haja exclusão de duas linhas com a mesma chave única alternativa (X2_UNICO), haveria duplicidade de chave. Dessa forma, todas as chaves únicas criadas por X2_UNICO recebem automaticamente R_E_C_D_E_L_ no final.

Implementação no banco de dados

Campos do tipo DATA.

Os campos tipo data são armazenados como caractere de 8 dígitos.

Campos do tipo NUMÉRICO

Os campos do tipo numérico são armazenados como FLOAT (ponto flutuante) de 8 dígitos. Os campos são armazenados de forma binária, dessa forma um campo de 8 dígitos pode armazenar um número muito maior que 8 inteiros ou decimais.

Campos do tipo LÓGICO (BOOLEANO)

Os campos tipo lógico são armazenados como caractere de 1 dígito.
Campos do tipo MEMO (texto grande)

Os campos tipo memo são armazenados como BLOB (binary large object). O nome do tipo de campo pode variar conforme o banco de dados. No MS Sql Server o campo é armazenado como image. Os campos MEMO vituais estilo Protheus (MSMM) são armazenados de forma diferente, na tabela SYP.

Tabela TOP_FIELD

A tabela Top Field é utilizada para conversão automática dos tipos de dados diferentes de caractere para os tipos correspondentes na linguagem ADVPL, quando utilizamos modo de acesso ISAM. Ela também armazena a quantidade de inteiros e casas decimais dos campos numéricos.

A Top Field é alimentada automaticamente durante a criação de uma tabela pelo DBAccess e também é atualizada quando as propriedades dos campos são alteradas usando comandos e funções padrões do ADVPL.

Dessa forma, quando um campo é criado ou mantido pelo configurador, atualização de versão ou update, a top field é corretamente alimentada.

Por outro lado, se a tabela é manipulada diretamente no banco, pode ocorrer divergência entre a Top Field e a tabela física. Isso vai causar mau funcionamento do sistema.

A tabela Top Field só faz a conversão automática de tipo no modelo ISAM. Quando trabalhamos com result sets de querys, devemos utilizar a função TcSetField() para converter manualmente os tipos de dados.

A linguagem SQL

Definição:

•Structured Query Language, ou Linguagem de Consulta Estruturada ou SQL, é uma linguagem de pesquisa declarativa para banco de dados relacional (base de dados relacional). Muitas das características originais do SQL foram inspiradas na álgebra relacional.

•O principal objetivo do SQL é o de providenciar um método de acesso às bases de dados de forma interativa através de consultas (queries) à base de dados.

•A linguagem SQL baseia-se na lógica de conjuntos ao invés da lógica procedural (varredura).

Vantagens do SQL:

•É uma linguagem universal de acesso a dados, e apesar das variações, roda em uma enorme quantidade de plataformas.

•Concentra-se nos resultados desejados, deixando ao banco de dados a missão de escolher a melhor estratégia para consulta dos dados.

•Reduz o trafego de rede pois muitos dados são avaliados no servidor do banco de dados, sem que obrigatoriamente sejam enviados à aplicação, como no ISAM.

Desvantagens:

•A lógica de conjuntos não é natural para muitas pessoas, o que exige certo tempo de adaptação.

•Queries grandes e complexas podem ser tornar muito abstratas, dificultando o entendimento.

•Devido ao desconhecimento dos fundamentos da linguagem, pode-se fazer uma query esperando uma resposta e receber algo totalmente diferente, e o pior, sem saber disso.

Queries no Protheus

Existem duas formas de executar queries no Protheus:

Construção de string: nesse modelo, o comando SQL (statement) é construído em uma varíável caractere e enviado ao banco de dados usando a função TcGenQry() ( mais usado ).
Embedded SQL: nesse modelo a query é construída diretamente no programa ADVPL usando uma sintaxe específica.

Em ambos os modelos, o funcionamento e a performance são os mesmos.

Abaixo listamos as principais funções e comandos de suporte a queries no ADVPL:

Comando / função	Descrição
#IFDEF TOP / #ENDIF	Diretiva de compilação utilizada para separar o trecho de código a ser usado apenas em TOP / SQL.
TcGenQry( )	Efetua o disparo da query passada como string para o DBAccess.
dbUseArea()	Efetua a criação de uma área de trabalho para o record set retornado por TcGenQry()
GetNextAlias()	Retorna um alias para ser utilizado no record set definido em dbUseArea()
ChangeQuery()	Função de framework para compatibilizar a sintaxe das queries aos diferentes bancos de dados (SQL Server, Oracle, IBM DB2, Informix, MySql entre outros). Seu uso é obrigatório, salvo em casos especiais.
TcSetField()	Compatibiliza os tipos de dados diferentes de caractere retornados pela query aos tipos do ADVPL.
SqlOrder()	Converte uma sintaxe de chave índice em ADVPL para sintaxe SQL para uso na cláusula ORDER BY da query.
xFilial( )	Usada na construção da query para selecionar a filial corrente, da mesma forma que em ISAM.
RetSqlName()	Retorna o nome físico da tabela, baseado em um alias, para ser usado em na contrução do “SELECT ... FROM TABELA” da query.
DTOS()	Usada para quando deseja-se comparar um campo data do banco com uma variável tipo data do ADVPL.
dbGoTop()	Move o ponteiro do record set para a primeira linha.
dbSkip()	Move o ponteiro do record set para a linha seguinte. Diferentemente da varredura ISAM, o ponteiro só poderá ser movido “para frente”. Usado nos “loops” de varredura.
Eof()	Informa se o record set chegou ao fim.
dbCloseArea()	Fecha a área de trabalho e apaga o record set. Após essa função, o record set não pode mais ser acessado. Uso obrigatório ao final do processo.
TcGetDb()	Devolve o nome do SGBD (sistema gerenciador de banco de dados) em uso. Exemplo : “MSSQL” -> SQL Server, "ORACLE“ -> Oracle. Usado apenas em casos especiais, quando se deseja difenciar sintaxe, etc...
BeginSql	Comando que marca o início de uma query em “embedded SQL”
EndSql	Comando que marca o término de uma query em “embedded SQL”
%	Marca o início e o fim de uma expressão em “embedded SQL”.

Desenvolvendo Queries

Nos primeiros momentos do DBAccess, um tipo de query foi bastante utilizado pois necessitava de poucas mudanças na aplicação original. É a chamada “query de recno”.

Apesar de menos eficiente, esse modelo ainda é útil quando é necessário posicionar a tabela real para alterar dados ou excluir.

Esta query retorna um campo R_E_C_N_O_ para que na varredura seja efetuado um dbgoto() no registro real.

Ao contrário do que se poderia supor, esta abordagem é mais rápida que simplesmente varrer a tabela ISAM, mesmo indexada, pelas seguintes razões:

Efetuar um dbkip() no record set é muito mais rápido que o dbskip() na tabela real.
O dbGoto() na tabela real é muito eficiente, pois o R_E_C_N_O_ é a chave primária de todas as tabelas e seu índice é diferenciado e muito rápido.

A query consegue eficientemente desprezar as linhas deletadas logicamente, ao passo que o dbskip() na tabela física ter de desprezar sequencialmente os excluídos. Existem casos reais na TOTVS de um único dbskip() levar 20 minutos por ter encontrado 1 milhão de registros excluídos no início da tabela.

Exemplo de “query de recno”

Esta query posiciona registros pois chamará uma função que depende do registro posicionado.

O primeiro campo retornado é o R_E_C_N_O_ que recebe o apelido SC9RECNO.

Exemplo

lQuery    := .T.  
cAliasSC9 := GetNextAlias()


cQuery := "SELECT R_E_C_N_O_ SC9RECNO,C9_PEDIDO,C9_ITEM "
cQuery += "FROM "+RetSqlName("SC9")+" SC9 "
cQuery += "WHERE SC9.C9_FILIAL='"+xFilial("SC9")+"' AND "
If ( lInverte )
	cQuery += "SC9.C9_OK<>'"+cMarca+"' AND "
Else
	cQuery += "SC9.C9_OK='"+cMarca+"' AND "
EndIf
cQuery += "SC9.C9_BLEST<>'10' AND "
cQuery += "SC9.C9_BLEST<>'ZZ' AND "
cQuery += "SC9.C9_BLCRED<>'10' AND "
cQuery += "SC9.C9_BLCRED<>'ZZ' AND " 
cQuery += "SC9.D_E_L_E_T_=' ' "
cQuery += "AND "+aFiltro[2]
cQuery := ChangeQuery(cQuery)
dbUseArea(.T.,"TOPCONN",TcGenQry(,,cQuery),cAliasSC9,.T.,.T.)
TcSetField( cAliasSC9, “SC9RECNO”, “N”, 10, 0 ) // Utilizar tcsetfield para o campo RECNO 
//É efetuada varredura do record set e efetuado dbGoto() na tabela real
dbSelectArea(cAliasSC9)
While !Eof()
  .
  .
  .
If lValido
  If lQuery
	dbSelectArea("SC9")
	dbGoto((cAliasSC9)->SC9RECNO)
  EndIf
  dbSelectArea("SC9")
  If Empty(c460Cond) .Or. &(c460Cond)
  //------------------------------------------------
  // Verifica ponto de entrada
  //------------------------------------------------
  If ExistBlock("MA461EST")
	lValido := ExecBlock("MA461EST",.F.,.F.)
  EndIf
  If lValido
	Begin Transaction
  	a460Estorna()
	End Transaction
  EndIf
  EndIf
EndIf
.
.
EndIf

Sequência para desenvolvimento de uma query básica no Protheus:

•Defina um Alias (apelido) para o record set da query. Não utilize alias fixo, pois em caso de recursividade haverá erro por duplicidade. Utilize GetNextAlias():

cAliasAB9 := GetNextAlias()

•Comece a escrever a string da query. Evite usar “*” na seleção de campos, pois todos os campos serão retornados aumentando o trárego de rede.

cQuery := "SELECT AB9_DTFIM, AB9_HRFIM FROM “

•Utilize RetSqlName( cAlias ) para retornar o nome físico da tabela.

cQuery += RetSqlName( "AB9" ) + " AB9 "

•Efetue o filtro por filial. Sempre iguale o campo filial ao xFilial da tabela correspondente. Nunca compare o campo _FILIAL de uma tabela com o campo _FILIAL de outra. Algumas rotinas de processamento multi-filial podem comparar o campo _FILIAL a uma variável contendo o código da filial (exceção).

cQuery += "WHERE "
cQuery += "AB9_FILIAL='" + xFilial( "AB9" ) + "' AND “

•Efetue o filtro para remover linhas excluídas. Compare D_E_L_E_T_=‘ ‘ pois é mais rápido que fazer D_E_L_E_T_<>’*’

  cQuery += "AB9.D_E_L_E_T_=' '"

•Coloque o maior número possível de condições de filtro na query. Quanto mais restritivo for o filtro, menos registros vão ser incluídos no record set e menos trafégo ocorrerá na rede local

cQuery += "AB9_NUMOS='"  + ( cAliasAB7 )->AB7_NUMOS + ( cAliasAB7 )->AB7_ITEM + "' AND “

•Utilize a cláusula ORDER BY apenas quando precisar de resultados ordenados. ORDEY BY diminui a performance da query.

•Utilize a função ChangeQuery() para que a sintaxe da query seja adequada aos diferentes bancos de dados.

cQuery := ChangeQuery( cQuery )

•Efetue o disparo da query e criação de seu record set através do uso conjunto de dbUseArea() e TcGenQry(). Nessa chamada utilizaremos:

“TOPCONN” -> Nome da RDD do DBAccess
cQuery -> String que contém a query (statement) propriamente dito.
cAliasAB9 -> Alias do record set definido por GetNextAlias()

  DbUseArea( .T., "TOPCONN", TcGenQry( , , cQuery ), cAliasAB9, .F., .T. )

•Caso existam campos com tipo diferente de caractere na lista de retorno, deve-se utilizar TcSetField() para converter para um tipo válido no ADVPL. Nesse caso, converteremos o campo AB9_DTFIM para o tipo data do ADVPL.

TcSetField( cAliasAB9, "AB9_DTFIM", "D", 8, 0 )

•Quando for necessário converter uma grande quantidade de campos de uma tabela, pode-se usar a estrutura da mesma usando a função dbStruct() e efetuar uma varredura.

aStruAB9 := AB9->( dbStruct() )
For nLoop := 1 to Len( aStruAB9 )
If aStruAB9[ nLoop, 2 ] <> “C” // só quando não for caractere
TcSetField( cAliasAB9, aStruAB9[nLoop,1], aStruAB9[nLoop,2], aStruAB9[nLoop,3], aStruAB9[nLoop,4])
EndIf

Next nLoop

•Utilizar o record set conforme desejado. Nesse caso, será efetuada varredura por While.Utilizar dbSkip() para varrer e Eof() para testar o fim do record set.

While (cAliasAB9)->( Eof())  
  dDataF := Max(dDataF,( cAliasAB9 )->AB9_DTFIM)
  If ( ( cAliasAB9 )->AB9_DTFIM == dDataF )
    cHoraF := If(cHoraF< ( cAliasAB9 )->AB9_HRFIM,;
       ( cAliasAB9 )->AB9_HRFIM,cHoraF)
  EndIf
  ( cAliasAB9 )->( DbSkip() )
EndDo

•Ao final do processo, excluir o record set com dbCloseArea(). Após o dbCloseArea(), é recomendável selecionar uma tabela real, pois o Protheus ficará sem tabela aberta e um GetArea() subsequente poderá derrubar o sistema

( cAliasAB9)->( dbCloseArea() )

dbSelectArea( “AB9” ) // seleção de tabela “preventiva”

Consultando mais de uma tabela

Em muitos casos, os dados que precisamos dependem da interação entre duas ou mais tabelas. O SQL é bastante eficiente para tratar este tipo de consulta, que chamamos de JOIN.

O tipo mais comum de JOIN é chamado de INNER JOIN. Neste modelo, devem existir dados tanto na primeira tabela (“esquerda”) quanto na segunda (“direita”) para que alguma linha seja recuperada.

O INNER JOIN pode ser escrito tanto no “estilo antigo”, em que os campos de uma tabela simplesmente são comparados com os campos de outra tabela, ou no “estilo novo” (ANSI) onde o relacionamento é explicitado.

Abaixo listamos um exemplo de INNER JOIN usando o “estilo antigo”. As tabelas AB9 (atendimentos) e AAG (tipos de ocorrências) são relacionadas através dos campo AB9_CODPRB e AAG_CODPRB (código de ocorrência). O que se deseja é trazer, junto dos dados do atendimento, o tipo da ocorrência (AAG_TIPPRB) que existe apenas em AAG.

cAlias := GetNextAlias()
cQuery := “”
cQuery += "SELECT AB9_DTINI,AB9_HRINI,AB9_DTFIM,AB9_HRFIM,AAG_TIPPRB FROM "       
cQuery += RetSqlName( "AB9" ) + " AB9," + RetSqlName( "AAG" ) + " AAG WHERE "
cQuery += "AB9_NUMOS='" + AB7->AB7_NUMOS + AB7->AB7_ITEM + "' AND "
cQuery += "AB9_CODPRB=AAG_CODPRB AND " 
cQuery += "AB9_FILIAL='" + xFilial("AB9") + "' AND "         
cQuery += "AAG_FILIAL='" + xFilial("AAG") + "' AND "
cQuery += "AB9.D_E_L_E_T_=‘ ' AND "
cQuery += "AAG.D_E_L_E_T_=‘ '" 
                      
cQuery := ChangeQuery( cQuery )
                 
DbUseArea( .T., "TOPCONN", TcGenQry( ,, cQuery ), cAlias, .F., .T. )

Tipos mais avançados de JOIN : OUTER JOIN

O OUTER JOIN permite que dados sejam recuperados mesmo que um do lados (tabela) não possua correspondência. Por isso é “externo”.

Existem duas variantes : LEFT JOIN preserva os dados da tabela da “esquerda”, ou primeira tabela mencionada, mesmo que não encontre uma linha correspondente na tabela da “direita”.

RIGHT JOIN preserva os dados da tabela da “direita”, ou segunda tabela mencionada, mesmo que não encontre uma linha correspondente na tabela da “esquerda”.

No Protheus, o OUTER JOIN pode ser escrito apenas em sintaxe ANSI. Sintaxes antigas como o *= do SQL Server não devem ser utilizadas, pois ocorrerá erro em outros SGBDs.

•Abaixo listamos um exemplo de OUTER JOIN usando LEFT JOIN. A tabela principal é a tabela DA1 (itens da tabela de preços) , portanto é a tabela da “esquerda”. A segunda tabela é a tabela SB1 (produtos), portanto a tabela da “direita”. Essa query traz os dados de DA1 mesmo que não exista uma linha correspondente em SB1.

cQuery := "SELECT DA1.*,DA1.R_E_C_N_O_ DA1RECNO, B1_DESC, B1_PRV1 FROM "
cQuery += RetSqlName("DA1")+ " DA1 "
cQuery += "LEFT JOIN " +RetSqlName("SB1")+ " SB1  "
cQuery += "  ON SB1.B1_FILIAL  = '"+xFilial("SB1")+"'"
cQuery += "  AND SB1.B1_COD     = DA1.DA1_CODPRO"
cQuery += "  AND SB1.D_E_L_E_T_ = ' ' "
cQuery += "WHERE DA1.DA1_FILIAL = '"+xFilial("DA1")+"'"
cQuery += "  AND DA1.DA1_CODTAB = '"+DA0->DA0_CODTAB+"'"
cQuery += "  AND DA1.D_E_L_E_T_ = ' ' "
cQuery += "ORDER BY "+SqlOrder(DA1->(IndexKey()))

Funções de agregação

As funções de agregação do SQL permitem que um conjunto de linhas sejam agregadas em uma única linha.

As funções de agregação podem otimizar a performance do sistema pois reduzem o número de linhas retornadas diminuindo o tráfego de rede.

Principais funções de agregação:

COUNT() – Contagem de registros
SUM() – Soma dos valores de uma expressão
MAX() – Máximo valor de uma expressão
MIN() - Mínimo valor de uma expressão

Ao informar uma função de agregação em um SELECT, não se pode trazer outros campos na seleção a menos que façam parte de uma cláusula GROUP BY. Isso ocorre porque a função de agregação é resultado de vários registros e não faz sentido trazer apenas um deles.

Esta query exemplo retorna a quantidade total de itens liberados de um pedido de vendas (que ainda não foram faturados), considerando todas as sequencias de liberação existentes (SC9).

cAliasSC9 := GetNextAlias()
cQuery := "SELECT SUM(C9_QTDLIB) C9_QTDLIB "
cQuery += "FROM "+RetSqlName("SC9")+" SC9 "
cQuery += "WHERE SC9.C9_FILIAL='"+xFilial("SC9")+"' AND "
cQuery += "SC9.C9_PEDIDO='" +(cCurSorSC6)->C6_NUM+"' AND "
cQuery += "SC9.C9_ITEM='"   +(cCurSorSC6)->C6_ITEM+"' AND "
cQuery += "SC9.C9_NFISCAL='"+Space(Len(SC9->C9_NFISCAL))+"' AND
cQuery += "SC9.D_E_L_E_T_=' ' "

cQuery := ChangeQuery(cQuery)

dbUseArea(.T.,"TOPCONN",TcGenQry(,,cQuery),cAliasSC9,.T.,.T.)
nQtdLib := (cAliasSC9)->C9_QTDLIB
dbCloseArea()
dbSelectArea("SC9")

Status do documento	Concluído
Data	25/03/2015
Versão	1.0
Versão anterior	1.0
Autores	Sérgio Luís de Alcântara Silveira

Páginas filhas

Desenvolvendo queries no Protheus