samedi 5 novembre 2016

MySQL query syntax error involving IF-ELSE statement

I have been trying to find the source of a syntax error I've been getting for the last little while in an SQL query on MySQL. I'm new to MySQL, so I believe I am just having a dumb moment here.. My query is as follows:

DELIMITER //
DROP PROCEDURE IF EXISTS `validateUser` // 

CREATE PROCEDURE validateUser(IN inUsername VARCHAR(50), IN inPassword VARCHAR(50))
BEGIN
  DECLARE outId INT; 
  DECLARE outLastLoginDate DATETIME;
  SELECT outId = id, outLastLoginDate = lastLoginDate FROM users WHERE username = inUsername AND password = inPassword LIMIT 1;

  IF outId IS NOT NULL THEN
    BEGIN
      UPDATE users
      SET lastLoginDate = NOW()
      WHERE id = outId
    END;
  ELSE
    BEGIN
      SELECT -1 --Invalid username/password combination
    END;
  END IF;
END//
DELIMITER ;

I am trying to make a simple procedure that checks if a username and password combination exists in a table, then returns the lastLoginDate as well as the id associated with the user. Of course, if the user is successfully verified, I would like the lastLoginDate to be updated. The problem I am having is with the IF-ELSE statement, and the error reads as follows:

ERROR 1064 (42000) at line 4 in file: 'validateUser.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END; ELSE BEGIN SELECT -1 --Invalid username/password combination ' at line 12

Aucun commentaire:

Enregistrer un commentaire