| Bug #73989 | Major bug on VisualStudio Debugger | ||
|---|---|---|---|
| Submitted: | 21 Sep 2014 4:55 | Modified: | 17 Jan 2015 8:55 |
| Reporter: | Akshay Mohan MK | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Visual Studio Integration | Severity: | S3 (Non-critical) |
| Version: | 6.9.3 | OS: | Windows |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | Routine Debugger, VisualStudioIntegration | ||
[21 Sep 2014 4:55]
Akshay Mohan MK
[21 Sep 2014 5:10]
Akshay Mohan MK
DROP PROCEDURE IF EXISTS SpStockTransfer;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SpStockTransfer`(SRC INT,DEST INTEGER,ITEMUNIT INTEGER,nQTY INTEGER,TRMODE INTEGER)
BEGIN
-- ======================================================================================
-- Created by : Akshay Mohan.MK
-- Created on : 24 JULY 2014
-- Project : SalesFactory
-- Version : 1.0.0.0
-- Execute : Call SpStockTransfer(4,2,1,1,1);
-- Purpose : To Process Stock Transfer
--
-- Modification
-- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- ---------------------------------------------------------------------------------------
-- Modified by Date Versions Purpose
-- ---------------------------------------------------------------------------------------
--
--
--
--
--
--
-- ---------------------------------------------------------------------------------------
-- =======================================================================================
DECLARE ERCODE CHAR(5) DEFAULT '00000';
DECLARE ERTEXT VARCHAR(150) DEFAULT ' ';
DECLARE STID INT DEFAULT 0;
DECLARE CURSTOCK INT DEFAULT 0;
DECLARE EXPDATE DATE;
DECLARE SELCID INT;
DECLARE VRNO INT;
DECLARE STKREGID INT;
DECLARE TOTALSTOCK INT;
DECLARE TRSTOCKID INT DEFAULT 0;
DECLARE TRQTY INT DEFAULT 0;
DECLARE TEMPQTY INT DEFAULT 0;
DECLARE RMARK VARCHAR(200);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
ERCODE=RETURNED_SQLSTATE,ERTEXT=MESSAGE_TEXT;
IF ERCODE<>'00000' THEN
ROLLBACK;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT=ERTEXT;
ELSE
COMMIT;
END IF;
END;
IF src=dest THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Source store and destination store are cannot be same';
END IF;
DROP TEMPORARY TABLE IF EXISTS TEMPSTOCKLIST;
CREATE TEMPORARY TABLE TEMPSTOCKLIST(STOCKID INTEGER PRIMARY KEY NOT NULL,QUANTITY INT,STORE INT);
SET rmark=concat((select fngetremark(8,1)),cast(src AS char(5)));
SET rmark=concat(rmark,(concat(' To ',cast(dest as char(5)))));
START TRANSACTION WITH CONSISTENT SNAPSHOT;
IF TRMODE=1 THEN
-- Purchase Transfer Begins --
SELECT sl.qty,sl.stockid,sl.expiredate,sl.id
INTO curstock,stid,expdate,selcid
FROM stocklink SL
RIGHT JOIN stockregister SR
ON SL.stockregid=sr.id
WHERE sr.itemunitid=itemunit
AND sr.storeid=src
AND sr.unclearedstock=nqty*-1
AND sl.stockstatus=1
ORDER BY sr.voucherno DESC,sr.stockdate DESC
LIMIT 1;
IF curstock>=0 OR curstock IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Insufficient stock in source store.';
ELSE
INSERT INTO stocktransfer
(transferid,
stockid,
itemid,
itemunitid,
qty,
transferdate,
fromstoreid,
tostoreid,
transferremark,
transfermode,
TransferType,
`status`)
SELECT
(SELECT FnGetNextID('TransferID')),
ps.stockid,
ps.itemid,
ps.itemunitid,
curstock,
(SELECT FnGetTransDate()),
src,
dest,
rmark,
trmode,
(SELECT FnGetStockTransTypeMode(trmode,1)),
3
FROM purchasestock ps
WHERE ps.stockid=stid;
SELECT voucherno
INTO vrno FROM
purchasestock
WHERE stockid = stid;
SET @itemid=-1;
SELECT itemid
INTO @itemid FROM
setitemunit
WHERE itemunitid = itemunit;
CALL spupdatestockregister(@itemid,itemunit,ABS(curstock),2,(SELECT fngettransdate()),1,vrno,(SELECT FnGetCurrentID('TransferID')),src,stkregid);
CALL spinsertstocklink(src,stid,stkregid,ABS(curstock),8,2);
CALL spupdatestockregister(@itemid,itemunit,ABS(curstock),1,(SELECT fngettransdate()),1,vrno,(SELECT FnGetCurrentID('TransferID')),dest,stkregid);
CALL spinsertstocklink(dest,stid,stkregid,ABS(curstock),8,1);
END IF;
-- Purchase Transfer End --
ELSE
-- Non purchase transfer--
SELECT qty
INTO curstock
FROM stocklink
WHERE storeid=src AND stockstatus=2
ORDER BY lastmodified ASC,stockid ASC,ID ASC
LIMIT 1;
IF IFNULL(ABS(curstock),0)<nqty THEN
SELECT IFNULL(SUM(qty),0)
INTO curstock
FROM stocklink
WHERE storeid=src AND stockstatus=2;
IF ABS(curstock)<nqty THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Insufficiant stock in source store.';
END IF;
ELSEIF ABS(curstock)=nqty THEN
INSERT INTO tempstocklist(
stockid,
store,
quantity)
SELECT stockid,
storeid,
qty
FROM stocklink
WHERE storeid=src AND stockstatus=2
ORDER BY lastmodified ASC,stockid ASC,ID ASC;
ELSEIF ABS(curstock)>nqty THEN
SET @tqty=0;
WHILE @tqty<=nqty DO
INSERT INTO tempstocklist(
stockid,
store,
quantity)
SELECT stockid,
storeid,
qty
FROM stocklink
WHERE stockid NOT IN(
SELECT stockid FROM tempstocklist) /*Hope Error is here*/
AND stockstatus=2 and storeid=src
ORDER BY stockid ASC,lastmodified ASC,qty ASC LIMIT 1;
SELECT IFNULL(SUM(quantity),0) INTO @tqty FROM tempstocklist;
END WHILE;
END IF;
SELECT IFNULL(SUM(quantity),0)
INTO @remstock
FROM tempstocklist;
SET @qty=0;
WHILE ABS(tempqty)<nqty DO
SELECT itemid
INTO @itemid
FROM setitemunit
WHERE itemunitid=itemunit; -- Fetch ItemID
SELECT stockid,
quantity
INTO trstockid,
trqty
FROM tempstocklist
ORDER BY stockid ASC,
quantity ASC LIMIT 1; -- Get stockid and qty
SET tempqty=tempqty+trqty;
IF ABS(tempqty)>nqty THEN
SET @temp=tempqty-nqty;
SET trqty=trqty-@temp;
END IF;
INSERT INTO stocktransfer(
transferid,
stockid,
itemid,
itemunitid,
qty,
transferdate,
fromstoreid,
tostoreid,
transferremark,
transfermode,
transfertype,
`status`)
VALUES((SELECT fngetnextid('transferid')),
trstockid,
@itemid,
itemunit,
trqty,
(SELECT FnGetTransDate()),
src,
dest,
rmark,
trmode,
(SELECT FnGetStockTransTypeMode(TRMODE,1)),
3
);
SELECT voucherno
INTO vrno
FROM purchasestock
WHERE stockid=trstockid;
CALL SpUpdateStockRegister(@itemid,itemunit,ABS(trqty),2,(SELECT FnGetTransDate()),1,vrno,(SELECT FnGetCurrentID('transferid')),src,@regid);
CALL SpInsertStockLink(src,trstockid,@regid,ABS(trqty),8,2);
CALL SpUpdateStockRegister(@itemid,itemunit,ABS(trqty),1,(SELECT FnGetTransDate()),1,vrno,(SELECT FnGetCurrentID('transferid')),dest,@regid);
CALL SpInsertStockLink(dest,trstockid,@regid,ABS(trqty),8,1);
END WHILE;
-- Insert TransationLOg
END IF;
-- DROP TEMPORARY TABLE IF EXISTS tempstocklist;
END$$
DELIMITER ;
[17 Dec 2014 8:55]
Chiranjeevi Battula
Hello Akshay Mohan MK, Thank you for the bug report. I tried to reproduce the issue at my end using Visual Studio 2013 (VB.Net), MySQL Connector/Net 6.9.5 but Couldn't trace out any issue in debug a store procedure and please find out screenshot. Could you please list out the steps you tried out at your end? Please provide repeatable test case in order for us to confirm this issue at our end. Thanks, Chiranjeevi.
[17 Dec 2014 8:57]
Chiranjeevi Battula
Store Procedure debug screenshot
Attachment: Debug.PNG (image/png, text), 62.03 KiB.
[17 Dec 2014 8:57]
Chiranjeevi Battula
please refer the link http://dev.mysql.com/doc/connector-net/en/connector-net-visual-studio-debugger.html
[18 Jan 2015 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
