MySQL Forums
Forum List  »  Stored Procedures

can't create Stored Procedure
Posted by: Mitch Stone
Date: August 09, 2005 05:19PM

I am running MySQL 5.0.10a on WinXP. I have written the following script to create a few tables and stored procedures. The problem is that when I run the script from the command line:

mysql -u root -D ssdb -e "source this_script.sql"

I get the error:

ERROR 1054 (42S22) at line 43 in file: 'this_script.sql': Unknown column 'aID' in 'field list'

That particular line corresponds to the "START TRANSACTION;" statement. I've tried commenting out and removing this line, but then the error just points to the next line, which is (currently) the "CREATE TEMPORARY TABLE" statement. I've tried reordering the statements, removing the CREATE TEMP TABLE and creating that table as a permanent table, I've tried moving the DELETE statements before the START TRANS statement, adding "SET AUTOCOMMIT=0;" after the DECLARE, and various other permutations, all with no luck.

I can create the problem stored procedure within the command-line tool:

? mysql -u root -D ssdb
mysql> CREATE PROCEDURE ...

and it doesn't produce any errors or warnings, but when I call the procedure, or it's fired from a trigger, it sez the stored procedure doesn't exist. When I tried to create the SP in the MySQL Administrator, it crashed the database. I seem to recall I managed to get this to work on an earlier version of MySQL (5.0.4?), but it won't work now.

I've gone over the code with a fine tooth comb and can't for the life of me figure out what's wrong. Have I made a mistake in the code, or am I trying to exceed MySQL's limitations? Can anyone help?!?

Thanks,
Mitch

/***************************************/

DROP TABLE IF EXISTS SS_ApplicationRoles;

CREATE TABLE IF NOT EXISTS SS_ApplicationRoles
(
appID INTEGER NOT NULL,
roleID INTEGER NOT NULL,
CONSTRAINT pkSSApplicationRoles PRIMARY KEY ( appID, roleID )
);

/* =================================== */

DROP TABLE IF EXISTS SS_RoleExpansions;

CREATE TABLE IF NOT EXISTS SS_RoleExpansions
(
appID INTEGER NOT NULL,
roleID INTEGER NOT NULL,
subappID INTEGER NOT NULL,
subroleID INTEGER NOT NULL,
CONSTRAINT pkSSRoleExpansions PRIMARY KEY ( appID, roleID, subappID, subroleID )
);

/* =================================== */

DROP TABLE IF EXISTS SS_ExpansionRoles;

CREATE TABLE IF NOT EXISTS SS_ExpansionRoles (
appID INTEGER,
roleID INTEGER
);

/* =================================== */

DROP PROCEDURE IF EXISTS SS_ExpandRoles_2;

delimiter //
CREATE PROCEDURE SS_ExpandRoles_2 (
aID INTEGER,
rID INTEGER)
BEGIN
DECLARE lvl INTEGER DEFAULT 1;

START TRANSACTION; /* required to prevent lock error when called from parent procedure */

CREATE TEMPORARY TABLE stack (item1 INTEGER, item2 INTEGER, sLevel INTEGER);
DELETE FROM SS_ExpansionRoles;

INSERT INTO stack (item1, item2, sLevel) VALUES (aID, rID, lvl);

WHILE lvl > 0 DO
IF EXISTS (SELECT item1, item2 FROM stack WHERE sLevel = lvl) THEN
SELECT item1, item2
INTO aID, rID
FROM stack
WHERE sLevel = lvl
LIMIT 1;

INSERT INTO SS_ExpansionRoles
VALUES (aID, rID);

DELETE FROM stack
WHERE sLevel = lvl
AND item1 = aID
AND item2 = rID;

INSERT INTO stack
SELECT subappID, subroleID, lvl + 1
FROM SS_RoleDefinitions
WHERE appID = aID
AND roleID = rID;

IF ROW_COUNT() > 0 THEN
SELECT lvl = lvl + 1;
END IF;

ELSE
SET lvl = lvl - 1;
END IF;
END WHILE;

COMMIT; /* required to prevent lock error when called from parent procedure */
END//
delimiter ;

/* =================================== */

DROP PROCEDURE IF EXISTS SS_ExpandRoles;

delimiter //
CREATE PROCEDURE PDT.SS_ExpandRoles ()
BEGIN
DECLARE done1, done2 INTEGER DEFAULT 1;
DECLARE aID, rID, saID, srID INTEGER DEFAULT 0;
DECLARE cRoles CURSOR FOR SELECT DISTINCT appID, roleID FROM SS_ApplicationRoles;
DECLARE csubRoles CURSOR FOR SELECT appID, roleID FROM SS_ExpansionRoles;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1, done2 = 1;

-- rebuild locationGroupExpansions from scratch
DELETE FROM SS_RoleExpansions;
INSERT INTO SS_RoleExpansions
SELECT appID, roleID, appID, roleID FROM SS_ApplicationRoles;

OPEN cRoles;
REPEAT
FETCH cRoles INTO aID, rID;
IF NOT done1 THEN

CALL SS_ExpandRoles_2(aID, rID);

OPEN csubRoles;
REPEAT
FETCH csubRoles INTO saID, srID;
IF NOT done2 THEN
IF NOT EXISTS (SELECT appID, roleID, subappID, subroleID FROM SS_RoleExpansions
WHERE appID = aID AND roleID = rID
AND subappID = saID AND subroleID = srID) THEN
INSERT INTO SS_RoleExpansions VALUES (aID, rID, saID, srID);
END IF;
END IF;
UNTIL done2 END REPEAT;
CLOSE csubRoles;

END IF;
UNTIL done1 END REPEAT;
CLOSE cRoles;
END//
delimiter ;

/* =================================== */

DROP TRIGGER SS_ApplicationRoles.SS_AppRoles_ti;

CREATE TRIGGER SS_AppRoles_ti
AFTER INSERT
ON SS_ApplicationRoles
FOR EACH ROW
CALL SS_ExpandRoles();

DROP TRIGGER SS_ApplicationRoles.SS_AppRoles_tu;

CREATE TRIGGER SS_AppRoles_tu
AFTER UPDATE
ON SS_ApplicationRoles
FOR EACH ROW
CALL SS_ExpandRoles();

DROP TRIGGER SS_ApplicationRoles.SS_AppRoles_td;

CREATE TRIGGER SS_AppRoles_td
AFTER DELETE
ON SS_ApplicationRoles
FOR EACH ROW
CALL SS_ExpandRoles();

Options: ReplyQuote


Subject
Views
Written By
Posted
can't create Stored Procedure
3810
August 09, 2005 05:19PM
1907
August 29, 2005 08:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.