Search code examples
mysqlstored-proceduresmariadbcursoradvantage-database-server

Conversion Example of Stored Procedure Cursor within a Cursor for MySQL and MariaDB from Advantage

I am migrating from Advantage Database Server 11 to MySQL 8.0.33 and also testing MariaDB 11.0.2. I haven't landed on which one to use yet but I know I will need to use cursor variables and cursors within cursors a lot. I believe MariaDB has that feature. I successfully converted the SQL for MySQL/MariaDB so the procedure will finally create and run, but it does not complete or return results.

Here is the original Advantage SQL that runs correctly and returns results:

CREATE PROCEDURE AllItemIDBom
   ( 
      RootItemID Integer OUTPUT,
      SortChar CHAR ( 44 ) OUTPUT,
      LevelID Integer OUTPUT,
      ParentItemID Integer OUTPUT,
      Seq Integer OUTPUT,
      Line Integer OUTPUT,
      ItemID Integer OUTPUT,
      LevelQty DOUBLE ( 2 ) OUTPUT,
      LevelWastePC DOUBLE ( 2 ) OUTPUT,
      RollDownQty DOUBLE ( 2 ) OUTPUT,
      OptionStdCost LOGICAL OUTPUT
   ) 
BEGIN 
DECLARE SOItems CURSOR;
DECLARE cursor1 CURSOR;
DECLARE cursor2 CURSOR;
DECLARE cursor3 CURSOR;
// return variables  
DECLARE cycleID Integer;
DECLARE @tempSort Char(40);
DECLARE setNodeID1 Integer;

// select the group of Items
OPEN SOItems AS select ItemID from Item where ItemMfgTypeID >1;

WHILE FETCH SOItems DO
setNodeID1 =1000;
// set top level record as a place holder
  insert into __OUTPUT (RootItemID,SortChar, LevelID, ParentItemID, Seq, Line, ItemID, LevelQty, LevelWastePC, RollDownQty,OptionStdCost) 
    values (SOItems.ItemID,'0',0,SOItems.ItemID,0,0,SOItems.ItemID,1,0,1,TRUE);
// first pass go to the ItemMtl
  OPEN cursor1 AS SELECT ItemMtl.* FROM ItemMtl 
        where ItemMtl.ItemID=SOItems.ItemID
        and (EffectDate IS NULL OR EffectDate <= curdate())  
            and (ExpireDate IS NULL OR curdate() < ExpireDate)
        ORDER BY LINE ;

  WHILE FETCH cursor1 DO
    setNodeID1 =setNodeID1 +1;
    insert into __OUTPUT (RootItemID, SortChar, LevelID, ParentItemID, Seq, Line, ItemID, LevelQty, LevelWastePC, RollDownQty,OptionStdCost) 
      values (SOItems.ItemID, cast (setNodeID1 as SQL_CHAR) ,1, cursor1.ItemID,   
        cursor1.Seq, cursor1.Line, cursor1.MaterialItemID,  cursor1.UtopianQty, 
        ifNull(cursor1.WastePC,0),Round(Cursor1.UtopianQty /(1-IFNULL(Cursor1.WastePC,0)/100),4),cursor1.OptionStdCost);   
      
  END WHILE;
  CLOSE cursor1;

  cycleID = 1;
  // loop through
  WHILE cycleID < 10 DO
    OPEN cursor2 AS SELECT * FROM __OUTPUT  where  LevelID=cycleID and RootItemID =SOItems.ItemID;
    WHILE FETCH cursor2 DO    
      OPEN cursor3 AS SELECT ItemMtl.*
        FROM ItemMtl 
          where ItemMtl.ItemID=cursor2.ItemID 
              and (EffectDate IS NULL OR EffectDate <= curdate())  
                and (ExpireDate IS NULL OR curdate() < ExpireDate)
      ORDER BY LINE;
        setNodeID1 = 2000;          
      WHILE FETCH cursor3 DO
          setNodeID1 =setNodeID1 +1;      
            @tempSort =  RTRIM(cursor2.SortChar) + RTRIM(cast(setNodeID1 as SQL_CHAR));      
        insert into __OUTPUT  (RootItemID, SortChar, LevelID, ParentItemID, Seq, Line, ItemID, LevelQty, LevelWastePC, RollDownQty,OptionStdCost)   
        values (SOItems.ItemID, @tempSort, cycleID+1, cursor3.ItemID, 
             cursor3.Seq, cursor3.Line, cursor3.MaterialItemID, ifnull(cursor3.UtopianQty,0), ifnull(cursor3.WastePC,0),
            Round(Cursor3.UtopianQty /(1-IFNULL(Cursor3.WastePC,0)/100) * cursor2.RollDownQty,4), cursor3.OptionStdCost );
      END WHILE;  
      CLOSE cursor3;
      
    END WHILE;    
    CLOSE cursor2;
    cycleID = cycleID +1;
  END WHILE;
END WHILE;
CLOSE SOItems;

And here is the converted procedure I am testing in MySQL 8.0.33 and MariaDB 11.0.2. It doesn't complete or return results so something isn't right. I'm used to declaring the cursor, setting it later, then accessing and using its results in another cursor, so I had to move things around a bit to convert it. Advantage could also return entire result sets so had to use temp table in this version.

DELIMITER //

CREATE PROCEDURE AllItemIDBom()
BEGIN                  
-- return variables  
DECLARE cycleID Integer;
DECLARE tempSort Char(40);
DECLARE setNodeID1 Integer;
-- cursor variables
DECLARE SOItems_ItemID Integer; 
DECLARE cursor1_ItemID Integer; 
DECLARE cursor1_Seq Integer; 
DECLARE cursor1_Line Integer; 
DECLARE cursor1_MaterialItemID Integer; 
DECLARE cursor1_UtopianQty DOUBLE; 
DECLARE cursor1_WastePC VARCHAR(200); 
DECLARE cursor1_OptionStdCost BOOLEAN; 
DECLARE cursor2_ItemID Integer; 
DECLARE cursor2_SortChar Integer; 
DECLARE cursor2_RollDownQty DOUBLE; 
DECLARE cursor3_ItemID Integer; 
DECLARE cursor3_Seq Integer; 
DECLARE cursor3_Line Integer; 
DECLARE cursor3_MaterialItemID Integer; 
DECLARE cursor3_UtopianQty DOUBLE; 
DECLARE cursor3_WastePC DOUBLE; 
DECLARE cursor3_OptionStdCost BOOLEAN;      
-- setup cursor and handler
DECLARE SOItemsDone Integer DEFAULT 0;
DECLARE SOItems CURSOR FOR SELECT ItemID FROM Item WHERE ItemMfgTypeID >1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET SOItemsDone = 1;  
    
-- prepare processing table
DROP TABLE IF EXISTS temp_AllItemIDBom;
CREATE TEMPORARY TABLE temp_AllItemIDBom
   (
      RootItemID Integer,
      SortChar CHAR (44),
      LevelID Integer,
      ParentItemID Integer,
      Seq Integer,
      Line Integer,
      ItemID Integer,
      LevelQty DOUBLE,
      LevelWastePC DOUBLE,
      RollDownQty DOUBLE,
      OptionStdCost CHAR(1)
   );

-- select the group of Items
OPEN SOItems;
loop1: REPEAT   
      FETCH SOItems INTO SOItems_ItemID;
      SET setNodeID1 = 1000;
      -- set top level record as a place holder
      INSERT INTO temp_AllItemIDBom (RootItemID,SortChar,LevelID,ParentItemID,Seq,Line,ItemID,LevelQty,LevelWastePC,RollDownQty,OptionStdCost) 
            VALUES (SOItems_ItemID,'0',0,SOItems_ItemID,0,0,SOItems_ItemID,1,0,1,TRUE);

      -- first pass go to the ItemMtl
      BEGIN
            -- setup cursor and handler
            DECLARE Cur1Done Integer DEFAULT 0;
            DECLARE cursor1 CURSOR FOR SELECT ItemID,Seq,Line,MaterialItemID,UtopianQty,WastePC,OptionStdCost FROM ItemMtl WHERE ItemMtl.ItemID=SOItems_ItemID
                                                      AND (EffectDate IS NULL OR EffectDate <= curdate())  
                                                       AND (ExpireDate IS NULL OR curdate() < ExpireDate)
                                                      ORDER BY LINE;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur1Done = 1;  
            
            OPEN cursor1;
            loop2: REPEAT
                  FETCH cursor1 INTO cursor1_ItemID,cursor1_Seq,cursor1_Line,cursor1_MaterialItemID,cursor1_UtopianQty,cursor1_WastePC,cursor1_OptionStdCost;    
                  SET setNodeID1 =setNodeID1 +1;
                  INSERT INTO temp_AllItemIDBom (RootItemID,SortChar,LevelID,ParentItemID,Seq,Line,ItemID,LevelQty,LevelWastePC,RollDownQty,OptionStdCost) 
                        VALUES (SOItems_ItemID, cast(setNodeID1 as CHAR) ,1, cursor1_ItemID,   
                              cursor1_Seq, cursor1_Line, cursor1_MaterialItemID, cursor1_UtopianQty, 
                              ifNull(cursor1_WastePC,0),Round(Cursor1_UtopianQty /(1-IFNULL(Cursor1_WastePC,0)/100),4),cursor1_OptionStdCost); 
            UNTIL Cur1Done END REPEAT loop2;
            CLOSE cursor1;
      END;

      SET cycleID = 1;
      -- loop through children
      loop3: WHILE cycleID < 10 DO
            BEGIN
                  -- setup cursor and handler
                  DECLARE Cur2Done Integer DEFAULT 0;
                  DECLARE cursor2 CURSOR FOR SELECT ItemID,SortChar,RollDownQty FROM temp_AllItemIDBom WHERE LevelID=cycleID AND RootItemID=SOItems_ItemID;
                  DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur2Done = 1; 
                  OPEN cursor2;
                  loop4: REPEAT  
                        BEGIN
                              DECLARE Cur3Done Integer DEFAULT 0;
                              DECLARE cursor3 CURSOR FOR SELECT ItemID,Seq,Line,MaterialItemID,UtopianQty,WastePC,OptionStdCost FROM ItemMtl WHERE ItemMtl.ItemID=cursor2_ItemID 
                                                                                                             AND (EffectDate IS NULL OR EffectDate <= curdate())  
                                                                                                             AND (ExpireDate IS NULL OR curdate() < ExpireDate)
                                                                                                            ORDER BY LINE;      
                              DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur3Done = 1; 
                              FETCH cursor2 INTO cursor2_ItemID,cursor2_SortChar,cursor2_RollDownQty; 
                              OPEN cursor3;
                              SET setNodeID1 = 2000;  
                              loop5: REPEAT
                                    FETCH cursor3 INTO cursor3_ItemID,cursor3_Seq,cursor3_Line,cursor3_MaterialItemID,cursor3_UtopianQty,cursor3_WastePC,cursor3_OptionStdCost;
                                    SET setNodeID1 =setNodeID1 +1;      
                                    SET tempSort = RTRIM(cursor2_SortChar) + RTRIM(cast(setNodeID1 as CHAR));      
                                    INSERT INTO temp_AllItemIDBom  (RootItemID, SortChar, LevelID, ParentItemID, Seq, Line, ItemID, LevelQty, LevelWastePC, RollDownQty,OptionStdCost)   
                                    VALUES (SOItems_ItemID, tempSort, cycleID+1, cursor3_ItemID, 
                                                 cursor3_Seq, cursor3_Line, cursor3_MaterialItemID, ifnull(cursor3_UtopianQty,0), ifnull(cursor3_WastePC,0),
                                                Round(Cursor3_UtopianQty /(1-IFNULL(Cursor3_WastePC,0)/100) * cursor2_RollDownQty,4), cursor3_OptionStdCost );
                              UNTIL Cur3Done END REPEAT loop5;
                              CLOSE cursor3;   
                        END;  
                  UNTIL Cur2Done END REPEAT loop4;    
                  CLOSE cursor2;
                  SET cycleID = cycleID +1;  
            END;
      END WHILE loop3;
UNTIL SOItemsDone END REPEAT loop1;
CLOSE SOItems;

SELECT * FROM temp_AllItemIDBom;

END
//

DELIMITER ;

Example Usage: ItemNo 1016-FR is ItemID 10004, a 16" BMX Bike Frame with a bill of material of four components.

enter image description here

The procedure gets the top level and all it's children, and their children, through all indented levels, outputting the below results by level (though in this particular example there is only a single level).

enter image description here

Here is a script to setup a small database with sample data for testing the procedure.

CREATE DATABASE TestProc;
GRANT ALL PRIVILEGES ON TestProc.* TO 'root'@'localhost' WITH GRANT OPTION;
USE TestProc;
CREATE TABLE Item ( 
  ItemID Integer,
  ItemMfgTypeID Integer);
INSERT INTO Item (ItemID,ItemMfgTypeID)
VALUES (10004,2),
       (10001,1),
       (10008,1),
       (10009,1),
       (10010,1);
CREATE TABLE ItemMtl (
  ItemID Integer,
  Seq Integer,
  Line Integer,
  MaterialItemID Integer,
  UtopianQty Double,
  WastePC Double,
  OptionStdCost Boolean,
  EffectDate Date,
  ExpireDate Date);
INSERT INTO ItemMtl (ItemID,Seq,Line,MaterialItemID,UtopianQty,WastePC,OptionStdCost,EffectDate,ExpireDate)
VALUES (10004,1,10,10001,6,0,True,NULL,NULL),
       (10004,2,20,10008,5.5,0,True,NULL,NULL),
       (10004,3,30,10009,0.35,0,True,NULL,NULL),
       (10004,4,40,10010,0.25,0,True,NULL,NULL);

Thank you in advance!

Solution

  • I figured it out and thought I would share the answer. This one produces the desired results/output. Though I will probably end up rewriting the procedure as a recursive CTE based something akin to Bill's recommendation, this one keeps it as a stored procedure and truly rewrites it, converted from Advantage to MySQL 8.0.33 / MariaDB 11.0.2. Part of our desire for it as a procedure is to avoid any embedding of SQL in our app, keeping it all in the database.

    DELIMITER //

CREATE PROCEDURE AllItemIDBom()
BEGIN                  
-- return variables  
DECLARE cycleID Integer;
DECLARE tempSort Char(40);
DECLARE setNodeID1 Integer;
-- cursor variables
DECLARE SOItems_ItemID Integer; 
DECLARE cursor1_ItemID Integer; 
DECLARE cursor1_Seq Integer; 
DECLARE cursor1_Line Integer; 
DECLARE cursor1_MaterialItemID Integer; 
DECLARE cursor1_UtopianQty DOUBLE; 
DECLARE cursor1_WastePC VARCHAR(200); 
DECLARE cursor1_OptionStdCost BOOLEAN; 
DECLARE cursor2_ItemID Integer; 
DECLARE cursor2_SortChar VARCHAR(16383); 
DECLARE cursor2_RollDownQty DOUBLE; 
DECLARE cursor3_ItemID Integer; 
DECLARE cursor3_Seq Integer; 
DECLARE cursor3_Line Integer; 
DECLARE cursor3_MaterialItemID Integer; 
DECLARE cursor3_UtopianQty DOUBLE; 
DECLARE cursor3_WastePC DOUBLE; 
DECLARE cursor3_OptionStdCost BOOLEAN;      
-- setup handler toggles
DECLARE SOItemsDone Integer DEFAULT 0;
DECLARE Cur1Done Integer;
DECLARE Cur2Done Integer;
DECLARE Cur3Done Integer;
-- setup 1st cursor and handler
DECLARE SOItems CURSOR FOR SELECT ItemID FROM Item WHERE ItemMfgTypeID IN (2,3,4) AND suspended = false AND MRPInclude= true;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET SOItemsDone = 1;  
    
-- prepare processing table
DROP TABLE IF EXISTS temp_AllItemIDBom;
CREATE TEMPORARY TABLE temp_AllItemIDBom
   (
      RootItemID Integer,
      SortChar CHAR (44),
      LevelID Integer,
      ParentItemID Integer,
      Seq Integer,
      Line Integer,
      ItemID Integer,
      LevelQty DOUBLE,
      LevelWastePC DOUBLE,
      RollDownQty DOUBLE,
      OptionStdCost CHAR(1)
   );

-- select the group of Items
SET SOItemsDone = 0;
OPEN SOItems;
BLOCK1: REPEAT   
  SET Cur1Done = 0;
  FETCH SOItems INTO SOItems_ItemID;      
  IF NOT SOItemsDone THEN 
    SET setNodeID1 = 1000;
    -- set top level record as a place holder
    INSERT INTO temp_AllItemIDBom (RootItemID,SortChar,LevelID,ParentItemID,Seq,Line,ItemID,LevelQty,LevelWastePC,RollDownQty,OptionStdCost) 
          VALUES (SOItems_ItemID,'0',0,SOItems_ItemID,0,0,SOItems_ItemID,1,0,1,TRUE);

    -- first pass go to the ItemMtl
    BEGIN
      -- setup 2nd cursor and handler
      DECLARE cursor1 CURSOR FOR SELECT ItemID,Seq,Line,MaterialItemID,UtopianQty,WastePC,OptionStdCost FROM ItemMtl WHERE ItemMtl.ItemID=SOItems_ItemID
                                 AND (EffectDate IS NULL OR EffectDate <= curdate())  
                                 AND (ExpireDate IS NULL OR curdate() < ExpireDate)
                                 ORDER BY LINE;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur1Done = 1;  
      
      OPEN cursor1;
      BLOCK2: REPEAT
        FETCH cursor1 INTO cursor1_ItemID,cursor1_Seq,cursor1_Line,cursor1_MaterialItemID,cursor1_UtopianQty,cursor1_WastePC,cursor1_OptionStdCost;    
        IF NOT Cur1Done THEN 
          SET setNodeID1 = setNodeID1 + 1;
          INSERT INTO temp_AllItemIDBom (RootItemID,SortChar,LevelID,ParentItemID,Seq,Line,ItemID,LevelQty,LevelWastePC,RollDownQty,OptionStdCost) 
            VALUES (SOItems_ItemID, cast(setNodeID1 as CHAR) ,1, cursor1_ItemID,cursor1_Seq, cursor1_Line, cursor1_MaterialItemID, cursor1_UtopianQty, 
                  ifNull(cursor1_WastePC,0),Round(Cursor1_UtopianQty /(1-IFNULL(Cursor1_WastePC,0)/100),4),cursor1_OptionStdCost); 
        END IF;
      UNTIL Cur1Done END REPEAT BLOCK2;
      CLOSE cursor1;
    END;

    SET cycleID = 1;
    -- loop through children
    BLOCK3: WHILE cycleID < 10 DO
      SET Cur2Done = 0;
      BEGIN
        -- setup 3rd cursor and handler
        DECLARE cursor2 CURSOR FOR SELECT ItemID,SortChar,RollDownQty FROM temp_AllItemIDBom WHERE LevelID=cycleID AND RootItemID=SOItems_ItemID;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur2Done = 1; 
        OPEN cursor2;
        BLOCK4: REPEAT 
          SET Cur3Done = 0;
          FETCH cursor2 INTO cursor2_ItemID,cursor2_SortChar,cursor2_RollDownQty; 
          IF NOT Cur2Done THEN 
            BEGIN
              -- setup cursor and handler
              DECLARE cursor3 CURSOR FOR SELECT ItemID,Seq,Line,MaterialItemID,UtopianQty,WastePC,OptionStdCost FROM ItemMtl WHERE ItemMtl.ItemID=cursor2_ItemID 
                                         AND (EffectDate IS NULL OR EffectDate <= curdate())  
                                         AND (ExpireDate IS NULL OR curdate() < ExpireDate)
                                         ORDER BY LINE;      
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur3Done = 1; 
              OPEN cursor3;
              SET setNodeID1 = 2000;  
              BLOCK5: REPEAT
                FETCH cursor3 INTO cursor3_ItemID,cursor3_Seq,cursor3_Line,cursor3_MaterialItemID,cursor3_UtopianQty,cursor3_WastePC,cursor3_OptionStdCost;
                IF NOT Cur3Done THEN
                  SET setNodeID1 = setNodeID1 + 1;      
                  SET tempSort = CONCAT(RTRIM(cursor2_SortChar),RTRIM(cast(setNodeID1 as CHAR)));
                  INSERT INTO temp_AllItemIDBom (RootItemID,SortChar,LevelID,ParentItemID,Seq,Line,ItemID,LevelQty,LevelWastePC,RollDownQty,OptionStdCost)   
                  VALUES (SOItems_ItemID,tempSort,cycleID+1,cursor3_ItemID,cursor3_Seq,cursor3_Line,cursor3_MaterialItemID,ifnull(cursor3_UtopianQty,0),ifnull(cursor3_WastePC,0),
                    Round(Cursor3_UtopianQty /(1-IFNULL(Cursor3_WastePC,0)/100) * cursor2_RollDownQty,4),cursor3_OptionStdCost);
                END IF;
              UNTIL Cur3Done END REPEAT BLOCK5;
              CLOSE cursor3;   
            END;  
          END IF;
        UNTIL Cur2Done END REPEAT BLOCK4;    
        CLOSE cursor2;
        SET cycleID = cycleID +1;  
      END;
    END WHILE BLOCK3;
  END IF;
UNTIL SOItemsDone END REPEAT BLOCK1;
CLOSE SOItems;

SELECT * FROM temp_AllItemIDBom ORDER BY RootItemID,SortChar;
END
//
DELIMITER ;

    As it turns out, to prevent the infinite looping issue, I (a) added "IF NOT done THEN" type of statements to each block and (b) moved all fetch statements to just before those IF statements.