Editing Questions (FireDAC)
Go Up to FAQ (FireDAC)
This topic contains a list of questions and answers related to data editing.
Q1: What does "[FireDAC][DApt]-400. Update command updated [0] instead of [1] record" mean?
A: This error is often raised when FireDAC includes some float / double / single / date / datetime / time or some other table fields, which are subject to precision loss in the WHERE phrase. When an application assigns values to parameters, the precision loss may happen. As result of that, the WHERE phrase returns no records.
FireDAC may include such fields into WHERE depending on UpdateOptions.UpdateMode. Sometimes you can see this error with upWhereKeyOnly. Although you have specified upWhereKeyOnly, FireDAC may still use upWhereAll. This happens when there is no PK field defined. No PK fields may be defined when:
- fiMeta is excluded from FetchOptions.Items;
- or a table in SQL does not have primary key defined;
- or UpdateOptions.KeyFields is empty;
- or none of TField's has pfInKey in ProviderFlags.
Another reason with some DBMSs (SQL Server, PostgreSQL) is that the table has a trigger which modifies the data. With SQL Server put SET NOCOUNT ON at trigger beginning. With PostgreSQL set UpdateOptions.CountUpdatedRecords to False.
Q2: When is it necessary to use TFDUpdateSQL?
A: FireDAC generates updating SQL commands automatically, when the original SQL command is a simple SELECT or a SELECT with JOIN, where a single table preserves the primary key fields. So, the TFDUpdateSQL usage is optional. TFDUpdateSQL is required when:
- The original SQL command is not a SELECT command (for example, stored procedure returning result sets).
- The original SELECT command does not preserve the primary key (for example, join several tables, have DISTINCT or GROUP BY clauses).
- An application needs non-standard update SQL command (for example, the application posts updates using stored procedure calls).
Q3: Is it possible to use macros inside SQLs in FDUpdateSQL?
A: Use the following code:
FDUpdateSQL1.Commands[arInsert].MacroByName('MacroName').Value := 'value';
Q4: In CachedUpdates mode, why does calling ApplyUpdates more than once try to post inserted records again?
A: After calling ApplyUpdate, you should call CommitUpdates. After this call, all changes will be deleted from internal cache.
Q5: How not to refresh a detail TFDQuery after a master TFDQuery is scrolled or posted?
A: There are two ways to do that:
- Implement master-detail linkage on your own. This will require to add TDataSource.OnDataChange event handler. This is the "standard" way.
- Use centralized cached updates.
Q6: How to remove dataset records without removing them from the database?
A: You can work directly with internal dataset data storage. It is accessible through TFDDataSet.Table property. For example, to delete the row with index 3 do the following:
FDQuery1.Table.Rows[3].Free;
FDQuery1.UpdateCursorPos;
FDQuery1.Resync([]);
For example, to delete the current record, do the following:
FDQuery1.UpdateCursorPos;
FDQuery1.GetRow.Free;
FDQuery1.UpdateCursorPos;
FDQuery1.Resync([]);
And finally you can use CachedUpdates mode. Set a dataset to the cached updates mode, then delete a record and call CommitUpdates.
Q7: Using FireDAC, how can I create ATable.UpdateToDataset(BTable , 'mykey', [mtufEdit, mtufAppend])?
A: Use the TFDDataSet.CopyDataSet method with the following options:
- [coAppend] - append all records from ASource (as it was);
- [coEdit] - edit only records with existing key values;
- [coAppend, coEdit] - edit records with existing keys and append records with non-existent keys.
Q8: How can I assign a value to a ftGUID field?
A: Use the following code:
(AMemTable.FieldByName('Field1') as TGUIDField).AsGuid := aGUID;
Q9: How can I specify a default value for a dataset field?
A: Assign an expression to the TField.DefaultExpression property.
Q10: Is the TField.DefaultExpression property supported in the same way as TField.CustomConstraint, and is the effect of the expression written as the default value for a field?
Yes, if a field is a normal result set field. If a field is fkInternalCalc, then the result of the DefaultExpression will be used as a field value and will be updated like any other calculated fields.
Q11: Is an expression {fn DAYOFMONTH({fn CURDATE()})} correct?
A: No, you are using FireDAC escape functions. They are supported only in the SQL commands, not in expressions. In expressions, like constraints and default value expressions, you should use functions and syntax, supported by FireDAC expression evaluator:
DAYOFMONTH(CURDATE())
Also, to use such functions, you should include uADStanExprFuncs unit into your application.
Q12: How can I specify the default value for a dataset Boolean field?
A: To specify a default value for a dataset field, assign the required expression to the TField.DefaultExpression property.
To assign the default value to a Boolean field, you can use one of the following strings - F, FA, FAL, FALS, FALSE - as False names. The same applies for True.
Q13: Assigning TField.CustomConstraint does not work. What is wrong?
Q: Appending constraint via:
FDQuery.FieldByName('FIELD_NAME').CustomConstraint := 'FIELD_NAME > 1';
FDQuery.UpdateConstraints;
FDQuery.Table.Constraints.Check(FDQuery.GetRow(), rsModified, ctAtEditEnd);
Is not working and an exception is not raised.
A: That is OK (explanation will follow).
Q: But:
FDQuery.Constraints.Add.CustomConstraint := 'FIELD_NAME > 1';
FDQuery.UpdateConstraints;
FDQuery.Table.Constraints.Check(FDQuery.GetRow(), rsModified, ctAtEditEnd);
Is working! Why?
A: Also OK.
Q: What exactly do ctAtEditEnd and ctAtColumnChange mean?
A: These enums are specifying the event when FireDAC should check constraints:
- ctAtEditEnd - the Post is called
- ctAtColumnChange - a field value is modified
Now the explanation:
FDQuery.FieldByName('FIELD_NAME').CustomConstraint := 'FIELD_NAME > 1';
This adds a field-level constraint. They are checked at ctAtColumnChange event only.
FDQuery.Constraints.Add.CustomConstraint := 'FIELD_NAME > 1';
This adds a record-level constraint. They are checked at ctAtEditEnd event only.