Error in Debug/Step Mode Leaves dbForge in Unknown State

[KiwiJem]

Hi
I am trying to create and debug TRIGGERS on various databases and tables. The tables are live in a production database, as we dont have a dirtbox.

I created a trigger procedure which has been working for months. (Version 1)

I needed to change the name of a table and columns due to a change in the database structure.

I opened the working trigger, and changed the name of various columns and one variable, and saved it just fine (Version 2). I took it for a test.

I single stepped into the trigger in the recommended method by using a procedure to pass in the required parameters, and stepped into the trigger.

If the trigger debug process strikes an error (eg I had "typoed" a reference to Varants,VarID instead of Variants.VarID) it flags an error. On acknowledging the error, the debug/step tools disappear, and you APPEAR to be back in normal edit mode for the trigger code, BUT YOU ARE NOT!!

Attempts to edit the code here can be disasterous. I corrected one reference (from the comma to the dot as described already) and tried to save the file (version 3) which it SHOULD have been happy with.

It refused to save due to "duplicate variable declaration"s. WHICH I HAD NOT TOUCHED since it last saved correctly.

It turned out that the editor had kept the first 16 lines up to the end of the first Comment line, and then substituted the OLD ORIGINAL CODE from BEFORE the first edits - i.e version 1.

As an experienced programmer and teacher of programming , it looks to me like the pointers to the linked lists used for editing were not being re-initialised properly - they were pointing to really stale text which had probably been dealloced, but was still in memory.

This is where a lot of even "experienced" programmers come unstuck with linked lists etc - it is easy to set a pointer on alloc() but dealloc does not AUTOMATICALLY clear pointers. Please get your programmers to check this carefully.

I had the same problem with the previous release, but assumed you would fix bugs between releases.

Some of the time, any attempts to edit result in nothing happening. You cant move the cursor, you cant edit anything. This is far preferable, because at least you can close the tables, close dbForge Studio, and reopen everything and nothing has been damaged.

Apart from closing everything and re-starting it all again, I could find no "work-around" for this bug.

Is there one please?

Here follow Versions 1 to 3 of the code.

Why do I have them all? Because I pasted them into an external text editor, because I am learning not to trust dbForge Studio. This happens to me FREQUENTLY, but dbForge Studio is still the best affordable tool for debugging trigger procedures, so I persist.

Verion 1 (worked fine for months. Tables include "Builds" and variable bldid)

Code: Select all

BEGIN
  DECLARE stnn          TEXT;
  DECLARE brdid         INT;
  DECLARE panid         INT;
  DECLARE woid          INT;
  DECLARE bldid         INT;
  DECLARE tsid          INT;
  DECLARE stationid     INT;
  DECLARE vOperatorID   INT;
  DECLARE breason       TEXT;
  DECLARE bstat         INT;
  DECLARE stamp         TEXT;

  DECLARE brdsn         TEXT;
  DECLARE vOperatorName TEXT;
  #DECLARE breason TEXT;
  #DECLARE stnn TEXT;
  DECLARE brdcnt        INT;
  DECLARE brdfail       INT;
  DECLARE brdsuffix     TEXT;

  SET brdsn = IFNULL(NEW.SN, 0);
  SET stamp = IFNULL(NEW.IDate, 0);
  SET stnn = IFNULL(NEW.SystemID, 0);
  SET brdcnt = IFNULL(NEW.BrdCount, 0);
  SET vOperatorName = IFNULL(NEW.Inspector, 0);
  SET breason = IFNULL(NEW.DefectCode, 0);
  SET brdfail = IFNULL(NEW.BrdFail, 0);
/*
  SET stnn = "INLINE-AOI-1";
  SET stamp = "2009-01-01 13:00:00";
  SET brdsn = "336510020069-22";
  SET brdcnt = "1";
  SET vOperatorName = "Jeremy";
  SET breason = "1";
  SET brdfail = "0";
*/

  # board status

  INSERT INTO pippa.Logs (LogType, LogMsg) VALUES (
    "Info", IFNULL(CONCAT("Debug 1 - SerNo=",  brdsn, "  Station=", stnn,
    "  BrdCount=", brdcnt, "  BrdFail=", brdfail, "  Reason=", breason, 
    "  Inspector=", vOperatorName), 1));

  # Only create ResultSets record in pippa if this is the test summary line
  IF NEW.BrdCount = 1 THEN

    IF NEW.BrdFail = 1 THEN
      SELECT
        DefectCode.Description
      INTO
        breason
      FROM
        DefectCode
      WHERE
        (DefectCode.Code = NEW.DefectCode);
      #Fail 
      SET bstat = 3;
    ELSE
      SET breason = "Pass";
      SET bstat = 1;
    #pass  
    END IF;

    SELECT
      OperatorID
    INTO
      vOperatorID
    FROM
      pippa.Operators
    WHERE
      Operators.OperatorName = vOperatorName;

    IF vOperatorID IS NULL THEN
      SET vOperatorID := 1;
    END IF;
    # OperatorID 1 is "Unknown"

    SELECT
      StnID
    INTO
      stationid
    FROM
      pippa.Stations
    WHERE
      Stations.StnName = stnn;

    IF stationid IS NULL THEN
      SET stationid := 1;
    END IF;

    INSERT INTO pippa.Logs (LogType, LogMsg) 
      VALUES ("Info", IFNULL(CONCAT(stnn, " Info 2 - Panel SerNo ", brdsn, ": StnID=", stationid), 2));

    # where is the dash, if any
    SET brdsuffix := RIGHT(brdsn, LOCATE('-', REVERSE(brdsn)) - 1);

    IF LENGTH(brdsuffix) = 0 THEN
      # no suffix
      SET brdsn = RIGHT(CONCAT(brdsn, '001'), 16);
    ELSE
      # strip off -xx style suffix
      SET brdsn := LEFT(brdsn, LOCATE('-', brdsn) - 1);
      # add 000 style board suffix
      SET brdsn = CONCAT(brdsn, RIGHT(CONCAT('00', brdsuffix), 3));
    END IF;
    # add a leading 0 if is old barcode length (new are 16)
    IF LENGTH(brdsn) = 15 THEN
      SET brdsn = CONCAT('0', brdsn);
    END IF;

    # Find BoardID and PanelID from Board Serial Number
    SELECT
      BoardID
    INTO
      brdid
    FROM
      pippa.Boards
    WHERE
      BoardSerialNo = brdsn;

    IF brdid IS NULL THEN
      # not found in boards table
      INSERT INTO pippa.Logs (LogType, LogMsg)
        VALUES ("Error", IFNULL(CONCAT(stnn, " Error 3 - Panel SerNo ", brdsn, " is not in the Boards table"), 3));
    ELSE
      INSERT INTO pippa.Logs (LogType, LogMsg) 
        VALUES ("Info", IFNULL(CONCAT(stnn, " Info 4 - Panel SerNo ", brdsn, ": BoardID=", brdid), 4));
      # Find PanelId    
      SELECT
        PanelID
      INTO
        panid
      FROM
        pippa.Boards
      WHERE
        BoardID = brdid;

      IF panid IS NULL THEN
        INSERT INTO pippa.Logs (LogType, LogMsg) 
          VALUES ("Error", IFNULL(CONCAT(stnn, " Error 5 - Panel SerNo ", brdsn, " does not belong to a panel"), 5));
      ELSE
        # Find WorkOrder ID which includes SN as a board serial number
        INSERT INTO pippa.Logs (LogType, LogMsg) 
          VALUES ("Info", IFNULL(CONCAT(stnn, " Info 6 - Panel SerNo ", brdsn, ": PanelID=", panid), 6));
        SELECT
          pippa.WOrders.WOrderID
        INTO
          woid
        FROM
          pippa.Panels INNER JOIN pippa.WOrders ON pippa.Panels.WOrderID = pippa.WOrders.WOrderID
        WHERE
          (((Panels.PanelID) = panid) AND ((WOrders.WOApproved) IS NOT NULL) AND ((WOrders.WORetired) IS NULL));

        IF woid IS NULL THEN
          INSERT INTO pippa.Logs (LogType, LogMsg) 
            VALUES ("Error", IFNULL(CONCAT(stnn, " Error 7 - Panel SerNo ", brdsn, " not part of a known order"), 7));
        ELSE
          # Is there a current build for this work order?
          INSERT INTO pippa.Logs (LogType, LogMsg) 
            VALUES ("Info", IFNULL(CONCAT(stnn, " Info 8 - Panel SerNo ", brdsn, ": WOrderid=", woid), 8));
          SELECT
            Builds.BuildID
          INTO
            bldid
          FROM
            pippa.Builds INNER JOIN pippa.WOrders ON Builds.BuildID = WOrders.BuildID
          WHERE
            (((WOrders.WOrderID) = woid) AND ((WOrders.WOApproved) IS NOT NULL) AND ((WOrders.WORetired) IS NULL));

          IF bldid IS NULL THEN
            INSERT INTO pippa.Logs (LogType, LogMsg) VALUES ("Error", IFNULL(CONCAT(stnn, " Error 9 - Panel SerNo ", brdsn, " Work Order WOrderID=", woid, " not part of a known Build"), 9));
          ELSE
            # find TestSetID for this Build and Machine Type based on Station
            INSERT INTO pippa.Logs (LogType, LogMsg) 
              VALUES ("Info", IFNULL(CONCAT(stnn, " Info 10 - Panel SerNo ", brdsn, ": BuildID=", bldid), 10));

            SELECT
              TestSets.TSetID
            INTO
              tsid
            FROM
              pippa.TestSets
            WHERE
              (((TestSets.StnId = stationid) AND ((TestSets.BuildID) = bldid) 
                AND ((TestSets.TSetApproved) IS NOT NULL) AND ((TestSets.TSetRetired) IS NULL)));

            IF tsid IS NULL THEN
              INSERT INTO pippa.Logs (LogType, LogMsg) 
                VALUES ("Error", IFNULL(CONCAT(stnn, " Error 12 - Panel SerNo ", brdsn, " WOrderID=", woid, " BuildID=", bldid, " has no TestSet for Station ", stnn), 12));
            ELSE
              # Good to go! Insert pippa ResultSet record
              INSERT INTO pippa.Logs (LogType, LogMsg) 
                VALUES ("Info", IFNULL(CONCAT(stnn, " Info 13 - Panel SerNo ", brdsn, ": TSetID=", tsid), 13));

              INSERT INTO pippa.ResultSets (StnID, TSetID, BoardID, RSetTStamp, RSetStatus, RSetReason, OperatorID)                 VALUES (stationid, tsid, brdid, stamp, bstat, breason, vOperatorID);
              UPDATE
                pippa.Boards
              SET
                StnID = stationid, BoardTStamp = stamp
              WHERE
                BoardID = brdid;
            END IF;
          #tsid

          END IF;
        # end bldid
        END IF;
      # endwoid
      END IF;
    # PanelID



    END IF;
  # end Board serial num to ID
  END IF;
# BrdCount = 1

END

Verion 2 (variable bldid was changed to varid and table was changed from Builds to Variants - which is how you can recognise version 2)

Code: Select all

BEGIN
  DECLARE stnn          TEXT;
  DECLARE brdid         INT;
  DECLARE panid         INT;
  DECLARE woid          INT;
  DECLARE varid         INT;
  DECLARE tsid          INT;
  DECLARE stationid     INT;
  DECLARE vOperatorID   INT;
  DECLARE breason       TEXT;
  DECLARE bstat         INT;
  DECLARE stamp         TEXT;

  DECLARE brdsn         TEXT;
  DECLARE vOperatorName TEXT;
  #DECLARE breason TEXT;
  #DECLARE stnn TEXT;
  DECLARE brdcnt        INT;
  DECLARE brdfail       INT;
  DECLARE brdsuffix     TEXT;

  SET brdsn = IFNULL(NEW.SN, 0);
  SET stamp = IFNULL(NEW.IDate, 0);
  SET stnn = IFNULL(NEW.SystemID, 0);
  SET brdcnt = IFNULL(NEW.BrdCount, 0);
  SET vOperatorName = IFNULL(NEW.Inspector, 0);
  SET breason = IFNULL(NEW.DefectCode, 0);
  SET brdfail = IFNULL(NEW.BrdFail, 0);
/*
  SET stnn = "INLINE-AOI-1";
  SET stamp = "2009-01-01 13:00:00";
  SET brdsn = "336510020069-22";
  SET brdcnt = "1";
  SET vOperatorName = "Jeremy";
  SET breason = "1";
  SET brdfail = "0";
*/

  # board status

  INSERT INTO pippa.Logs (LogType, LogMsg) VALUES (
    "Info", IFNULL(CONCAT("Debug 1 - SerNo=",  brdsn, "  Station=", stnn,
    "  BrdCount=", brdcnt, "  BrdFail=", brdfail, "  Reason=", breason, 
    "  Inspector=", vOperatorName), 1));

  # Only create ResultSets record in pippa if this is the test summary line
  IF NEW.BrdCount = 1 THEN

    IF NEW.BrdFail = 1 THEN
      SELECT
        DefectCode.Description
      INTO
        breason
      FROM
        DefectCode
      WHERE
        (DefectCode.Code = NEW.DefectCode);
      #Fail 
      SET bstat = 3;
    ELSE
      SET breason = "Pass";
      SET bstat = 1;
    #pass  
    END IF;

    SELECT
      OperatorID
    INTO
      vOperatorID
    FROM
      pippa.Operators
    WHERE
      Operators.OperatorName = vOperatorName;

    IF vOperatorID IS NULL THEN
      SET vOperatorID := 1;
    END IF;
    # OperatorID 1 is "Unknown"

    SELECT
      StnID
    INTO
      stationid
    FROM
      pippa.Stations
    WHERE
      Stations.StnName = stnn;

    IF stationid IS NULL THEN
      SET stationid := 1;
    END IF;

    INSERT INTO pippa.Logs (LogType, LogMsg) 
      VALUES ("Info", IFNULL(CONCAT(stnn, " Info 2 - Panel SerNo ", brdsn, ": StnID=", stationid), 2));

    # where is the dash, if any
    SET brdsuffix := RIGHT(brdsn, LOCATE('-', REVERSE(brdsn)) - 1);

    IF LENGTH(brdsuffix) = 0 THEN
      # no suffix
      SET brdsn = RIGHT(CONCAT(brdsn, '001'), 16);
    ELSE
      # strip off -xx style suffix
      SET brdsn := LEFT(brdsn, LOCATE('-', brdsn) - 1);
      # add 000 style board suffix
      SET brdsn = CONCAT(brdsn, RIGHT(CONCAT('00', brdsuffix), 3));
    END IF;
    # add a leading 0 if is old barcode length (new are 16)
    IF LENGTH(brdsn) = 15 THEN
      SET brdsn = CONCAT('0', brdsn);
    END IF;

    # Find BoardID and PanelID from Board Serial Number
    SELECT
      BoardID
    INTO
      brdid
    FROM
      pippa.Boards
    WHERE
      BoardSerialNo = brdsn;

    IF brdid IS NULL THEN
      # not found in boards table
      INSERT INTO pippa.Logs (LogType, LogMsg)
        VALUES ("Error", IFNULL(CONCAT(stnn, " Error 3 - Panel SerNo ", brdsn, " is not in the Boards table"), 3));
    ELSE
      INSERT INTO pippa.Logs (LogType, LogMsg) 
        VALUES ("Info", IFNULL(CONCAT(stnn, " Info 4 - Panel SerNo ", brdsn, ": BoardID=", brdid), 4));
      # Find PanelId    
      SELECT
        PanelID
      INTO
        panid
      FROM
        pippa.Boards
      WHERE
        BoardID = brdid;

      IF panid IS NULL THEN
        INSERT INTO pippa.Logs (LogType, LogMsg) 
          VALUES ("Error", IFNULL(CONCAT(stnn, " Error 5 - Panel SerNo ", brdsn, " does not belong to a panel"), 5));
      ELSE
        # Find WorkOrder ID which includes SN as a board serial number
        INSERT INTO pippa.Logs (LogType, LogMsg) 
          VALUES ("Info", IFNULL(CONCAT(stnn, " Info 6 - Panel SerNo ", brdsn, ": PanelID=", panid), 6));
        SELECT
          pippa.WOrders.WOrderID
        INTO
          woid
        FROM
          pippa.Panels INNER JOIN pippa.WOrders ON pippa.Panels.WOrderID = pippa.WOrders.WOrderID
        WHERE
          (((Panels.PanelID) = panid) AND ((WOrders.WOApproved) IS NOT NULL) AND ((WOrders.WORetired) IS NULL));

        IF woid IS NULL THEN
          INSERT INTO pippa.Logs (LogType, LogMsg) 
            VALUES ("Error", IFNULL(CONCAT(stnn, " Error 7 - Panel SerNo ", brdsn, " not part of a known order"), 7));
        ELSE
          # Is there a current VARIANT for this work order?
          INSERT INTO pippa.Logs (LogType, LogMsg) 
            VALUES ("Info", IFNULL(CONCAT(stnn, " Info 8 - Panel SerNo ", brdsn, ": WOrderid=", woid), 8));
          SELECT
            Varants,VarID
          INTO
            varid
          FROM
            pippa.Variants INNER JOIN pippa.WOrders ON Variants.VarID = WOrders.VarID
          WHERE
            (((WOrders.WOrderID) = woid) AND ((WOrders.WOApproved) IS NOT NULL) AND ((WOrders.WORetired) IS NULL));

          IF varid IS NULL THEN
            INSERT INTO pippa.Logs (LogType, LogMsg) VALUES ("Error", IFNULL(CONCAT(stnn, " Error 9 - Panel SerNo ", brdsn, " Work Order WOrderID=", woid, " has no matching Variant"), 9));
          ELSE
            # find TestSetID for this Variant and Machine Type based on Station
            INSERT INTO pippa.Logs (LogType, LogMsg) 
              VALUES ("Info", IFNULL(CONCAT(stnn, " Info 10 - Panel SerNo ", brdsn, ": VarID=", varid), 10));

            SELECT
              TestSets.TSetID
            INTO
              tsid
            FROM
              pippa.TestSets
            WHERE
              (((TestSets.StnId = stationid) AND ((TestSets.VarID) = varid) 
                AND ((TestSets.TSetApproved) IS NOT NULL) AND ((TestSets.TSetRetired) IS NULL)));

            IF tsid IS NULL THEN
              INSERT INTO pippa.Logs (LogType, LogMsg) 
                VALUES ("Error", IFNULL(CONCAT(stnn, " Error 12 - Panel SerNo ", brdsn, " WOrderID=", woid, " VarID=", varid, " has no TestSet for Station ", stnn), 12));
            ELSE
              # Good to go! Insert pippa ResultSet record
              INSERT INTO pippa.Logs (LogType, LogMsg) 
                VALUES ("Info", IFNULL(CONCAT(stnn, " Info 13 - Panel SerNo ", brdsn, ": TSetID=", tsid), 13));

              INSERT INTO pippa.ResultSets (StnID, TSetID, BoardID, RSetTStamp, RSetStatus, RSetReason, OperatorID)                 VALUES (stationid, tsid, brdid, stamp, bstat, breason, vOperatorID);
              UPDATE
                pippa.Boards
              SET
                StnID = stationid, BoardTStamp = stamp
              WHERE
                BoardID = brdid;
            END IF;
          #tsid

          END IF;
        # end varid
        END IF;
      # endwoid
      END IF;
    # PanelID



    END IF;
  # end Board serial num to ID
  END IF;
# BrdCount = 1

END

Verion 3 (after editing on ONE LINE ONLY "Varants,VarID" to "Variants.VarID" Note the new varid PLUS the old bldid variable plus all the old table/column names.) Note also the version 1 code position-wise was added at the end of the first comment. BTW There seems to be editorial instability in comments, and items which are not SQL key words. (As with my last post on copy/paste, if you do NOT include the label, the copy/paste was fine.)

Code: Select all

BEGIN
  DECLARE stnn          TEXT;
  DECLARE brdid         INT;
  DECLARE panid         INT;
  DECLARE woid          INT;
  DECLARE varid         INT;
  DECLARE tsid          INT;
  DECLARE stationid     INT;
  DECLARE vOperatorID   INT;
  DECLARE breason       TEXT;
  DECLARE bstat         INT;
  DECLARE stamp         TEXT;

  DECLARE brdsn         TEXT;
  DECLARE vOperatorName TEXT;
  #DECLARE breason TEXT;BEGIN
  DECLARE stnn          TEXT;
  DECLARE brdid         INT;
  DECLARE panid         INT;
  DECLARE woid          INT;
  DECLARE bldid         INT;
  DECLARE tsid          INT;
  DECLARE stationid     INT;
  DECLARE vOperatorID   INT;
  DECLARE breason       TEXT;
  DECLARE bstat         INT;
  DECLARE stamp         TEXT;

  DECLARE brdsn         TEXT;
  DECLARE vOperatorName TEXT;
  #DECLARE breason TEXT;
  #DECLARE stnn TEXT;
  DECLARE brdcnt        INT;
  DECLARE brdfail       INT;
  DECLARE brdsuffix     TEXT;

  SET brdsn = IFNULL(NEW.SN, 0);
  SET stamp = IFNULL(NEW.IDate, 0);
  SET stnn = IFNULL(NEW.SystemID, 0);
  SET brdcnt = IFNULL(NEW.BrdCount, 0);
  SET vOperatorName = IFNULL(NEW.Inspector, 0);
  SET breason = IFNULL(NEW.DefectCode, 0);
  SET brdfail = IFNULL(NEW.BrdFail, 0);
  /*
  SET stnn = "INLINE-AOI-1";
  SET stamp = "2009-01-01 13:00:00";
  SET brdsn = "336510020069-22";
  SET brdcnt = "1";
  SET vOperatorName = "Jeremy";
  SET breason = "1";
  SET brdfail = "0";
*/

  # board status

  INSERT
    INTO pippa.Logs (LogType, LogMsg)
    VALUES
      (
      "Info", IFNULL(CONCAT("Debug 1 - SerNo=", brdsn, "  Station=", stnn,
      "  BrdCount=", brdcnt, "  BrdFail=", brdfail, "  Reason=", breason,
      "  Inspector=", vOperatorName), 1));

  # Only create ResultSets record in pippa if this is the test summary line
  IF NEW.BrdCount = 1 THEN

    IF NEW.BrdFail = 1 THEN
      SELECT
        DefectCode.Description
        INTO
          breason
        FROM
          DefectCode
        WHERE
          (DefectCode.Code = NEW.DefectCode);
      #Fail 
      SET bstat = 3;
    ELSE
      SET breason = "Pass";
      SET bstat = 1;
    #pass  
    END IF;

    SELECT
      OperatorID
      INTO
        vOperatorID
      FROM
        pippa.Operators
      WHERE
        Operators.OperatorName = vOperatorName;

    IF vOperatorID IS NULL THEN
      SET vOperatorID := 1;
    END IF;
    # OperatorID 1 is "Unknown"

    SELECT
      StnID
      INTO
        stationid
      FROM
        pippa.Stations
      WHERE
        Stations.StnName = stnn;

    IF stationid IS NULL THEN
      SET stationid := 1;
    END IF;

    INSERT
      INTO pippa.Logs (LogType, LogMsg)
      VALUES
        ("Info", IFNULL(CONCAT(stnn, " Info 2 - Panel SerNo ", brdsn, ": StnID=", stationid), 2));

    # where is the dash, if any
    SET brdsuffix := RIGHT(brdsn, LOCATE('-', REVERSE(brdsn)) - 1);

    IF LENGTH(brdsuffix) = 0 THEN
      # no suffix
      SET brdsn = RIGHT(CONCAT(brdsn, '001'), 16);
    ELSE
      # strip off -xx style suffix
      SET brdsn := LEFT(brdsn, LOCATE('-', brdsn) - 1);
      # add 000 style board suffix
      SET brdsn = CONCAT(brdsn, RIGHT(CONCAT('00', brdsuffix), 3));
    END IF;
    # add a leading 0 if is old barcode length (new are 16)

    IF LENGTH(brdsn) = 15 THEN
      SET brdsn = CONCAT('0', brdsn);
    END IF;

    # Find BoardID and PanelID from Board Serial Number
    SELECT
      BoardID
      INTO
        brdid
      FROM
        pippa.Boards
      WHERE
        BoardSerialNo = brdsn;

    IF brdid IS NULL THEN
      # not found in boards table
      INSERT
        INTO pippa.Logs (LogType, LogMsg)
        VALUES
          ("Error", IFNULL(CONCAT(stnn, " Error 3 - Panel SerNo ", brdsn, " is not in the Boards table"), 3));
    ELSE
      INSERT
        INTO pippa.Logs (LogType, LogMsg)
        VALUES
          ("Info", IFNULL(CONCAT(stnn, " Info 4 - Panel SerNo ", brdsn, ": BoardID=", brdid), 4));
      # Find PanelId    
      SELECT
        PanelID
        INTO
          panid
        FROM
          pippa.Boards
        WHERE
          BoardID = brdid;

      IF panid IS NULL THEN
        INSERT
          INTO pippa.Logs (LogType, LogMsg)
          VALUES
            ("Error", IFNULL(CONCAT(stnn, " Error 5 - Panel SerNo ", brdsn, " does not belong to a panel"), 5));
      ELSE
        # Find WorkOrder ID which includes SN as a board serial number
        INSERT
          INTO pippa.Logs (LogType, LogMsg)
          VALUES
            ("Info", IFNULL(CONCAT(stnn, " Info 6 - Panel SerNo ", brdsn, ": PanelID=", panid), 6));
        SELECT
          pippa.WOrders.WOrderID
          INTO
            woid
          FROM
            pippa.Panels
              INNER JOIN pippa.WOrders
                  ON pippa.Panels.WOrderID = pippa.WOrders.WOrderID
          WHERE
            (((Panels.PanelID) = panid) AND ((WOrders.WOApproved) IS NOT NULL) AND ((WOrders.WORetired) IS NULL));

        IF woid IS NULL THEN
          INSERT
            INTO pippa.Logs (LogType, LogMsg)
            VALUES
              ("Error", IFNULL(CONCAT(stnn, " Error 7 - Panel SerNo ", brdsn, " not part of a known order"), 7));
        ELSE
          # Is there a current build for this work order?
          INSERT
            INTO pippa.Logs (LogType, LogMsg)
            VALUES
              ("Info", IFNULL(CONCAT(stnn, " Info 8 - Panel SerNo ", brdsn, ": WOrderid=", woid), 8));
          SELECT
            Builds.BuildID
            INTO
              bldid
            FROM
              pippa.Builds
                INNER JOIN pippa.WOrders
                    ON Builds.BuildID = WOrders.BuildID
            WHERE
              (((WOrders.WOrderID) = woid) AND ((WOrders.WOApproved) IS NOT NULL) AND ((WOrders.WORetired) IS NULL));

          IF bldid IS NULL THEN
            INSERT
              INTO pippa.Logs (LogType, LogMsg)
              VALUES
                ("Error", IFNULL(CONCAT(stnn, " Error 9 - Panel SerNo ", brdsn, " Work Order WOrderID=", woid, " not part of a known Build"), 9));
          ELSE
            # find TestSetID for this Build and Machine Type based on Station
            INSERT
              INTO pippa.Logs (LogType, LogMsg)
              VALUES
                ("Info", IFNULL(CONCAT(stnn, " Info 10 - Panel SerNo ", brdsn, ": BuildID=", bldid), 10));

            SELECT
              TestSets.TSetID
              INTO
                tsid
              FROM
                pippa.TestSets
              WHERE
                (((TestSets.StnId = stationid) AND ((TestSets.BuildID) = bldid)
                AND ((TestSets.TSetApproved) IS NOT NULL) AND ((TestSets.TSetRetired) IS NULL)));

            IF tsid IS NULL THEN
              INSERT
                INTO pippa.Logs (LogType, LogMsg)
                VALUES
                  ("Error", IFNULL(CONCAT(stnn, " Error 12 - Panel SerNo ", brdsn, " WOrderID=", woid, " BuildID=", bldid, " has no TestSet for Station ", stnn), 12));
            ELSE
              # Good to go! Insert pippa ResultSet record
              INSERT
                INTO pippa.Logs (LogType, LogMsg)
                VALUES
                  ("Info", IFNULL(CONCAT(stnn, " Info 13 - Panel SerNo ", brdsn, ": TSetID=", tsid), 13));

              INSERT
                INTO pippa.ResultSets (StnID, TSetID, BoardID, RSetTStamp, RSetStatus, RSetReason, OperatorID)
                VALUES
                  (stationid, tsid, brdid, stamp, bstat, breason, vOperatorID);
              UPDATE
                pippa.Boards
                SET
                  StnID = stationid, BoardTStamp = stamp
                WHERE
                  BoardID = brdid;
            END IF;
          #tsid

          END IF;
        # end bldid

        END IF;
      # endwoid

      END IF;
    # PanelID



    END IF;
  # end Board serial num to ID

  END IF;
# BrdCount = 1

END
  #DECLARE stnn TEXT;
  DECLARE brdcnt        INT;
  DECLARE brdfail       INT;
  DECLARE brdsuffix     TEXT;

  SET brdsn = IFNULL(NEW.SN, 0);
  SET stamp = IFNULL(NEW.IDate, 0);
  SET stnn = IFNULL(NEW.SystemID, 0);
  SET brdcnt = IFNULL(NEW.BrdCount, 0);
  SET vOperatorName = IFNULL(NEW.Inspector, 0);
  SET breason = IFNULL(NEW.DefectCode, 0);
  SET brdfail = IFNULL(NEW.BrdFail, 0);
/*
  SET stnn = "INLINE-AOI-1";
  SET stamp = "2009-01-01 13:00:00";
  SET brdsn = "336510020069-22";
  SET brdcnt = "1";
  SET vOperatorName = "Jeremy";
  SET breason = "1";
  SET brdfail = "0";
*/

  # board status

  INSERT INTO pippa.Logs (LogType, LogMsg) VALUES (
    "Info", IFNULL(CONCAT("Debug 1 - SerNo=",  brdsn, "  Station=", stnn,
    "  BrdCount=", brdcnt, "  BrdFail=", brdfail, "  Reason=", breason, 
    "  Inspector=", vOperatorName), 1));

  # Only create ResultSets record in pippa if this is the test summary line
  IF NEW.BrdCount = 1 THEN

    IF NEW.BrdFail = 1 THEN
      SELECT
        DefectCode.Description
      INTO
        breason
      FROM
        DefectCode
      WHERE
        (DefectCode.Code = NEW.DefectCode);
      #Fail 
      SET bstat = 3;
    ELSE
      SET breason = "Pass";
      SET bstat = 1;
    #pass  
    END IF;

    SELECT
      OperatorID
    INTO
      vOperatorID
    FROM
      pippa.Operators
    WHERE
      Operators.OperatorName = vOperatorName;

    IF vOperatorID IS NULL THEN
      SET vOperatorID := 1;
    END IF;
    # OperatorID 1 is "Unknown"

    SELECT
      StnID
    INTO
      stationid
    FROM
      pippa.Stations
    WHERE
      Stations.StnName = stnn;

    IF stationid IS NULL THEN
      SET stationid := 1;
    END IF;

    INSERT INTO pippa.Logs (LogType, LogMsg) 
      VALUES ("Info", IFNULL(CONCAT(stnn, " Info 2 - Panel SerNo ", brdsn, ": StnID=", stationid), 2));

    # where is the dash, if any
    SET brdsuffix := RIGHT(brdsn, LOCATE('-', REVERSE(brdsn)) - 1);

    IF LENGTH(brdsuffix) = 0 THEN
      # no suffix
      SET brdsn = RIGHT(CONCAT(brdsn, '001'), 16);
    ELSE
      # strip off -xx style suffix
      SET brdsn := LEFT(brdsn, LOCATE('-', brdsn) - 1);
      # add 000 style board suffix
      SET brdsn = CONCAT(brdsn, RIGHT(CONCAT('00', brdsuffix), 3));
    END IF;
    # add a leading 0 if is old barcode length (new are 16)
    IF LENGTH(brdsn) = 15 THEN
      SET brdsn = CONCAT('0', brdsn);
    END IF;

    # Find BoardID and PanelID from Board Serial Number
    SELECT
      BoardID
    INTO
      brdid
    FROM
      pippa.Boards
    WHERE
      BoardSerialNo = brdsn;

    IF brdid IS NULL THEN
      # not found in boards table
      INSERT INTO pippa.Logs (LogType, LogMsg)
        VALUES ("Error", IFNULL(CONCAT(stnn, " Error 3 - Panel SerNo ", brdsn, " is not in the Boards table"), 3));
    ELSE
      INSERT INTO pippa.Logs (LogType, LogMsg) 
        VALUES ("Info", IFNULL(CONCAT(stnn, " Info 4 - Panel SerNo ", brdsn, ": BoardID=", brdid), 4));
      # Find PanelId    
      SELECT
        PanelID
      INTO
        panid
      FROM
        pippa.Boards
      WHERE
        BoardID = brdid;

      IF panid IS NULL THEN
        INSERT INTO pippa.Logs (LogType, LogMsg) 
          VALUES ("Error", IFNULL(CONCAT(stnn, " Error 5 - Panel SerNo ", brdsn, " does not belong to a panel"), 5));
      ELSE
        # Find WorkOrder ID which includes SN as a board serial number
        INSERT INTO pippa.Logs (LogType, LogMsg) 
          VALUES ("Info", IFNULL(CONCAT(stnn, " Info 6 - Panel SerNo ", brdsn, ": PanelID=", panid), 6));
        SELECT
          pippa.WOrders.WOrderID
        INTO
          woid
        FROM
          pippa.Panels INNER JOIN pippa.WOrders ON pippa.Panels.WOrderID = pippa.WOrders.WOrderID
        WHERE
          (((Panels.PanelID) = panid) AND ((WOrders.WOApproved) IS NOT NULL) AND ((WOrders.WORetired) IS NULL));

        IF woid IS NULL THEN
          INSERT INTO pippa.Logs (LogType, LogMsg) 
            VALUES ("Error", IFNULL(CONCAT(stnn, " Error 7 - Panel SerNo ", brdsn, " not part of a known order"), 7));
        ELSE
          # Is there a current VARIANT for this work order?
          INSERT INTO pippa.Logs (LogType, LogMsg) 
            VALUES ("Info", IFNULL(CONCAT(stnn, " Info 8 - Panel SerNo ", brdsn, ": WOrderid=", woid), 8));
          SELECT
            Varants.VarID
          INTO
            varid
          FROM
            pippa.Variants INNER JOIN pippa.WOrders ON Variants.VarID = WOrders.VarID
          WHERE
            (((WOrders.WOrderID) = woid) AND ((WOrders.WOApproved) IS NOT NULL) AND ((WOrders.WORetired) IS NULL));

          IF varid IS NULL THEN
            INSERT INTO pippa.Logs (LogType, LogMsg) VALUES ("Error", IFNULL(CONCAT(stnn, " Error 9 - Panel SerNo ", brdsn, " Work Order WOrderID=", woid, " has no matching Variant"), 9));
          ELSE
            # find TestSetID for this Variant and Machine Type based on Station
            INSERT INTO pippa.Logs (LogType, LogMsg) 
              VALUES ("Info", IFNULL(CONCAT(stnn, " Info 10 - Panel SerNo ", brdsn, ": VarID=", varid), 10));

            SELECT
              TestSets.TSetID
            INTO
              tsid
            FROM
              pippa.TestSets
            WHERE
              (((TestSets.StnId = stationid) AND ((TestSets.VarID) = varid) 
                AND ((TestSets.TSetApproved) IS NOT NULL) AND ((TestSets.TSetRetired) IS NULL)));

            IF tsid IS NULL THEN
              INSERT INTO pippa.Logs (LogType, LogMsg) 
                VALUES ("Error", IFNULL(CONCAT(stnn, " Error 12 - Panel SerNo ", brdsn, " WOrderID=", woid, " VarID=", varid, " has no TestSet for Station ", stnn), 12));
            ELSE
              # Good to go! Insert pippa ResultSet record
              INSERT INTO pippa.Logs (LogType, LogMsg) 
                VALUES ("Info", IFNULL(CONCAT(stnn, " Info 13 - Panel SerNo ", brdsn, ": TSetID=", tsid), 13));

              INSERT INTO pippa.ResultSets (StnID, TSetID, BoardID, RSetTStamp, RSetStatus, RSetReason, OperatorID)                 VALUES (stationid, tsid, brdid, stamp, bstat, breason, vOperatorID);
              UPDATE
                pippa.Boards
              SET
                StnID = stationid, BoardTStamp = stamp
              WHERE
                BoardID = brdid;
            END IF;
          #tsid

          END IF;
        # end varid
        END IF;
      # endwoid
      END IF;
    # PanelID



    END IF;
  # end Board serial num to ID
  END IF;
# BrdCount = 1

END

Help please?

[Elias]

Hello,

Thank you for such carefull report. As I can see in the Version 3 listing (see the fragment below) some variable are realy duplicated in the code and MySql server correctly throws the error during trigger saving.

Code: Select all

BEGIN 
  DECLARE stnn          TEXT;
  DECLARE brdid         INT; 
  DECLARE panid         INT; 
  DECLARE woid          INT; 
  DECLARE varid         INT; 
  DECLARE tsid          INT; 
  DECLARE stationid     INT; 
  DECLARE vOperatorID   INT; 
  DECLARE breason       TEXT; 
  DECLARE bstat         INT; 
  DECLARE stamp         TEXT; 

  DECLARE brdsn         TEXT; 
  DECLARE vOperatorName TEXT; 
  #DECLARE breason TEXT;BEGIN 
  DECLARE stnn          TEXT;
  DECLARE brdid         INT; 
  DECLARE panid         INT; 
  DECLARE woid          INT; 
  DECLARE bldid         INT; 
  DECLARE tsid          INT; 
  DECLARE stationid     INT; 
  DECLARE vOperatorID   INT; 
  DECLARE breason       TEXT; 
  DECLARE bstat         INT; 
  DECLARE stamp         TEXT; 

Please tell us more about apllication hanging during code editing. We have fixed the bug with application hanging after code pasting in the current build.

[KiwiJem]

This is the wrong thread to answer the hanging problem. I will reply in that thread, so that THIS issue is not misunderstood.

THIS thread is about dangling memory pointers, which need to be tracked down and attended to by your programming team - and I would hate you to think otherwise.

Yes, I am glad that you too can see the errors created BY YOUR EDITOR in version 3.

What I did was to analyse the error, and give you a heads-up on where to start looking for the bug. I have been programming for a living since 1974, so I know a memory pointer error when I see one. Do you?

I hope your programming team does. Please pass this on to them.

Regards

[Elias]

Hello, thank you for an advice. I am the author of the formatter and member of the team that coded the editor.

Can you tell me how did you get the text of the Version 3?

[KiwiJem]

Hi
I am glad you are able to understand the subtlety and implications of the problem. From an earlier reply, I assumed you had misunderstood, and were simply telling me I had duplicated declarations - thus it looked to me typical of a "help" desk response. I had seen the duplication - and it was that very unwanted duplication which started this whole thread. I apologise for letting my frustration show.

The first 9 paragraphs of the original post are meant to explain exactly what I did.

When the debugger stopped because of a typo (which had happily saved to the stored trigger code - i.e. was not recognised by MySQL as a syntax error), the debugger reported the error, stopped the debug session, and it then APPEARED to be in edit mode. WHICH IS INTUITIVE BEHAVIOUR**.

But that is when the first few lines of my correct code (up to the end of the first comment line) had the ENTIRE stale code added to it on screen.

This stale code was the entire ORIGINAL code version 1 PRIOR to successfully editing it and saving it to the database as version 2 before I ran it in debug mode. That is how I *know* you have a dangling pointer - beacuse nothing should be pointing to the code that is OLDER than that currently saved to the database - unless it is ONLY for the purposes of UNDO.

So how did I get it? Since it was editable code, I copied an pasted it into an external text editor, and then pasted it into the posting.

I can only say I am pleased that it was so badly messed up that it refused to save version 3 when I tried, as I had not noticed how badly it was messed up, and I would have lost my current trigger procedure.

Anyway, it may no longer be a problem in the newest build, so I will let you know if I encounter the problem again.

**Even in Single Step Mode, I would expect the code on and below the next step and current variables to be editable, because if a line is ABOUT to be executed, you will still be able to pass the NEW version to the debug engine with no trouble. For a good example of how this is done seamlessly with a remote debug engine, try the debugger in NuSphere's phpED.
They also allow lines ABOVE the current execution point to be saved for next execution - eg next iteration of a loop. Since the code is held in the editing app, and only one line or block passed to the debug engine at a time, it is possible to do it cleanly, and eliminates the restrictive "mode" thing.

Regards, kiwijem

[Elias]

Hello.

Now I've understood you correctly . I think that text has been corrupted by formatter on paste. We paid attention to the code formatting in triggers and found some problems that are fixed in the current build. Thanks a lot for assistance.

As for modifying variables I can say that we are going to add this feature to the next version backlog. dbForge Studio for MySql debugger does not interpret procedures statements but injects control and evaluation statements to the procedures code and controls execution from the another session. Thats why we cannot change code during debugging and variable modification is a complicated feature.

[KiwiJem]

Thank you