Very informative. THank you. I'll go the two scripts route.

--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:
> At 11:31 PM 17/01/2005 +0100, you wrote:
>
> >Robert martin schrieb:
> >
> > > I think it is not recommended to mix DDL and DML statements in
the same
> > > script, but I suspect inserting a 'Commit' before trying to use
the new
> > > tables may help.
> >
> >... and if not then it's time to disconnect/reconnect to database to
> >make changes visible.
>
> That's not correct - or rather, it becomes an ugly necessity if your
> application has managed to screw up a DDL operation so badly that
you have
> caused a deadlock involving the system tables. It's certainly not
how the
> dbms is designed to work.
>
> It's true - axiomatic - that you must NEVER try to perform DML on an
> uncommitted DDL object.
>
> Mixing DDL and DML in a single script running in isql is risky, because
> isql runs two separate transactions, one for DDL and one for DML.
In an
> isql script you can use SET AUTO ON to ensure that each DDL
statement gets
> autocommitted; but timing is of the essence, since the DML statements
> won't get committed, and therefore their transaction won't see DDL
> statements until it is itself committed. In isql you can work around
this
> by calling commit on *every* DML statement, so that both
transactions are
> constantly autocommitting.
>
> IBO's script component is a batch file of statements that get
executed one
> by one when you call Execute on the script. By default, all of the
> statements run in the same transaction, so, if you are not careful
about
> committing ALL statements in a timely manner, inside the script, you
will
> have grief - not just when you try to insert data into the uncommitted
> objects but when you try to run a later DDL statement that depends
on an
> earlier object in the same transaction that is still uncommitted,
e.g. a
> foreign key, a trigger, a stored procedure, etc.
>
> TIP:
> If you want to script both DDL and DML, write separate scripts. If you
> want to perform a series of DDL ops involving dependencies, put the
> dependent ops in a later script.
>
> Make each script so that nothing gets committed while the script is
> running, but gets committed only if and when the last statement
> succeeds. The ib_script component gives you the ability to
intervene when
> something goes wrong, and aborts the script. This controlled
approach is a
> whole lot safer than blindly executing a monumental script that just
> carries on and leaves the database in an unknown state when
something fails.
>
> Helen