Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SP in MySql for login credentials

Posted on 2009-05-17
15
Medium Priority
?
378 Views
Last Modified: 2012-05-07
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
Comment
Question by:srk1982
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24409363
Hi,
The following is a stored procedure to check the user login credentials:

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

Gary
0
 

Author Comment

by:srk1982
ID: 24409437
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
 
LVL 10

Expert Comment

by:mahome
ID: 24409689
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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

Author Comment

by:srk1982
ID: 24409723
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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24409760
Try changing the name from GetAllUsers to something else
0
 

Author Comment

by:srk1982
ID: 24409771
hi tbsgadi,


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


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

DELIMITER ;
0
 
LVL 10

Accepted Solution

by:
mahome earned 1000 total points
ID: 24409835
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
 

Author Comment

by:srk1982
ID: 24410015
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
 
LVL 10

Expert Comment

by:mahome
ID: 24410108
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
 
LVL 10

Expert Comment

by:mahome
ID: 24410113
0
 

Author Closing Comment

by:srk1982
ID: 31582484
Thanks
0
 

Author Comment

by:srk1982
ID: 24410151
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
 
LVL 10

Expert Comment

by:mahome
ID: 24410177
Oh I made the order wrong
DoesExist bit OUT -> OUT DoesExist bit

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

Author Comment

by:srk1982
ID: 24410209
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
 
LVL 10

Expert Comment

by:mahome
ID: 24410292
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question