Sorting, Searching, Locating, Filtering Questions (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to FAQ (FireDAC)

This topic contains a list of questions and answers related to sorting, searching, locating and filtering datasets.

Q1: The dataset sorts non-English strings incorrectly. I am also getting "[FireDAC][DatS]-2. Object [] is not found" error. What is wrong?

A: To fix the issue, do the following:

  • Open $(BDS)\source\data\firedac\FireDAC.inc;
  • Find the lines:
{$define FireDAC_NOLOCALE_DATA}   // define to use binary data comparison
{$define FireDAC_NOLOCALE_META}   // define to use binary metadata comparison
  • Comment them out;
  • Save the file and recompile your application.

Note: If you are only working with English (ASCII) texts, ensure that the lines above are uncommented. This speeds up sort and locate operations considerably.

Q2: Is it necessary to create an index for ordering data?

A: You do not need to define indexes if you only need to order data. IndexFieldNames will work for you. Indexes allow you to define the views, which are a mix of filtering and ordering. Note that indexes and IndexDefs are mutually exclusive for all FireDAC datasets. This means that you should fill either indexes or IndexDefs, but not both.

Q3: Can lookup fields be used in index definitions?

A: No, they cannot. But you can use internal calculated fields in indexes. In order to do this, add persistent fields, then add fkInternalCalc field, create OnCalcFields event handlers and calculate this field in the event handler. For example: 

procedure TForm21.FDTable1CalcFields(DataSet: TDataSet);
begin
  DataSet.FieldByName('f_calc').AsString :=
    FDMemTable2.Lookup('code',
      DataSet.FieldByName('f_code').AsInteger, 'name');
end;

Q4: Can calculated fields be used in index definitions?

A: The current FireDAC release does not support fields with FieldKind = fkCalculated in Locate, Lookup, IndexFieldNames, and so on. The workaround is to use fkInternalCalc fields.

Q5: When I call the FindNearest method at run time I receive an error message stating that there is no active index

A: The index must be selected, not just active. For that:

  • Set Indexes[i].Selected := True
  • Or set FDTable1.IndexFieldName := <name of your index>

Q6: How can I define a different character collation for TADDataset indexing?

A: In general, there are three options:

  • Assign LCID to TFDMemTable.Table.Locale. FireDAC uses CompareStringA and CompareStringW with SORT_STRINGSORT flag. The default collation is DBMS independent. It is LOCALE_USER_DEFAULT. See Win API documents for details.
  • Change the source code - FireDAC.DatS.pas, TFDDatSRow.CompareData and implement your own comparison algorithm.
  • Register a custom function with an expression evaluator. See FireDAC.Stan.Expr.pas for registration details. Then you can use this function at TFDMemTable.Indexes[..].Expression. For example: Expression := 'MySort(Name)'.

In the future we will implement custom collations. That will be useful for SQLite driver cases, like yours.

Q7: How can I order data in this way: COL1 descending, COL2 ascending?

A: IndexFieldNames := 'col_1:D;col_2';

Q8: FDQuery.Locate raises "Function sequence error" with SQL Server. What is wrong?

A: It looks like FDQuery was opened after an explicit transaction start, then a transaction was committed. And then you call FDQuery.Locate, but not all records have been fetched from FDQuery, instead Locate implicitly calls FetchAll. This is why this error is raised on SQL Server.

To avoid this, do the following:

SQL Server behavior is to invalidate open cursors after Commit/Rollback.

Q9: What is the best way to implement Lookup tables? They seem to be big because opening/loading is slow

A: If you do not need all records on the client, use TFDQuery with SELECT ... WHERE ....

Q10: Do you have any tips on how to handle Lookup fields to get the best performance?

A: Set TFDQuery.IndexFieldNames to LookupKeyFields, then FireDAC will use client index to locate records in a lookup dataset.

If the number of unique key values is not high, set LookupCache to True.

Q11: I fail to filter by the DateTime value. What is wrong?

Q: I am using Delphi 2007 with FireDAC 2.0.11.895 on a Postgres database. When I try to filter on a DateTime field, I cannot seem to get an exact match with a value passed to it like this: 

created_date = '8/10/2009 14:42:14'           // or even...
created_date = '8/10/2009 14:42:14.247'     // ... the exact time with millisec

Instead I have to end up using something like this: 

created_date >= '8/10/2009 14:42:14' AND created_date < '8/10/2009 14:42:15' or
created_date >= '8/10/2009 14:42:14.000' AND created_date <= '8/10/2009 14:42:14.999'

A: The problem is that the DBMS or DBMS API may round the time value. While a programmer expects to see the .247 as a fractional part, it may be .246 or something else. This is not a FireDAC failure, but it is how a DBMS is working.

You can round the time value to the seconds and compare that value with a constant without the fractional part: 

uses
  FireDAC.Stan.ExprFuncs;
...
  FDQuery1.Filter = 'TimeStampDiff(''second'', created_date,
    convert(''timestamp'', ''8/10/2009 14:42:14'')) = 0';
Retrieved from "http://docwiki.embarcadero.com/RADStudio/Berlin/e/index.php?title=Sorting,_Searching,_Locating,_Filtering_Questions_(FireDAC)&oldid=212209"