Datetime paramtere and Union all problem

[zoltan]

Hi there!

I am Zoltan and I am using ZeosDBO in Lazarus 2.0.4 windows version.
At my workplace previously I was using Delphi 6.0 for developing programs, but since Delphi 6 has problems with newer Windows versions,
also Lazarus has much more features and plug-ins - like Zeos - I started to use Lazarus for developing softwares. We are using Oracle as main database for our logistic system and my programs have to use that database.
My old programs, written in Delphi, work perfectly with Oracle. My Lazarus programs have some special problems.
I have observed two main problems:
- the UNION ALL statement causes problems to Zeos, basically we cannot use it, but this would be the smaller problem
- the HUGE problem is when the SQL statement uses datetime parameters
- in most cases datetime parameters in the WHERE clause works fine
- but in some cases, and that is connected to certain dates, then it crashes
a concret example: see the attached SQL :
---------------------------------------------------------------------------------------------------------------
select SubStr(b.bemerkung,1,50) as bemerkung,
t.tnre, SubStr(t.bez,1,25) as Megnevezes,
To_Char(b.mge) as darab, (b.mge * b.ekmittel) as DEPWert,
TK.UPE AS UPE,
(b.mge*tk.upe) as upewert,
t.gew as suly,
b.lagerort, To_Char(b.na_dat) as Idopont,
to_char((select sum(bst) from tup u where u.tnr=t.tnr))
as Keszlet,
G.GITPH AS GITPH, G.BEZ AS GITPHBEZ,
to_char(T.LINR) AS LIEFKOD,
GPT.NAME AS LIEFNEV
from bstkorr b, tst t, GITPH G, GPT, tstkalk tk
where SubStr(b.na_dat,1,10) >= :KezdDat
and SubStr(b.na_dat,1,10) <= :VegDat
and b.bstkorrcd = :KorrKod
and t.tnr = b.tnr
AND T.GITPH=G.GITPH
AND T.LINR=GPT.GPTNR
AND B.TNR=TK.TNR
AND trunc(B.na_dat) > trunc(TK.ABDAT)
AND trunc(B.na_dat)<=trunc(TK.BISDAT)
order by lagerort;
---------------------------------------------------------------------------------------------------------------
it can be seen that this SQL statement has 3 parameters to pass :
- KezdDat - date format YYYY-MM-DD
- VegDat - date format YYYY-MM-DD
- KorrKod - integer

if KezdDat = '2021-03-05' and VegDat = '2021-03-30' then it works perfectly, it has a result of 102 rows
BUT
if KezdDat = '2021-03-01' and VegDat = '2021-03-30' then it crashes, the result has only 1 row!!!

in the Delphi version
KezdDat = '2021-03-01' and VegDat = '2021-03-30' gives 132 rows and that is the good result.

I set a trace for the Oracle Instant Client and the result is the following:
- BDE file - created with my old Delphi program - at position 540506 starts the SQL block given to the Oracle client
this was the original SQL statement, this had to be truncated because of the previous UNION ALL problem
this statement has 6 parameters, but basically doesnt matter
at the end of the block - position 544118 - all the parameters can be seen in plain

- LAZ file - created with my Lazarus program - the SQL statement without UNION ALL statement,
starts at position 698225 and ends at position 699203
the 3 parameters are missing !!!!

I have tried to change the type of the paramaters from datetime to string, but doesn't change, BUT if I insert
the desired dates instead of the parameters, then it works perfectly!!!

Thank You for Your help in advance!
Zoltan

[miab3]

Can you check it on the current versions?, that is:
Lazarus 2.0.12
and
ZEOS8 trunk svn 7412
https://sourceforge.net/p/zeoslib/code- ... ree/trunk/

Michał

[zoltan]

I would not like to reinstall the whole Lazarus enviromnet, I have a lot of plug-ins, that would take me several hours to reinstall everything.

I tried to compoile this revision that You sent to me, but I got an error message in the ZClasses unit:

-->> ZClasses.pas(3067,15) Error: A POP without a preceding PUSH <<--

In the source code the previous procedure has a line like :

-->> {$IFDEF FPC} {$PUSH} {$WARN 4055 off : Conversion between ordinals and pointers is not portable} {$ENDIF} <<--

[miab3]

Already this bug is fixed in svn 7418.

Michał

[marsupilami]

Hello Zoltan,

I would not like to reinstall the whole Lazarus enviromnet, I have a lot of plug-ins, that would take me several hours to reinstall everything.

That isn't necessary. The compiler stays the same.

I tried to compoile this revision that You sent to me, but I got an error message in the ZClasses unit:

-->> ZClasses.pas(3067,15) Error: A POP without a preceding PUSH <<--

In the source code the previous procedure has a line like :

-->> {$IFDEF FPC} {$PUSH} {$WARN 4055 off : Conversion between ordinals and pointers is not portable} {$ENDIF} <<--

Could you please try the latest revision? I fixed that bug.

Best regards,

Jan

[zoltan]

Hello Jan!

Things are getting somewhat better, but still problems:
- zcomponent package I cannot build / install into Lazarus, thus every time I start that project it always complains about missing packages
- in the Oracle trace log still dont find the parameters, but that block somewhat changed
- it seems to me like a timing problem, because :
- the test end date was always 2021-03-30
- start date was 2021-03-05 (the worked before correctly) - test was successful
- start date 2021-03-01 - failed, only 1 row
- 2021-03-04 - successful
- 2021-03-01 - successful
- 2021-01-01 - failes, only 1 row
- 2021-02-01 - successful
- 2021-01-15 - successful
- 2021-01-01 - successful
- 2020-01-01 - successful

It seems to me like Zeos doesnt wait for end-of-query from Oracle Client. But just guessing

Thank You for Your help!
Zoltan

[zoltan]

Found another - in my opinion - very severe problem:
- when I want to load another project of mine, it displays an error message :
Component : TZConnection
AutoEncodeString: Unknown property
- the only solution I can do to make my project work is to delete the old TZConnection component,
set a new one
- the problem with this is that even if I name the new component as the previous one, from ZQuery / ZTable
components from all forms loses the Connection property, so I have to redefine it one-by-one again all of it.

Could You just let that property in the code for backward compatibility ?

Thank You
Zoltan

[marsupilami]

Hello Zoltan,

Things are getting somewhat better, but still problems:
- zcomponent package I cannot build / install into Lazarus, thus every time I start that project it always complains about missing packages

Which of them doesn't work? Building ZComponent? Or installing it into Lazarus? Regarding your other posts: Which version of Zeos do you use in this case?

- in the Oracle trace log still dont find the parameters, but that block somewhat changed
- it seems to me like a timing problem, because :
- the test end date was always 2021-03-30
- start date was 2021-03-05 (the worked before correctly) - test was successful
- start date 2021-03-01 - failed, only 1 row
- 2021-03-04 - successful
- 2021-03-01 - successful
- 2021-01-01 - failes, only 1 row
- 2021-02-01 - successful
- 2021-01-15 - successful
- 2021-01-01 - successful
- 2020-01-01 - successful

It seems to me like Zeos doesnt wait for end-of-query from Oracle Client. But just guessing

We will have to look into this...

Found another - in my opinion - very severe problem:
- when I want to load another project of mine, it displays an error message :
Component : TZConnection
AutoEncodeString: Unknown property

This is expected to happen when upgrading from Zeos 7.2 to Zeos 8.0. It shouldn't happen on Zeos 7.2. Please always note which problems you find in which version of Zeos.

- the only solution I can do to make my project work is to delete the old TZConnection component,
set a new one
- the problem with this is that even if I name the new component as the previous one, from ZQuery / ZTable
components from all forms loses the Connection property, so I have to redefine it one-by-one again all of it.

Could You just let that property in the code for backward compatibility ?

Why would you delete the TZConnection component? Just tell Lazarus to ignore AutoEncodeStrings and save your project. At least that is the way it works with Delphi. Keeping around AutoEncodeStrings forever is not an option.

Best regards,

Jan

[zoltan]

Hello Jan!

I was testing with Zeos downloaded from svn: r7426 I have downloaded yesterday.

ZComponent can be build, but cannot be installed into Lazarus.If I open another project, I got a message that ZComponent is required, but not in the list of the project. In previous Zeos versions ZComponent was build into Lazarus and it worked well. I attach a screenshot about the problem...

It's ok to ignore the AutoEncodeStrings warning at loading, but I cannot compile my project because of this. And after ignoring if I want to change to another project, Lazarus crashes, I have to close it from Taskmanager. The only way I can change to another project : project 1 (ignored warning) -> new application (empty) -> project 2. I cannot compile my Lazarus project having this error message.
Trying to compile my project with ignored error message I got :
- Warning: Recompiling elatt, checksum changed for ZDataset
- elmod.pas(78,42) Fatal: Cannot find elatt used by elmod of the Project Inspector.
ELATT form is included into the project's list.
As I said before, the only way to get my project work is to redefine the ZConnection component, but in that case I have to re-set the Connection property in ALL ZQuery and ZTable components. In a program having more the 60 forms, it's not so funny And I should do this in all my projects :(:(:(
How about You keep this AutoEncodeStrings option too, but in the code You say :
AutoEncodeStrings = xxxxxx (the new default setting)

Thanks for the help!
Zoltan

[miab3]

Hi,

Did you do as I wrote here? viewtopic.php?f=3&t=44184
(Have you rebuilt Lazarus with zcomponentdesign.lpk?)

Michał

[zoltan]

Michal,

Yes, I did rebuild / install Lazarus with zcomponentdesign.lpk.
I had to do it since I could not build zcomponent into Lazarus.
As I had attached the screenshot before, there is no Install possibility for zcomponent package into Lazarus.
Next time I open my project will complain that it doesn't find zcomponent in the unit list.
This was working perfectly in previous versions.

Zoltan

[miab3]

And you deleted all the old zeoslib files before rebuilding??

Michał

[zoltan]

I have unzipped the svn files into a new, empty directory. As far as I have seen Lazarus compiles Zeos into the ....\packages\lazarus\lib folder....
Do I have to delete other files from other folders ?

Zoltan

[miab3]

1. As far as I know Lazarus remembers paths to libraries and files and I have already encountered the use of outdated files.
2. Actually. The version with a separate zcomponentdesign.lpk appeared at the beginning of October 2020 and I have never tested it with Lazarus older than 2.0.6((or even 2.0.8 ).

Michał

[zoltan]

Michal,

1. Ok, I will check the use of outdated files and paths.
2. I am using Lazarus 2.0.4 under Windows 7.

Zoltan