(I tried to format this post using the yahoo web post, but the formatting seems all gone, so here is another post directly via email, hopefully easier to read!)


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

SELECT MAX(PersonNo) FROM PERSON
CREATE SEQUENCE PersonNo_PERSON_GEN
SET GENERATOR PersonNo_PERSON_GEN TO 1;

CREATE TRIGGER PersonNo_PERSON_TRIG FOR PERSON
BEFORE INSERT
AS
BEGIN
NEW.PersonNo = GEN_ID(PersonNo_PERSON_GEN, 1);
END


Thanks!
Jacob


[Non-text portions of this message have been removed]