Estou com um problema para validar queries no Ambiente Postgres com Linux 64 (Em Docker).
Docker de Referência: https://github.com/emebatista/docker-protheus-2310
Versão do DBACCESS:
versão 23.1.1.4
Versão do Postgres:
PostgreSQL 15.7 (Debian 15.7-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
Versão do sistema Linux:
PRETTY_NAME="Debian GNU/Linux 12 (bookworm)" NAME="Debian GNU/Linux" VERSION_ID="12" VERSION="12 (bookworm)" VERSION_CODENAME=bookworm ID=debian HOME_URL="https://www.debian.org/" SUPPORT_URL="https://www.debian.org/support" BUG_REPORT_URL="https://bugs.debian.org/"
Temos o costume em buscar queries compiladas, queries armazenadas em banco de dados ou até mesmo ferramentas para validar a query pelo Protheus, mas em todos os casos temos os mesmos problemas.
O mesmo problema não ocorre ao fazer as queries diretamente pela ferramenta DBEAVER conectada ao BD Postgres.
Erro gerado por umas das queries:
THREAD ERROR ([15260], carlos.niemeyer, WORK_NIEMEYER) 26/06/2024 08:39:56 : TOP Error -19 - Invalid Query Fields ( Syntax Error ). Query : SELECT EXTRACT(DAY FROM DIFF) AS DAYS,EXTRACT(HOUR FROM DIFF) AS HOURS,EXTRACT(MINUTE FROM DIFF) AS MINUTES,FLOOR(EXTRACT(SECOND FROM DIFF)) AS SECONDS,ROUND(EXTRACT(SECOND FROM DIFF) - FLOOR(EXTRACT(SECOND FROM DIFF)),3) AS MILISECONDS FROM (SELECT TO_TIMESTAMP('18:36:54.507','HH24:MI:SS.FF3') - TO_TIMESTAMP('18:36:06.786','HH24:MI:SS.FF3') AS DIFF) AS SUBQRY ( From tPostgresConnection::GetQueryFile ) Thread ID [40] User [Administrador] IO [2263] Tables [53] MaxTables [55] Comment [PROCQUERY - TCPIP - ThreadID(15260)] SP [ ] Traced [No] SQLReplay [No] InTran [No] DBEnv [POSTGRES/PROTHEUS] DBThread [(ID 1952)] Started [26/06/2024 11:39:50] IP [172.18.0.1:1137] IDLE [ ] InTransact [ ] Memory [0] Running [ROP_QUERY for 0 s.] RCV [229696] SND [626915] TCBuild [20230303-20240401] TCVersion [23.1.1.4] ARCH [64] OS [LINUX] BUILD [RELEASE]
Query na íntegra:
SELECT EXTRACT(DAY FROM DIFF) AS DAYS,EXTRACT(HOUR FROM DIFF) AS HOURS,EXTRACT(MINUTE FROM DIFF) AS MINUTES,FLOOR(EXTRACT(SECOND FROM DIFF)) AS SECONDS,ROUND(EXTRACT(SECOND FROM DIFF) - FLOOR(EXTRACT(SECOND FROM DIFF)),3) AS MILISECONDS FROM (SELECT TO_TIMESTAMP('18:36:54.507','HH24:MI:SS.FF3') - TO_TIMESTAMP('18:36:06.786','HH24:MI:SS.FF3') AS DIFF) AS SUBQRY
Já foi tentado tirar quebras de Linhas CRLF, espaços, porém o resultado é o mesmo.
Outra Query com Problema seria:
SELECT USER AS USUARIO_BD
Gerando o Erro Abaixo:
THREAD ERROR ([22944], carlos.niemeyer, WORK_NIEMEYER) 26/06/2024 08:50:04 : Error : 1 (42601) (RC=-1) - ERROR: syntax error at or near ""; Error while executing the query Error : 1 () (RC=-1) - ( From tPostgresConnection::GetQueryFile ) Thread ID [61] User [Administrador] IO [2297] Tables [53] MaxTables [55] Comment [PROCQUERY - TCPIP - ThreadID(22944)] SP [ ] Traced [No] SQLReplay [No] InTran [No] DBEnv [POSTGRES/PROTHEUS] DBThread [(ID 1998)] Started [26/06/2024 11:49:24] IP [172.18.0.1:1137] IDLE [ ] InTransact [ ] Memory [0] Running [ROP_QUERY for 0 s.] RCV [229834] SND [629673] TCBuild [20230303-20240401] TCVersion [23.1.1.4] ARCH [64] OS [LINUX] BUILD [RELEASE]
Se a Query é executada por dentro do Protheus, via DBACCESS, ele falha se a query for enviada com espaços, tabs ou quebras de linhas, o que não ocorre quando estamos executando a query pelo DBeaver.
Existe relamente essa limitação de espaços e caracteres invisíveis nas queries do Postgres pelo DBACCESS ou tem algo a ver com o sistema operacional Linux?
