Subject Re: [IBO] What component?
Author Paul Vinkenoog
Hi Rafael,

> Can you explain how to use Set Term end end^? I saw that you donĀ“t
> close the last setterm ... why?

This "set term" stuff is needed if you create or alter a stored
procedure or trigger. The default terminator is ";" (semicolon), but
SP's and triggers also contain a number of those. There's no way for
the parser to tell which semicolon belongs *within* the SP, and which
one *terminates* the SP.

That's why you must temporarily assign another terminator. You can
choose any terminator you like, as long as it doesn't occur within the
SP. People often use $$ or ## or !! or ^.

Before the "create procedure", you tell Firebird that you introduce a
new terminator:

set term ## ;

Note that the SET TERM statement itself must still be closed with the
current terminator, a semicolon.

Now comes your create/alter procedure/trigger statement, with the body
of the procedure containing semicolons to close the statements.
Because the current terminator is now ##, the parser knows that the
create or alter statement is not finished if it encounters a semicolon.

After the final "end", you put your current terminator ## to tell the
parser that your procedure definition is complete. You can do that
like this

end ##

or like this


After that, you want your default terminator back, so you write
another SET TERM statement:

set term ; ##

This one tells the parser to switch back to the semicolon as
terminator, but the line itself is still interpreted under the regime
of the ## terminator - that's why you have to close it with ##

After that, everything's back to normal.

Note 1:

If you define a number of SP's/triggers in a row, you only need
two SET TERM lines: before the first one, and after the last one.

Note 2:

You can also put SET TERM $$ ; right at the start of your script, and
SET TERM ; $$ at the end.
This way you don't have to put SET TERMs around procedures and
triggers. Just use semicolons within them and $$ after the final
"end". But watch it: now you must also close *every* statement outside
SP/trigger definitions with $$ (or whatever terminator you choose):

create domain DREC_ID integer not null $$
create domain PERSON_NAME varchar( 32 ) $$


Paul Vinkenoog