Datetime paramtere and Union all problem

[zoltan]

Also I never had any problem with Zeos 7.2.8-stable, except this Oracle date and UNION ALL problems.

Zoltan

[miab3]

Understand that our team of several people is not able to maintain all versions of ZEOS, at most the current version, i.e. 8 and possibly the penultimate one, i.e. 7.2.10. Therefore, we are most interested in the bug found in version 8 (or 7.2.10) and the current versions of Lazarus.

Michał

[zoltan]

Ok, I understand, then I will install = build into Lazarus the latest 7.2.xx version and try to get around the Oracle problems.
About Lazarus, Jan said that actually it's no need to re-install Lazarus, because the compiler is the same. I also don't thinkk that I should totally re-build my whole Lazarus environment having several plug-ins built into Lazarus, considering that the actual Lazarus version is 2.0.12, so not major changes since version 2.0.4 that I am using actually.

Thatnk for Your help !
Zoltan

[miab3]

The Lazarus 2.0.12 version had a slightly larger change compared to 2.0.10 and older (transition from fpc 3.0.4 to fpc 3.2.0)

Michał

[zoltan]

Michal,

I have installed version 7.2.10-stable from Your website. It works much better then version 7.2.8, but it still has this timing problem.
Note: when I wanted to install this 7.2.10 version, Lazarus showed me version 7.2.8. I checked in the lpk file and the version numbers are 7.2.8
Is this intentional or its just a forgotten update ?

Zoltan

[marsupilami]

I have installed version 7.2.10-stable from Your website. It works much better then version 7.2.8, but it still has this timing problem.

Could you please try to create a sample application and sample data that shows the problem? It really would help with debugging.

Note: when I wanted to install this 7.2.10 version, Lazarus showed me version 7.2.8. I checked in the lpk file and the version numbers are 7.2.8
Is this intentional or its just a forgotten update ?

This is just a forgotten update. When I did the Zeos 7.2.10 update, I was a bit hasty.

[zoltan]

It's ok, I just signaled the mismatch, basically I dont care.

Ok... So since Your last post some more problems came upon, so I wrote a little software:

----------------------------------------------------------------------------------------------------------------------------------------------------------------
unit TF1;

{$mode objfpc}{$H+}

interface

uses
Classes, SysUtils, db, Forms, Controls, Graphics, Dialogs, DBGrids, StdCtrls,
ExtCtrls, ZConnection, ZDataset;

type

{ TF1F }

TF1F = class(TForm)
BSQL1: TButton;
BSQL2: TButton;
BSQL3: TButton;
BExit: TButton;
DataSource: TDataSource;
DBGrid1: TDBGrid;
Label1: TLabel;
STRecNo: TStaticText;
ZCOracle: TZConnection;
ZQuery: TZQuery;

procedure BExitClick(Sender: TObject);
procedure BSQL1Click(Sender: TObject);
procedure BSQL2Click(Sender: TObject);
procedure BSQL3Click(Sender: TObject);
procedure FormOnCreate(Sender: TObject);
procedure FormOnActivate(Sender: TObject);
procedure FormOnClose(Sender: TObject; var CloseAction: TCloseAction);
private
{ private declarations }

public
{ public declarations }

end;

var
F1F: TF1F;

implementation

{$R *.lfm}

var Activated:boolean;

{********************************************************************}

procedure TF1F.FormOnCreate(Sender: TObject);

begin
Activated:=False;
end; { procedure F1F.FormOnCreate }

{********************************************************************}

procedure TF1F.FormOnActivate(Sender: TObject);

begin
if Not Activated then
begin
Activated:=True;

// TF1F.ZCOracle.HostName:='xxxxxxx';
// TF1F.ZCOracle.Database:='xxxxxxx';
// TF1F.ZCOracle.User :='xxxxxxx';
// TF1F.ZCOracle.Password:='xxxxxxx';
// set to our Oracle database

try
F1F.ZCOracle.Connect;

except
on E: exception do
ShowMessage('Database Connection Error : '+E.Message);
end; { try }
end; { if Not Activated then }
end; { procedure F1F.FormOnActivate }

{********************************************************************}

procedure TF1F.BSQL1Click(Sender: TObject);
{ Error : }
{- 103 records found if date in line 13 = 2021-03-05 }
{- 1 record found if date in line 13 = 2021-03-01 }
{- 135 records found if line 8 commented >//< }

begin
if F1F.ZQuery.Active then
F1F.ZQuery.Close;

F1F.ZQuery.SQL.Clear;
// F1F.ZQuery.SQL.Add('');
{ 1} F1F.ZQuery.SQL.Add(' select SubStr(b.bemerkung,1,50) as bemerkung,');
{ 2} F1F.ZQuery.SQL.Add(' t.tnre, SubStr(t.bez,1,25) as Megnevezes,');
{ 3} F1F.ZQuery.SQL.Add(' To_Char(b.mge) as darab, (b.mge * b.ekmittel) as DEPWert,');
{ 4} F1F.ZQuery.SQL.Add(' TK.UPE AS UPE,');
{ 5} F1F.ZQuery.SQL.Add(' (b.mge*tk.upe) as upewert,');
{ 6} F1F.ZQuery.SQL.Add(' t.gew as suly,');
{ 7} F1F.ZQuery.SQL.Add(' b.lagerort, To_Char(b.na_dat) as Idopont,');
{ 8} F1F.ZQuery.SQL.Add(' to_char((select sum(bst) from tup u where u.tnr=t.tnr)) as Keszlet,');
{ 9} F1F.ZQuery.SQL.Add(' G.GITPH AS GITPH, G.BEZ AS GITPHBEZ,');
{10} F1F.ZQuery.SQL.Add(' to_char(T.LINR) AS LIEFKOD,');
{11} F1F.ZQuery.SQL.Add(' GPT.NAME AS LIEFNEV');
{12} F1F.ZQuery.SQL.Add(' from bstkorr b, tst t, GITPH G, GPT, tstkalk tk');
{13} F1F.ZQuery.SQL.Add(' where SubStr(b.na_dat,1,10)>='+#39+'2021-03-01'+#39);
{14} F1F.ZQuery.SQL.Add(' and SubStr(b.na_dat,1,10)<='+#39+'2021-03-31'+#39);
{15} F1F.ZQuery.SQL.Add(' and b.bstkorrcd = 3');
{16} F1F.ZQuery.SQL.Add(' and t.tnr = b.tnr');
{17} F1F.ZQuery.SQL.Add(' AND T.GITPH=G.GITPH');
{18} F1F.ZQuery.SQL.Add(' AND T.LINR=GPT.GPTNR');
{19} F1F.ZQuery.SQL.Add(' AND B.TNR=TK.TNR');
{20} F1F.ZQuery.SQL.Add(' AND trunc(B.na_dat)>trunc(TK.ABDAT)');
{21} F1F.ZQuery.SQL.Add(' AND trunc(B.na_dat)<=trunc(TK.BISDAT)');

try
F1F.ZQuery.Open;

F1F.STRecNo.Caption:=IntToStr(F1F.ZQuery.RecordCount);

except
on E: exception do
ShowMessage('Query Error : '+E.Message);
end; { try }
end; { procedure TF1F.BSQL1Click }

{********************************************************************}

procedure TF1F.BSQL2Click(Sender: TObject);
{ Error : }
{- 1 record found if line 2 is in }
{- 992 records found if line 2 commented >//< }

begin
if F1F.ZQuery.Active then
F1F.ZQuery.Close;

F1F.ZQuery.SQL.Clear;
//{ 1} F1F.ZQuery.SQL.Add('');
{ 1} F1F.ZQuery.SQL.Add('select t.tnre, t.bezk, t.markenkey,t.gitph,');
{ 2} //F1F.ZQuery.SQL.Add('(select sum(tup.bst) from tup where tup.tnr=t.tnr) as keszlet,');
{ 3} F1F.ZQuery.SQL.Add('(select sum(nvl(obsmge,0)) from libsp where libsp.tnr=t.tnr) as nyitott_szall_rend,');
{ 4} F1F.ZQuery.SQL.Add('nvl(entfkz,0) AS ENTFKZ, entfdat,');
{ 5} F1F.ZQuery.SQL.Add('(select tt.tnre from tst tt where t.entftnr=tt.tnr)as entfall_cikk,');
{ 6} F1F.ZQuery.SQL.Add('NVL(t.aufbkz,0) AS AUBKZ,t.aufbdat,');
{ 7} F1F.ZQuery.SQL.Add('(select tt.tnre from tst tt where t.aufbtnr=tt.tnr) as aufb_cikk');
{ 8} F1F.ZQuery.SQL.Add('from tst t');
{ 9} F1F.ZQuery.SQL.Add('where (trunc(entfdat)>=trunc(sysdate-5)');
{10} F1F.ZQuery.SQL.Add(' or trunc(aufbdat)>=trunc(sysdate-5))');
{11} F1F.ZQuery.SQL.Add('order by gitph');

try
F1F.ZQuery.Open;

F1F.STRecNo.Caption:=IntToStr(F1F.ZQuery.RecordCount);

except
on E: exception do
ShowMessage('Query Error : '+E.Message);
end; { try }
end; { procedure TF1F.BSQL2Click }

{********************************************************************}

procedure TF1F.BSQL3Click(Sender: TObject);
{ Error : }
{- 16389 records found if line 6 commented >//< }
{- >OCI_ERROR: ORA-00933: SQL command not properly ended< if line 6 in }

begin
if F1F.ZQuery.Active then
F1F.ZQuery.Close;

F1F.ZQuery.SQL.Clear;
//{ 1} F1F.ZQuery.SQL.Add('');
{ 1} F1F.ZQuery.SQL.Add('select t.tnre,t.bezk,t.stdlager,t.gitph,p.lfdnr,');
{ 2} F1F.ZQuery.SQL.Add(' p.fieldname,p.valueold,p.valuenew,p.app_user,p.datum');
{ 3} F1F.ZQuery.SQL.Add('from protokoll p, tst t');
{ 4} F1F.ZQuery.SQL.Add('where p.key1c=t.tnr');
{ 5} F1F.ZQuery.SQL.Add(' and trunc(datum)>=trunc(sysdate-5)');
{ 6} //F1F.ZQuery.SQL.Add(' and fieldname in ('+#39+'entfkz'+#39+','+#39+'aufbkz'+#39+')');
{ 7} F1F.ZQuery.SQL.Add(' and (valuenew is NULL or valuenew='+#39+'3'+#39+')');
{ 8} F1F.ZQuery.SQL.Add(' and valueold<>'+#39+'5'+#39);
{ 9} F1F.ZQuery.SQL.Add('order by p.datum,t.tnre,p.lfdnr');

try
F1F.ZQuery.Open;

F1F.STRecNo.Caption:=IntToStr(F1F.ZQuery.RecordCount);

except
on E: exception do
ShowMessage('Query Error : '+E.Message);
end; { try }
end; { procedure TF1F.BSQL3Click }

{********************************************************************}

procedure TF1F.BExitClick(Sender: TObject);

begin
F1F.Close;
end; { procedure TF1F.BExitClick }

{********************************************************************}

procedure TF1F.FormOnClose(Sender: TObject; var CloseAction: TCloseAction);

begin
Activated:=False;

if F1F.ZQuery.Active then
F1F.ZQuery.Close;

if F1F.ZCOracle.Connected then
F1F.ZCOracle.Disconnect;

CloseAction:=caFree;
end; { procedure F1F.FormOnClose }

{********************************************************************}

end.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

All 3 SQL commands are running perfectly in Toad for Oracle.

If something is not clear, then please ask.

Thank You for Your help!
Zoltan

[zoltan]

Hi!

It seems to me that I found an error in the Zeoslib Oracle library.

Let's take my original 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 came out that the marked line causes the problem. If that subquery returns a NULL value,
because in the TUP table there is no matching record, then the whole query will fail, returning
only 1 record.
If I am using the Oracle connection library included in Lazarus (TOracleConnection) then I will
have the whole result but I also will have an error message saying that there are some NULL
values. Zeoslib doesnt return any error message, only the whole query fails.

If I change that subquery to the next form :

NVL(to_char((select sum(bst) from tup u where u.tnr=t.tnr)),0) as Keszlet

then Zeoslib also returns the correct record set.

Thank You for Your correction in advance!
Zoltan

[aehimself]

Please put on a TZSQLMonitor on and log the events. Do you receive an ORA-24347 or something similar?

I'm asking because I just met a similar issue, where symptoms were the same but that got patched since.
If you are using 7.2 it's possible that the changes were not yet ported. On 8.0 it's possible that there is an other error code we need to convert from failing to warning only.

Ugyanitt bojler eladó :)

[zoltan]

Ok, I will put SQLMonitor and send the log.

Using ZeosLib connection I got no error message. I got error message using Lazarus built-in OracleConnection object.

[zoltan]

Ok, used SQLMonitor and got the following log :

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
2021-11-25 14:31:09 cat: Connect, proto: oracle-9i, msg: CONNECT TO "ETN03DB.prod.hu" AS USER "etndbhurep"
2021-11-25 14:31:09 cat: Execute, proto: oracle-9i, msg: SET TRANSACTION ISOLATION LEVEL DEFAULT
2021-11-25 14:32:21 cat: Prepare, proto: oracle-9i, msg: Statement 1 :
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 where tup.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) >= '2021-03-01'
and SubStr(b.na_dat,1,10) <= '2021-03-31'
and b.bstkorrcd = 3
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

2021-11-25 14:32:23 cat: Execute prepared, proto: oracle-9i, msg: Statement 1
2021-11-25 14:32:23 cat: Prepare, proto: oracle-9i, msg: Statement 2 : SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT, COLUMN_ID FROM SYS.ALL_TAB_COLUMNS Where TABLE_NAME = 'TST' order by COLUMN_ID
2021-11-25 14:32:23 cat: Execute prepared, proto: oracle-9i, msg: Statement 2
2021-11-25 14:32:27 cat: Prepare, proto: oracle-9i, msg: Statement 3 : SELECT SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') FROM DUAL
2021-11-25 14:32:27 cat: Execute prepared, proto: oracle-9i, msg: Statement 3
2021-11-25 14:32:27 cat: Prepare, proto: oracle-9i, msg: Statement 4 : SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT, COLUMN_ID FROM SYS.ALL_TAB_COLUMNS Where TABLE_NAME = 'BSTKORR' order by COLUMN_ID
2021-11-25 14:32:28 cat: Execute prepared, proto: oracle-9i, msg: Statement 4
2021-11-25 14:32:29 cat: Prepare, proto: oracle-9i, msg: Statement 5 : SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT, COLUMN_ID FROM SYS.ALL_TAB_COLUMNS Where TABLE_NAME = 'GITPH' order by COLUMN_ID
2021-11-25 14:32:29 cat: Execute prepared, proto: oracle-9i, msg: Statement 5
2021-11-25 14:32:30 cat: Prepare, proto: oracle-9i, msg: Statement 6 : SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT, COLUMN_ID FROM SYS.ALL_TAB_COLUMNS Where TABLE_NAME = 'GPT' order by COLUMN_ID
2021-11-25 14:32:30 cat: Execute prepared, proto: oracle-9i, msg: Statement 6
2021-11-25 14:32:32 cat: Prepare, proto: oracle-9i, msg: Statement 7 : SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT, COLUMN_ID FROM SYS.ALL_TAB_COLUMNS Where TABLE_NAME = 'TSTKALK' order by COLUMN_ID
2021-11-25 14:32:33 cat: Execute prepared, proto: oracle-9i, msg: Statement 7
2021-11-25 14:32:34 cat: Other, proto: oracle-9i, msg: OCI_SUCCESS_WITH_INFO: ORA-24347: Üres (NULL) oszlop figyelmeztetés az összesítő függvényben
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Ok, here I got that Oracel error message that You also mentioned. Here I see, but in my application I got no error message only 1 row.
I tried again with Lazarus OracleConnection, there I got error message but it also gives back the full recordset.

[zoltan]

Now I have checked, that I am using an old Zeos version, 7.2.10 from April, 2021.

[aehimself]

Zoltan,

Yes, seems to be exactly the same. Seeing that you use oracle-9i protocol also made it clear it's 7.2 (in 8 there are no separate protocols for different versions).

This means that the patch was not applied to 7.2 yet.

I'll try to move it over but it will take time until the pull requests are accepted, applied and synced back to Git.
Basically what you can do:
- You can wait for me to port the patch to 7.2, issue the pull request and for it to get applied to SVN (edit: patch ported, pull request created)
- Check the commit in the pull request and make the same modifications in your local Zeos source (only a couple of lines, really)
- Try Zeos 8 as this issue is already fixed there

[aehimself]

Fyi, patch got merged to SVN ~1 hour ago. Tests are running but showing stable so far.
You can try refreshing your Zeos 7.2 installation from SVN now, or Git tomorrow.

[zoltan]

Wow... this means that in the actual ZeosLib Stable 7.2.14 it already contains the patch ? If yes, then I will install into Lazarus.

Btw... Can You tell me please that a stable version of ZeosLib 8.x when migth be expected ?

Thank You!
Zoltán