Solved

SP in MySql for login credentials

Posted on 2009-05-17
15
374 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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
 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

617 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