A couple of minor issues with dbForge Studio for MySQL version 3.50.305.
The first is an editing issue, and the second is a possible debugger issue. Neither of these is a show stopper. Thanks in advance...
I have attached below the entire SP I referred to recently using the PREPARE statements, and I am now very happy with the way it is running, thank you.
The editing issue is that in the SP code below, when you position the cursor at the start of the line:
Code: Select all
SET vBoardStr = substr(vBoardStr, 1, char_length(vBoardStr) - 1); # remove trailing comma(This is the lazy man's way of replicating a line, the text-book way is to waste time - to pick up your mouse again and re-position the cursor exactly where it still is, underneath the line to copy, thus un-highlighting the line just copied, and then do a single Ctrl-V to insert. But this lazy way normally works perfectly, with the first Ctrl-V exactly replacing the highlighted text, and the second Ctrl-V adding to it underneath.)
I had mentioned this in a previous post for an earlier release, but was unable to reproduce it on demand at the time. I cannot construct an SP from scratch to reproduce it, but if I copy and paste the entire code below into a new SP, it demonstrates the comment copy paste issue as described.
I have only ever seen it mis-behave on a line that IS a comment, or includes a comment at the end, and perhaps only inside a conditional block.
The SECOND minor issue is that the debugger does not appear to stop when I tell it to. It does BREAK where expected, but if I then STOP the debug session, the result is unexpected.
If I set breakpoints on these two lines
Code: Select all
PREPARE vbsx FROM @vbdstr;(Except I often cannot see local variable value or type by the normal hovering the mouse pointer over the variables at these particular breakpoints as I can at others. I suspect there is something "different" about these breakpoints for the PREPARE statement.)
Then, because I noticed a syntax error inthe SQL I was about to prepare, I pressed the square STOP button on the debug toolbar to cancel the debug session. Imagine my surprise when the PREPARE statement was then executed, reporting an SQL error back from MySQL.
I do not know whether it would have EXECUTEd it as well had there not been an error, but it did not want to stop! This happened on every occasion I tried it while there was an SQL syntax error (the final comma had not been removed properly). I did not try it again after I fixed the SQL, as there was no further need for debugging it at the time.My expectation is that if, at a breakpoint, you press stop, no further statements will be executed before it reverts to editing mode. At least, it has normally stopped when told in the past. Or have I missed something (again)?
Thanks
Jem Stanners
Code: Select all
CREATE DEFINER = 'root'@'%'
PROCEDURE pippa.CreatePanelsAndBoards(IN pWOrderNo TEXT)
BEGIN
# Looks into WOQty num boards in WOrders table and will create or add panels and boards to match qty in WOrders
DECLARE vWOrderID, vWOQty, vBuildID, vPartID, vPartNumX, vPartNumY, VPanelID ,AA INT;
DECLARE vNumPanels, vBoardsPerPanel, vRslt, vONoLength, vLStart, vLLength INT;
DECLARE vSubPosn, vActualBoards, vActualPanels, vBoardCnt, vExistPanels INT;
DECLARE vi, vj, vPanelSerialNo, vBoardSerialNo, vReason, vLineChar, vSubChar, vPONo TEXT;
DECLARE vPrefix, vPanelIndex, vBoardStr TEXT;
DECLARE vPartial, vDebug BOOLEAN;
DECLARE curNumxy CURSOR FOR
SELECT
Parts.PartNumX, Parts.PartNumY
FROM
(Builds
INNER JOIN Parts
ON Builds.PartID = Parts.PartID)
INNER JOIN WOrders
ON Builds.BuildID = WOrders.BuildID
WHERE
(((WOrders.WOrderNo) = pWOrderNo));
DECLARE curWorders CURSOR FOR
SELECT
WOrderID, WOQty
FROM
pippa.WOrders
WHERE
WOrderNo = pWOrderNo;
body:
BEGIN
#vDebug true prevents writing to Panels and Boards tables
SET vDebug = FALSE;
SET vPartial = FALSE;
SET pWOrderNo = UPPER(pWOrderNo);
IF SUBSTR(pWOrderNo, 1, 1) = "C" THEN
# replace "C" with zero leave other letters like S for Samples
SET pWOrderNo = CONCAT('0', SUBSTR(pWOrderNo, 2));
END IF;
IF (LOCATE('/', pWOrderNo) != 7) THEN
SET vRslt := -1;
SET vReason := 'Must have 6 digits in Work Order Number before the slash';
LEAVE body;
END IF;
SET VPONo = LEFT(pWOrderNo, 6);
SET vSubPosn = LOCATE('+', pWOrderNo);
IF vSubPosn = 0 THEN
# no sub assembly suffix
SET vLineChar = RIGHT(CONCAT('000', SUBSTR(pWOrderNo, 8)), 3);
SET vSubChar = '';
ELSE
SET vLineChar = RIGHT(CONCAT('000', SUBSTR(pWOrderNo, 8, vSubPosn - 8)), 3);
SET vSubChar = SUBSTR(pWOrderNo, vSubPosn + 1, 1);
END IF;
SET vPrefix = CONCAT(vPONo, vLineChar);
# find number of boards expected in the panel
OPEN curNumxy;
FETCH curNumxy INTO vPartNumX, vPartNumY;
CLOSE curNumxy;
IF (vPartnumX IS NULL) OR (VPartNumY IS NULL) THEN
SET vRslt := -2;
SET vReason := 'NumX and NumY must be specified in Parts Table';
LEAVE body;
END IF;
SET vBoardsPerPanel := vPartNumX * vPartNumY;
IF vBoardsPerPanel = 0 THEN
SET vRslt := -3;
SET vReason := 'Cannot have zero boards per panel in Parts Table';
LEAVE body;
END IF;
# Find work Order ID and Board Quantity from Work Order
OPEN curworders;
FETCH curWorders INTO vWOrderID, vWOQty;
CLOSE curworders;
# round up to next whole panel number of boards
SET vNumPanels = (vWOQty + (vBoardsPerPanel / 2) - 1) / vBoardsPerPanel;
## SET vWOQty = vNumPanels * vBoardsPerPanel;
IF (vPartial) AND (vNumpanels > 1000) THEN
SET vRslt := -4;
SET vReason := 'Max 10,000 panels can be specified per Work Order';
LEAVE body;
END IF;
IF vNumpanels > 10000 THEN
SET vRslt := -5;
SET vReason := 'Max 1,000 panels can be specified per Work Order for Sub Assemblies';
LEAVE body;
END IF;
IF vNumpanels vNumPanels THEN
LEAVE panelloop;
END IF;
IF vPartial THEN
SET vPanelIndex = RIGHT(CONCAT('000', vi), 3);
ELSE
SET vPanelIndex = RIGHT(CONCAT('0000', vi), 4);
END IF;
SET vPanelSerialNo = CONCAT(vPrefix, vPanelIndex, vSubChar);
IF NOT vDebug THEN
INSERT
INTO Panels (WOrderID, PanelSerialNo)
VALUES
(vWOrderID, vPanelSerialNo);
SET vPanelID = LAST_INSERT_ID();
ELSE
SET vPanelID = vi;
END IF;
SET vActualPanels = vActualPanels + 1;
SET vj = 1;
boardloop:
LOOP
IF vj > vBoardsPerPanel THEN
LEAVE boardloop;
END IF;
# add board data to vBoardStr string
SET vBoardSerialNo = CONCAT(vPanelSerialNo, RIGHT(CONCAT('000', vj), 3));
SET vBoardStr = concat(vBoardStr, '(', vj, ',', vPanelID, ',', vBoardSerialNo, '),');
SET vBoardCnt = vBoardCnt+1;
SET vActualBoards = vActualBoards + 1;
SET vj = vj + 1;
IF (NOT vDebug) AND (vBoardCnt > 32) THEN
# only send the query once in 32 boards
SET AA = char_length(vBoardStr); # debug
SET vBoardStr = substr(vBoardStr, 1, char_length(vBoardStr) - 1); # remove trailing comma
SET @vbdstr = concat('INSERT INTO Boards (BoardPosn, PanelID, BoardSerialNo) VALUES ', vBoardStr, ';');
SET vBoardStr = @vbdstr;
PREPARE vbsx FROM @vbdstr;
EXECUTE vbsx;
DEALLOCATE PREPARE vbsx;
SET vBoardStr = '';
SET vBoardCnt = 0;
END IF;
END LOOP boardloop;
# next panel
SET vi = vi + 1;
END LOOP panelloop;
# complete remaining board inserts
IF (NOT vDebug) AND (vBoardCnt > 0) THEN
# send any remaining query since last thousand boards
SET AA = char_length(vBoardStr); #debug
SET vBoardStr = substr(vBoardStr, 1, char_length(vBoardStr) - 1); # remove trailing comma
SET @vbdstr = concat('INSERT INTO Boards (BoardPosn, PanelID, BoardSerialNo) VALUES ', vBoardStr, ';');
SET vBoardStr = @vbdstr;
PREPARE vbsx FROM @vbdstr;
EXECUTE vbsx;
DEALLOCATE PREPARE vbsx;
SET vBoardCnt = 0;
END IF;
SET vRslt = 1;
IF vExistPanels = 0 THEN
IF NOT vDebug THEN
SET vReason = CONCAT(vActualPanels, ' Panels = ', vActualBoards, ' Boards Created');
ELSE
SET vReason = CONCAT('Debug Mode: ', vActualPanels, ' Panels = ', vActualBoards, ' Boards would be Created');
END IF;
ELSE
IF NOT vDebug THEN
SET vReason = CONCAT(vActualPanels, ' Panels = ', vActualBoards, ' Boards Added');
ELSE
SET vReason = CONCAT('Debug Mode: ', vActualPanels, ' Panels = ', vActualBoards, ' Boards would be Added');
END IF;
END IF;
END body;
SELECT
vRslt AS SerialStatus, vReason AS SerialReason;
END
. Of cource such execution can be dangerous, if the next statement is unwanted... So we will think about breaking the debugging connection rather than soft switching off the debugger.