|
Updating data with UniDAC |
|
|
|
Updating data with UniDAC |
|
This topic describes common approaches to data edit with dataset components of UniDAC.
TUniTable, TUniQuery, and TUniStoredProc are UniDAC components that allow retrieving and editing data. To edit data with each of the components, specify key field names in the KeyFields property. If KeyFields is an empty string, Oracle, PostgreSQL, InterBase, SQLite, and all ODBC-based providers will try to request information about primary keys from the server sending an additional query (this may negatively affect the performance). SQL Server and MySQL providers will use the metainformation sent by the server together with data. The SQL Server provider has the UniqueRecords option that allows automatically requesting primary key fields from the table if they were omitted in the query.
If the dataset to be opened has no fields that uniquely identify a record, this problem can be solved with Oracle, Firebird 2.0, PostgreSQL, and SQLite servers by the server means. With the Oracle and SQLite servers you should add the RowID column to your query. With Firebird 2.0 - DB_KEY. With PostgreSQL server OID column can be used as key field if your table is created with OIDs. More information about these fields you will find in the documentation of the correspondent server.
For a dataset having data from several tables, only one table will be updatable by default. You should specify the table name to be updatable in the UpdatingTable property, otherwise the table to which belongs the first field in the field list will be updatable. If the SetFieldsReadOnly option is set to True (by default), fields that are not used in automatically generated update SQL statements are marked read-only. With the Oracle, PostgreSQL, and all ODBC-based providers for complicated queries (statements that use multiple tables, Synonyms, DBLinks, aggregated fields) we recommend to keep the ExtendedFieldsInfo option enabled.
If Insert/Post, Update, or RefreshRecord operation has affected more than one record, UniDAC raises an exception. To suppress such exceptions, you should set the StrictUpdate option to False.
For more flexible control over data modifications you can fill update SQL statements. They are represented by the SQLInsert, SQLUpdate, SQLDelete, and SQLRefresh properties and are executed automatically on Insert/Post, Edit/Post, Delete, and Refresh operations. At design-time you can generate default update SQL statements at the SQL Generator tab in component editor. The generated statements can be modified corresponding your needs. But if the update queries are generated dynamically for each record, only changed values are sent to the server.
For some particular cases this functionality is not enough. It can be extended with the TUniUpdateSQL component. TUniUpdateSQL allows associating a separate TUniSQL/TUniQuery/TUniStoredProc component for each update operation.
UniDAC allows caching updates at the client (so-called Cashed Updates mode), and then post all updates in a batch. It means that changes are not reflected at the server immediately after calling Post or Delete. All cached changes are posted to the server after calling the ApplyUpdates method. The UpdateBatchSize option lets setting up the number of changes to be posted at the same time.
If you have defined default values or expressions for columns in a database table, you can setup UniDAC so that it requests these expressions from the server. These expressions will be assigned to the DefaultExpression property of TField objects. If the DefaultExpression values have already been filled, they are replaced. This behaviour is controlled by the DefaultValues option, which is disabled by default.
When editing a dataset, it is often convenient not to fill key field values manually but automatically generate them. There are three ways to do it.
The first way, the most usable one, is to use server means for automatic generating of the key field values.
SQL Server, MySQL, and SQLite allow defining autoincrement columns in the table. This does not require additional handling at the client. For SAP Sybase ASE, Oracle, PostgreSQL, and InterBase providers it is necessary to specify the KeySequence (KeyGenerator for InterBase) specific option. Automatically generated values are reflected in the dataset automatically.
The second way is to generate and fill the key field value in the BeforePost event handler. As a rule this way requires executing a query to retrieve some information from the server. So this way may be useful only in some particular cases.
The third way is to create the AFTER INSERT trigger that fills the field with the appropriate value. But there is a problem with returning the value generated by the trigger. Although this problem can be solved (see the next paragraph in this topic), this approach is considered nonoptimal. So try choosing another approach if possible.
However, retrieving generated values can be disabled for SQL Server provider with the QueryIdentity specific option. This should increase performance of records inserting.
For certain situations UniDAC allows automatically refreshing records in the dataset in order to keep their values up-to-date.
With RefreshOptions you can make UniDAC refresh the current record before editing, after inserting or deleting. It is done by executing an additional query.
The DMLRefresh option allows refreshing the current record after insert or update similarly to RefreshOptions, but it works in a different way. This allows achieving higher performance than with RefreshOptions. DMLRefresh is not supported by the MySQL, SQLite, and ODBC-based providers.
If you want to control which fields of the current record need to be refreshed after insert or update, you should do the following: define in your update queries output parameters with names that correspond the field names in your dataset, and set the ReturnParams option to True. After the update query has been executed, dataset reads values of the output parameters and puts them into fields with the correspondent names.