> 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 ...

Btw, you can also learn a lot by using a monitoring facility like
TIB_MonitorDialog from the beginning. This way, you get familiar what's
happening behind the scene. For instance, you will see, that the Append
method first fetches all records of the table to the client, which is
no-go in a client-server environment.


HTH.

--
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!