Incorrect isql -x script extraction for external procedures with no output parameters.

[sim1984]

Let's say we have two external procedures, one selective and one non-selective, written as follows:

/***
create procedure insert_row (
	n integer not null
)
	external name 'isql_udr_bug!insert_row'
	engine udr;
***/
FB_UDR_BEGIN_PROCEDURE(insert_row)
	FB_UDR_MESSAGE(InMessage,
		(FB_INTEGER, n)
	);

	FB_UDR_EXECUTE_PROCEDURE
	{
		AutoRelease<IAttachment> att(context->getAttachment(status));
		AutoRelease<ITransaction> tra(context->getTransaction(status));
		AutoRelease<IMessageMetadata> inMetadata(this->procedure->metadata->getInputMetadata(status));
		att->execute(
			status,
			tra,
			0,
			"INSERT INTO test_table(n) VALUES(?)",
			3,
			inMetadata,
			in,
			nullptr,
			nullptr
		);
	}

	FB_UDR_FETCH_PROCEDURE
	{
		return false;
	}
FB_UDR_END_PROCEDURE


/***
create procedure gen_rows (
	start_n integer not null,
	end_n integer not null
) returns (
	n integer not null
)
	external name 'isql_udr_bug!gen_rows'
	engine udr;
***/
FB_UDR_BEGIN_PROCEDURE(gen_rows)
	FB_UDR_MESSAGE(InMessage,
		(FB_INTEGER, start)
		(FB_INTEGER, end)
	);

	FB_UDR_MESSAGE(OutMessage,
		(FB_INTEGER, result)
	);

	FB_UDR_EXECUTE_PROCEDURE
	{
		out->resultNull = FB_FALSE;
		out->result = in->start - 1;
	}

	FB_UDR_FETCH_PROCEDURE
	{
		return out->result++ < in->end;
	}
FB_UDR_END_PROCEDURE

Now let's create a new database using these procedures:

create database 'inet://localhost:3054/D:\fbdata\4.0\isql_bug.fdb'
user SYSDBA password 'masterkey'
default character set utf8;

create table test_table(
   id bigint generated by default as identity,
   n integer,
   constraint pk_test_table primary key(id)
);

create procedure insert_row (
	n integer not null
)
external name 'isql_udr_bug!insert_row'
engine udr;


create procedure gen_rows (
	start_n integer not null,
	end_n integer not null
) returns (
	n integer not null
)
external name 'isql_udr_bug!gen_rows'
engine udr;

exit;

Now let's extract the database creation script using isql -x

isql inet://localhost:3054/D:\fbdata\4.0\isql_bug.fdb -u SYSDBA -p masterkey -x -o D:\fbdata\4.0\isql_bug.sql

As a result, we get a script with the following content:

SET SQL DIALECT 3; 

/* CREATE DATABASE 'inet://localhost:3054/D:\fbdata\4.0\isql_bug.fdb' PAGE_SIZE 8192 DEFAULT CHARACTER SET UTF8; */


COMMIT WORK;

/* Table: TEST_TABLE, Owner: SYSDBA */
CREATE TABLE TEST_TABLE (ID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
        N INTEGER,
CONSTRAINT PK_TEST_TABLE PRIMARY KEY (ID));

COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures headers */
CREATE OR ALTER PROCEDURE GEN_ROWS (START_N INTEGER NOT NULL,
END_N INTEGER NOT NULL)
RETURNS (N INTEGER NOT NULL)
AS 
BEGIN SUSPEND; END ^
CREATE OR ALTER PROCEDURE INSERT_ROW (N INTEGER NOT NULL)
AS 
BEGIN SUSPEND; END ^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;

COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures bodies */

ALTER PROCEDURE GEN_ROWS (START_N INTEGER NOT NULL,
END_N INTEGER NOT NULL)
RETURNS (N INTEGER NOT NULL)
EXTERNAL NAME 'isql_udr_bug!gen_rows'
ENGINE UDR ^

ALTER PROCEDURE INSERT_ROW (N INTEGER NOT NULL)
EXTERNAL NAME 'isql_udr_bug!insert_row'
ENGINE UDR ^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;

Note that the pre-creation of the INSERT_ROW procedure contains SUSPEND, which results in an error because the procedure has no output parameters.

[sim1984]

The error occurs on all versions of Firebird that support UDR, ie 3.0 and above.