>I've got a new problem in my BDE to Firebird migration, and I suspect its related to transaction management,
>which is non existant in my BDE app.
>
>Here is what I'm trying to do: In my old code I've got two TTable components
>
>AddressTable: TTable;
>PersonTable: TTable;
>
>connected to two tables "Address" and "Person" in a Paradox database.
>
>The Person table has a field called AddressNo referring to an entry in the Address table.
>
>Now I want to create a new entry in the person table. In the process I need a new entry in the address table:
>
>consider the call:
>
>AddPerson('Joe', 'High street 7');
>
>procedure TForm1.AddPerson(APersonName, AStreet: string);
>var
> ID: Integer;
>begin
> ID := MakeAddress(AStreet);
> with PersonTable do begin
> Open;
> Append;
> FieldByName('PersonName').AsString := APersonName;
> FieldByName('AddressNo').AsInteger := ID;
> Post;
> Close;
> end;
>end;
>
>function TForm1.MakeAddress(AStreet: string): Integer;
>begin
> with AddressTable do begin
> Open;
> Append;
> FieldByName('Street').AsString := AStreet;
> Post;
> Result := FieldByName('AddressNo').AsInteger;
> Close;
> end;
>end;
>
>When porting this to corresponding IBO components, I get this error:
>
>violation of FOREIGN KEY constraint "INTEG_185" on table "PERSON" Foreign key reference target does not exist.
>
>It seems the newly created record in the address table isn't visible at the point of posting to the person table...
>
>I know for sure, that a valid ID is returned.
>
>Can you please help me on this one?
>
>Thanks!
>Jacob

Hei Jacob!

The simplest way to 'fix' this, would be to use the same TIB_Transaction for both PersonTable and AddressTable (assuming that the result of MakeAddress is correct). I would have considered doing bigger changes (see below), but that ought to be enough to make things work.

We chose to switch more or less directly from BDE/InterBase to native IBO/InterBase, rather than go through TDataset compatible IBO/InterBase many years ago (before Firebird was born), and you are switching both database and component set simultaneously and still try to use TDataset compatible IBO.

Trying to make Firebird behave like Paradox IS a mistake (though I don't know whether you do that), they are vastly different and you might not notice Firebirds potential when Firebird is used as if it was Paradox (Paradox might even be considerably quicker). On the other hand, using Firebird as a proper client/server database, it is a lot better than Paradox. Note that I'm not saying it will not work changing from BDE/Paradox to TDataset compatible IBO/Firebird, just be prepared for it to take time before you see any benefits (you might immediately notice that Firebird is less error prone in a multiuser environment, but the users of your system might not notice too much improvement until you start thinking in terms of datasets rather than tables).

Here's one alternative way of coding the MakeAddress function (assuming TIB_DSQL1.SQL is set to 'INSERT INTO MyAddressTable(AddressNo, Street) VALUES (:AddressNo, :Street)'):

function TForm1.MakeAddress(AStreet: string): Integer;
begin
with TIB_DSQL1 do begin
Result:=TIB_Connection1.Gen_ID(AddressTableGenerator, 1);
ParamByName('AddressNo').AsInteger:=Result;
ParamByName('Street').AsString := AStreet;
Execute;
end;
end;

HTH,
Set (Svein Erling amongst Scandinavians)