Subject Re: [firebird-support] InterBase, Dephi, BDE and Transactions
Author Helen Borrie
At 07:11 PM 15/11/2004 +0000, you wrote:

>Hello to everybody!
> I´m trying to use InterBase (really it´s FireBird) with Delphi
>5 and BDE but I have a problem with Transactions.
> I want to manage my DataBase editing with explicit transactions
>(using DataBase.StartTransaction, Commit and RollBack) but I have
>conflicts with what I think is an implicit transaction that BDE
>makes each time I make a Table.Post. In the example code below, I
>connect to a DataBase, I open a Table, start a transaction, edit a
>Field, and when I try to Post the record, I get the error "Nested
>transactions not supported".

Your code:

> DataBaseGeneral : TDataBase;
> TablaDeProvincias: TTable;
> DataBaseGeneral:=TDataBase.Create(nil);
> with DataBaseGeneral do
> begin
> DataBaseName:='General';
> DriverName:='INTRBASE';
> Params.Values['SERVER NAME']:='General.GDB';
> Params.Values['USER NAME']:='SYSDBA';
> Params.Values['PASSWORD']:='masterkey';
> LoginPrompt:=False;
> Connected:=True;
> end;
> TablaDeProvincias:=TTable.Create(nil);
> with TablaDeProvincias do
> begin
> TableName:='Provinc';
> IndexFieldNames:='NOMBRE';
> DataBaseName:=DataBaseGeneral.DataBaseName;
> Open;
> DataBaseGeneral.StartTransaction;
> Edit;
> TStringField(FieldByName('NOMBRE')).Text:='1'+TStringField
> Post;
> DataBaseGeneral.Commit;
> Close;
> DataBaseGeneral.Connected:=False;
> end;

OK, the cause of this is not understanding that the transaction is wrapped
around the entire operation. The sequence of events is this:

start transaction
prepare query if not prepared
run query (i.e. call Open)
do some stuff
post changes
run query (i.e. call Refresh)
do some stuff
post changes
commit or rollback transaction

What you are trying to do is this:

start transaction
prepare query if not prepared
run query (i.e. call Open)
start another transaction
do some stuff
post changes

Hence, the "nested transaction" error.

> I tried to change the settings of BDE with the BDE
>Administrator but I couldn´t make it work. I tried with SQLPASSTHRU
>connected to the DataBase using an Alias or not using an Alias, I
>tried with SQLQRYMODE null or SERVER, with COMMIT RETAIN FALSE or
>TRUE, etc. None of this worked!!!

Nothing like this will work to make the database do something it can't
do. In Firebird and InterBase, you can't start another transaction inside
a transaction.

> Does anybody knows what can I do with this?

Well, here are some more problems you have with the BDE that you are using:

1. Delphi 5's BDE driver for InterBase doesn't support ODS 10 or higher
databases. It is for InterBase 5 (ODS 9) and below.

2. Delphi 6 shipped with BDE v.5.2, which does have a driver for ODS 10
databases. However, the driver is buggy and crags on some of the data
types it is meant to support. (At Delphi 6, Borland replaced the BDE with
its new DB Express driver layers for client/server databases). You have to
buy Delphi 6 to get your hands on the driver -- for a while, you could buy
BDE 5.2 from the Borland shop, but it has been off the listings now for
about three years.

3. In the BDE, you can't have multiple concurrent transactions per
connection in any form (though this isn't the cause of your problem). To
get some form of explicit control over transaction aging, you can set
COMMIT RETAIN to false in the driver settings. However, the only
workaround to the default autocommit behaviour is to use cached updates.

In short, as David said, the BDE is dead. To write Delphi apps for
Firebird, you need to use something else for connectivity - IBX (if you
want to take the risk of incompatibility), IB Objects, FIBPlus, UIB. Your
choices will be somewhat limited by using such an old Delphi. IB Objects
supports Delphi 5 but I don't know with any certainty about the
can explore some of the possibilities by going to this link page: