Refresh record in UNION query

[FCS]

Hello,

I have problem with RefreshRecord on first record if this record comes from second table.

The example of query is:

SELECT
osr_egb_dz.*,
obr.kod_obr pv_obreb_kod,
gm.kod_gm pv_gmina_kod,
wpis.poz_rej pv_kerg_wpis,
wypis.poz_rej pv_kerg_wypis
FROM osr_egb_dz
LEFT JOIN osr_obreby AS obr ON osr_egb_dz.obreb_id = obr.id
LEFT JOIN osr_gminy AS gm ON obr.gmina_id = gm.id
LEFT JOIN osr_rej AS wpis ON osr_egb_dz.kerg_wpis_id = wpis.id
LEFT JOIN osr_rej AS wypis ON osr_egb_dz.kerg_wypis_id = wypis.id
WHERE osr_egb_dz.id>0
AND osr_egb_dz.obreb_id=108
UNION ALL
SELECT
osr_egb_dz_h.*,
obr.kod_obr pv_obreb_kod,
gm.kod_gm pv_gmina_kod,
wpis.poz_rej pv_kerg_wpis,
wypis.poz_rej pv_kerg_wypis
FROM osr_egb_dz_h
LEFT JOIN osr_obreby AS obr ON osr_egb_dz_h.obreb_id = obr.id
LEFT JOIN osr_gminy AS gm ON obr.gmina_id = gm.id
LEFT JOIN osr_rej AS wpis ON osr_egb_dz_h.kerg_wpis_id = wpis.id
LEFT JOIN osr_rej AS wypis ON osr_egb_dz_h.kerg_wypis_id = wypis.id
WHERE osr_egb_dz_h.id>0 AND osr_egb_dz_h.obreb_id=108
ORDER BY pv_gmina_kod,pv_obreb_kod,nr_dz_l,nr_dz_m,nr_dz_o;

If the first record comes from the second table osr_egb_dz_h the error is generated:
"Project abc.exe raised exception class EDataBaseError with message 'Refresh failed. Found 0 records'"

When I replace RefreshRecord with Refresh it works but takes more time.

Both tables have the same structure, the table osr_egb_dz_h stores historical data.

UniDac 6.02, PostgreSQL 9.4.1 (direct access), BDS 2006 Prof, Win 7x64 Ultimate,

Regards
Michal

[FredS]

Try changing your Dataset's Key property to assure a unique record within the data.

[FCS]

Hello,

It doesn't solve the problem.

Regards
Michal

[FCS]

Hello,

It doesn't solve the problem.

Before calling RefreshRecord I set the query on the proper record:

if SQL_Query.Locate(Pole_RekNum, AktAdres, []) then begin
end;

or

SQL_Query.First;
while not SQL_Query.Eof do begin
if SQL_Query.FieldByName(Pole_RekNum).AsInteger=AktAdres then begin
BREAK;
end else SQL_Query.next;
end;

SQL_Query.RefreshRecord;

where
Pole_RekNum='id';
AktAdres = integer id value for the record.

If SQL_Query.FieldByName(Pole_RekNum).AsInteger=AktAdres is true on the first record in the Query and this record comes from UNION-ed table the RefreshRecord raises error.
For second and next records RefreshQuery works fine, even if they comes from UNION-ed table.

Regards
Michal

[azyk]

For SQL queries, that contain a UNION ALL construct, UniDAC doesn't know, what exact section owns a record of the record set. And therefore it can't generate a correct SQL query for refreshing the record. Namely, the WHERE condition.

To solve the problem, you should manually generate data refresh SQL query for the TUniQuery.SQLRefresh.Text property. For example:

SELECT
osr_egb_dz.*,
obr.kod_obr pv_obreb_kod,
gm.kod_gm pv_gmina_kod,
wpis.poz_rej pv_kerg_wpis,
wypis.poz_rej pv_kerg_wypis
FROM osr_egb_dz
LEFT JOIN osr_obreby AS obr ON osr_egb_dz.obreb_id = obr.id
LEFT JOIN osr_gminy AS gm ON obr.gmina_id = gm.id
LEFT JOIN osr_rej AS wpis ON osr_egb_dz.kerg_wpis_id = wpis.id
LEFT JOIN osr_rej AS wypis ON osr_egb_dz.kerg_wypis_id = wypis.id
WHERE osr_egb_dz.id>0
AND osr_egb_dz.obreb_id=108
and osr_egb_dz.id = :id
UNION ALL
SELECT
osr_egb_dz_h.*,
obr.kod_obr pv_obreb_kod,
gm.kod_gm pv_gmina_kod,
wpis.poz_rej pv_kerg_wpis,
wypis.poz_rej pv_kerg_wypis
FROM osr_egb_dz_h
LEFT JOIN osr_obreby AS obr ON osr_egb_dz_h.obreb_id = obr.id
LEFT JOIN osr_gminy AS gm ON obr.gmina_id = gm.id
LEFT JOIN osr_rej AS wpis ON osr_egb_dz_h.kerg_wpis_id = wpis.id
LEFT JOIN osr_rej AS wypis ON osr_egb_dz_h.kerg_wypis_id = wypis.id
WHERE osr_egb_dz_h.id>0 AND osr_egb_dz_h.obreb_id=108
and osr_egb_dz_h.id = :id
ORDER BY pv_gmina_kod,pv_obreb_kod,nr_dz_l,nr_dz_m,nr_dz_o;

[FCS]

Hello,

Thanks. It solves the problem. In my case I had to add the second condition for SQLRefresh

"and osr_egb_dz.status = :status"

because was raised the error:
"Project abc.exe raised exception class EDataBaseError with message 'Refresh failed. Found 2 records'"

I indeed have two records with the same ID, because each table has its own serial primary key.

Could you tell me how the UniQuery knows what table comes a record from ?
Is this information accessible from code ?

Regards
Michal

[azyk]

Unfortunately, TUniQuery has no such functionality, since while executing the query the PostgreSQL server doesn't return the information - what table owns a particular record.

[FCS]

Hello,

How does the RefreshRecord know what record to get (from what table in UNION SELECT) ?

Regards
Michal

[azyk]

For SQL queries, that contain a UNION ALL construct, UniDAC doesn't know, what exact section owns a record of the record set.

[FCS]

Hello,

Thanks for your answer.

Regards
Michal

[CristianP]

Maybe is not the best solution but for updates this is working for me:
- for every table in the SELECT statement I put a column where I write the table name.
- at BeforePost I compose the SQL using table name from that column and write to SQLUpdate that will be executed.

You can use the same approach writing to SQLRefresh and calling RefreshRecord.

Best regards,
Cristian Peta

[CristianP]

I found in my code also the refresh procedure:

Code: Select all

procedure OferteRefreshRecord;
begin
  uqOferteL.SQLRefresh.Clear;
  uqOferteL.SQLRefresh.Add('SELECT * FROM ' + uqOferteL.FieldByName('SourceTable').AsString);
  uqOferteL.SQLRefresh.Add('WHERE IDUnicOferta=''' + uqOferteL.FieldByName('IDUnicOferta').AsString + '''');
  uqOferteL.RefreshRecord;
  uqOferteL.SQLRefresh.Clear;
end;

[CristianP]

Adding SourceTable field:

SELECT
osr_egb_dz.*,
obr.kod_obr pv_obreb_kod,
gm.kod_gm pv_gmina_kod,
wpis.poz_rej pv_kerg_wpis,
wypis.poz_rej pv_kerg_wypis,
'osr_egb_dz' AS SourceTable
FROM osr_egb_dz
LEFT JOIN osr_obreby AS obr ON osr_egb_dz.obreb_id = obr.id
LEFT JOIN osr_gminy AS gm ON obr.gmina_id = gm.id
LEFT JOIN osr_rej AS wpis ON osr_egb_dz.kerg_wpis_id = wpis.id
LEFT JOIN osr_rej AS wypis ON osr_egb_dz.kerg_wypis_id = wypis.id
WHERE osr_egb_dz.id>0
AND osr_egb_dz.obreb_id=108
and osr_egb_dz.id = :id
UNION ALL
SELECT
osr_egb_dz_h.*,
obr.kod_obr pv_obreb_kod,
gm.kod_gm pv_gmina_kod,
wpis.poz_rej pv_kerg_wpis,
wypis.poz_rej pv_kerg_wypis,
'osr_egb_dz_h' AS SourceTable
FROM osr_egb_dz_h
LEFT JOIN osr_obreby AS obr ON osr_egb_dz_h.obreb_id = obr.id
LEFT JOIN osr_gminy AS gm ON obr.gmina_id = gm.id
LEFT JOIN osr_rej AS wpis ON osr_egb_dz_h.kerg_wpis_id = wpis.id
LEFT JOIN osr_rej AS wypis ON osr_egb_dz_h.kerg_wypis_id = wypis.id
WHERE osr_egb_dz_h.id>0 AND osr_egb_dz_h.obreb_id=108
and osr_egb_dz_h.id = :id
ORDER BY pv_gmina_kod,pv_obreb_kod,nr_dz_l,nr_dz_m,nr_dz_o;

[FCS]

Hello,

Cristian, your solutions looks fine.
Thank you.

Regards
Michal