Subject Re: [firebird-support] Re: DDL and DML in the one transaction
Author Peter Lee
Hi Adam,

>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.
Thanks for the clarification. I was thinking of having your first
statement in one script (V1 - V2), and at the end of that script (and
transaction), updating a version table to show that DB is now V2. Then
in V2 - V3 (new transaction), do the required update commands etc, and
update the version table to V3. I don't anticipate any structure
changes to the version table, but you never know! :-)

>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.
This process is really for our single user customers, who are using the
embedded server. We are planning on having another little application
that is run by the sysadmin does these updates outside of the
application for our multi user customers - with various warnings about
not having anybody using the system etc.

Thinking about it, this could also use the embedded server, and just be
run on the server itself - and I guess this would mean that nobody else
can be using the db.

Thanks for the ideas.


Peter Lee

Peter Lee ptle@...
Rising Software Australia Pty. Ltd.
Publishers of 'Auralia' - Ear Training and 'Musition' - Theory Training
Ph: +61 3 9481 3320 FAX: +61 3 9481 3380 USA Freecall: 1 888 667 7839