Comportamento de queries - Colunas calculadas
Após enviar ao banco de dados uma solicitação de abertura de query, em caso de sucesso na operação, o TOTVS | DBAccess obtém do banco de dados a quantidade de colunas que serão retornadas, o tamanho, tipo e especificações de cada uma das colunas, para retornar ao sistema uma estrutura definida de informações. Além disso, o cursor aberto, é lido conforme são solicitados os próximos registros (função DBSkip()) e a cada requisição, uma linha do cursor é retornada até não haver mais linhas no cursor, atingindo o final do arquivo AdvPL (função EOF()).
| Abrangência |
|---|
| ERP 10 e 11 |
Ao especificar uma coluna de retorno na query, que corresponde as informações lidas diretamente de uma ou mais colunas(s) da tabela, cada coluna retornada pela query contém uma definição do tipo e tamanho correspondente ao campo de origem do dado de retorno. Deste modo, se considerarmos, por exemplo, que a query SELECT A1_A_FILIAL FROM SA1990, onde o campo A1_FILIAL na tabela SA1990 é um campo do tipo varchar, com 2 bytes de tamanho, quando o cursor for aberto no banco de dados, o TOTVS | DBAcces irá obter do banco de dados que o result-set gerado possui apenas uma coluna, e esta coluna é do tipo varchar, com no máximo 2 bytes de tamanho.
Os banco de dados não apenas permitem recuperar retornos diretamente lidos de uma ou mais tabelas, mas também é possível abrir um cursor onde uma ou mais colunas contenham um resultado calculado, utilizando-se de expressões e/ou funções nativas do banco de dados. Para esses tipos de retorno, cada banco de dados pode apresentar comportamentos diferenciados ao descrever o tamanho de retorno da(s) coluna(s).
Este comportamento pode variar de acordo com o banco de dados, expressão e/ou funções utilizadas para gerar a coluna de retorno, além das possíveis diferenças entre versões e builds do mesmo banco de dados e/ou parametrizações específicas do banco de dados e/ou aplicação Client/ODBC/API utilizada para acesso ao banco.
Para ilustrar melhor estas diferenças, observe abaixo os resultados obtidos com queries, em banco de dados distintos, onde foi criada uma tabela com a mesma estrutura, e 10 linhas de dados, e nela foram executadas algumas queries, onde temos como retorno uma coluna calculada. A tabela possui os campos CPOC, tipo Caractere, tamanho 10 bytes, numerados de 0000000001 a 0000000010 e um campo numérico CPON, alimentado com valores positivos. Para cada query, apresentamos o valor retornado ao AdvPL, contendo o tipo de dado retornado e o tamanho (len) da coluna, no formato (Tipo (tamanho)).
Query SELECT MIN(CPOC) CPOTESTE FROM TESTRET
Retorno Todos os bancos retornaram uma coluna com tamanho de 10 bytes.
MSSQL 2000 -> C ( 10) [0000000001]
Informix -> C ( 10) [0000000001]
Oracle -> C ( 10) [0000000001]
MySQL -> C ( 10) [0000000001]
DB2 -> C ( 10) [0000000001]
Query SELECT MIN(CPOC) CPOTESTE FROM TESTRET WHERE CPON < 0
Retorno Todos os bancos retornaram uma coluna vazia.
Observações: Para mais detalhes sobre o banco de dados Postgres, consulte o tópico Comportamento diferenciado com banco de dados Postgres neste documento.
MSSQL 2000 -> C ( 10) [ ]
Postgres -> C ( 0) []
Informix -> C ( 10) [ ]
Oracle -> C ( 10) [ ]
MySQL -> C ( 10) [ ]
DB2 -> C ( 10) [ ]
Query SELECT LEFT(CPOC,5) CPOTESTE FROM TESTRET
Retorno Todos os bancos de dados que suportam a função Left() retornaram uma coluna de informações, onde os 5 primeiros bytes eram '00000'.
Observações: Os bancos de dados retornaram que a coluna tem 5 bytes de tamanho. Porém, o banco de dados DB2 informa que a coluna tem tamanho de 4000 bytes. Este mesmo retorno foi obtido no DB2 quando utilizadas as funções Right(), Replace() e Repeat(). Nos bancos de dados DB2 e Postgres, foi retornado o tamanho de 2000 bytes para essas funções.
SQL -> C ( 5) [00000]
MySQL -> C ( 5) [00000]
DB2 -> C ( 4000) [00000 (...) ]
Query SELECT SUBSTRING(CPOC,2,4) CPOTESTE FROM TESTRET ( ou ) SELECT SUBSTR(CPOC,2,4) CPOTESTE FROM TESTRET
Retorno Todos os banco de dados retornaram uma coluna de tamanho 4 bytes, exceto o POSTGRES, que retornou tamanho de 2000 bytes.
MSSQL 2000 -> C ( 4) [0000]
Postgres -> C ( 2000) [0000 (...) ]
Informix -> C ( 4) [0000]
Oracle -> C ( 4) [0000]
MySQL -> C ( 4) [0000]
DB2 -> C ( 4) [0000]
Devido a essas diferenças de comportamento, alguns cuidados devem ser tomados ao desenvolver aplicações que utilizam colunas calculadas em queries. Nestes casos, deve-se utilizar uma função de banco de dados para assegurar que o banco de dados retorne o tamanho de coluna adequado ao TOTVS | DBAccess. Exemplo:
SELECT REPLACE(CPOC,'0','_') AS CPOTESTE FROM TESTRET
Se o desenvolvedor tem conhecimento que o campo, da base de dados, CPOC desta tabela possui 10 bytes de tamanho, deve-se fazer um cast desse retorno para char ou varchar com tamanho de 10 bytes. Dessa forma, o banco de dados retorna o tamanho esperado da coluna para o TOTVS | DBAccess quando a query é abertura. Isto evita que espaços desnecessários trafeguem pela rede para atender a requisição e, que este valor excedente seja usado para alimentar uma variável da linguagem AdvPL.
A seguir, observe um exemplo de como utilizar esse recurso na query descrita acima, utilizando uma sintaxe aceita pela maioria dos bancos de dados homologados:
SELECT CAST( REPLACE(CPOC,'0','_') AS VARCHAR(10)) CPOTESTE FROM TESTRET
No entanto, é importante salientar que cada banco de dados possui, com pequena variações, uma sintaxe para permitir o CAST de um retorno. Para obter mais detalhes sobre essa informação, consulte a documentação do banco de dados em questão.
Comportamento diferenciado com banco de dados Postgres
O banco de dados Postgres possui um comportamento diferenciado entre as builds do server, do banco de dados, e uma parametrização na configuração do ODBC que pode interferir na maneira como os atributos das colunas de retorno de um query são retornados.
Em versões anteriores ao Postgres 8.x, as diferenças de comportamento em relação aos demais banco de dados é significativa em praticamente todas as funções de retorno calculado. Para estes casos, em se tratando de Postgres versão anterior a 8.x, a precisão de retorno da coluna corresponde ao tamanho máximo de retorno do tipo varchar. (Este tamanho máximo pode ser configurado na conexão ODBC do Postgres, e o valor padrão é 65536 bytes.).
Na função SubStr(), mesmo que o terceiro parâmetro esteja configurado com a quantidade final limitada de bytes, o banco de dados descreve essa coluna de retorno como sendo um varchar() com precisão do maior valor varchar() que será retornado pelo client. Na função Max(), quando especificado um campo da base de dados do tipo Char ou varchar, temos um comportamento semelhante.
Quando foram homologados os bancos de dados Postgres para uso como o ERP e o TOTVS | DBAccess, praticamente não eram utilizadas queries com colunas calculadas nas aplicações AdvPL e este comportamento diferenciado não foi percebido. Conforme as rotinas foram evoluindo, buscando mais performance através do uso de queries, tornou-se perceptível essas diferenças operacionais.
Algumas destas diferenças operacionais foram assimiladas pelas funções do ERP, por exemplo a ChangeQuery(), que em versão atualizada (Microsiga Protheus 10 e 11, fonte aplib070.prw com data igual ou superior a 08/03/2008), aplicam o CAST() automaticamente para SELECT MAX(CAMPO), quando o banco de dados é Postgres, e o campo selecionado for um campo da base de dados, presente no dicionário de campos (SX3).
Para todos os efeitos, para utilização com versões homologadas de Postgres inferior a 8.x, é necessário alterar a configuração ODBC do Postgres, para limitar o retorno do tipo varchar para 2000 bytes, pois o retorno do valor padrão (65536 bytes) não é suportado pelo TOTVS | DBAccess. Alteramos essa configuração no arquivo .odbc.ini, no Linux, e/ou no arquivo de configuração do ODBC do Postgres utilizado, inserindo na seção da conexão/identificação do banco de dados a seguinte chave:
MaxLongVarcharSize=2000
Atualmente, está em processo de homologação as versões mais recentes (8.x) do banco de dados Postgres. Quando homologado, a informação estará disponível na TDN - TOTVS Developer Network.
Visão Geral
Import HTML Content
Conteúdo das Ferramentas
Tarefas