Re: [SQL] Stored Procedure
Hi there, I changed a few things in your code, unfortunately untill you supply me with your error I cant give you a full explanation on what went wrong, but there are quite a few syntax errors in that snippit of code.
So lets begin, first things first you SQL query, when you want to obtain perfect matches try and avoid the "like" keyword, the way you did it is fine, by try to stick to = as if you accidentilly add a wild card character your login script would not work successfully.
Lastly the extra variable creation is unnecessary, when you have a procedure and you specify an IN or OUT variables they are already predefined for you, so all you need to do is a select into the variable and nothing else is needed, also please remember to specify the IN and OUT types, as this just allows for better readability.
Hope this helps,
Kind Regards
and lastly, the code xD
CREATE PROCEDURE CheckLogin(IN Login varchar(25), IN PASS varchar(10),OUT ReturnValue INT)
BEGIN
select count(*) INTO ReturnValue from accounts where Login = ID and Pass = PASS;
END
Re: [SQL] Stored Procedure
Thx's, I'll check it out and let ya know :P
Re: [SQL] Stored Procedure
One last addon my apologies that i missed it xD
Your IN variable Login was not matching to ID, so that just needs to be login as well, the following code snippit should work. Sorry about that
CREATE PROCEDURE CheckLogin(IN Login varchar(25), IN PASS varchar(10),OUT ReturnValue INT)
BEGIN
select count(*) INTO ReturnValue from accounts where Login = Login and Pass = PASS;
END
Im not sure on the impact of having the variable name the same as the column name, and dont have mysql with me currently to check, will check later, unless you beat me to it. If it still doesnt want to work try the following rather
CREATE PROCEDURE CheckLogin(IN szLogin varchar(25), IN szPASS varchar(10),OUT ReturnValue INT)
BEGIN
select count(*) INTO ReturnValue from accounts where Login = szLogin and Pass = szPASS;
END
Re: [SQL] Stored Procedure
Yea, first script you gave me tells me, Unknown Column 'test' in 'field list'
Edit Both say:
I input, "test, test, 0" gives me "1054 - Unknown column 'test' in 'field list'.
Edit2nd:
I tried
"
BEGIN
set Username = szLogin;
set Pwd = szPASS;
select count(*) INTO ReturnValue from accounts where Username = Login and Pwd = Pass;
END
"
Still didnt work xD
Re: [SQL] Stored Procedure
Hi, sorry again, i seem to be getting confused, the first time you posted your query was
select count(*) as ReturnVal from accounts where Login like ID and Pwd like PASS
however your latest post shows it as such :
select count(*) INTO ReturnValue from accounts where Username = Login and Pwd = Pass;
...your column names changes. Could you please do a describe (/dt accounts) for me and post the output so i can get the column names. In the where clause the first name relates to the column name in the table and the second relates to the argument being passed.
Use this one again as is :
CREATE PROCEDURE CheckLogin(IN szLogin varchar(25), IN szPASS varchar(10),OUT ReturnValue INT)
BEGIN
select count(*) INTO ReturnValue from accounts where Login=szLogin and Pass=szPASS;
END
however first run this query :
select count(*) from accounts where Login = 'test' and Pass = 'test';
And what is the output? If this causes an error either your table name or column names are incorrect. Also next time you run, can you please copy and paste the error mysql gives you.
Kind Regards, hope to hear from you soon.
Re: [SQL] Stored Procedure
Its,
Code:
select count(*) from accounts where ID = 'test' and PASS = 'test';
And It returned 1.
test & test is a real test login in the tables, and ID & PASS are the columns.
EDIT:
When I put into Parameter 1414 - OUT or INOUT argument 3 for routine ecafe. CheckLogin is not a variable or NEW pseudo-variable in BEFORE trigger.
but when I put 1054 - Unknown Column 'test' in 'field list'.
My PROCEDURE
Code:
BEGIN
Declare ReturnValue int;
set ReturnValue =0;
select count(*) INTO ReturnValue from accounts where szLogin=ID and szPASS=PASS;
END
PS, With or without Decalring and setting I still get the same issue. So I just left it there.
Re: [SQL] Stored Procedure
Just one quick question i need, are you calling this from PHP, because PHP: MySQL (PDO) - Manual says that pdo_mysql uses server side prepare statements or emulates they. But server side prepared statements do not support CALL yet: MySQL AB :: MySQL 5.1 Reference Manual :: 11.7 SQL Syntax for Prepared Statements
So if thats the case you will need to do the procedure inline in your code, I will gladly assist with this as well, also please test the proc by replacing this line of code in your procedure "select count(*) INTO ReturnValue from accounts where szLogin=ID and szPASS=PASS;" with this line of code "select count(*) from accounts where ID = 'test' and PASS = 'test';" then call it again from withing mysql, and not from withing a php page (if you are using a php page) please post all responses and outputs.
Kind Regards
Re: [SQL] Stored Procedure
c# program, I'm trying to make a login via mysql. And I got a CheckLogin script thats calling it.I think I might just do the script via c#/mysql except just mysql. xD