--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 01:27 PM 7/05/2009, you wrote:
>
> >BUT
> >i forcibly use of option of fb2.1 temp table ,domain in sp then can not downgrade to 2.0.5 !
> >
> >
> >my problem occurs when in one sp call other sp in return show me this error
>
> Show us the source code for the two SPs AND the SELECT statement that your application sends. Someone can probably help you to code these in a way that avoids your problems.
>
> ./heLen
>

Base :
create or alter procedure PRC_SYS$SUBSYSTEM_OBJ (
I_CONECTION DM_GUID)
returns (
RET_ROW DM_ID,
RET_STATE DM_ID,
RET_ERROR_NUM DM_ID,
RET_ERROR_GROUP varchar(20),
RET_ERROR_COMMENT varchar(150),
RET_SUBSYS DM_ID,
RET_SUBSYS_NAME DM_NAME_L2,
RET_SUBSYS_CAPTION DM_NAME_L2,
RET_SUBSYS_PRIORITY_DISP DM_ID,
RET_SUBSYS_ICON DM_ID,
RET_SUBSYS_KID DM_ID,
RET_SUBSYS_GUID DM_GUID,
RET_SYSOBJ_PRIORITY_CREATE DM_ID,
RET_SYSOBJ_NID DM_ID,
RET_SYSOBJ_KID DM_ID,
RET_SYSOBJ_NAME DM_NAME_L2,
RET_SYSOBJ_TTYPE DM_ID,
RET_SYSOBJ_GUID DM_GUID,
RET_SYSOBJ_CMMNT DM_COMMENT_110,
RET_SYSOBJ_DB_NAME DM_NAME_L2,
RET_SGROUP_NID DM_ID,
RET_SGROUP_KID DM_ID,
RET_SGROUP_NAME DM_NAME_L2,
RET_SGROUP_CAPTION DM_NAME_L2,
RET_SGROUP_ICON DM_ID,
RET_SGROUP_PRIORITY_DISP DM_ID,
RET_SGROUP_GUID DM_GUID)
AS
--DECLARE VARIABLE RET_SYSOBJ_NID DM_ID;
--DECLARE VARIABLE RET_SUBSYS DM_ID;
DECLARE VARIABLE Z_USER DM_ID;
DECLARE VARIABLE Z_ROLE DM_ID;
BEGIN
-- GET GRANT.
EXECUTE PROCEDURE FUN_GET_CUR_USERSECINFO RETURNING_VALUES :Z_USER;
EXECUTE PROCEDURE FUN_GET_CUR_SUBSYSROLE RETURNING_VALUES :Z_ROLE;
FOR
SELECT RET_SUBSYS_ID,RET_OBJ_ID FROM PRC_SYS$OBJ_DIRECT_GRANT(:Z_USER,:Z_ROLE)
UNION
SELECT RET_SUBSYS_ID,RET_OBJ_ID FROM PRC_SYS$OBJ_GRANT(:Z_USER,:Z_ROLE)
ORDER BY 2
INTO :RET_SUBSYS,:RET_SYSOBJ_NID
DO BEGIN
RET_SUBSYS_NAME = NULL; RET_SUBSYS_CAPTION = NULL; RET_SUBSYS_PRIORITY_DISP = NULL; RET_SUBSYS_ICON = NULL;
RET_SUBSYS_KID = NULL; RET_SUBSYS_GUID = NULL; RET_SYSOBJ_PRIORITY_CREATE = NULL; RET_SYSOBJ_KID = NULL;
RET_SYSOBJ_NAME = NULL; RET_SYSOBJ_TTYPE = NULL; RET_SYSOBJ_GUID = NULL; RET_SGROUP_GUID = NULL;
RET_SYSOBJ_CMMNT = NULL; RET_SYSOBJ_DB_NAME = NULL; RET_SGROUP_NID = NULL; RET_SGROUP_KID = NULL;
RET_SGROUP_NAME = NULL; RET_SGROUP_CAPTION = NULL; RET_SGROUP_ICON = NULL; RET_SGROUP_PRIORITY_DISP = NULL;


SELECT S.NAME, S.CAPTION, S.ICON, S.PRIORITY_DISP, S.KID, S.GUID ,
G.NAME, G.CAPTION, G.ICON, G.PRIORITY_DISP, G.KID, G.GUID
FROM SOFT$SUBSYS S
JOIN SOFT$SUBSYS$GROUP G ON (G.NID = S.GROUP_LINK)
WHERE (S.NID = :RET_SUBSYS)
INTO :RET_SUBSYS_NAME, :RET_SUBSYS_CAPTION, :RET_SUBSYS_ICON, :RET_SUBSYS_PRIORITY_DISP,:RET_SUBSYS_KID, :RET_SUBSYS_GUID,
:RET_SGROUP_NAME, :RET_SGROUP_CAPTION, :RET_SGROUP_ICON, :RET_SGROUP_PRIORITY_DISP,:RET_SGROUP_KID, :RET_SGROUP_GUID;

SELECT O.NAME, O.TTYPE, O.DB_NAME, O.GUID, O.KID, O.CMMNT ,L.PRIORITY_CREATE
FROM SOFT$SUBSYS$OBJECT$LINK L
JOIN SOFT$SUBSYS$OBJ O ON (O.NID = L.OBJ)
WHERE ((L.OBJ = :RET_SYSOBJ_NID) AND (L.SUBSYS = :RET_SUBSYS))
INTO :RET_SYSOBJ_NAME, :RET_SYSOBJ_TTYPE, :RET_SYSOBJ_DB_NAME, :RET_SYSOBJ_GUID, :RET_SYSOBJ_KID, :RET_SYSOBJ_CMMNT,
:RET_SYSOBJ_PRIORITY_CREATE;

SUSPEND;
END
END


create or alter procedure FUN_GET_CUR_USERSECINFO
returns (
RET_USER_SEC_INFO dm_id)
AS
DECLARE VARIABLE Z_USER_ID DM_ID;
BEGIN
RET_USER_SEC_INFO = NULL;
Z_USER_ID = NULL;
EXECUTE PROCEDURE FUN_GET_CUR_USERID RETURNING_VALUES :Z_USER_ID;
SELECT U.NID FROM SOFT$SEC$USERINFO U WHERE (U.L_USER = :Z_USER_ID) INTO :RET_USER_SEC_INFO;
SUSPEND;
END


create or alter procedure FUN_GET_CUR_USERID
returns (
RET_USER_ID dm_id)
AS
BEGIN
RET_USER_ID = NULL;
SELECT NID FROM ORG$PERSON ORGP WHERE ORGP.DB_NAME = CURRENT_USER INTO RET_USER_ID;
SUSPEND;
END