SQL, how do you write your queries?

OK - I know there are many ways to do a simple SQL Query... Like:


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);
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...