Bug #2503 stored procedure creation errors

• Submitted: 24 Jan 2004 15:20
• Modified: 24 Jan 2004 20:30
• Reporter: Ted Toth
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.0a-alpha
• OS: Windows (Window XP Professional)
• CPU Architecture: Any

[24 Jan 2004 15:20] Ted Toth

Description:
I'm trying to port the Rainbow Portal DB (www.rainbowportal.net) from MSSQL to MySQL and for the most part it has been working (I created all of the table successfully and a number of stored procedures) however I've run into a few problem with stored procedures that I cannot resolve so I think they might be actaul defects. I've included the table create commands along with the stored procedures and the errors that occur. I ported a number of other tables and stored procedures which were similar in nature so I don't understand why these are failing.

delimiter |

CREATE TABLE `rb_Modules` (
  `ModuleID` int(11) NOT NULL auto_increment,
  `TabID` int(11) NOT NULL default '0',
  `ModuleDefID` int(11) NOT NULL default '0',
  `ModuleOrder` int(11) NOT NULL default '0',
  `PaneName` varchar(50) NOT NULL default '',
  `ModuleTitle` text,
  `AuthorizedEditRoles` text,
  `AuthorizedViewRoles` text,
  `AuthorizedAddRoles` text,
  `AuthorizedDeleteRoles` text,
  `AuthorizedPropertiesRoles` text,
  `CacheTime` int(11) NOT NULL default '0',
  `ShowMobile` tinyint(4) default NULL,
  `AuthorizedPublishingRoles` text,
  `NewVersion` tinyint(4) default NULL,
  `SupportWorkflow` tinyint(4) default NULL,
  `AuthorizedApproveRoles` text,
  `WorkflowState` tinyint(4) default NULL,
  `LastModified` datetime default NULL,
  `LastEditor` text,
  `StagingLastModified` datetime default NULL,
  `StagingLastEditor` text,
  `SupportCollapsable` tinyint(4) default NULL,
  `ShowEveryWhere` tinyint(4) default NULL,
  PRIMARY KEY  (`ModuleID`),
  KEY `_WA_Sys_ModuleDefID_023D5A04` (`ModuleDefID`),
  KEY `_WA_Sys_TabID_023D5A04` (`TabID`),
  KEY `_WA_Sys_ShowEveryWhere_023D5A04` (`ShowEveryWhere`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

CREATE PROCEDURE rb_GetLastModified
	(
		pModuleId int,
		pWorkflowVersion int,
		OUT pLastModifiedBy text,
		OUT pLastModifiedDate datetime
	)
BEGIN
	IF (pWorkflowVersion = 1) THEN
		SELECT LastEditor INTO pLastModifiedBy,	
			LastModified INTO pLastModifiedDate       
		FROM
		       rb_Modules
		WHERE 
			ModuleID = pModuleId;
	ELSE
		select StagingLastModified INTO pLastModifiedDate,
			StagingLastEditor INTO pLastModifiedBy
		from rb_Modules
		where ModuleID = pModuleId;
	END IF;

END |

ERROR 1311 (42000): Undeclared variable: LastModified

CREATE TABLE `rb_tabs` (
  `TabID` int(11) NOT NULL auto_increment,
  `ParentTabId` int(11) default NULL,
  `TabOrder` int(11) NOT NULL default '0',
  `PortalID` int(11) NOT NULL default '0',
  `TabName` varchar(50) NOT NULL default '',
  `MobileTabName` varchar(50) NOT NULL default '',
  `AuthorizedRoles` text,
  `ShowMobile` tinyint(4) NOT NULL default '0',
  `TabLayout` int(11) default NULL,
  PRIMARY KEY  (`TabID`),
  KEY `_WA_Sys_ParentTabId_014935CB` (`ParentTabId`),
  KEY `_WA_Sys_PortalID_014935CB` (`PortalID`),
  KEY `_WA_Sys_ShowMobile_014935CB` (`ShowMobile`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

CREATE PROCEDURE rb_GetAuthViewRoles
(
    pPortalID    int,
    pModuleID    int,
    OUT pAccessRoles varchar (256),
    OUT pViewRoles   varchar (256)
)
BEGIN

SELECT  
    rb_Tabs.AuthorizedRoles INTO pAccessRoles,
    rb_Modules.AuthorizedViewRoles INTO pViewRoles
    
FROM    
    rb_Modules
  INNER JOIN
    rb_Tabs ON rb_Modules.TabID = rb_Tabs.TabID
    
WHERE   
    rb_Modules.ModuleID = pModuleID
  AND
    rb_Tabs.PortalID = pPortalID;
END |

ERROR 1311 (42000): Undeclared variable: rb_Modules

How to repeat:
Run mysql cut the command from specified in the description window and execute them to see the errors.

[24 Jan 2004 20:30] MySQL Verification Team

According with the Manual's instructions I noticed syntax errors,
(pay attention in the SELECT .. INTO statement) Please read the
Manual for further instructions at:

http://www.mysql.com/doc/en/Stored_Procedures.html

I only pasted the part of the stored procedures creation and
please compare with the reported ones:

mysql> CREATE PROCEDURE rb_GetLastModified
    ->         (
    ->                 pModuleId int,
    ->                 pWorkflowVersion int,
    ->                 OUT pLastModifiedBy text,
    ->                 OUT pLastModifiedDate datetime
    ->         )
    -> BEGIN
    ->         IF (pWorkflowVersion = 1) THEN
    ->                 SELECT LastEditor, LastModified INTO pLastModifiedBy,
    ->                          pLastModifiedDate
    ->                 FROM
    ->                        rb_Modules
    ->                 WHERE
    ->                         ModuleID = pModuleId;
    ->         ELSE
    ->                 select StagingLastModified, StagingLastEditor INTO pLastModifiedDate,
    ->                           pLastModifiedBy
    ->                 from rb_Modules
    ->                 where ModuleID = pModuleId;
    ->         END IF;
    ->
    ->
    -> END |
Query OK, 0 rows affected (0.05 sec)

............................

mysql> CREATE PROCEDURE rb_GetAuthViewRoles
    -> (
    ->     pPortalID    int,
    ->     pModuleID    int,
    ->     OUT pAccessRoles varchar (256),
    ->     OUT pViewRoles   varchar (256)
    -> )
    -> BEGIN
    ->
    ->
    -> SELECT
    ->     rb_Tabs.AuthorizedRoles,rb_Modules.AuthorizedViewRoles INTO
    ->     pAccessRoles, pViewRoles
    ->
    -> FROM
    ->     rb_Modules
    ->   INNER JOIN
    ->     rb_Tabs ON rb_Modules.TabID = rb_Tabs.TabID
    ->
    -> WHERE
    ->     rb_Modules.ModuleID = pModuleID
    ->   AND
    ->     rb_Tabs.PortalID = pPortalID;
    -> END |
Query OK, 0 rows affected (0.02 sec)