Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Changing the number of type of parameters in stored procedure does not check dependencies [CORE1320] #1739

Open
firebird-automations opened this issue Jun 14, 2007 · 4 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 12310 ] => Firebird [ 14890 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Fergus McDonald (fergusm)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

priority: Minor [ 4 ] => Major [ 3 ]

Version: 2.1.3 [ 10302 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant