Updating Data with ODAC Dataset Components
ODAC dataset components which descend from TCustomDADataSet provide different ways for reflecting local changes on the server.
The first approach is to use automatic generation of update SQL statements. When using this approach you should either specify Key Fields (the KeyFields property) or include RowID field into you SELECT SQL statement to avoid requesting of KeyFields from the server. When SELECT statement uses multiple tables, you can use UpdatingTable property to specify which table will be updated. If UpdatingTable is blank, the first table of the FROM clause will be used. When using sophisticated SELECT SQL statements (statements that use multiple tables, Synonyms, DBLinks, aggregated fields) we recommend to enable ExtendedFieldsInfo option. When this option is enabled, additional requests to the server may be performed to obtain more information about updating objects. This helps to generate correct updating SQL statements but may result in performance decrease. To avoid editing the fields that will not be used in update SQL statements use the SetFieldsReadOnly option. You can increase performance by refreshing fields using RETURNING clause when insert or update is performed. To enable this feature enable DMLRefresh and ReturnParams options.
Another approach is to set update SQL statements using SQLInsert, SQLUpdate and SQLDelete properties. Use them to specify SQL statements that will be used for corresponding data modifications. It is useful when generating data modification statements is not possible (for example when working with data of a cursor, returned by a stored procedure) or you need to execute some specific statements. You may also assign TOraUpdateSQL component to the UpdateObject property. TOraUpdateSQL component holds all updating SQL statements in one place. You can generate all these SQL statements using ODAC design time editors. For more careful customization of data update operations you can use InsertObject, ModifyObject and DeleteObject properties of TOraUpdateSQL component.