Browsing Tables (FireDAC)
Go Up to Working with Commands (FireDAC)
Contents
Using TFDTable
To browse a single database table, sort and filter records, and edit the data that FireDAC offers to TFDTable component. The TFDTable transparently generates a SELECT statement based on the TFDTable property values and called methods, and sends it to a DBMS.
The TFDTable has two main operation modes:
- Live Data Window mode. Allows bidirectional navigation through large data volumes with a minimal memory usage.
- Standard mode. This mode is similar to TFDQuery. TFDTable generates a single SELECT command and uses the result set to walk through table records.
To open a table, the TableName property must be specified. Additionally, the IndexFieldNames or IndexName properties may be set.
- Note: To use the IndexName property, the fiMeta must be included into FetchOptions.Items. For example:
FDTable1.TableName := 'CUSTOMERS';
FDTable1.IndexFieldNames := 'CustNo';
FDTable1.Open;
To force TFDTable to quote field names, set FormatOptions.QuoteIdentifiers to True.
Live Data Window Mode
In Live Data Window (LDW) mode, FireDAC queries and keeps in memory only 2 * FetchOptions.RowsetSize of records - a window into the table data. When the application navigates through the table data, FireDAC automatically scrolls or positions the LDW to the required position. This offers the following benefits:
- Minimizes memory usage and allows you to work with large data volumes, similarly to an unidirectional dataset.
- Enables bidirectional navigation, in contrast to an unidirectional dataset.
- Gives always fresh data, reducing the need to refresh the dataset.
- Does not give a delay to fetch all result set data, required to perform sorting, record location, jumping to last record, etc.
The Filter property, range filtering, the IndexFieldNames and IndexName properties, the Locate and Lookup methods, key locating, setting RecNo, setting a bookmark, etc., are performed by additional SELECT commands or by setting additional phrases for the main SELECT command. After changing of Filter, IndexFieldNames or IndexName, the current position in dataset can change, too. To preserve the current position, save a bookmark before the change and restore it after the change.
In LDW mode, the Filter property value is substituted as is into the WHERE phrase. To make the expression compatible with the DBMS and the local expression engine, the application can use FireDAC escape sequences. For example:
FDTable1.Filter := 'DateField = {d ' + FormatDateTime('yyy-mm-dd', Trunc(MonthCalendar1.Date)) + '}';
FDTable1.Filtered := True;
By design, LDW mode always applies the ORDER BY phrase to the SELECT commands. The key requirement for the correct LDW work, are:
- A table must have unique or primary key. See Unique Identifying Fields for more details.
- The server-side sort collation and client-side sort collation must be the same. Otherwise, TFDTable may produce duplicated rows and raise "unique key violation" error.
Although FireDAC minimizes the number of generated and executed SQL commands in LDW mode, it still produces a heavier DB load than TFDQuery. So, application developers should carefully choose when to use TFDTable and LDW mode.
Setting the LDW Mode
The LDW mode is used when all the following conditions are met:
- CachedUpdates is False by default.
- FetchOptions.Unidirectional is False by default.
- FetchOptions.CursorKind is set to ckAutomatic or ckDynamic (ckAutomatic is the default value).
- The table has primary or unique key.
Otherwise, the standard mode is used.
You can change the mode only when TFDTable is inactive. Consequently, ou can change CachedUpdates only when the table is inactive. Additionally, the performance can be improved by changing FetchOptions.LiveWindowParanoic (is False, by default) and FetchOptions.LiveWindowFastFirst (is False, by default).
Avoiding "Unique Key Violation" Error in LDW Mode
To make the database sort order the same as the client-side sort order, you can use FormatOptions.SortLocale and FormatOptions.SortOptions to adjust the client-side sort order. For example, German-speaking developers may set up TFDTable to query the Firebird database with ISO8859_1 character set containing German language string data:
uses
Windows;
...
// Set locale ID to German phone book collation
FDTable1.FormatOptions.SortLocale := MAKELCID(MAKELANGID (LANG_GERMAN, SUBLANG_GERMAN), SORT_DEFAULT);
// Use the the punctuation and other symbols case insensitive sorting
FDTable1.FormatOptions.SortOptions := [soNoSymbols];
FDTable1.IndexFieldNames := 'NAME';
FDTable1.TableName := 'CUSTOMERS';
FDTable1.Open;
Depending on a DBMS, you can make the following additional settings:
| DBMS | Settings |
|---|---|
| Firebird | Including soNoSymbols into SortOptions may be required. |
| MySQL | Including soNoSymbols into SortOptions may be required. |
| Oracle | ALTER SESSION SET NLS_COMP=ANSI execution may be required. |
| SQLite | Set 'SortLocale to 0. |
For example, Oracle developers may execute the command:
FDConnection1.Connected := True;
FDConnection1.ExecSQL('ALTER SESSION SET NLS_COMP=ANSI');
Also, it is recommended that you set FormatOptions.StrsTrim to False.
- Note: When a developer fails to adjust the client-side sort collation, LDW mode may be turned off by setting FetchOptions.CursorKind to ckDefault, ckStatic, or ckForwardOnly.