You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
When you have a stored procedure that utilises another stored procedure, Firebird allows you to modify the parameters of the internal stored procedure without complaint. This leads to both a situation where backups will not correctly restore, and the outer procedure encountering a runtime exception.
Script to duplicate in iSQL
(WARNING, this will cause the backup of the database to fail, so don't perform this on databases containing important data)
----
SET TERM ^ ;
CREATE PROCEDURE SP_HAS_FIVEPARAMS
(
VPARAM1 INTEGER,
VPARAM2 INTEGER,
VPARAM3 INTEGER,
VPARAM4 INTEGER,
VPARAM5 INTEGER
)
RETURNS
(
OUTPUTPARAM INTEGER
)
AS
BEGIN
OUTPUTPARAM = :VPARAM1 + :VPARAM2 + :VPARAM3 + :VPARAM4 + :VPARAM5;
SUSPEND;
END
^
CREATE PROCEDURE SP_OUTER_PROC
RETURNS
(
OUTERPARAM INTEGER
)
AS
BEGIN
SELECT OUTPUTPARAM
FROM SP_HAS_FIVEPARAMS(1,2,3,4,5)
INTO :OUTERPARAM;
SUSPEND;
END
^
SET TERM ;
^
COMMIT;
/* calling SP_OUTER_PROC returns 15 here as expected */
SELECT * from SP_OUTER_PROC;
/* Now we modify SP_HAS_FIVEPARAMS. IMO, this should fail because of the dependency in SP_OUTER_PROC */
SET TERM ^ ;
ALTER PROCEDURE SP_HAS_FIVEPARAMS
(
VPARAM1 INTEGER,
VPARAM2 INTEGER,
VPARAM4 INTEGER,
VPARAM5 INTEGER
)
RETURNS
(
OUTPUTPARAM INTEGER
)
AS
BEGIN
OUTPUTPARAM = :VPARAM1 + :VPARAM2 + :VPARAM4 + :VPARAM5;
SUSPEND;
END
^
SET TERM ; ^
COMMIT;
/* In Firebird 2, you need to close and relaunch iSQL here so it does not cache the old SP_HAS_FIVEPARAMS. */
/* The following DML now raises an exception */
SELECT * from SP_OUTER_PROC;
/* We can live with the exception, however the exception also prevents any backup taken from this point on from restoring correctly. Views, Stored Procedures and Triggers are not restored. A data pump is required to completely restore the backup. */
---
Let me know if you need anything else.
Adam
The text was updated successfully, but these errors were encountered:
This problem can result in the calling procedure operating incorrectly with no exception being reported. For example:
SET TERM ^ ;
CREATE DATABASE 'mltest'^
COMMIT^
connect 'mltest'^
CREATE PROCEDURE PSUB (test char(15)) returns (test2 char(15)) as begin test2 = test; suspend; end^
CREATE PROCEDURE PCALLER returns (test3 varchar(30)) as begin select test2 || 'x' from psub('abc') into :test3; suspend; end^
COMMIT^
ALTER PROCEDURE PSUB (test varchar(13)) returns (test2 varchar(13)) as begin test2 = test; suspend; end^
COMMIT^
set heading off^
select test3 from pcaller^
ALTER PROCEDURE PCALLER returns (test3 varchar(30)) as begin select test2 || 'x' from psub('abc') into :test3; suspend; end^
COMMIT^
select test3 from pcaller^
Results in the following output:
c x
abcx
As you can see, the PCALLER proc returns a different result after it is recompiled without changes, but no exceptions are produced at any point.
I raise the severity of this bug
I confirm it under 2.1.3
and it is really a problem since it lead to unrestorable backup
we really should better check dependency between SP.
in support list there is another test case similar ([firebird-support] update parameter list of a stored procedure
Date : Tue, 3 Nov 2009 11:18:41 +0000 (UTC) )
Submitted by: Adam Gardner (s3057043)
When you have a stored procedure that utilises another stored procedure, Firebird allows you to modify the parameters of the internal stored procedure without complaint. This leads to both a situation where backups will not correctly restore, and the outer procedure encountering a runtime exception.
Script to duplicate in iSQL
(WARNING, this will cause the backup of the database to fail, so don't perform this on databases containing important data)
----
SET TERM ^ ;
CREATE PROCEDURE SP_HAS_FIVEPARAMS
(
VPARAM1 INTEGER,
VPARAM2 INTEGER,
VPARAM3 INTEGER,
VPARAM4 INTEGER,
VPARAM5 INTEGER
)
RETURNS
(
OUTPUTPARAM INTEGER
)
AS
BEGIN
OUTPUTPARAM = :VPARAM1 + :VPARAM2 + :VPARAM3 + :VPARAM4 + :VPARAM5;
SUSPEND;
END
^
CREATE PROCEDURE SP_OUTER_PROC
RETURNS
(
OUTERPARAM INTEGER
)
AS
BEGIN
SELECT OUTPUTPARAM
FROM SP_HAS_FIVEPARAMS(1,2,3,4,5)
INTO :OUTERPARAM;
SUSPEND;
END
^
SET TERM ;
^
COMMIT;
/* calling SP_OUTER_PROC returns 15 here as expected */
SELECT * from SP_OUTER_PROC;
/* Now we modify SP_HAS_FIVEPARAMS. IMO, this should fail because of the dependency in SP_OUTER_PROC */
SET TERM ^ ;
ALTER PROCEDURE SP_HAS_FIVEPARAMS
(
VPARAM1 INTEGER,
VPARAM2 INTEGER,
VPARAM4 INTEGER,
VPARAM5 INTEGER
)
RETURNS
(
OUTPUTPARAM INTEGER
)
AS
BEGIN
OUTPUTPARAM = :VPARAM1 + :VPARAM2 + :VPARAM4 + :VPARAM5;
SUSPEND;
END
^
SET TERM ; ^
COMMIT;
/* In Firebird 2, you need to close and relaunch iSQL here so it does not cache the old SP_HAS_FIVEPARAMS. */
/* The following DML now raises an exception */
SELECT * from SP_OUTER_PROC;
/* We can live with the exception, however the exception also prevents any backup taken from this point on from restoring correctly. Views, Stored Procedures and Triggers are not restored. A data pump is required to completely restore the backup. */
---
Let me know if you need anything else.
Adam
The text was updated successfully, but these errors were encountered: