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@...