Can I programmatically exit a Firebird script

Question

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?

Answer 1

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).

Answer 2

If the script is supposed to be executed only via IBExpert (not other tools like isql or FlameRobin), you can use IBExpert script extensions.

{$IFEXISTS select 1 from sometable where somevalue = 1 }
-- put your script here
{$ENDIF}

For example, this is how I write DB upgrade scripts, where each script needs to be executed in a specific order:

-- Upgrade db version from 201 to 202
{$IFNOTEXISTS select 1 from DBINFO where DB_VERSION = 201 }
execute block as begin
    exception EX_DB_UPGRADE 'Wrong DB_VERSION';
end;
{$ELSE}
-- <upgrade_script_here>

update DBINFO set DB_VERSION = 202 where DB_VERSION = 201;
commit;
{$ENDIF}