I don't believe it is possible to create a table and populate that table
within the same transaction. You are using Firebird 2.1, I believe it
supports temporary tables, maybe you can accomplish what you need with that.



Jerry Sands





From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] On Behalf
Of Toby Leonard
Sent: Friday, August 08, 2008 4:27 PM
To: IBObjects@yahoogroups.com
Subject: [IBO] Mixing DDL and DML in a Transation



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. However, I get a "Table Unknown" error on the
insert. 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. 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. Thanks in advance.

FB 2.1 embedded, IBO 4.8.6, D2007

procedure TForm1.XactTest(const ADBConn: TIB_Connection);
var
Xact: TIB_Transaction;
DSQL: TIB_DSQL;
s: String;
begin
Xact := TIB_Transaction.Create(nil);
try
Xact.IB_Connection := ADBConn;
Xact.Isolation := tiConcurrency;

try
DSQL := TIB_DSQL.Create(nil);
try
DSQL.IB_Connection := ADBConn;
DSQL.IB_Transaction := Xact;

Xact.StartTransaction;

// If table exists, drop it
// (omitted for brevity)

// Create table
s :=
'CREATE TABLE FOO ' +
'( ' +
' BAR INTEGER NOT NULL, ' +
' CONSTRAINT PK_FOO PRIMARY KEY (BAR) ' +
')';
DSQL.ExecuteDDL(s);

// This commits the table, but it won't be rolled back if the insert
// fails.
Xact.SavePoint;

// Add a row
DSQL.SQL.Text :=
'insert into ' +
' foo (' +
' bar) ' +
' values TYPO (' + // Note typo here forces a rollback
' :bar)';
DSQL.ParamValues['bar'] := 5;
DSQL.ExecSQL;

Xact.Commit;
finally
FreeAndNil(DSQL);
end;
except
if Xact.InTransaction then
begin
Xact.Rollback;
end;

raise;
end;
finally
FreeAndNil(Xact);
end;
end;

Toby
--
Toby Leonard | tobyl@...
<mailto:tobyl%40netedgesolutions.com>





[Non-text portions of this message have been removed]