| Subject | Re: [IBO] TIB_Script |
|---|---|
| Author | Helen Borrie |
| Post date | 2007-10-28T09:59:04Z |
At 08:18 PM 28/10/2007, you wrote:
A script is nothing but a batch of statements. Within the restrictions limiting statements to DDL and executable DML (no SELECT statements) you apply the same discipline that you would if you were executing the statements inside the wrappers you are used to. So - you need to commit primary key/unique constraints before they can be referred by foreign key constraints.
As ever, it is best practice to perform DDL first and to commit each statement. You can include COMMIT statements in a script, but you can simplify this by mapping the DDL script's IB_Transaction to an explicit transaction that has ServerAutocommit true. (Equivalent to SET AUTO ON, the default for DDL statements in isql.) Either way provides the safeguard that your IB_script can react to exceptions occurring as the result of one statement and affect what happens subsequently, e.g., by logging errors and even skipping the rest of the script, some isql scripts can't do.
You can't roll back an autocommit script but TIB_Script's statement events mean you can organise a fallback strategy in case of exceptions.
When the DDL script completes to your satisfaction you can have another TStrings ready, loaded with the statements you want to execute in your DML script. After swapping out the old SQL and before executing the new script, change ServerAutoCommit to False so that your DML statements all execute in a single transaction. You generally *do* want rollback capability for DML.
Helen
>Lucas Franzen wrote:TIB_Script doesn't change the rules the database lives by, Aage! :-)
> >
> > Aage Johansen schrieb:
> >> I seem to remember that one is not supposed to use COMMITs within a
> >> script (TIB_Script). Is this correct?
> >>
> >> I need to define several tables with indexes, constraints, triggers,
> >> generators and grants (preferably in a single script). Can I do any
> >> number of DDL statements and just do the single commit afterwards?
> >
> > yes.
> >
>
>Hmmm.
>Testing seems to indicate that creating foreign key contraints
>require previous DDL to be committed. "Object in use" ...
A script is nothing but a batch of statements. Within the restrictions limiting statements to DDL and executable DML (no SELECT statements) you apply the same discipline that you would if you were executing the statements inside the wrappers you are used to. So - you need to commit primary key/unique constraints before they can be referred by foreign key constraints.
As ever, it is best practice to perform DDL first and to commit each statement. You can include COMMIT statements in a script, but you can simplify this by mapping the DDL script's IB_Transaction to an explicit transaction that has ServerAutocommit true. (Equivalent to SET AUTO ON, the default for DDL statements in isql.) Either way provides the safeguard that your IB_script can react to exceptions occurring as the result of one statement and affect what happens subsequently, e.g., by logging errors and even skipping the rest of the script, some isql scripts can't do.
You can't roll back an autocommit script but TIB_Script's statement events mean you can organise a fallback strategy in case of exceptions.
When the DDL script completes to your satisfaction you can have another TStrings ready, loaded with the statements you want to execute in your DML script. After swapping out the old SQL and before executing the new script, change ServerAutoCommit to False so that your DML statements all execute in a single transaction. You generally *do* want rollback capability for DML.
Helen