0

I am having an issue with making a MySql Routine.

Here is the code:

DELIMITER '$$';
CREATE PROCEDURE User.Login(IN UserName CHAR(32), IN PWord CHAR(32))
BEGIN
Select `userID`, `userType`, `userMainEmail`, `groupID`
    From `at_users`
    Where `userName` = UserName And `userPass` = PWord
    LIMIT 1
FOR UPDATE;

    Update `at_users`
    Set `lastLoginAt` = now()
    Where `userName` = UserName And `userPass` = PWord;
END$$

What it's supposed to do is a simple login task that's done just about everywhere... get a users record, then update the table with now()

What I am getting on creating this is:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your     MySQL server version for the right syntax to use near '' at line 12

Line 12 is the line right before END$$

Yes, all fields and tables exist. :)

EDIT: NEW CODE

DELIMITER //;
CREATE PROCEDURE User.Login(IN UserName CHAR(32), IN PWord CHAR(32))
BEGIN
Select `userID`, `userType`, `userMainEmail`, `groupID`
    From `at_users`
    Where `userName` = UserName And `userPass` = PWord
    LIMIT 1
FOR UPDATE;

    Update `at_users`
    Set `lastLoginAt` = now()
    Where `userName` = UserName And `userPass` = PWord;
END//
DELIMITER ;

I am trying this exact code from the command line, after logging in and selecting the proper database to USE.

After the last line 'DELIMITER ;' nothing happens and I stay at the -> line

Kevin
  • 2,684
  • 6
  • 35
  • 64
  • Check the syntax: [DELIMITER](http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html). It should be `DELIMITER $$;` without quotes. – Jocelyn Oct 25 '12 at 22:04
  • p.s. I'm trying to run this through phpmyadmin SQL tab. I will try directly on the server in a little while. – Kevin Oct 26 '12 at 14:07
  • Add `DELIMITER ;` after the last line (after `END$$`) – Jocelyn Oct 26 '12 at 14:46
  • tried that too. when I do that, I get no error, but the Routine is not created – Kevin Oct 26 '12 at 15:47
  • ok. I'm attempting to do this from shell as well, and nothing is happenning. The new method I am trying is up in the question – Kevin Oct 26 '12 at 15:52
  • little update, I took out the ; from the first DELIMITER line, and put in the rest of the code. Now I am getting an error 'Unknown database `User`' – Kevin Oct 26 '12 at 15:58
  • My first comment was incorrect. It must be `DELIMITER $$` without quotes and **without semi colon**. I tried it in phpmyadmin and it works, the procedure gets created. – Jocelyn Oct 26 '12 at 16:04
  • Then remove `User.` in front of the procedure name, to create the procedure in the currently selected database. – Jocelyn Oct 26 '12 at 16:05

1 Answers1

0

Got it.

Here is the code I ended up having to use:

DELIMITER //
CREATE PROCEDURE UserLogin(IN UserName CHAR(32), IN PWord CHAR(32))
    BEGIN
        Select `userID`, `userType`, `userMainEmail`, `groupID`
        From `at_users`
        Where `userName` = UserName And `userPass` = PWord
        LIMIT 1
        FOR UPDATE;

        Update `at_users`
        Set `lastLoginAt` = now()
        Where `userName` = UserName And `userPass` = PWord;
    END//
DELIMITER ;
Kevin
  • 2,684
  • 6
  • 35
  • 64