0

I have created the following stored procedure but my Mysql throws ERROR 1064. What I need to do is for every user that exists in table logbackup, to find the date of his first action, then the date of his last action and calculate the duration. Even though, I know I could do it easily in a different way, I would like some help with any syntax errors that I may have since I am new in mysql.

 EDIT:


            USE 'databasename'
            DROP procedure IF EXISTS `userEngagementCalc`;
            DELIMITER $$
            USE `databasename`$$
            CREATE DEFINER=`root`@`%localhost` PROCEDURE `userEngagementCalc`()
            CREATE PROCEDURE userEngagementCalc()
            BEGIN

            DECLARE v_username varchar(50);
            DECLARE startDate datetime;
            DECLARE endDate datetime;
            DECLARE duration int;
            DECLARE done INT DEFAULT FALSE;

            DECLARE cur1 cursor for 
                    select distinct username
                    from logbackup;
            DECLARE continue handler for not found set done=true;


                OPEN cur1;

                igmLoop: LOOP
                    fetch cur1 into v_username;
                    if done then leave igmLoop; end if;

                    set @startDate:=(select STR_TO_DATE(created_date, '%a %b %d %Y %H:%i:%s') from logbackup where username=v_username order by STR_TO_DATE(created_date, '%a %b %d %Y %H:%i:%s') asc LIMIT 1);
                    set @endDate:=(select STR_TO_DATE(created_date, '%a %b %d %Y %H:%i:%s') from logbackup where username=v_username order by STR_TO_DATE(created_date, '%a %b %d %Y %H:%i:%s') desc LIMIT 1);
                    set @duration:= TIME_TO_SEC(TIMEDIFF(@startDate,@endDate));

                    INSERT INTO  userengagement (username,startDate,endDate,duration) VALUES (v_username,@startDate,@endDate,@duration);
                END LOOP igmLoop;   

                CLOSE cur1;
            END$$
            DELIMITER;

I am trying to execute this scipt on Ubuntu mysql terminal. Does it need any special manipulation>? What I receive from mysql except from the ERROR messages is something like the following:

Display all 813 possibilities? (y or n)
?                            DAY_MINUTE                   INNODB                            MULTILINESTRINGFROMWKB       SESSION_USER                 VARIANCE
ABS                          DAY_SECOND                   INOUT                        MULTIPOINT                   SET                          VARYING
ACOS                         DEALLOCATE                   INSENSITIVE                  MULTIPOINTFROMTEXT           SHA                          VAR_POP
ACTION                       DEC

I appreciate your kind help.

user2008973
  • 325
  • 3
  • 8
  • 22
  • 1
    What line is the error coming from? – ethrbunny Nov 19 '14 at 12:26
  • Also - try `set @startDate=` - add the ampersand. – ethrbunny Nov 19 '14 at 12:29
  • It's weird, almost every line! I have also an error like Unknown column 'v_username' in 'field list' – user2008973 Nov 19 '14 at 12:30
  • @ethrbunny I guess, I have to alter also the declaration? – user2008973 Nov 19 '14 at 12:31
  • n/m - those are session vars: http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference – ethrbunny Nov 19 '14 at 12:33
  • make sure to bracket declaration with 'delimiter' calls: http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html – ethrbunny Nov 19 '14 at 12:35
  • OK what I did not mention is that I am trying to create it on mysql Ubuntu terminal. Does it need any special manipulation? Or I just execute the script the same way I execute queries? – user2008973 Nov 19 '14 at 12:43
  • The link provided by @ethrbunny is a bit misleading, as its explains how to invoke a stored procedure with the .NET driver. It does mention how a procedure must be declared though, i.e. with proper DELIMITER. [This manual page](http://dev.mysql.com/doc/refman/5.6/en/stored-programs-defining.html) is probably more relevant. – RandomSeed Nov 19 '14 at 13:14
  • @RandomSeed Yes, I know I have updated my code, though. Please, take a look, please. It's for Ubuntu Mysql terminal. But it still cannot be executed. – user2008973 Nov 19 '14 at 13:19
  • 3
    Okay, there are [TAB] characters in your script, which are interpreted as a call to the automatic code completion by your consle client (even when you copy/paste the snippet). Remove them and try again. – RandomSeed Nov 19 '14 at 13:35

0 Answers0