Queries for desktop developers

From InterBase

Go Up to Working with Queries


As a desktop developer you are already familiar with the basic table, record, and field paradigm used by Delphi and InterBase Express. You feel very comfortable using a TIBTable component to gain access to every field in every data record in a dataset. You know that when you set a TableName property of a table, you specify the database table to access.

Chances are you have also used range methods and a filter property of TIBTable to limit the number of records available at any given time in your applications. Applying a range temporarily limits data access to a block of contiguously indexed records that fall within prescribed boundary conditions, such as returning all records for employees whose last names are greater than or equal to “Jones” and less than or equal to “Smith.” Setting a filter temporarily restricts data access to a set of records that is usually non-contiguous and that meets filter criteria, such as returning only those customer records that have a California mailing address.

A query behaves in many ways very much like a table filter, except that you use the SQL property of the query component (and sometimes the Params property) to identify the records in a dataset to retrieve, insert, delete, or update. In some ways a query is even more powerful than a filter because it lets you access:

  • More than one table at a time (called a “join” in SQL).
  • A specified subset of rows and columns in its underlying table(s), rather than always returning all rows and columns. This improves both performance and security. Memory is not wasted on unnecessary data, and you can prevent access to fields a user should not view or modify.

Queries can be verbatim, or they can contain replaceable parameters. Queries that use parameters are called parameterized queries. When you use parameterized queries, the actual values assigned to the parameters are inserted into the query before you execute, or run, the query. Using parameterized queries is very flexible, because you can change a user’s view of and access to data on the fly at runtime without having to alter the SQL statement.

Most often you use queries to select the data that a user should see in your application, just as you do when you use a table component. Queries, however, can also perform update, insert, and delete operations as well as retrieving records for display. When you use a query to perform insert, update, and delete operations, the query ordinarily does not return records for viewing.

To learn more about using the SQL property to write a SQL statement, see Specifying the SQL statement to execute. To learn more about using parameters in your SQL statements, see Setting parameters. To learn about executing a query, see Executing a query of the InterBase Language Reference.

Advance To: