Solved

Mysql Stored Procedure cant return value

Posted on 2008-06-20
5
2,267 Views
Last Modified: 2013-12-12
Dear all,

here is my script,

1st Question :

CREATE DEFINER = 'root'@'localhost' PROCEDURE `HelloWorld`(out p_text VARCHAR(30))
   BEGIN
    set @p_text = 'Hello';
END;

//at PHP script
// db_query is the function to connect to DB

$res_111 = db_query("call HelloWorld(@a);", 'S');

final "$res_111" no output return; also no error.

May i know how to write it with return the output?


Question 2 :

//store PROCEDURE  code

CREATE DEFINER = 'root'@'localhost' PROCEDURE `sp_select`(IN uid INTEGER(11), OUT result VARCHAR(50))
 BEGIN            
    SELECT username from admin where admin_index =uid into @result;    
    select @result;
END;

//at PHP script
// db_query is the function to connect to DB

$res_111 = db_query("call sp_select(3,@a);", 'S');

final "$res_111" no output return; also no error.

But right will return data from DB?

Any one how to write it?

0
Comment
Question by:sonny_j81
  • 3
  • 2
5 Comments
 
LVL 20

Expert Comment

by:virmaior
ID: 21831033
try this:

CREATE DEFINER = 'root'@'localhost' PROCEDURE `sp_select`(IN uid INTEGER(11), OUT result VARCHAR(50))
 BEGIN            
    SELECT username from admin where admin_index =uid into @result;    
    RETURN @result;
END;
0
 
LVL 1

Author Comment

by:sonny_j81
ID: 21836185
Dear sir ,
At mysql only function can use return command, other i think cant support it.

still got any idea to do it

urgent


thank q
ffrom

sonny
0
 
LVL 20

Expert Comment

by:virmaior
ID: 21836235
well switch and use a function...
you are asking for something to return a value.


alternately

add a query AFTER that query

'SELECT @result'
0
 
LVL 1

Author Comment

by:sonny_j81
ID: 21836368
Dear virmaior,

can any way to return value from the store proceduce?
rather than after that query re-select the data.

if my output is array? the store proceduce can support it?

from

sonny
0
 
LVL 20

Accepted Solution

by:
virmaior earned 500 total points
ID: 21837450
By definition, a procedure does not return a value.  Only a function does.

As such, you should use a function if you plan on returning a value.  To return a set of values, you could either GROUP_CONCAT them at the end of the function or else query through them in a temporary table.

What you are wanting actually seems best-suited to a VIEW
since you want to be able to query all of the admin users at once.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

705 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

15 Experts available now in Live!

Get 1:1 Help Now