SP in MySql for login credentials

Hi,
The following is a stored procedure to check the user login credentials:

http://www.treenode.net/?p=3

Gary

Hi tbsgadi,

i tried that already...

i am getting error for that too...

Error Code : 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 4
(0 ms taken)

Error Code : 1054
Unknown column 'input_password' in 'field list'
(0 ms taken)

Error Code : 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 'SELECT userID FROM userTBL WHERE username = , @username,  AND
      ' at line 1
(0 ms taken)

Error Code : 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 1
(0 ms taken)

Error Code : 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 'NULL' at line 1
(0 ms taken)

Error Code : 1243
Unknown prepared statement handler (complete_sql) given to EXECUTE
(0 ms taken)

Error Code : 1243
Unknown prepared statement handler (complete_sql) given to DEALLOCATE PREPARE
(0 ms taken)

Error Code : 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 'END$$' at line 1
(0 ms taken)

Did you execute the command
DELIMETER $$
before?

I had to change the quotes, where not the following ", after I copied it out the page.

Did you create the userTBL with the necessary columns?

Hi mahome,

 I created the user table and the query is working when it is executed normally.
But when i put that in sp...i am getting problem.

Now i tried this..

_________________________________
DELIMITER $$
CREATE PROCEDURE sp_GetAllUsers()
    BEGIN
      select username from tbl_users      
    END

DELIMITER ;
_________________________________


Still getting the error...am i missing something ?

Error Code : 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 'DELIMITER' at line 5
(0 ms taken)

Try changing the name from GetAllUsers to something else

hi tbsgadi,


Thanks for your effort..
Tried with this...same error :(


DELIMITER $$
CREATE PROCEDURE Test()
    BEGIN
      select username from tbl_users      
    END

DELIMITER ;

Oh thats really a slap for the begginer[ME]. Thanks for your effort MAHOME.
Not getting any errors now.

Can u please tell me the changes for the below ms sql sp.
i want that to be in mysql. what are the changes in syntax?

Thanks a lot...

CREATE PROCEDURE sp_LoginCheck
 
@UserName varchar(50),
@Password varchar(50),
@DoesExist bit OUTPUT
 
AS
 
SET @DoesExist = 0; -- set to false initially, we'll assume for now it doesn't exist
 
IF(SELECT COUNT(Uid) FROM tbl_Users 
WHERE UserName = @UserName and 
Password=@Password) > 0
 
     SET @DoesExist = 1;  -- more then one returned, return true
 
RETURN @DoesExist
GO

Select allOpen in new window

Try this:

CREATE PROCEDURE sp_LoginCheck( 
UserName varchar(50),
Password varchar(50),
DoesExist bit OUTPUT )
 
BEGIN
SET DoesExist = 0; -- set to false initially, we'll assume for now it doesn't exist
 
declare userCount INT;
 
SELECT COUNT(Uid) FROM tbl_Users 
INTO userCount
WHERE UserName = UserName and 
Password=Password;
 
IF userCount > 0 THEN 
     SET DoesExist = 1;  -- more then one returned, return true
END IF;
 
END
$$

Select allOpen in new window

Here is a good tutorial: http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

Thanks

Hi Manhome,

i tried the below sp


i am getting this error...

Error Code : 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 'out )
 
BEGIN
SET DoesExist = 0; -- set to false initially, we'll assume for now' at line 4
(0 ms taken)

DELIMITER $$
 
CREATE PROCEDURE sp_LoginCheck( 
UserName varchar(50),
Password varchar(50),
DoesExist bit OUT )
 
BEGIN
SET DoesExist = 0; -- set to false initially, we'll assume for now it doesn't exist
 
declare userCount INT;
 
SELECT COUNT(Uid) FROM tbl_Users 
INTO userCount
WHERE UserName = UserName and 
Password=Password;
 
IF userCount > 0 THEN 
     SET DoesExist = 1;  -- more then one returned, return true
END IF;
 
END
$$
DELIMITER ;

Select allOpen in new window

Oh I made the order wrong
DoesExist bit OUT -> OUT DoesExist bit

see here: http://www.mysqltutorial.org/stored-procedures-parameters.aspx

Hi Manhome,

Even for delaring a variable there is error, I tried changing it to :

declare userCount INT default 0;

still getting the error :

Error Code : 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 'declare userCount INT default 0;
 
SELECT COUNT(Uid) FROM tbl_Users
INTO userCo' at line 9
(0 ms taken)

Sorry I can't write code that works directly:

Again wrong order: INTO has to be before from.

SELECT COUNT(Uid) 
INTO userCount
FROM tbl_Users
WHERE UserName = UserName and
Password=Password;

Select allOpen in new window