I've written a small code block (which later be used in a event in MySQL but I'm getting an error when declaring a seemingly standard INT variable:
BEGIN
DECLARE myvar INT;
SELECT TIMESTAMPDIFF INTO myvar (MINUTE,(select user_hb_stamp from eclipse_users where username = 'user1'),(SELECT NOW()));
IF (myvar > 5)
UPDATE eclipse_users SET logged=0 WHERE username = 'user1';
END
What I'm doing wrong?
Thanks in advance.
Your select statement is malformed. Try this:
SELECT myvar := TIMESTAMPDIFF(MINUTE,
(select user_hb_stamp from eclipse_users where username = 'user1'),
NOW());
Although you can use into for variables, I prefer to just set them directly -- unless you are writing code that needs to be compatible with Oracle. (And then all the function calls won't work.)
A more typical way to write this is:
SELECT myvar := TIMESTAMPDIFF(MINUTE, user_hb_stamp, NOW())
FROM eclipse_users
WHERE username = 'user1';
EDIT:
As for the declare itself, the problem is probably the lack of delimiter statement. Try:
delimiter $$
create procedure . . .
begin
declare p_myvar int;
select p_myvar := TIMESTAMPDIFF(MINUTE, user_hb_stamp, NOW())
from eclipse_users
where username = 'user1';
. . .
end$$
delimiter ;
delimiter //so that any;are not interpreted as end of code block.