Hi, greetings from a (satisfied) lurker.

I want to add a NOT NULL constraint to an existing column. Unless I'm
totally missing something, this can't be done in a single command and
I'm on board with the idea of using a temporary column and
accomplishing the task in several steps. I'm not super-experienced
using transactions but I understand that FB supports DDL in
transactions and this seems like a natural time to use one.

The code below fails on the first update statement ("Column unknown
TEMP_COL") though I would have thought that the new column could be
"seen" within the transaction. The rollback line is executed and
temp_col is not added, as expected.

I thought maybe savepoints was the way to go but now I'm really out of
my depth and am also fuzzy on how/if IBO supports nested transactions.
The sequence of steps in the code runs fine without transaction
control but is unsafe, I should think. Am using FB2 & IBO 4.7 Beta
14. Many thanks for any insights.

Best-
-David



sqlTemp := TIB_DSQL.Create( Application );
trnTemp := TIB_Transaction.Create( Application );

trnTemp.IB_Connection := whichDatabase;
trnTemp.AutoCommit := False;


with sqlTemp do begin

IB_Transaction := trnTemp;

trnTemp.StartTransaction;

try

ExecuteDDL( 'ALTER TABLE myTable ADD temp_col varchar(3) ' );

ExecuteDML( 'UPDATE myTable SET temp_col = real_col WHERE real_col
IS NOT NULL; ', Nil );

ExecuteDML( 'UPDATE myTable SET temp_col = ''xxx'' WHERE real_col
IS NULL; ', Nil );

ExecuteDDL( 'ALTER TABLE myTable DROP real_col; ' );

ExecuteDDL( 'ALTER TABLE myTable ADD real_col varchar(3)
CONSTRAINT nn_real_col NOT NULL ' );

ExecuteDML( 'UPDATE myTable SET real_col = temp_col; ', Nil );

ExecuteDDL( 'ALTER TABLE myTable DROP temp_col; ' );

trnTemp.Commit;

except
on Exception do begin
trnTemp.Rollback;
Raise;
end;

end;

end;