DX gives you precise control over the sequence of commits and and the number of update statements per commit. By managing the transaction size of commits, you can optimize your application and minimize database lockouts.
DX default settings automatically manage database updates by:
- Collecting all edits made to a single record;
- Issuing one Update statement when the Query cursor moves off the current record;
- Committing the Update as soon as it occurs.
By modifying one Data Access widget resource and a series of Query widget resources, you can specify a wide range of alternative commit and update behaviors. DX also supports the use of stored procedures or triggers that issue their own commit or rollback statements.
Automatic vs. Manual Transactions
DBPak provides a choice of automatic or manual transaction control. The default is automatic, where DBPak begins a transaction before any updates and commits specified by the commitStyle resource on the Query widget. Manual transactions are also available, allowing applications to control transactions on their own.
Two Query widget resources, XmNupdateStyle and XmNcommitStyle, control how updates are collected and when commits are executed.
- XmNupdateStyle manages how updates are collected. By default, this resource is set to XiDB_UPDATE_AT_MOTION, which collects multiple updates to a record and issues one Update statement when the cursor moves off the record. Alternatively, XmNupdateStyle can be set to XiDB-UPDATE_PER_EDIT. This setting issues a separate Update statement for each record modification.
XmNcommitStyle controls when updates are committed. The default setting (XiDB_COMMIT_PER_UPDATE) causes a commit to execute immediately after DBPak updates the database. This helps keep transactions short to provent other users from being locked out of the database.
DBPak increments its count of uncommitted work after every successful operation. This count may be queried with XtGetValues to retrieve the XmNuncommittedWork resource of the Database Access widget.
This count is also used to control the sensitivity of Control widgets whose XmNqueryOpcode is set to either XiDB_COMMIT or XiDB_ROLLBACK. The COMMIT and ROLLBACK controls automatically become sensitive whenever the uncommittedWork count is greater than zero.
Note that the count is kept by the Database Access widget, even though the Control widgets they effect are attached to Query widgets. This can cause situations where a "Commit" button would be sensitive on an application screen that has no data on it. This behavior is semantically correct because commit operations are made on behalf of all Query widgets in an application, and not just the widget controlling the button.
Explicit Commit or Rollback Operations
By calling the Query widget function XiDBQueryOperation with the XiDB_COMMIT or XiDB_ROLLBACK parameter, you can instruct applications to explicitly commit or rollback the current set of updates. DBPak keeps track of uncommitted transactions, and will not issue the commit or rollback unless there is uncommitted work. This allows developers to build update logic into their applications without having to worry about improper commands being sent to the server.
- Refer to the Transactions in DBPak, Database Access Widgets, and Database Access Widget Resource Definitions sections ofDX HyperHelp.
- Refer to the Programmers' Guide of your DBMS for specific information on how your database handles update and commit transactions.