SQL in Delphi

SQL (Structured Query Language) is a standardized language for defining and manipulating data in a relational database. In accordance with the relational model of data, the database is perceived as a set of tables, relationships are represented by values in tables, and data is retrieved by specifying a result table that can be derived from one or more base tables. Queries take the form of a command language that lets you select, insert, update, find out the location of data, and so forth.

In Delphi: TQuery

If you are going to use SQL in your applications, you will become very familiar with the TQuery component. Delphi enables your applications to use SQL syntax directly though TQuery component to access data from Paradox and dBase tables (using local SQL - subset of ANSI standard SQL), Databases on the Local InterBase Server, and Databases on remote database servers. 
Delphi also supports heterogeneous queries against more than one server or table type (for example, data from an Oracle table and a Paradox table).TQuery has a property called SQL, which is used to store the SQL statement. 

TQuery encapsulates one or more SQL statements, executes them and provides methods by which we can manipulate the results. Queries can be divided into two categories: those that produce result sets (such as a SELECT statement), and those that don't (such as an UPDATEor INSERT statement). Use TQuery.Open to execute a query that produces a result set; use TQuery.ExecSQL to execute queries that do not produce result sets.

The SQL statements can be either static or dynamic, that is, they can be set at design time or include parameters (TQuery.Params) that vary at run time. Using parameterized queries is very flexible because you can change a user's view of and access to data on the fly at run time.

All executable SQL statements must be prepared before they can be executed. The result of preparation is the executable or operational form of the statement. The method of preparing an SQL statement and the persistence of its operational form distinguish static SQL from dynamic SQL. At design time a query is prepared and executed automatically when you set the query component's Active property to True. At run time, a query is prepared with a call to Prepare and executed when the application calls the component's Open or ExecSQL methods.

A TQuery can return two kinds of result sets: "live" as with TTable component (users can edit data with data controls, and when a call to Post occurs changes are sent to the database), "read-only" for display purposes only. To request a live result set, set a query component's RequestLive property to True, and be aware that SQL statement must meet some specific requirements (no ORDER BY, SUM, AVG, etc.)

A query behaves in many ways very much like a table filter, and in some ways, a query is even more powerful than a filter because it lets you access:

• more than one table at a time ("join" in SQL)
• a specified subset of rows and columns from its underlying table(s), rather than always returning all of them

Simple Example

Now let's see some SQL in action. Although we could use the Database Form Wizard to create some SQL examples for this example we will do it manually, step by step:

1. Place a TQuery, TDataSource, TDBGrid, TEdit, and a TButton component on the main form. 
2. Set TDataSource component's DataSet property to Query1. 
3. Set TDBGrid component's DataSource property to DataSource1. 
4. Set TQuery component's DatabaseName property to DBDEMOS. 
5. Double-click on SQL property of a TQuery to assign the SQL statement to it.
6. To make the grid display data at design time, change TQuery component's Active property to True.
The grid displays data from Employee.db table in three columns (FirstName, LastName, Salary) even if Employee.db has 7 fields, and the result set is restricted to those records where the FirstName begins with 'R'. 

7. Now assign the following code to the OnClick event of the Button1.

procedure TForm1.Button1Click(Sender: TObject);
begin
Query1.Close;{close the query}
//assign new SQL expression
Query1.SQL.Clear;
Query1.SQL.Add ('Select EmpNo, FirstName, LastName');
Query1.SQL.Add ('FROM Employee.db');
Query1.SQL.Add ('WHERE Salary > ' + Edit1.Text);
Query1.RequestLive := true;
Query1.Open; {open query + display data}
end;

8. Run your application. When you click on the Button (as long as Edit 1 has a valid currency value in it), the grid will display the EmpNo, FirstName and LastName fields for all records where Salary is greater than the specified currency value.

In this example, we created a simple static SQL statement with live result set (we haven't changed any of displayed records) just for displaying purposes.