Solved

SP in MySql for login credentials

Posted on 2009-05-17
15
369 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
  • 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
 

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 250 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now