FireDAC Components for Dataflex

Build 20.0.7587

CACHE Statements

When caching is enabled, CACHE statements provide complete control over the data that is cached and the table to which it is cached. The CACHE statement executes the SELECT statement specified and caches its results to a table with the same name in the cache database or to table specified in <cached_table_name>. The component updates or inserts rows to the cache depending on whether or not they already exist in the cache, so the primary key, which is used to identify existing rows, must be included in the selected columns.

See Caching Data for more information on different caching strategies.

CACHE Statement Syntax

The cache statement may include the following options that alter its behavior:

CACHE [ <cached_table_name> ] [ WITH TRUNCATE | AUTOCOMMIT | SCHEMA ONLY | DROP EXISTING | ALTER SCHEMA ] <select_statement> 

WITH TRUNCATE

If this option is set, the component removes existing rows in the cache table before adding the selected rows. Use this option if you want to refresh the entire cache table but keep its existing schema.

AUTOCOMMIT

If this option is set, the component commits each row individually. Use this option if you want to ignore the rows that could not be cached due to some reason. By default, the entire result set is cached as a single transaction.

DROP EXISTING

If this option is set, the component drops the existing cache table before caching the new results. Use this option if you want to refresh the entire cache table, including its schema.

SCHEMA ONLY

If this option is set, the component creates the cache table based on the SELECT statement without executing the query.

ALTER SCHEMA

If this option is set, the component alters the schema of the existing table in the cache if it does not match the schema of the SELECT statement. This option results in new columns or dropped columns, if the schema of the SELECT statement does not match the cached table.

Common Queries

Use the following cache statement to cache all rows of a table:

CACHE SELECT * FROM Accounts

Use the following cache statement to cache all rows of a table into the cache table CachedAccounts:

CACHE CachedAccounts SELECT * FROM Accounts

Use the following cache statement for incremental caching. The DateModified column may not exist in all tables. The cache statement shows how incremental caching would work if there were such a column. Also, notice that, in this case, the WITH TRUNCATE and DROP EXISTING options are specifically omitted, which would have deleted all existing rows.

CACHE CachedAccounts SELECT * FROM Accounts WHERE DateModified > '2013-04-04'

Use the following cache statements to create a table with all available columns that will then cache only a few of them. The sequence of statements cache only accountid and Name even though the cache table CachedAccounts has all the columns in Accounts.

CACHE CachedAccounts SCHEMA ONLY SELECT * FROM Accounts
CACHE CachedAccounts SELECT accountid, Name FROM Accounts

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7587