TSQLQuery with lookup field in TMemDataset

[dpap]

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]

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]

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]

Hi

but 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]

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]

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]

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!