TSQLQuery with lookup field in TMemDataset
lazarus / 16-Databases

----------------------------------------------------------------------
dpap · 2025-01-11 09:59

I have a TSQLquery that has a field that corresponds to a record of a
TMemDataset. How can I set a lookup field for this field?

----------------------------------------------------------------------
Thaddy · 2025-01-11 12:42

Don't use TMemDataset, use TBufDataset. That can use lookups easily.
TMemDataset has way less feautures than TBufDataset, which can perform
the same tasks as a memory only dataset.

----------------------------------------------------------------------
dpap · 2025-01-11 16:44

Let have:
Q : table in a SQL database, that keeps the persons data in the fields
Name:string(50), Town:integer,....
M : a table that keeps the Towns data in the fields ID:integer,
TownName:string(30),....
Now I want to present a person's TownName where Q.Town=M.ID
If both tables were in the same(or not) database, this would be
achived with the statement SELECT Q.Name,Q.Town,M.nameOfTown FROM Q
LEFT JOIN M ON Q.town=M.ID
but what can I do if the table M is an inmemory table (TBufDataset)?

----------------------------------------------------------------------
cdbc · 2025-01-11 18:26

Hi
Quotebut what can I do if the table M is an inmemory table
(TBufDataset)?I'd use M.Locate(Q.Town..)...
...Time for you to go sniffing in the sources, it's all there 
Regards Benny

----------------------------------------------------------------------
dpap · 2025-01-11 19:42

Finally I make the lookup tables permanent in the database and now I
can use the JOIN command. I didn't found other way.

----------------------------------------------------------------------
egsuh · 2025-01-12 13:55

Simply idea.


1. first open the SQLquery with one additional null field, like

    SELECT Q.Name, Q.Town, null as nameOfTown FROM Q

2. Add event handler to SQLQuery, probably AfterOpen or AfterScroll

      if M.Locate('NameOfTown', [Q.FieldByName('Town').AsInteger])
then begin
         Q.Edit;
         Q.FiledByName('NameOfTown').AsString :=
Q.FieldByName('NameOfTown').AsString;
         Q.Post;
      end; 

Haven't tested whether this will work.

----------------------------------------------------------------------
dpap · 2025-01-12 17:56

YES! It works  :)With this trick now I can add lookup and calculated
fields. Many Thanks!

PS. But I have a major problem as I am newby In lazarus AND SQL coming
from Delphi where I handn't such problems. If you please see my post
https://forum.lazarus.freepascal.org/index.php/topic,69838.0.html and
Give your help!