[SOLVED] How to get OUT parameter from mysql stored procedure using php

[duniya]

Hi all,

 

I am using Mysql 5.0.41 and PHP5 compiled with MySqli

 

I have searched google, read mysql & PHP documentation but for some reason I cant seem to understand

how to retrieve the OUT parameter of a mysql stored procedure in PHP. Does anyone have any ideas???

 

for example I have the following stored procedure:

 

--------------------------------------------------------------------------

 

create procedure emailCount(address varchar(100), OUT counter tinyint(2))

select count(email) into counter from temp where email = address;

 

---------------------------------------------------------------------------

The store procedure is used to find how many rows in the temp table has the same email address.

 

In PHP I call the procedure

 

<?
include("db.inc.php");
$db = db::connect(); //I am using the singleton pattern for the database connection

$count = 0; 
$mail = 'email@gmail.com' ;
$db->query("call jsp_emailCount('$mail', '$count')") ;
echo $count ; 
?>

 

But the $count variable doesnt get updated.

 

I have verfied that the stored procedure does work from mysql command line.

 

Thanks in advance for any help

 

Best regards

[effigy]

See example 1723 here. If you don't want to use PDO (since you've already got mysqli setup) pass a MySQL variable as the OUT parameteter--@out, for example--and then run SELECT @out. To avoid running two select statements, why not turn your procedure into a function? SELECT same_addr_cnt('email').

[duniya]

Hi effigy,

 

Thank you for your reply.  I have had a look at the link you posted.  As you said I would prefer to use mysqli.  When you said, "pass a MySQL variable as the OUT parameteter--@out, for example--and then run SELECT @out." Did you mean I should do something like:

 

$db->query("call emailCount('$mail', '@out')") ;
$result = $db->query("Select @out") ;

 

I assume then I would need to process the $result variable to retrieve the @out value?

 

If i do it this way using mysql variable.  If two different scripts were to call this stored procedure emailCount(..) will there be two mysql variable called 'out' or would both scripts used the same 'out' variable?

 

if I was to do it as a stored function would I do it like this?

 

$result = $db->query("SELECT same_addr_cnt('email')") ;

 

Then process the $result variable?

 

I know that stored procedures and stored function differ in the sense that:

 

-  stored function can return a value whereas stored procedures can not.

-  all parameters to stored functions can only be input parameters.

 

Is there any performance of security issue between stored procedures and stored functions that I should be aware of?

 

Once again thank you for your time.

Best regards,

Duniya.

 

[effigy]

When you said, "pass a MySQL variable as the OUT parameteter--@out, for example--and then run SELECT @out." Did you mean I should do something like:

 

$db->query("call emailCount('$mail', '@out')") ;
$result = $db->query("Select @out") ;

 

I assume then I would need to process the $result variable to retrieve the @out value?

 

Right, except you don't need the single quotes around @out.

 

If i do it this way using mysql variable.  If two different scripts were to call this stored procedure emailCount(..) will there be two mysql variable called 'out' or would both scripts used the same 'out' variable?

 

Per the manual:

 

User-defined variables are connection-specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client connection are automatically freed when that client exits.

 

You should be fine since your scripts will have different connection ids.

 

if I was to do it as a stored function would I do it like this?

 

$result = $db->query("SELECT same_addr_cnt('email')") ;

 

Then process the $result variable?

 

Yes.

 

Is there any performance of security issue between stored procedures and stored functions that I should be aware of?

 

The manual uses the word "routine" to refer to stored procedures and functions, and states that...

 

Stored routines can provide improved performance because less information needs to be sent between the server and the client. The tradeoff is that this does increase the load on the database server because more of the work is done on the server side and less is done on the client (application) side. Consider this if many client machines (such as Web servers) are serviced by only one or a few database servers.

 

From a security standpoint procedures offer more controls in terms of who can execute what, but I'm not sure about functions. As long as you properly sterilize the data before passing it to the routine, you should be fine.

 

If you plan on working with these a great deal, I recommend this book.