This is my first post - I am new to dbForge / MySQL
(Currently converting from MS SQL 2000)
I am able to use both Select row_count() into _result or Set _result = row_Count() statements for both "insert into my_table" and "delete from my_table" constructs.
The correct _result value is shown (in debug mode) and as returned as a OUT parameter, indicating a row has been inserted or deleted.
However when used in an "update table set a = _a, b = _b .. where c = _c" construct, while the target row is correctly updated the _result value is returned as 0.
debugging Stored Procedure Row_Count() after SQL update
The problem now appears to be sporadic. It works okay for the first go of a 'new' session. Is it something to do with the debug session / environment settings?
The text version looks like:
PROCEDURE sss.myPromote_BQH(IN _HeaderID INT, IN _BatchStatus INT, IN _LastRecd INT, OUT _Result INT)
MODIFIES SQL DATA
COMMENT 'Update the current batch header to the next level'
BEGIN
DECLARE _Found INT DEFAULT 0;
DECLARE _FirstRecd INT;
declare _Test INT default 0;
SET _Result = 0;
SELECT COUNT(*) INTO _Found
FROM bq_detail
WHERE HeaderId = _HeaderID;
START TRANSACTION;
SELECT FirstRecd INTO _FirstRecd
FROM bq_header
WHERE bq_header.HeaderID = _HeaderID
FOR UPDATE;
/* set _Test = (_LastRecd - _FirstRecd) + 1;
this should be = to _Found */
UPDATE bq_header
SET
BatchStatus = _BatchStatus,
LastRecd = _LastRecd
WHERE
bq_header.HeaderID = _HeaderID;
Select ROW_COUNT() into _Result;
COMMIT;
END
The text version looks like:
PROCEDURE sss.myPromote_BQH(IN _HeaderID INT, IN _BatchStatus INT, IN _LastRecd INT, OUT _Result INT)
MODIFIES SQL DATA
COMMENT 'Update the current batch header to the next level'
BEGIN
DECLARE _Found INT DEFAULT 0;
DECLARE _FirstRecd INT;
declare _Test INT default 0;
SET _Result = 0;
SELECT COUNT(*) INTO _Found
FROM bq_detail
WHERE HeaderId = _HeaderID;
START TRANSACTION;
SELECT FirstRecd INTO _FirstRecd
FROM bq_header
WHERE bq_header.HeaderID = _HeaderID
FOR UPDATE;
/* set _Test = (_LastRecd - _FirstRecd) + 1;
this should be = to _Found */
UPDATE bq_header
SET
BatchStatus = _BatchStatus,
LastRecd = _LastRecd
WHERE
bq_header.HeaderID = _HeaderID;
Select ROW_COUNT() into _Result;
COMMIT;
END
Thank you for your reply. I am slowly getting familar with the dbForge tool and it is working very well - this little glitch aside. Sometimes when you start out in unfamiliar territory even little unexpected things can be hard to "get".
BTW is there any preference / advantage in either construct:
SET _Result = ROW_COUT();
vs.
SELECT ROW_COUNT() INTO _Result ?
BTW is there any preference / advantage in either construct:
SET _Result = ROW_COUT();
vs.
SELECT ROW_COUNT() INTO _Result ?