> When the application starts, it checks to see what version of the
> database is present on disk, and upgrades it using our scripts as
> necessary. We though we should wrap this in a transaction, so that if
> something goes wrong, we've not done a 'half way' update.
>
> So, we start a transaction, and then start sending our sql commands

from

> our script, if there's an exception, we'll do a rollback.
>

Firebird does not support mixing DDL and DML in a single transaction,
so that approach is not going to work. Funny things will happen if you
attempt it.

Check the version number, and if it is too old, then notify the user
via an error message. Shutdown and take a backup of the database (this
is your atomicity protection) and then run the scripts now you are the
only user. Bring the database back online and you can now relaunch the
application. This can all be done programatically with a bit of effort.
If you are the only user (eg embedded) then you can take a file copy
instead of gbak providing nothing is connected.



Adam