Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

SP in MySql for login credentials

Hi experts,

       I am new to MySql.  I just want to write a stored procedure to check the
user name and passord login credentials and return the number of affected rows.
If the value is 1 i will allow the user inside the application. If the value is 0 i will not
allow the user in side the application.
0
srk1982
Asked:
srk1982
  • 7
  • 6
  • 2
1 Solution
 
tbsgadiCommented:
Hi,
The following is a stored procedure to check the user login credentials:

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

Gary
0
 
srk1982Author Commented:
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)

0
 
mahomeCommented:
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?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
srk1982Author Commented:
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)
0
 
tbsgadiCommented:
Try changing the name from GetAllUsers to something else
0
 
srk1982Author Commented:
hi tbsgadi,


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


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

DELIMITER ;
0
 
mahomeCommented:
You have to end your procedure with $$ and use ; in your procuedure

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

Open in new window

0
 
srk1982Author Commented:
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

Open in new window

0
 
mahomeCommented:
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
$$

Open in new window

0
 
mahomeCommented:
0
 
srk1982Author Commented:
Thanks
0
 
srk1982Author Commented:
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 ;

Open in new window

0
 
mahomeCommented:
Oh I made the order wrong
DoesExist bit OUT -> OUT DoesExist bit

see here: http://www.mysqltutorial.org/stored-procedures-parameters.aspx
0
 
srk1982Author Commented:
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)
0
 
mahomeCommented:
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;

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now