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).

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 | DBAccess 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)).

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 PostgreSQL

O banco de dados PostgreSQL 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 PostgreSQL 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 PostgreSQL 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 PostgreSQL, e o valor padrão é 255 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 valor máximo do 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 o banco de dados PostgreSQL para uso com 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 é PostgreSQL, e o campo selecionado for um campo da base de dados, presente no dicionário de campos (SX3).

Podemos contornar essa situação alterando na conexão ODBC (DSN) o parâmetro Unknown Sizes de Maximum para Longest, conforme imagens abaixo:

Em Linux (usando o arquivo .odbc.ini) ou por string de conexão, a configuração Unknown Sizes pode ser definida colocando a chave UnknownSizes=2, onde 0 é a opção Maximum, 1 é a opção Don't Know e 2 é Longest; já a configuração Max Varchar pode ser modificada através da chave MaxLongVarcharSize=255, onde 255 é o valor desejado.

Apesar dessa configuração, caso a query não retorne registros, o PostgreSQL colocará o tamanho do campo calculado usando o valor em Max Varchar, independente da configuração em Unknown Sizes. Portanto, para garantir a plena utilização com o banco de dados PostgreSQL, é necessário passar a query pela ChangeQuery, que fará as alterações necessárias para que o retorno venha conforme esperado.

Veja também

• Configuração ODBC PostgreSQL
• Bancos de dados homologados
• ChangeQuery