Hello,

see below (inline).

> Hi - I'm still struggling with TIBOTable.
>
> I need to use it in a transition from BDE/Paradox to IB_/Firebird. I have a lot of old code using TTable and I need a way of getting it to work through simple text replacements (and possibly some tweaks).
>
> The code below is a test BDE/Paradox app, which I have translated into IBO/Firebird using GReplace. The script to generate the Firebird database is also included.
>
> The problem is that when posting to the Person table (see below), I get
>
> violation of FOREIGN KEY constraint "INTEG_3" on table "PERSON" Foreign key reference target does not exist.
>
> It has been suggested to me, that I need to use the same TIB_Transaction for both tables. But I understand that the TIBODatabase component already has a inbuilt transaction that is being used. So I suspect I need to configure this one rather than adding another transaction component. Btw I have tried that, but couldn't get it to work.
>
> Here is my code (very short):
>
> unit Unit1;
>
> interface
>
> uses
> Forms, DB, IBODataset, IB_Components, IB_Access, Classes, Controls,
> StdCtrls;
>
> type
> TForm1 = class(TForm)
> MyDatabase: TIBODatabase;
> Button1: TButton;
> PersonTable: TIBOTable;
> AddressTable: TIBOTable;
> procedure Button1Click(Sender: TObject);
> private
> function MakeAddress(AStreet: string): Integer;
> procedure AddPerson(APersonName, AStreet: string);
> procedure SetPerson;
> public
> end;
>
> var
> Form1: TForm1;
>
> implementation
>
> {$R *.dfm}
>
> function TForm1.MakeAddress(AStreet: string): Integer;
> begin
> with AddressTable do begin
> GeneratorLinks.Add('AddressNo=ADDRESSNO_ADDRESS_GEN');
> Open;
> Append;
> FieldByName('Street').AsString := AStreet;
> Post;
> Result := FieldByName('AddressNo').AsInteger;
> Close;
> end;
> end;
>
> procedure TForm1.AddPerson(APersonName, AStreet: string);
> var
> ID: Integer;
> begin
> ID := MakeAddress(AStreet);
> with PersonTable do begin
> GeneratorLinks.Add('PersonNo=PERSONNO_PERSON_GEN');
> Open;
> Append;
> FieldByName('PersonName').AsString := APersonName;
> FieldByName('AddressNo').AsInteger := ID;
> Post;<--------- here happens the error
> Close;
> end;
> end;
>
> procedure TForm1.Button1Click(Sender: TObject);
> begin
> AddPerson('Jacob', 'High street');
> end;
>
> end.
>
> and the dfm for completenes:
>
> object Form1: TForm1
> ...
> object MyDatabase: TIBODatabase
> SQLDialect = 3
> DatabaseName = 'BdeTest2'
> Params.Strings = (
> 'SQL DIALECT=3'
>
> 'PATH=C:\Udvikling\Delphi 2007\Projects\Test\IBOTest\MasterDetail' +
> '\MDTest2.fdb'
> 'USER NAME=SYSDBA')
> Isolation = tiCommitted
> SessionName = 'Default'
> Left = 44
> Top = 56
> SavedPassword = 'xxx'
> end
> object PersonTable: TIBOTable
> DatabaseName = 'BdeTest2'
> IB_Connection = MyDatabase
> RecordCountAccurate = True
> TableName = 'Person'
> FieldOptions = []
> Left = 132
> Top = 56
> end
> object AddressTable: TIBOTable
> DatabaseName = 'BdeTest2'
> IB_Connection = MyDatabase
> RecordCountAccurate = True
> TableName = 'Address'
> FieldOptions = []
> Left = 220
> Top = 60
> end
> end
>
> and the script generating my database:
>
> /* Creating tables */
> CREATE TABLE ADDRESS (
> AddressNo INT,
> Street CHAR(40),
> PRIMARY KEY (AddressNo)
> );
>
> CREATE ASCENDING INDEX AddressIndex ON ADDRESS (Street) /* AddressIndex */
>
> CREATE TABLE PERSON (
> PersonNo INT,
> PersonName CHAR(40),
> AddressNo INT,
> PRIMARY KEY (PersonNo)
> );
>
> CREATE ASCENDING INDEX PersonNameIndex ON PERSON (PersonName) /* PersonNameIndex */
>
> CREATE ASCENDING INDEX AddressNoIndex ON PERSON (AddressNo) /* AddressNoIndex */
>
> /* Creating referential integrities */
> ALTER TABLE PERSON
> ADD FOREIGN KEY (AddressNo) REFERENCES ADDRESS (AddressNo)
>
> SELECT MAX(AddressNo) FROM ADDRESS
> CREATE SEQUENCE AddressNo_ADDRESS_GEN
> SET GENERATOR AddressNo_ADDRESS_GEN TO 1;
>
> CREATE TRIGGER AddressNo_ADDRESS_TRIG FOR ADDRESS
> BEFORE INSERT
> AS
> BEGIN
> NEW.AddressNo = GEN_ID(AddressNo_ADDRESS_GEN, 1);
> END

Although I don't use TIBOTable, I guess the problem is in the trigger
here, because you are assigning a new generator value via this trigger
again, thus overwriting the generator value produced by IBO.

You'd better write your auto-increment triggers like that:

CREATE TRIGGER AddressNo_ADDRESS_TRIG FOR ADDRESS
BEFORE INSERT
AS
BEGIN
IF (NEW.AddressNo IS NULL) THEN
NEW.AddressNo = GEN_ID(AddressNo_ADDRESS_GEN, 1);
END


Just a wild guess ...


--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!