Problem with query on Firebird 2.5
-
- Posts: 13
- Joined: Fri 03 Sep 2010 14:33
Problem with query on Firebird 2.5
I have a table with the following schema in a Firebird 2.5 DATEbase with the connection being made by your DevArtInterbase driver (dbExpIDA.dll).
CREATE TABLE EFIN_ORDEMSERVICO (
CDORDEMSERVICO INTEGER NOT NULL,
NURECIBOOS INTEGER,
NUORDEMSERVICO INTEGER,
NMFAVORECIDO VARCHAR(100),
DTORDEMSERVICO TIMESTAMP,
FLATIVO CHAR(1) NOT NULL,
DEMOTIVOCANCELAMENTO VARCHAR(150),
VLTOTALRECIBOBRUTO NUMERIC(15,2),
VLDESCONTO NUMERIC(15,2),
VLTOTALRECIBOLIQ NUMERIC(15,2),
VLTOTALCONTABIL NUMERIC(15,2),
VLTOTALREEMBOLSO NUMERIC(15,2),
VLTOTALTAXAS NUMERIC(15,2),
DTPAGTO DATE,
FLISENTO CHAR(1),
CDSETOR INTEGER,
CDCORRENTISTA INTEGER,
DELOGINDESCONTO VARCHAR(20),
DELOGIN VARCHAR(20),
DTCANCELAMENTO DATE,
DELOGINCANCELAMENTO VARCHAR(20),
DEDESCONTOOS DESCRICAO,
FLDEPOSITOANTECIPADO CHAR(1),
DELOGINALTERACAO VARCHAR(20),
DTALTERACAO TIMESTAMP,
DELOGINPAGTO VARCHAR(20),
DTREGISTROPGTO TIMESTAMP,
VLTOTALVINCULADO NUMERIC(15,2),
FLCOBRARUMTERCO CHAR(1),
FLOSAVULSA CHAR(1)
);
And this SQL is being executed with a ClientDATEset SQLQuery DATESetProvider component set.
select
cdOrdemServico,
nuOrdemServico,
dtPagto as OS_dtPagto,
flIsento as OS_flIsento,
cdCorrentista as OS_Correntista,
cast( DTORDEMSERVICO as Date ) as OS_dtCriacao,
cast( (VLTOTALRECIBOLIQ - VLTOTALVINCULADO ) as numeric(15,2) ) as Saldo
from efin_OrdemServico
where flAtivo = 'S'
and (cdOrdemServico containing '350124')
When I debug this on Delphi (with stop on Delphi exceptions checked), its returns:
Field “cdCorrentista” not found
But, as shown in the DDL above, it exists (and works well both on IbExpert and the Firebird ISQL command line tool)
When I add this line on SQL : “cdCorrentista,” in select list, another error occurs:
Field “flAtivo” not found
But, again, it exists (see DDL).
When I put this line on SQL : “flativo,” in select list, another error occurs:
Field “dtPagto” not found
It’s seems to be a problem with the use of alias on select fields list.
When running the software outside delphi the exception don't happen (it's treated) but it's terrible to debug an application with this problem.
CREATE TABLE EFIN_ORDEMSERVICO (
CDORDEMSERVICO INTEGER NOT NULL,
NURECIBOOS INTEGER,
NUORDEMSERVICO INTEGER,
NMFAVORECIDO VARCHAR(100),
DTORDEMSERVICO TIMESTAMP,
FLATIVO CHAR(1) NOT NULL,
DEMOTIVOCANCELAMENTO VARCHAR(150),
VLTOTALRECIBOBRUTO NUMERIC(15,2),
VLDESCONTO NUMERIC(15,2),
VLTOTALRECIBOLIQ NUMERIC(15,2),
VLTOTALCONTABIL NUMERIC(15,2),
VLTOTALREEMBOLSO NUMERIC(15,2),
VLTOTALTAXAS NUMERIC(15,2),
DTPAGTO DATE,
FLISENTO CHAR(1),
CDSETOR INTEGER,
CDCORRENTISTA INTEGER,
DELOGINDESCONTO VARCHAR(20),
DELOGIN VARCHAR(20),
DTCANCELAMENTO DATE,
DELOGINCANCELAMENTO VARCHAR(20),
DEDESCONTOOS DESCRICAO,
FLDEPOSITOANTECIPADO CHAR(1),
DELOGINALTERACAO VARCHAR(20),
DTALTERACAO TIMESTAMP,
DELOGINPAGTO VARCHAR(20),
DTREGISTROPGTO TIMESTAMP,
VLTOTALVINCULADO NUMERIC(15,2),
FLCOBRARUMTERCO CHAR(1),
FLOSAVULSA CHAR(1)
);
And this SQL is being executed with a ClientDATEset SQLQuery DATESetProvider component set.
select
cdOrdemServico,
nuOrdemServico,
dtPagto as OS_dtPagto,
flIsento as OS_flIsento,
cdCorrentista as OS_Correntista,
cast( DTORDEMSERVICO as Date ) as OS_dtCriacao,
cast( (VLTOTALRECIBOLIQ - VLTOTALVINCULADO ) as numeric(15,2) ) as Saldo
from efin_OrdemServico
where flAtivo = 'S'
and (cdOrdemServico containing '350124')
When I debug this on Delphi (with stop on Delphi exceptions checked), its returns:
Field “cdCorrentista” not found
But, as shown in the DDL above, it exists (and works well both on IbExpert and the Firebird ISQL command line tool)
When I add this line on SQL : “cdCorrentista,” in select list, another error occurs:
Field “flAtivo” not found
But, again, it exists (see DDL).
When I put this line on SQL : “flativo,” in select list, another error occurs:
Field “dtPagto” not found
It’s seems to be a problem with the use of alias on select fields list.
When running the software outside delphi the exception don't happen (it's treated) but it's terrible to debug an application with this problem.
-
- Posts: 13
- Joined: Fri 03 Sep 2010 14:33
-
- Posts: 13
- Joined: Fri 03 Sep 2010 14:33
I have received your sample, but still I could not reproduce the problem. Please, check that you have the last version of our dbExpress Driver.
If the problem is not solved then try to exclude strings from the SQL code to find the problem. First, try to exclude WHERE clause. After that, try to exclude fields from the SELECT LIST one by one. Let me know about the result.
If the problem is not solved then try to exclude strings from the SQL code to find the problem. First, try to exclude WHERE clause. After that, try to exclude fields from the SELECT LIST one by one. Let me know about the result.
-
- Posts: 13
- Joined: Fri 03 Sep 2010 14:33
-
- Posts: 13
- Joined: Fri 03 Sep 2010 14:33
Unfortunately, we still cannot reproduce the problem.
Please, try creating a new application with the same database and perform your sql script on it. Does the problem still exist?
Also, try runing our DbxIda demo on your computer. Please, let me know about the results.
Please, try creating a new application with the same database and perform your sql script on it. Does the problem still exist?
Also, try runing our DbxIda demo on your computer. Please, let me know about the results.
Last edited by AndreyZ on Mon 04 Oct 2010 11:55, edited 1 time in total.
-
- Posts: 13
- Joined: Fri 03 Sep 2010 14:33
-
- Posts: 13
- Joined: Fri 03 Sep 2010 14:33
I've tried with the same database I sent you at "Difference on query using dbExpInt and dbExpIda" and the problems exists there too, so it's not the database.
But only at Delphi 6, if I use Delphi 7 it all works well.
I'll check if installing the Update Packs 2 and 3 will solve this problems as well.
But only at Delphi 6, if I use Delphi 7 it all works well.
I'll check if installing the Update Packs 2 and 3 will solve this problems as well.
-
- Posts: 13
- Joined: Fri 03 Sep 2010 14:33
Solved by installing Delphi 6 Update Pack 1 and 2
You should add to the "Compatibility" at (http://www.devart.com/dbx/interbase/) something like:
Delphi 6 (Update Pack 2)
Would make easier to identify problems
But anyway, as I said, problem solved.
Thanks for the attention.
You should add to the "Compatibility" at (http://www.devart.com/dbx/interbase/) something like:
Delphi 6 (Update Pack 2)
Would make easier to identify problems
But anyway, as I said, problem solved.
Thanks for the attention.