At 08:55 AM 5/04/2005 +0000, you wrote:

>But I WERE inside the same transaction when I dropped, recreated and
>added the primary key to my table. After doing that, the transaction
>is committed and subsequent operations are performed on fully visible
>objects, so where's my mistake?
>
>Look again at the second script: it drops a table, creates a table
>with the same name (but it's a new one inside this transaction, 'cause
>a while ago we dropped the old one) and adds a primary key to that new
>table, then it commits the changes. What's wrong with it?
>
>It seems that Firebird "confuses" the old (dropped) and the new (just
>created) tables, adding the primary key in some way on the old table
>and leaving the new one untouched, although the new one reports to
>have its primary key (but actually it's not true).

You are very confused. In the second script, you set Autoddl off (meaning
that the work remains uncommitted until you explicitly commit it). Then
you dropped the table; without committing that, you created it
again; then, without committing it, you added a constraint to it; then,
without committing anything yet, you started a new transaction (because DDL
runs in its own transaction) and added records to a table that (from the
POV of the new transaction) is still the old table (that you have dropped
but not committed). It's anyone's guess what the state of things might
have been as that script progressed.

Was there any special purpose to this peculiar exercise? What did you seek
to demonstrate?

If you seriously want to create and modify metadata, do it in an orderly
fashion, in a way that you are in control of the state of the
metadata. The default behaviour of isql scripts for DDL (autoDDL on) is to
start a transaction, execute a DDL statement and commit that
statement. Start another transaction, execute another DDL statement and
commit that statement...and so on.

If you include DML statements in a script, isql starts a different
transaction that is NOT affected by autoDDL. Setting AutoDDL on does *not*
cause your DML transaction to get "autocommitted".

You must apply DML to committed DDL objects. Weird things happen when you
mess about as you did, with metadata ghosts hanging around in an
uncommitted transaction. >>>"As designed".<<< The wise and worldly simply
avoid running scripts that perform both DDL and DML, unless they are
totally in command of what will eventuate during execution of the script
commands. You're not.

./hb