Introduction

---

DBISAM generates two types of query result sets depending upon the makeup of a given SELECT SQL statement:


The following rules determine whether a query result set will be live or canned. Please see the Executing SQL Queries for more information on executing queries.

Single-table queries

---

Queries that retrieve data from a single table will generate a live result set provided that:

1) The TDBISAMQuery RequestLive property is set to True.

2) There is no DISTINCT keyword in the SELECT SQL statement.

3) Everything in the SELECT clause is a simple column reference or a calculated column, and no aggregation or calculated BLOB columns are allowed. Calculated columns remain read-only in the live result set.

4) There is no GROUP BY clause.

5) There is no ORDER BY clause, or there is an ORDER BY clause that minimally matches an existing index in the source table in terms of fields (from left to right) and case-sensitivity.

6) There is no TOP N clause.

7) There are no sub-queries in the WHERE clause.

Multi-table queries

---

All queries that join two or more tables or union two or more SELECT statements will automatically produce a canned result set.

Calculated Columns

---

For live query result sets with calculated fields, additional internal information identifies a result column as both read-only and calculated. Every update of any column in a given row causes recalculation of any dependent calculated columns in that same row.

Identifying a Live Result Set

---

You may use the TDBISAMQuery ResultIsLive property to determine if the result set of a given SELECT SQL statement is live or canned after the query has been executed:

begin
with MyQuery do
   begin
   SQL.Clear;
   SQL.Add('SELECT * FROM customer INNER JOIN');
   SQL.Add('INNER JOIN orders ON customer.ID=orders.CustID');
   SQL.Add('WHERE customer.ID=1000');
   Open;
   { In this case the result set will be canned due
      to the join condition }
   if ResultIsLive then
      ShowMessage('The result set is live')
   else
      ShowMessage('The result set is canned');
   end;
end;

Temporary Files

---

If a SELECT SQL statement is generating a canned result set, a temporary table will be created in the directory specified by the TDBISAMSession PrivateDir property for local sessions. If the query is being executed within a remote session, the location of the temporary table for the canned result set will be determined by the database server's configuration setting for the location of temporary tables, which can be modified remotely via the TDBISAMSession ModifyRemoteConfig method or locally on the server via the TDBISAMEngine ModifyServerConfig method. The TDBISAMQuery SessionName property determines what session is being used for the execution of the SQL statement. Please see the DBISAM Architecture topic for more information.