Using Explicit Transactions with InterBase

By: Borland Staff

Abstract: Using Explicit Transactions with InterBase and the BDE

Problem:
The BDE default transaction control is inefficient.  How do I take control of 
my InterBase transactions?

Solution:
 -----------------------------------------------------------------------------------------------------
 
 Internal Training:	InterBase Support
 Subject:		Using Explicit Transactions with InterBase
                                                             and the BDE.
 Author:		Markus Kemper
 Date:		8/27/98

-----------------------------------------------------------------------------------------------------

 BDE alias related (parameters)

 SQLPASSTHUR MODE:	Determines how the SQL will be dealt with
		when passed through the SQLLinks driver
		relating to a single alias connection.

 SQLQRYMODE:	Determines where the SQL query is 
		processed.

-----------------------------------------------------------------------------------------------------

 BDE alias suggested settings (bad for InterBase):

 SQLPASSTHUR MODE	SHARED AUTOCOMMIT
 SQLQRYMODE		

-----------------------------------------------------------------------------------------------------

 Using the BDE's default settings 'can' decrease performance with
 InterBase.  If your application is built to perform many related
 operations at a time you will not want to use the BDE's defaults.
 The defaults cause the BDE to control the transactions started
 and committed against the InterBase Server.  This can be a waste
 of processing power. InterBase is designed to perform many many
 database operations per transaction.  If it is appropriate for
 your application you will want to utilize this ability.

-----------------------------------------------------------------------------------------------------

 This example of Delphi code 'can' cause two transactions if using
 the BDE defaults.

 TqryOne.Close;
 TqryOne.Open;

 TqryTwo.Close;
 TqryTwo.Params[1].AsInteger := StrToInt(EdtEmpNo.Text);
 TqryTwo.Params[0].AsString := EdtLastName.Text;
 TqryTwo.ExecSQL;

-----------------------------------------------------------------------------------------------------

 BDE alias suggested settings (good for InterBase):

 SQLPASSTHUR MODE	SHARED NOAUTOCOMMIT
 SQLQRYMODE		SERVER

-----------------------------------------------------------------------------------------------------

 In order to take advantage of this you will need to think ahead
 when building your application.  You will need to understand when
 it is appropriate to 'start' and 'commit' your transactions to
 avoid 'conflicts' within the application as well as when running
 in a multiuser environment.  You can control transactions by using
 a parameter to the Inprise VCL component 'TDatabase'.  The
 parameters are 'StartTransaction' and 'Commit'.

-----------------------------------------------------------------------------------------------------

 By using your own transaction control you can contain the 
 transactions and only use one to perform the same operations.

 TdbOne.StartTransaction;

 TqryOne.Close;
 TqryOne.Open;

 TqryTwo.Close;
 TqryTwo.Params[1].AsInteger := StrToInt(EdtEmpNo.Text);
 TqryTwo.Params[0].AsString := EdtLastName.Text;
 TqryTwo.ExecSQL;

 TdbOne.Commit;

-----------------------------------------------------------------------------------------------------

Server Response from: ETNASC04