MySQL Stored Procedure

[JuggaloBrotha]

I've got a database with a Users table that has the fields: EmailAddress VarChar(200), PasswordHash VarChar(200), & Active TinyInt.
The PasswordHash is a SHA1() encrypted password.

What I'm trying to do is have the stored procedure accept an email address and the password, it will then return either a -1 (if the un/pass combination is bad) or the UserID value if the combination is good.

Here's what I have so far:

USE gr_jci;

DELIMITER $$

/* Create Login procedure */
DROP PROCEDURE IF EXISTS `spGetUserID`$$
CREATE PROCEDURE `spGetUserID`(IN _EmailAddress VarChar(200), IN _Password VarChar(50))
DETERMINISTIC
BEGIN
  Select (Case When u.UserID Is Null Then -1 Else u.UserID End) As UserID
  From Users u
  Where u.EmailAddress = _EmailAddress
    And u.PasswordHash = SHA1(_Password)
	And Active = 1;
END$$

DELIMITER ;

MySQL accepts that as valid sql but when I call the stored procedure I get the error:

Static analysis:

1 errors were found during analysis.

    Unrecognized keyword. (near "ON" at position 25)

MySQL said:
    #2014 - Commands out of sync; you can't run this command now

I've also tried:

USE gr_jci;

DELIMITER $$

/* Create Login procedure */
DROP PROCEDURE IF EXISTS `spGetUserID`$$
CREATE PROCEDURE `spGetUserID`(IN _EmailAddress VarChar(200), IN _Password VarChar(50))
DETERMINISTIC
BEGIN
  Select If(u.UserID Is Null, -1, u.UserID) As UserID
  From Users u
  Where u.EmailAddress = _EmailAddress
    And u.PasswordHash = SHA1(_Password)
	And Active = 1;
END$$

DELIMITER ;

As well as:

USE gr_jci;

DELIMITER $$

/* Create Login procedure */
DROP PROCEDURE IF EXISTS `spGetUserID`$$
CREATE PROCEDURE `spGetUserID`(IN _EmailAddress VarChar(200), IN _Password VarChar(50))
DETERMINISTIC
BEGIN
  Select IFNULL(u.UserID, -1) As UserID
  From Users u
  Where u.EmailAddress = _EmailAddress
    And u.PasswordHash = SHA1(_Password)
	And Active = 1;
END$$

DELIMITER ;

But both return the same error message.

Anyone have any idea why or have any suggestions on how to return either a -1 or the UserID integer in the record?

[techgnome]

There's actually a flaw with your logic in the first place... u.UserID won't be null if there is no matching row... in order for NULL to be returned, there would have to be a row. Sounds counter intuitive but it's the difference between getting 1 row(s) returned and 0 row(s) returned...

consider this snippet:

create table Users (ID int, UserName nvarchar(50), [Password] nvarchar(50), EmailAddress nvarchar(50))
go
insert into Users (ID, Username, [PAssword], EmailAddress) values (1, 'techgnome', 'foo', '[email protected]')
go

select ID from Users where Password = 'foo' and EmailAddress ='[email protected]'
select ID from Users where Password = 'foobar' and EmailAddress ='[email protected]'
go

drop table Users

Gives me this result:

(1 row(s) affected)
ID
-----------
1

(1 row(s) affected)

ID
-----------

(0 row(s) affected)

You can see I get nothing, not even a null for ID when there's no match...


And even when I add IsNull (I'm using SQL Server, but it should still apply) I still don't get the -1

create table Users (ID int, UserName nvarchar(50), [Password] nvarchar(50), EmailAddress nvarchar(50))
go
insert into Users (ID, Username, [PAssword], EmailAddress) values (1, 'techgnome', 'foo', '[email protected]')
go

select ID from Users where Password = 'foo' and EmailAddress ='[email protected]'
select ID from Users where Password = 'foobar' and EmailAddress ='[email protected]'
go

select isnull(ID, -1), 'is found' from Users where Password = 'foo' and EmailAddress ='[email protected]'
select isnull(ID, -1), 'is not found' from Users where Password = 'foobar' and EmailAddress ='[email protected]'
go

drop table Users

Results:

(1 row(s) affected)
ID
-----------
1

(1 row(s) affected)

ID
-----------

(0 row(s) affected)

            
----------- --------
1           is found

(1 row(s) affected)

            
----------- ------------

(0 row(s) affected)

What seems to work is to union all the selection with a dummy record ... making the dummy record last, and then taking the top 1:

create table Users (ID int, UserName nvarchar(50), [Password] nvarchar(50), EmailAddress nvarchar(50))
go
insert into Users (ID, Username, [PAssword], EmailAddress) values (1, 'techgnome', 'foo', '[email protected]')
go

select top 1 ID, Status from (
select ID, 'Found' as [Status] from Users where Password = 'foo' and EmailAddress ='[email protected]'
union all
select -1 as ID, 'Not found'
) D

select top 1 ID, Status from (
select ID, 'Found' as [Status] from Users where Password = 'phoo' and EmailAddress ='[email protected]'
union all
select -1 as ID, 'Not found'
) D

go


drop table Users

Yields the following resultS:

(1 row(s) affected)
ID          Status
----------- ---------
1           Found

(1 row(s) affected)

ID          Status
----------- ---------
-1          Not found

(1 row(s) affected)

Not sure if it solves the issue at hand, then again it might...

-tg

[JuggaloBrotha]

Oh that's a good point, no records means there would be no null column and if I had a record I would have a value to use anyways.

I tried a few various things with setting a variable then selecting on that variable using the ifnull() function to return -1 but I still kept getting the "Commands out of sync" error, I'm starting to wonder if MySQL doesn't have anything useful you can even do with stored procedures, but pressing on I'm trying to get something to work using the Union clause you mentioned. When I use this query it works just fine:

USE gr_jci;
Select usr.UserID From (
  Select u.UserID
  From Users u
  Where u.EmailAddress = 'email address'
    And u.PasswordHash = SHA1('password here') 
    And u.Active = 1
  Union
  Select -1 As 'UserID'
) usr
Order By usr.UserID DESC
Limit 1

But when I put it into a Stored Procedure:

USE gr_jci;

DELIMITER $$

/* Get the UserID (or -1) for the EmailAddress/Password combination */
DROP PROCEDURE IF EXISTS `spGetUserID`$$
CREATE PROCEDURE `spGetUserID`(IN _EmailAddress VarChar(200), IN _Password VarChar(50))
DETERMINISTIC
BEGIN
  Select usr.UserID From (
    Select u.UserID
    From Users u
    Where u.EmailAddress = _EmailAddress
      And u.PasswordHash = SHA1(_Password) 
      And u.Active = 1
    Union
    Select -1 As 'UserID'
  ) usr
  Order By usr.UserID DESC
  Limit 1
END$$

DELIMITER ;

I'm getting the error:

MySQL said:
#1064 - 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' at line 15

I'm at a loss to why this would cause a syntax error, any thoughts?

[techgnome]

Initially, no... I haven't had the chance to use sprocs with MySQL ... so I googled it... something I noticed in common with the examples I found that varied from your posted code... on the line just prior to the END$$ it is ended with a ;

Well, you had it on the earlier ones, but not on the most recent:

USE gr_jci;

DELIMITER $$

/* Get the UserID (or -1) for the EmailAddress/Password combination */
DROP PROCEDURE IF EXISTS `spGetUserID`$$
CREATE PROCEDURE `spGetUserID`(IN _EmailAddress VarChar(200), IN _Password VarChar(50))
DETERMINISTIC
BEGIN
  Select usr.UserID From (
    Select u.UserID
    From Users u
    Where u.EmailAddress = _EmailAddress
      And u.PasswordHash = SHA1(_Password) 
      And u.Active = 1
    Union
    Select -1 As 'UserID'
  ) usr
  Order By usr.UserID DESC
  Limit 1;
END$$

DELIMITER ;

which might explain why the error is "near 'END' at line 15"...
Just a guess.

-tg

[JuggaloBrotha]

I ended up adding the semicolon to it and it accepted the query in the procedure, when I tried calling the procedure I got the "Command out of sync" error again. No clue why it would work as a standalone query but not inside a stored procedure.

I'm doing this using xampp on my Win7 computer, I downloaded and installed it just yesterday, so everything should be the newest version.

[techgnome]

Sorry... no clue. My experience with sprocs is mostly limited to SQL Server, and the "just work" ... sprocs in MySQL are mostly a newer addition, and I've never really found a need for them before.

-tg

[techgnome]

IT seems this is a common problem:
https://www.google.com/webhp?sourcei...20sync&es_th=1


This thread though I think had the best explanation: http://stackoverflow.com/questions/1...rocedure-mysql

-tg

[techgnome]

This "Bug" report seems to back that up - https://bugs.mysql.com/bug.php?id=24783

It seems the answer is "works as designed"...

-tg

[make me rain]

I have gave it a try
i am using MySQL Version 5.6.23

i have created the table like one here

ID	int(6)	NOT NULL
UserName	varchar(25)	NOT NULL
Pwd	varchar(50)	NOT NULL
EmailAddress	VARCHAR(50)	NOT NULL
Active	Tinyint	NOT NULL

[make me rain]

And created a stored function to return the query match

CREATE DEFINER = `Admin`@`myhost` FUNCTION `spGetUserID`(Ip_EmailId VARCHAR(50) , 
Ip_UserName VARCHAR(50) , 
Ip_Pwd VARCHAR(50))
 
 
 RETURNS SMALLINT
BEGIN
 
 
    # parameters '[email protected]' , 'techgnome' , 'foo' 
 
 
DECLARE Op_UserId SMALLINT ;
 
 
SELECT users.ID INTO Op_UserId
FROM
users
WHERE
users.EmailAddress = Ip_EmailId 
AND
users.UserName = Ip_UserName 
AND
users.Pwd = SHA1(Ip_Pwd) 
AND
users.Active = 1;
 
 
 
 
RETURN IFNULL(Op_UserId,-1) ;
 
 
END;

the function works as expected , i haven't got any error in this case

[JuggaloBrotha]

I actually found out the issue wasn't with stored procedures in MySQL but rather that phpMyAdmin is a terrible tool to use for testing queries and stored procedures. I put this sp into my database:

USE gr_jci;

DELIMITER $$

/* Get the UserID (or -1) for the EmailAddress/Password combination */
DROP PROCEDURE IF EXISTS `spGetUserID`$$
CREATE PROCEDURE `spGetUserID`(IN _EmailAddress VarChar(200), IN _Password VarChar(50))
DETERMINISTIC
BEGIN
  Declare _UserID Int;

  Select u.UserID Into _UserID
  From Users u
  Where u.EmailAddress = _EmailAddress
    And u.PasswordHash = SHA1(_Password) 
    And u.Active = 1;
	
  Select IfNull(_UserID, -1) As 'UserID';
END$$

DELIMITER ;

And granted it gives me the "Commands out of sync; you can't run this command now" error message, but when I run it using the "mysql.exe -u root" in the xampp\mysql\bin folder it gives me the -1 or actual UserID values correctly.

Why it doesn't work in phpMyAdmin I have no clue, but I'm no longer going to use it for this project as it has other issues too.

[techgnome]

Ha! Yeah, I try to avoid using phpMyAdmin if I can help it. It's OK for basic queries but that's about it.

-tg