Environment: Firebird 2.0

I have 2 tables in a master/child relationship as follows:

CREATE TABLE MASTER (
MASTERID BIGINT NOT NULL,
DESC VARCHAR(200)
);

CREATE TABLE CHILD (
CHILDID BIGINT NOT NULL,
MASTERID BIGINT NOT NULL,
DESC VARCHAR(200)
);

MASTERID is the PK in MASTER, and CHILDID is the PK in CHILD, and
CHILD.MASTERID is properly set up as a foreign key to MASTER.

Both PKs are auto-populated with generators via an insert trigger.

Now, when I create my database, I want to automatically populate these
tables with data right from the start by executing as SQL script.

For example:

INSERT INTO MASTER (DESC) VALUES ("FIRST");
INSERT INTO MASTER (DESC) VALUES ("SECOND");
INSERT INTO MASTER (DESC) VALUES ("THIRD");

This works fine for the master table, since the trigger takes care of
the PK assignment. The problem comes when I want to insert the CHILD
entries. I don't know what the ID is for the MASTER entry...

INSERT INTO MASTER (DESC) VALUES ("FIRST");
INSERT INTO CHILD (MASTERID, DESC)
VALUES (<dont know this!>, 'FIRST CHILD OF FIRST');
INSERT INTO CHILD (MASTERID, DESC)
VALUES (<dont know this!>, 'SECOND CHILD OF FIRST');

I know that in FB 2.0, there is an extended syntax

INSERT INTO MASTER (DESC) VALUES ("FIRST") RETURNING MASTERID INTO
:varMASTERID;
INSERT INTO CHILD (MASTERID, DESC)
VALUES (:varMASTERID, 'FIRST CHILD OF FIRST');
INSERT INTO CHILD (MASTERID, DESC)
VALUES (:varMASTERID, 'SECOND CHILD OF FIRST');

But this requires a variable definition, which is not available in a SQL
script such as this.

I suppose I could use the new EXECUTE BLOCK statement for this, but is
there a limitation as to the size of an EXECUTE BLOCK statement? If I
have a relatively large dataset to initialize my database with that has
a significant number of relationship interdependencies that require
variable scope to be quite large, then this may not scale particularly well.

Another option is to write an application (C++ and IBPP, for example)
that does this work for me, but then anyone who is responsible for
dealing with the default data set would have to deal with C++ instead of
just living in the SQL world...

What is the 'best practice' for this scenario?

Thx!

Greg






__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com