Subject Re: [IBO] Mixing DDL and DML in a Transation
Author Helen Borrie
At 07:27 AM 9/08/2008, you wrote:
>I'm trying to create a table (if it doesn't exist) and populate it, all inside
>a transaction. If any of the data isn't kosher, I want the table to be rolled
>back along with the inserts.

Not possible.

>However, I get a "Table Unknown" error on the insert.

As designed. DDL statements perform a number of "under-the-hood" operations on commit...until the commit, the "object" is nothing but an entry in a system table. This data is visible to your transaction as a row in a system table but none of the required operations has yet occurred to make the *object* exist.

So - DON'T mix DDL and DML in the same transaction. Just don't. There is no workaround - it is *designed* that way. Even when you are performing only DDL, you also have to ensure that you don't try to do stuff within the same transaction that tries to operate on an object with uncommitted creation or alteration requests pending.

>I've found that calling the transaction's SavePoint procedure commits
>the create table DDL, but then of course it won't get rolled back should the
>insert fail.

You cannot achieve that. Give up.

Use a script to create or alter your database objects (IBO has TIB_Script) and take care to include COMMIT statements in the appropriate places. Use multiple scripts so that you start performing DML only after all of the DDL has committed safely.

> The code below illustrates the issue.
>I get the feeling I'm missing something simple, but the IBO FAQ and help
>haven't aided me.

It's not an issue that IBO even touches. IBO implements the API but it does not supply you with ways to break Firebird's or IB's architecture. As for "missing something simple", it's very important to understand the differences between DDL and DML...even without the protection that the engine throws up to prevent clients from corrupting databases, you should be designing your application architecture "with parachutes".