CREATE PROCEDURE RuleCheckForAccessRights (v_UserName CHAR(30),
v_legl_ent_name VARCHAR(30),
v_legl_ent_prd_grp VARCHAR(30),
v_legl_ent_region VARCHAR(30),
v_Type VARCHAR(30),
OUT v_ErrorType INTEGER,
OUT v_ErrorCode VARCHAR(25) )
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE l_error CHAR(5) DEFAULT '00000';
DECLARE v_UserId INTEGER;
DECLARE v_GroupId INTEGER;
DECLARE loc RESULT_SET_LOCATOR VARYING;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET l_error = '00000';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET l_error = SQLSTATE;
IF SUBSTR(l_error, 1, 1) >= '5'
AND SUBSTR(l_error, 1, 1) <= '9' THEN
RESIGNAL;
END IF;
END;
DECLARE GLOBAL TEMPORARY TABLE tempAssignedGroups (groupID INTEGER NOT NULL,
groupName VARCHAR(50) NOT NULL,
description VARCHAR(140) NOT NULL);
DECLARE GLOBAL TEMPORARY TABLE tempUserGroups (groupID INTEGER NOT NULL);
DECLARE GLOBAL TEMPORARY TABLE tempResultGroups (groupID INTEGER NOT NULL);
SELECT UserId
INTO v_UserId
FROM up
WHERE UserName = v_UserName
FETCH FIRST 1 ROWS ONLY;
BEGIN
INSERT INTO SESSION.tempUserGroups
(SELECT groupid FROM Mapping
WHERE UserId = v_UserId);
COMMIT;
INSERT INTO SESSION.tempAssignedGroups(
CALL ReadAssignedGroups (v_legl_ent_name, v_legl_ent_prd_grp, v_legl_ent_region, v_Type);
);
COMMIT;
IF ((SELECT COUNT(*) FROM SESSION.tempAssignedGroups) > 0) THEN
INSERT INTO SESSION.tempResultGroups
(SELECT groupid FROM SESSION.tempUserGroups A WHERE EXISTS
(SELECT 1 FROM SESSION.tempAssignedGroups B where A.GroupID = B.GroupID));
COMMIT;
IF ((SELECT COUNT(*) FROM SESSION.tempResultGroups) > 0) THEN
SET v_ErrorType = 0;
SET v_ErrorCode = '';
ELSE
SET v_ErrorType = 2;
SET v_ErrorCode = '50128';
END IF;
ELSE
SET v_ErrorType = 0;
SET v_ErrorCode = '';
END IF;
END;
END!][/code]
I tried to deploy the above PROCEDURE to the DB2 database but it erred out while deploying.I'm using the IBM DB2 Toolkit to deploy the PROC.
I'm getting the following error directed by the toolkit
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "CALL ReadAssignedGroups (v_legl" was found
following "tempAssignedGroups( ". Expected tokens may include:
"<query_expr_body>". LINE NUMBER=65. SQLSTATE=42601
Can anyone clarify how "CALL to a stored PROC" should be handled within DB2 and also suggest me how to insert the result set of the PROC into a table...
ReadAssignedGroups is not a UDF its also an Stored PROC its contains below mentioned code
[Drop procedure ReadAssignedGroups
@
CREATE PROCEDURE ReadAssignedGroups (v_legl_ent_name VARCHAR(30),
v_legl_ent_prd_grp VARCHAR(30),
v_legl_ent_region VARCHAR(30),
v_Type VARCHAR(30) )
LANGUAGE SQL
P1:BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE l_error CHAR(5) DEFAULT '00000';
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET l_error = '00000';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET l_error = SQLSTATE;
IF SUBSTR(l_error, 1, 1) >= '5'
AND SUBSTR(l_error, 1, 1) <= '9' THEN
RESIGNAL;
END IF;
END;
DECLARE GLOBAL TEMPORARY TABLE tempIncluded (groupID INTEGER NOT NULL,
groupName VARCHAR(50) NOT NULL,
description VARCHAR(140) NOT NULL);
DECLARE GLOBAL TEMPORARY TABLE tempExcluded (groupID INTEGER NOT NULL,
groupName VARCHAR(50) NOT NULL,
description VARCHAR(140) NOT NULL);
DECLARE GLOBAL TEMPORARY TABLE includedGroups (groupID INTEGER NOT NULL,
groupName VARCHAR(50) NOT NULL,
description VARCHAR(140) NOT NULL);
IF (v_Type = 'LegalEntity') THEN
INSERT INTO SESSION.tempIncluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname is null
AND a.RegionName is null
AND a.IsExcluded ='N'
AND a.GroupID = b.GroupID);
COMMIT;
ELSE
IF (v_Type = 'LOB') THEN
INSERT INTO SESSION.tempIncluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname is null
AND a.RegionName is null
AND a.IsExcluded ='N'
AND a.GroupID = b.GroupID);
COMMIT;
INSERT INTO SESSION.tempIncluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname = v_legl_ent_prd_grp
AND a.RegionName is null
AND a.IsExcluded ='N'
AND a.GroupID = b.GroupID);
COMMIT;
--| --Checking whether this lob is excluded from any ratabase.Groups or not
INSERT INTO SESSION.tempExcluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname = v_legl_ent_prd_grp
AND a.RegionName is null
AND a.IsExcluded ='Y'
AND a.GroupID = b.GroupID);
COMMIT;
INSERT INTO SESSION.includedGroups
(SELECT * FROM SESSION.tempIncluded WHERE GroupID not in (SELECT GroupID FROM SESSION.tempExcluded));
COMMIT;
ELSE
IF (v_Type = 'REGION') THEN
INSERT INTO SESSION.tempIncluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname is null
AND a.RegionName is null
AND a.IsExcluded ='N'
AND a.GroupID = b.GroupID);
COMMIT;
INSERT INTO SESSION.tempIncluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname = v_legl_ent_prd_grp
AND a.RegionName is null
AND a.IsExcluded ='N'
AND a.GroupID = b.GroupID);
COMMIT;
INSERT INTO SESSION.includedGroups
(SELECT * FROM SESSION.tempIncluded WHERE GroupID not in (SELECT GroupID FROM SESSION.tempExcluded));
COMMIT;
DROP TABLE SESSION.tempExcluded;
COMMIT;
DROP TABLE SESSION.tempIncluded;
COMMIT;
INSERT INTO SESSION.tempIncluded
(SELECT * FROM Session.includedGroups);
COMMIT;
INSERT INTO SESSION.tempIncluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname = v_legl_ent_prd_grp
AND a.RegionName = v_legl_ent_region
AND a.IsExcluded ='N'
AND a.GroupID = b.GroupID);
COMMIT;
INSERT INTO SESSION.tempExcluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname = v_legl_ent_prd_grp
AND a.RegionName = v_legl_ent_region
AND a.IsExcluded ='Y'
AND a.GroupID = b.GroupID);
COMMIT;
END IF;
END IF;
END IF;
P2:BEGIN
--| --output
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT DISTINCT GROUPID,
groupName,
description
FROM SESSION.tempIncluded;
--| --Output
DECLARE temp_cursor1 CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT DISTINCT GROUPID,
groupName,
description
FROM SESSION.tempIncluded;
--| --Out put
DECLARE temp_cursor2 CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT DISTINCT GROUPID,
groupName,
description
FROM SESSION.tempIncluded
WHERE GroupID NOT IN (SELECT GroupID
FROM SESSION.tempExcluded);
IF (v_Type = 'LegalEntity') THEN
--| --output
OPEN temp_cursor;
ELSE
IF (v_Type = 'LOB') THEN
OPEN temp_cursor1;
ELSE
IF (v_Type = 'REGION') THEN
--| --output
OPEN temp_cursor2;
END IF;
END IF;
END IF;
END P2;
END P1
@]
As far as concern ReadAssignedGroups PROC independently works fine.
Please have look on the code and let me know your valuable comments.
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
OK ... Here you cannot use the following
Code:
INSERT INTO SESSION.tempAssignedGroups(
CALL ReadAssignedGroups (v_legl_ent_name, v_legl_ent_prd_grp, v_legl_ent_region, v_Type);
);
You cannot use a CALL inside and INSERT statement ... Now your ReadAssignedGroups PROC will open one cursor and return the control back to RuleCheckForAccessRights .. now what you need to do is declare an RESULT_SET_LOCATOR to associate the open cursor in your calling PROC ...
You may do the following
Code:
BEGIN DECLARE SECTION;
RESULT_SET_LOCATOR VARYING locater1;
END DECLARE SECTION;
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
As visible from your code you are issuing lots of commits .... please declare your temporary tables as ON COMMIT PRESERVE ROWS .. or else after commit there wont be anything left in your temporary tables for the PROC to work on .... Be careful with commits ...