Problem with query on Firebird 2.5

natanieljr · Post by **natanieljr** » Fri 03 Sep 2010 14:40

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.

AndreyZ · Post by **AndreyZ** » Mon 06 Sep 2010 08:00

Hello,

It seems that you added fields into SQLQuery fields list at first and then added or changed the aliases in the SQL property. Check field names in the fields list (by Fields Editor) and in the SQL property. They must be equal.

natanieljr · Post by **natanieljr** » Wed 08 Sep 2010 11:38

Hello, AndreyZ

I checked all the datasets and there's no field added into de field list.

All our access is made using the "FieldByName" method.

AndreyZ · Post by **AndreyZ** » Wed 08 Sep 2010 13:13

I can not reproduce the problem in a way different from the one I described in the previous answer.
Please, send me a complete small sample to andreyz*devart*com to demonstrate the problem.

natanieljr · Post by **natanieljr** » Wed 08 Sep 2010 19:29

Hello AndreyZ

I'm sent an email with sample as you asked.

AndreyZ · Post by **AndreyZ** » Thu 09 Sep 2010 14:35

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.

natanieljr · Post by **natanieljr** » Thu 09 Sep 2010 17:01

I'm using the dbExpIDA.dll with the following

Version: 2.50.0.23
Size: 275Kb
Last changed: 07/02/2010 16:45

I's this the right (last) version?

AndreyZ · Post by **AndreyZ** » Fri 10 Sep 2010 11:33

You have the right version. Please, try to exclude strings from SQL code as I wrote in the previous post.

AndreyZ · Post by **AndreyZ** » Thu 16 Sep 2010 07:42

Was the problem solved? If not, please, contact me.

natanieljr · Post by **natanieljr** » Fri 17 Sep 2010 14:21

No.

The problem persists.

I tried to take all fields from my select (in the sample I sent to you) but even a simple

"select cdOrdemServico from efin_OrdemServico"

Give me the error.

AndreyZ · Post by **AndreyZ** » Wed 22 Sep 2010 07:43

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.

natanieljr · Post by **natanieljr** » Mon 27 Sep 2010 11:57

I'll do some tests this week and send you the results.

AndreyZ · Post by **AndreyZ** » Mon 04 Oct 2010 11:56

Have you performed the tests? Does the problem still exist?

natanieljr · Post by **natanieljr** » Mon 04 Oct 2010 12:40

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.

natanieljr · Post by **natanieljr** » Mon 04 Oct 2010 18:21

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.