Subject Re: [IBO] IB Script - New Tables not visible
Author Helen Borrie
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.

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.