Subject Re: [IBO] Using a IB_Script to create new fields
Author Helen Borrie
At 11:07 PM 9/03/2006, you wrote:
>I have an existing application, that connects to a Firebird database
>created by 3rd party database manager. I would now like my 'updated'
>application to create several new fields in the database. AFAIU i can
>use a script with something like:
>(from a previous post :)
>and then do
> after connecting to the database.
>This seems to work ok the first time its run, but once the new field
>has been added, running the program again causes problems, i assume
>due to trying to create a field that already exists.
>Is it enough to simply use
>try IB_script1.execute

Yep, it's one way, if the script is only doing one thing. ( But why
would you be using a script in that case? ) It's really up to you to
work out whether catching the first exception is all you need.

>or should I be trying to determine if the field exists, and if so how?
>Many thanks for any help given

If you want to (or need to, can't tell as I don't know why you're
using a script..) you can intervene in BeforeExecute or OnStatement
by calling a function that specifically tests for the presence of the
column[s]. If the function returns False, call the AbortExecuting
method (or, from OnStatement, you can return True in the SkipIt
argument if you just want to jump over that statement. Of course,
because it's a script, any commits that occurred in the script
previously will stay committed.

Here's a way you could write that function:

function YourDM.TestForColumn(aTableName, aColumnName:string):Boolean;
crsr: tib_cursor;
Result := False;
crsr := tib_dsql.create(self);
with crsr do
ib_connection = Yourconnection;
sql.add('select 1 from rdb$relation_fields' );
sql.add('where rdb$relation_name = :rn and rdb$field_name = :fn');
params[0].AsString := aTableName;
params[1].AsString := aColumnName;
if ((not BOF) and (not EOF)) then
Result := True;
// ib_transaction.Commit; // not needed if called in an
IB_Script event handler

Sorry for any errors, it's late, and this is just for a guideline anyway.