3

Consider the following script:

set term ^;

exit
^

execute block
as
begin
   execute statement 'this will fail';
end
^

The exit is perfectly valid and does cause script execution to end. At least in IBExpert where I am testing this. But I want to do this programmatically.

set term ^;

execute block
as
begin
   if (exists(select 1 from sometable where somevalue = 1)) then begin
      -- This only exits the block, not the script
      exit;
   end
end
^

execute block
as
begin
   execute statement 'this will fail';
end
^

Is the exit in my first example valid Firebird or is IBExpert handling this itself? Is there a different way to exit the entire script conditionally?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Paul
  • 5,700
  • 5
  • 43
  • 67

1 Answers1

3

You are confusing the use of exit in your script, with using exit inside Firebird statements (specifically the execute block). The plain exit in your script is interpreted by IBExpert as a signal to stop the script.

However when exit is part of an execute block, it is not part of your script, it is part of a statement that is sent to Firebird server for execution, and it does not have effect on the execution of the script itself.

The code in an execute block statement is PSQL, where EXIT has a specific meaning:

The EXIT statement causes execution of the procedure or trigger to jump to the final END statement from any point in the code, thus terminating the program.

Here, program is the procedure (an execute block is an anonymous procedure) or trigger.

In other words, an exit within an execute block causes termination of that execute block, nothing more.

I don't know if IBExpert supports more advanced scripting options, but you could look at returning a value from the execute block and use a condition in your script to exit (if that is possible in IBExpert). Another solution might be to raise an exception within the execute block (this assumes IBExpert stops the script on errors).

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Yeah, I totally get the difference in the `exit`. I only have it in the execute block as an example. I was hoping there was another keyword similar to how `leave` works with cursors. Something that would break a script fully. I guess it makes sense that there would not be since this is PSQL and it shouldn't communicate back to a script. The reason I thought it might be possible is because exit worked by itself. – Paul Apr 17 '18 at 19:50
  • @Paul `exit` by itself works, because that is how IBExpert handles it if it is a statement on its own. – Mark Rotteveel Apr 18 '18 at 07:58