<< Click to Display Table of Contents >> Navigation:  »No topics above this level« Transactions

SQL systems use transaction processing with commit and rollback; either the whole series of operations within the transaction is made permanent when the series completes, or the whole series is undone.

Transactions can be executed on all SQL platforms supported by BDE. A transaction is a series of programming commands that access data in the database. When the last of the series of commands has completed, the entire transaction is either committed or canceled. If it is committed, all changes performed within the transaction against the associated database are made permanent. If it is canceled, all changes performed against the associated database are undone.

Only one transaction can be active per connection to a SQL database. Any attempt to start an additional transaction before the first one terminates results in an error.

Also see SQL transaction control.

Default transactions

SQL operations always take place within the context of a transaction. When no explicit transaction occurs, the SQL driver manages the SQL server transactions transparently for the client. Any successful modification of SQL server data is immediately committed to ensure its permanence in the database. Default transaction behavior would apply if you are using BDE with a SQL server, but you are not explicitly using transactions (that is, setting the operations off between DbiBeginTran and DbiEndTran).

Beginning a transaction

The DbiBeginTran function is used to begin a transaction. After a successful DbiBeginTran call, the transaction state is active. The application specifies the isolation level to be used for the transaction when DbiBeginTran is called. Possible values are:

•xilDIRTYREAD: Uncommitted changes can be read.

•xilREADCOMMITTED: Other transactions' committed changes can be read.

•xilREPEATABLEREAD: Other transactions' changes to previously read data are not seen.

Availability and behavior of isolation and read repeatability capabilities vary by SQL server.

Ending a transaction

DbiEndTran ends the transaction. The application specifies the transaction end type. Possible values are

•xendCOMMIT: Commit the transaction.

•xendCOMMITKEEP: For some SQL drivers, commit the transaction and keep cursors.

•xendABORT: Roll back the transaction.

xendCOMMIT and xendABORT keep cursors if the driver and the database support keeping cursors. If the database does not support keeping cursors, four possibilities exist for each server cursor opened on behalf of the BDE user:

•A cursor for an open query with pending results is buffered locally. Other than prematurely reading the data, no visible effect remains.

•A cursor opened on a table supporting direct positioning is closed. No other behavior is affected.

•A cursor opened on a table that does not support direct positioning is opened initially in a different transaction or connection context, if the database supports this. This cursor remains open because it exists in a different context from the requested transaction.

•If none of the previous possibilities apply, the cursor is closed and subsequent access to the BDE objects associated with the server cursor returns an error.