Delphi and Access walkthrough

Using SQL to search your tables

Searching through a large database for a piece of information is an old and important problem.  

There are two different approaches, shown in the illustration.  The 'dumb' approach is what you're used to using Access at school. 

The Client-Server SQL approach means that there's less traffic on the network, less processing for the client workstation to do and better security.

SQL Search commands

Search for a specific value

WHERE USERNAME = "Keith"

Or for a pattern

WHERE CITY LIKE "S%"

.. searches for all cities starting with 'S'

WHERE CITY LIKE "%S%"

.. searches for cities with 'S' anywhere in their name.

How to compose and issue your own SQL commands in Delphi

Example code which uses an edit control (edit1) to allow the user to type a search term.  It will then search for that term appearing anywhere in the username field.

• Set up a string.  Write into that string the SQL that you need for your standard report/form.  Add to it the 'WHERE' statement, indicating the field(s) that you want to search and using a parameter for the search term.  A parameter in Delphi SQL is a colon followed by the parameter name.
  
• Use the FDQuery.ParamByName procedure to set the parameter with your search string.
  
• Finally run the query with FDQuery.Open
  

procedure TForm3.searchButtonClick(Sender: TObject);
var 
  sqlquery : string;
begin

  sqlquery := 'SELECT PUPILS.ID,  PUPILS.USERNAME, PUPILS.USERSCORE, SURNAMES.SURNAME '+
      'FROM PUPILS '+
      'INNER JOIN SURNAMES '+
      'ON (PUPILS.USERNAME = SURNAMES.FIRSTNAME)' +
      'WHERE PUPILS.USERNAME LIKE :firstname';  //the :firstname is a parameter 

      fdquery1.SQL.text := sqlquery;
      fdquery1.ParamByName('firstname').AsString := '%'+edit1.text+'%';  //set parameter
      fdquery1.Open; // run the query
end;