At 08:49 AM 09-10-02 +1000, you wrote:

>Note: While I have come across scripts that seem to need
>ServerAutoCommit, I dont like it. I usually try to resolve the
>problem by inserting explict COMMIT commands at appropriate
>points in the script.

I always thought ServerAutoCommit was designed for use with transactions
performing DDL. I was quite surprised to see Jason mention that it was
appropriate in some conditions to use it with DML. Ann Harrison's comments
in ib-support neither support nor deny either belief but she recommends not
using it for anything.

In a script, the appropriate means of committing as-you-go is to encase the
script inside SET AUTODDL ON....SET AUTODDL OFF directives. Such scripts
work fine with TIB_Script. However, any good SQL gets committed right up
to where the first error occurs so I don't add the directives until I know
that the script works 100%. When a db is under constant development, I
prefer to avoid committing until the script is complete, so I can rollback
the work so far and fix the error.

Also, I don't ever run huge scripts. I have a chain of scripts that I run
individually in the right order during development and alpha testing; and
link with INPUT directives for deployment. For me it makes debugging and
subsequent changes so much easier.

Nota bene that AUTODDL only works for DDL statements. I understand that
the engine actually starts and commits its own transaction within the
transaction that your script is running under (the only place I know of
where IB puts an embedded transaction within reach of the client). This is
another good reason to break up your scripts. Presumably if
ServerAutoCommit is true, any DML statements inside a DDL script would get
committed but I doubt if I would count on it. I always separate DDL and
DML into their own scripts.

Helen