> Just to clarify - mixing is a problem when you're doing DDL and DML on
> the same table /proc etc - if they are separate items, then it's ok?

I believe so, but this is often not practical. The big problems you
will get is when for example you create a new field with a NOT NULL
constraint. If you don't fill it with data, then you are going to get
all sorts of problems restoring a backup.

alter table tblA add somefield integer default 0 not null;
update tblA set somefield = 0 where somefield is null;

If you don't commit between these statements, you might not get the
results you expected.

One thing that is a definite problem is when your database is
multi-user, and you attempt to make a change to an object that is in
use by someone else. I do not think upgrading the database is really
the job of your application itself. Of course it is appropriate that
your application protest if the database version is too old, but
database upgrades need to happen offline to guarantee success. Of
course, if no-one is using the database, then it is just as easy to
take a file system copy. Our database upgrade tool does the following.

1. Rename the fdb file. If that fails, someone or some process is
using it. If it succeeds, then no-one will be able to connect to it
because no-one will know the new connection string.
2. A file copy is made of the renamed file to fall back on.
3. The database is then connected to and the latest version identified.
4. A script is generated to bring it to the current version, and that
script is run.
5. If no errors occurred, then the upgraded database is renamed back
and the backup disposed of.

With this approach, I can upgrade any database to current with no risk
of someone connecting at a bad time, the application detects the
connection could not be established and quits somewhat gracefully.

Adam