Make 'ISQL -X' smarter when extracted metadata contains SP w/o RETURNS but with SUSPEND clause (FB4.x+)

[pavel-zotov]

1. Run this script on FB 3.x (it will finish w/o errors):

shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb';

set term ^;
create procedure sp_test as
begin
    suspend;
end
^
commit
^

2. Make backup using FB 3.x and then restore using FB 4.x or 5.x (let target DB name is: r:\temp\tmp4test.fb5x.fdb)
3. Run %FB5_HOME%\isql.exe -x r:\temp\tmp4test.fb5x.fdb
4. Script will be like this:

SET SQL DIALECT 3; 

/* CREATE DATABASE 'localhost:r:\temp\tmp4test.fb5x.fdb' PAGE_SIZE 8192 DEFAULT CHARACTER SET NONE; */
COMMIT WORK;
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures headers */
CREATE OR ALTER PROCEDURE SP_TEST AS 
BEGIN SUSPEND; END ^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures bodies */
ALTER PROCEDURE SP_TEST AS 
begin
    suspend;
end ^
SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;

4. Open extracted metadata script, uncomment create database
5. Remove r:\temp\tmp4test.fb5x.fdb
6. Try to apply extracted metadata.
7. Error will raise:

/* Stored procedures headers */
CREATE OR ALTER PROCEDURE SP_TEST AS 
BEGIN SUSPEND; END ^
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-CREATE OR ALTER PROCEDURE SP_TEST failed
-Dynamic SQL Error
-SQL error code = -104
-SUSPEND could not be used without RETURNS clause in PROCEDURE or EXECUTE BLOCK
After line 11 in file R:\Temp\tmp4test.fb5X.meta.sql

Procedure without output parameters (i.e. w/o RETURNS clause in declaration) must not have SUSPEND according to #6483

It will be useful if isql -x will do smth like "carving" of SUSPEND from header and body of such SP

Otherwise long-time consuming task raises related to writing 'smart parser' of extracted metadata (because SP declaration can either have or have no 'returns' clause at one of its previous lines).

[mrotteveel]

ISQL should do no such thing. It returns the body as stored in the metadata of the database, no more no less.

[pavel-zotov]

The lack of this feature makes migration to 4.x+ difficult (for the case when you have to do a complete recompilation of metadata)

[mrotteveel]

That has always been the case. It's no different from a new version introducing new reserved words where you need to change the script to now quote certain words.

[sim1984]

This error occurs not only in this case, but also when exporting external procedures. External procedures without output parameters are always marked as selective for some reason. I think that when exporting metadata it is not so difficult to additionally check whether there are output parameters, and if there are none, never generate a procedure "header" containing SUSPEND.

[sim1984]

I mean this part of the script

/* Stored procedures headers */
CREATE OR ALTER PROCEDURE SP_TEST AS
BEGIN SUSPEND; END ^

The body that the user wrote is the user's problem.

[aafemt]

It is documented that SUSPEND can work as EXIT in some situation. I see no reason to throw an error if it is used in routines without output values.

The body that the user wrote is the user's problem.

Exactly quoted body is not written by user but generated by isql itself.