codevomit
asked on
Unwanted increment in MySql stored function.
Hi all,
I have a stored function which was causing me problems, I've wittled down my function to the offending parts, mysql seems to be incrementing by one when I UPDATE, the value I get on the next SELECT has gone up by 2 instead of one. Before you mention I should have "sends=sends+s" to increment by a value I have additional arithmetic being performed, what I'm giving you is a wittled down version which is enough to recreate the problem.
Thanks in advance.
I have a stored function which was causing me problems, I've wittled down my function to the offending parts, mysql seems to be incrementing by one when I UPDATE, the value I get on the next SELECT has gone up by 2 instead of one. Before you mention I should have "sends=sends+s" to increment by a value I have additional arithmetic being performed, what I'm giving you is a wittled down version which is enough to recreate the problem.
-- The table I'm using
CREATE TABLE IF NOT EXISTS `SentAndWraps` (
`UDID` varchar(500) NOT NULL,
`sends` int(11) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`UDID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `SentAndWraps` (`UDID`, `sends`) VALUES ('Test', 0);
-- What I run through netbeans, makes it easy to debug.
DROP FUNCTION AddWraps;
DELIMITER :
CREATE FUNCTION AddWraps(u VARCHAR(500), s INT(11), w INT(11))
RETURNS VARCHAR(500)
BEGIN
DECLARE sen INT(11) unsigned;
DECLARE rem INT(11) unsigned;
SELECT `sends` INTO sen FROM `SentAndWraps` WHERE `UDID`=u FOR UPDATE;
SET rem = sen + s;
UPDATE `SentAndWraps` SET `sends`=rem WHERE `UDID`=u; -- THIS LINE HERE INCREMENTS BY 1
RETURN CONCAT('What is retrived from DB:',sen,' what Im adding:',s,' what should be on the next retrievle:',rem);
END :
DELIMITER ;
SELECT AddWraps('Test',1,3);
Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
[code]
UPDATE `SentAndWraps` SET sends=sends+1;
[/code]
This also increments by 2;