Query.SQL.Text := 'SELECT * FROM PersonDB where LastName LIKE "'+ALastName+'%" order by LastName,FirstName ASC LIMIT 50';
of course using Parameter is a better way, like:
Query.SQL.Text := 'SELECT * FROM PersonDB where LastName LIKE :0 order by LastName,FirstName ASC LIMIT 50';
Query.SQL.Params[0].AsString := ALastName+'%';
Perhaps you set you statements in the ObjectInspector or you copy the statement from an other tool.
But what if you are new to SQL?
What kind of errors happens in your SQLQuery?
Misspelled fieldname? Missing brackets, missing space or ",".
The problem is,- normally - you can only find this errors by executing the statement...
In my FDK I have all database definition in my sourcecode. All fields can easily checked against this definition.
If I do a search for a Person in my PersonDB, I like to shorten the search result by typing not only the Lastname... I like a search of both Firstname and Lastname.
If I search for my name in this database I can put "Lau,Fr" into the Searchedit and I get the result of all Person starting with the Lastname LIKE "Lau%" and FirstName LIKE "Fr%".
Perhaps you do other combinations.
In this case you always have an If or Case statement to select the right SQL, - or - to Ignore NULL DB Fields.
In my latest development of my ORM/CRUD DB Interface, I tried to include this expectations in my FDK.
For the given search problem I have this solution in every application:
procedure TPersonViewModel.Search(Const ASearchFor : String);If I do a search for a Person in my PersonDB, I like to shorten the search result by typing not only the Lastname... I like a search of both Firstname and Lastname.
If I search for my name in this database I can put "Lau,Fr" into the Searchedit and I get the result of all Person starting with the Lastname LIKE "Lau%" and FirstName LIKE "Fr%".
Perhaps you do other combinations.
In this case you always have an If or Case statement to select the right SQL, - or - to Ignore NULL DB Fields.
In my latest development of my ORM/CRUD DB Interface, I tried to include this expectations in my FDK.
For the given search problem I have this solution in every application:
var
FirstName,
LastName : String;
P : Integer;
begin
LastName := ASearchFor.Trim+'%';
P := Pos(',',ASearchFor);
if P > 0
then begin
FirstName := Copy(LastName,succ(P),
Length(LastName));
Delete(LastName,P,Length(FirstName));
end
else FirstName := '';
// Normally
if FirstName.Trim = ''
then Query.SQL.Text := 'SELECT ...'// Without FirstName
else Query.SQL.Text := 'SELECT ...';// With FirstName
end;
But I don't like this untestable SQL in this procedure. So I came up with this:
procedure TPersonViewModel.Search(Const ASearchFor : String);
var
SearchResult : ICanCRUDSearch; // from my CRUD-Framework
// .. Same as above
begin
// .. Same as above and then
TCRUDSearch.&For(FPerson).
{} Where('LASTNAME').LIKE(LastName.Trim).
{} begin_Optional(FirstName.Trim <> '').
{} _AND.Where('FIRSTNAME').LIKE(FirstName.Trim).
{} end_Optional.
{} OrderBy('LASTNAME').OrderBy('FIRSTNAME ASC').
{} Limit(100).
{} Start(SearchResult);
if SearchResult.SyntaxOnly then
exit;
// Perform UserIO on SearchResult
end;
And for Unit-Testing you can just call this same procedure and "Start" performs a systaxcheck only.
Perhaps you call it "Over Engineering" - I call it helpful. And more:
I love this kind of fluid-source-code. It's so easy to read and understandable.
The {} in front of each line are only to prevent the sourcecode formatter to kill my structure.
The Fluid-Interface helps you to build your statement - of course you should know a little bit of SQL, but...e.g.
After "Where" you can only use a comparer "LIKE, EQUAL, GREATER...". That's why "Where" is defined as
ICanCRUDWhere = Interface
Function Where(Const AName : String) : ICanCRUDCompare;
end;
ICanCRUDCompare = Interface
Function LIKE(Const AValue : String) : ICanCRUDSearch;
Function EQUALS(Const AValue : String) : ICanCRUDSearch;
// more...
end;
The SearchResult stores the Data and you can iterate through it.
My FDK.FMXGridHelper can take this SearchResult and could direct perform the UserIO.
The CleanUp is done by RefCounting automatically.
Stay tuned for the next FDK update... More is coming...
