Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2011
    Location
    india- new delhi
    Posts
    31

    Unanswered: out parameter not working in mysql stored procedure

    i want to create a stored procedure which will have 2 IN and 1 OUT parameter,
    i want to pass username and password and want to get message [authenticate or not]
    my stored procedure syntax look like-

    DELIMITER $$
    DROP PROCEDURE IF EXISTS `zero`.`user_varify`$$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `user_varify`(IN user_name VARCHAR(15),IN pass_word VARCHAR(15), OUT message VARCHAR(100))
    BEGIN
    DECLARE temp_password VARCHAR(15);
    SELECT usr_pwd INTO temp_password from account_db where usr_name =user_name;
    IF temp_password = pass_word THEN
    SET message ='u r authenticated';
    END IF;
    IF temp_password != pass_word THEN
    SET message='u r not authenticated';
    END IF;
    END$$
    DELIMITER ;

    to run this i wrote
    CALL user_varify('amit','pathak',@message);
    when i try to execute it ,i got a message at the bottom of query browser like- "query cancelled'
    one thing is more when i add a line ----select message; in procedure after END IF, it is working properly, how it is happen-i want to know
    1-right syntax is select @message but it is not working ,select message; it is working why
    2-why i got the message like -"query cancelled"
    3- i m using mysql query browser 1.2.12 here, i can execute one query at same time, for second i need to delete first, is there any solution of this..
    I know i asked a lot, but really i spend my hours in this , when i failed then i posted my problem here.
    please reply me
    i got one thing more-> this is working fine in mysql text console....
    Last edited by rohitamitpathak; 03-08-11 at 07:31.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi, I am not sure why you are not using a STORED FUNCTION for this type of query. You can return a tinyint 0 => false and 1 => true.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Feb 2011
    Location
    india- new delhi
    Posts
    31
    Quote Originally Posted by it-iss.com View Post
    Hi, I am not sure why you are not using a STORED FUNCTION for this type of query. You can return a tinyint 0 => false and 1 => true.
    yes i can , but i m just a learner so i was trying - how to use stored procedure.....

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    There are several things about the script that you have written that may cause a problem. Firstly there is no exception handler defined. This can be a problem if the username does not exist.

    I have put together a mini-tutorial which can be found here.

    The better approach is to use a SELECT count(*) INTO variable. In this case you can compare both the username and password combination. If it matches then the variable will have a 1. If it does not then the variable will have the value 0.

    The IF statement also has an ELSE part. You do not need to do 2 IF statements where the second is the inverse of the first.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Feb 2011
    Location
    india- new delhi
    Posts
    31
    Quote Originally Posted by it-iss.com View Post
    There are several things about the script that you have written that may cause a problem. Firstly there is no exception handler defined. This can be a problem if the username does not exist.

    I have put together a mini-tutorial which can be found here.

    The better approach is to use a SELECT count(*) INTO variable. In this case you can compare both the username and password combination. If it matches then the variable will have a 1. If it does not then the variable will have the value 0.

    The IF statement also has an ELSE part. You do not need to do 2 IF statements where the second is the inverse of the first.
    good morning sir,
    u are right, this is a right code for that kind of problem..... i think you add this page over ur website for me.. u r so kind sir... thank you so much sir .....sir one thing is more, i wondered a lot and i found that to save date kind of value in database there are only one format in mysql yyyy/mm/dd , u r bound to save the value in this format but u can change into other format when u retrieve from database as u require , for this there are some mysql functions... is it true? or i need to work more on this topic

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    When INSERTing into the database you can use the function STR_TO_DATE function as follows:

    Code:
    STR_TO_DATE('01,5,2013','%d,%m,%Y')
    If you want to convert the date into a string you should use the DATE_FORMAT function as follows:

    Code:
    DATE_FORMAT(now(), '%d-%m-%Y')
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •