[SOLVED] DLookUp
lazarus / 16-Databases

----------------------------------------------------------------------
madref · 2015-04-02 20:13

In MS Access you have the function DLookUp.
In Delphi XE7 i have written my own version of this.
And in Lazarus i tried it to but it doesn't work.
Can anyone help me to get it working or is there an alternative?
Code: [Select]//------------------------------------------------------
------------------------
// MLookup
//
// Search for VELD in table TABEL with value VOORWAARDE.
//--------------------------------------------------------------------
----------
function TForm_RefereeMain.MLookup(Veld,Tabel,Voorwaarde:string):
variant;
var
  mySql : TSQLQuery;


begin
  mySQl := TSQLQuery.Create(nil);
  mySQl.DataBase:=Form_RefereeMain.SQLite3Connection_RefereeDB;
  try
    mySql.Open(Format('SELECT %s FROM %s WHERE
%s',[veld,tabel,voorwaarde]));
    Result := mySQL.Fields[0].asvariant;
  finally
    MySql.Free;
  end;
end;    // MLookup

----------------------------------------------------------------------
taazz · 2015-04-02 21:48

Code: [Select]    mySql.Open(Format('SELECT %s FROM %s WHERE %0:S =
%2:s',[veld,tabel,voorwaarde]));

ofcourse I might be waaaayyy of here I don't know how you use it.

----------------------------------------------------------------------
madref · 2015-04-02 22:30

As i told...it comes from delphi XE7 and that is more powerful

----------------------------------------------------------------------
taazz · 2015-04-02 22:33

Quote from: madref on April 03, 2015, 12:30:52 amAs i told...it comes
from delphi XE7 and that is more powerful
1) thats not what I asked.
2) irrelevant information where it comes from has nothing to do with
how you are using it.
3) thats my Q to step out, good luck.

----------------------------------------------------------------------
rvk · 2015-04-02 22:47

Quote from: madref on April 03, 2015, 12:30:52 amAs i told...it comes
from delphi XE7 and that is more powerful
Yeah, not a good move to say that here (or anywhere else for that
matter )

QuoteCode: [Select]mySql.Open(Format('SELECT %s FROM %s WHERE
%s',[veld,tabel,voorwaarde]));
Why are you not using the normal way to do this? You have done so in
all your other topics/examples.

So:
Code: [Select]mySql.SQL.Text := Format('SELECT %s FROM %s WHERE
%s',[veld,tabel,voorwaarde]);
mySql.Open;

QuoteAnd in Lazarus i tried it to but it doesn't work.
Again... you don't specify what doesn't work. Please always be clear
and state everything and any error you get clearly. Just saying... it
doesn't work doesn't help us help you. (I expect you got an error like
"Wrong number of parameters specified for call to "Open"" so that
would be a clue to use the other "sql.text"-method)

----------------------------------------------------------------------
madref · 2015-04-03 06:16

Well it's the truth that Delphi is way more powerful than Lazarus
But there is no alternative other for Delphi on a Mac

----------------------------------------------------------------------
rvk · 2015-04-03 07:25

Quote from: madref on April 03, 2015, 08:16:56 amWell it's the truth
that Delphi is way more powerful than Lazarus
Ai, and again.

Either you have a distorted view of "more powerful" or your
translation module is faulty.

You do know you can extend TSQLQuery.Open yourself quite easily? Have
you looked at the overloaded procedure Open in Delphi? Granted...
Embarcadero is "more powerful" as in that they can say what the users
want and don't want even if they want something different (i.e.
they're saying you don't want a Mac-version of Delphi) but to say
Delphi itself is "more powerful" is a misguided view. Delphi is
different than Lazarus/FPC.

Well... this might be my Q to step out  Good luck.

----------------------------------------------------------------------
marcov · 2015-04-03 07:46

Quote from: madref on April 03, 2015, 08:16:56 amWell it's the truth
that Delphi is way more powerful than Lazarus
But there is no alternative other for Delphi on a Mac

Actually at work (where we are fully licensed Delphi), I do mostly all
small potato stuff in Lazarus/FPC (trunk versions even). The utility
functions of FPC/Lazarus are simply better developed, and the ability
to zip the entire development system with the slower mutating projects
is a strong pro. (our projects nearly always have equipment attached,
so moving machines is common)

For the larger projects I use Delphi. One, and one reason only: the
debugger.

----------------------------------------------------------------------
mangakissa · 2015-04-03 08:00

If delphi has a more powerfull function for it, than it must be
something internally, because it uses the same way to get data than
you normally do. Or it's a function of Firedac, which stil do the
same.
But this is the function you looking for:
Code: [Select]//------------------------------------------------------
------------------------
// MLookup
//
// Search for VELD in table TABEL with value VOORWAARDE.
//--------------------------------------------------------------------
----------
uses DB, SQLdb

function MLookup(const Database, Veld,Tabel,Lookupveld : string; const
Voorwaarde :variant): variant;
var
  mySql       : TSQLQuery;
  Transaction : TSQLTransaction;
begin
  result      := '';
  mySQl       := TSQLQuery.Create(nil);
  Transaction := TSQLTransaction.create(nil);
  try
    mySQl.DataBase        := Database;
    transaction.database  := Database;
    mySQL.transaction     := Transaction;
    mySql.text            := Format('SELECT %s FROM %s WHERE %s =
:veldwaarde',[veld,tabel,Lookupveld]));
    mySql.params[0].Value := Voorwaarde; 
    mysql.active := true;
    Result := mySQL.Fields[0].Value;
    transaction.commit;
  finally
    Transaction.free;
    MySql.Free;
  end;
end;    // MLookup