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; -----------------------------------------------------------------------------------------------------
Last Modified: 24-OCT-00