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
After reconnect the follwing sceance fails to return correct result
After recompiling the stored procedure correct results are returned - until next reconnect!
Followning SQL:
select CALC_FIELD from ATABLE where ID = 0
should return 20 but returns 10 after a reconnect (until stored procedure is recompiled).
Script:
/******************************************************************************/
/* Generated by IBExpert 2008.05.08 03-08-2008 00:40:51 */
/******************************************************************************/
SET SQL DIALECT 3;
SET NAMES ISO8859_1;
CREATE DATABASE 'C:\strange_procedure.fdb'
USER 'SYSDBA' PASSWORD 'MASTERKEY'
PAGE_SIZE 4096
DEFAULT CHARACTER SET ISO8859_1;
CREATE TABLE ATABLE (
ID INTEGER NOT NULL,
RESULT_TO_RETURN FLOAT,
REFERENCE_ID INTEGER,
CALC_FIELD COMPUTED BY ((
select
RESULT
from
STRANGE_PROCEDURE(
ATABLE.RESULT_TO_RETURN,
ATABLE.REFERENCE_ID
)
))
);
INSERT INTO ATABLE (ID, RESULT_TO_RETURN, REFERENCE_ID) VALUES (1, 20, 0);
INSERT INTO ATABLE (ID, RESULT_TO_RETURN, REFERENCE_ID) VALUES (0, 10, NULL);
CREATE OR ALTER PROCEDURE STRANGE_PROCEDURE (
RESULT_TO_RETURN FLOAT,
REFERENCE_ID INTEGER)
RETURNS (
RESULT FLOAT)
AS
declare variable sum_of_referenced_items float;
begin
-- Initialize variables
RESULT = RESULT_TO_RETURN;
SUM_OF_REFERENCED_ITEMS = 0;
\-\- If reference is null then it
\-\- must be record ID = 0
if \(REFERENCE\_ID is null\) then
begin
select sum\(T\.CALC\_FIELD\)
from ATABLE T where T\.REFERENCE\_ID = 0
into :SUM\_OF\_REFERENCED\_ITEMS;
if \(SUM\_OF\_REFERENCED\_ITEMS \> :RESULT\)
then RESULT = SUM\_OF\_REFERENCED\_ITEMS;
end
suspend;
Submitted by: Poul Dige (tabulex)
Is related to CORE6351
After reconnect the follwing sceance fails to return correct result
After recompiling the stored procedure correct results are returned - until next reconnect!
Followning SQL:
select CALC_FIELD from ATABLE where ID = 0
should return 20 but returns 10 after a reconnect (until stored procedure is recompiled).
Script:
/******************************************************************************/
/* Generated by IBExpert 2008.05.08 03-08-2008 00:40:51 */
/******************************************************************************/
SET SQL DIALECT 3;
SET NAMES ISO8859_1;
CREATE DATABASE 'C:\strange_procedure.fdb'
USER 'SYSDBA' PASSWORD 'MASTERKEY'
PAGE_SIZE 4096
DEFAULT CHARACTER SET ISO8859_1;
SET TERM ^ ;
/******************************************************************************/
/* Stored Procedures */
/******************************************************************************/
CREATE OR ALTER PROCEDURE STRANGE_PROCEDURE (
RESULT_TO_RETURN FLOAT,
REFERENCE_ID INTEGER)
RETURNS (
RESULT FLOAT)
AS
BEGIN
SUSPEND;
END^
SET TERM ; ^
/******************************************************************************/
/* Tables */
/******************************************************************************/
CREATE TABLE ATABLE (
ID INTEGER NOT NULL,
RESULT_TO_RETURN FLOAT,
REFERENCE_ID INTEGER,
CALC_FIELD COMPUTED BY ((
select
RESULT
from
STRANGE_PROCEDURE(
ATABLE.RESULT_TO_RETURN,
ATABLE.REFERENCE_ID
)
))
);
INSERT INTO ATABLE (ID, RESULT_TO_RETURN, REFERENCE_ID) VALUES (1, 20, 0);
INSERT INTO ATABLE (ID, RESULT_TO_RETURN, REFERENCE_ID) VALUES (0, 10, NULL);
COMMIT WORK;
/******************************************************************************/
/* Stored Procedures */
/******************************************************************************/
SET TERM ^ ;
CREATE OR ALTER PROCEDURE STRANGE_PROCEDURE (
RESULT_TO_RETURN FLOAT,
REFERENCE_ID INTEGER)
RETURNS (
RESULT FLOAT)
AS
declare variable sum_of_referenced_items float;
begin
-- Initialize variables
RESULT = RESULT_TO_RETURN;
SUM_OF_REFERENCED_ITEMS = 0;
end^
SET TERM ; ^
Commits: b6b6897 40e1b3c
The text was updated successfully, but these errors were encountered: