MySQL stored procedure output parameters

[GuardianOfTheFlame]

Hi all,
I'm using a MySQL database that contains some stored procedures. Some of them have output parameters.
How can I call those sproc?
I've tried using mysqli, but I cannot understand how to call a procedure that execute a SELECT statement and use 2 output parameters. :-|
i.e. how can I execute this in PHP:
call sp_GetUsers('ProjectName',@ErrorNumber, @ErrorMessage)
(it returns a list of users and 2 output parameters used for logical error such as 'Invalid Project Name')

I hope someone can help me ':-|

Thanks!
Salo

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)

 

[Foamcow]

I'm not up on stored procs, but don't you just execute a query containing your call?

For example

$q = "call sp_GetUsers('ProjectName',@ErrorNumber, @ErrorMessage)";
$r = mysql_query($q);
$data = mysql_fetch_assoc($r);

Actually I'm guessing really

--
Tek-Tips Forums is Member Supported. Click Here to donate

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[GuardianOfTheFlame]

executing a query containing my call, I have the result of the SELECT statement of users, but I also want the informations stored in the output parameters (@ErrorNumber, @ErrorMessage) that are not in the select result.

For example using .NET, you have a parameter's collection and the recordset that contains the SELECT statement result.

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)

 

[Foamcow]

I think you can get the output parameters from a mysql session variable.

so you would do another select like

SELECT @ErrorNumber

http://lists.mysql.com/mysql/203544

--
Tek-Tips Forums is Member Supported. Click Here to donate

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[jpadie]

would you not do this the normal way?

mysql_query = 'call sp_GetUsers('ProjectName',@ErrorNumber, @ErrorMessage)';
$result = mysql_query('select @ErrorNumber, @ErrorMessage');
$row = mysql_fetch_assoc($result);
print_r($row);

 

[Foamcow]

That's what I was trying to say, and failing lol

--
Tek-Tips Forums is Member Supported. Click Here to donate

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[GuardianOfTheFlame]

I didn't thougth to select the session variable :-|
In this way I have to execute 2 queries, but if it works then it's ok...
so I try this:

$sql = "call sp_logon('$User',md5('$Pwd'), @Logged, @AsActideUser)";
$mysqli = new mysqli($this->host, $this->user, $this->pwd, ACTIDE_DB);
if($result=$mysqli->query($sql)) {
while ($row = $result->fetch_row()) {
print_r($row);
echo "<br><br>";
}
}
if($result=$mysqli->query("SELECT @Logged")) {
while ($row = $result->fetch_row()) {
echo "<br>Logged: $row[0] <br>";
$Logged=$row[0];
}
}
$mysqli->close();

but the second query return no result. is it a problem of session?

Thanks to all!
Salo

---

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)

 

[jpadie]

does your stored procedure return a result set?

 

[GuardianOfTheFlame]

yes, it's show me the projects created by the specified user.

The first print_r shows me the resultset, while the second print_r isn't executed 'cause

$mysqli->query("SELECT @Logged")

return no result.

---

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)

 

[jpadie]

can you post your stored procedure? if it is too mysqlish then i'll direct you to the mysql forum.

 

[GuardianOfTheFlame]

ok this is my stored procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `actide`.`sp_logon`$$

CREATE PROCEDURE `actide`.`sp_logon`(IN UserName VARCHAR(255), IN Pwd VARCHAR(255), OUT Logged TINYINT(1), OUT AsActideUser TINYINT(1))
    COMMENT 'log the user'
BEGIN
	DECLARE user_id INT(11);
	SET @user_id = -1;
	SET Logged=0;
	SET AsActideUser=0;
	# check if user and password exist in the actide_user table #
	SELECT id FROM actide.actide_users AS au WHERE au.user_name=UserName AND au.password=Pwd INTO @user_id;
	IF @user_id<>-1 THEN
		SET Logged=1;
		SET AsActideUser=1;
	ELSE
		# check if user and password exist in the project_users table #
		SELECT id FROM actide.project_users AS pu WHERE pu.user_name=UserName AND pu.password=Pwd INTO @user_id;
		IF @user_id<>-1 THEN
			SET Logged=1;
			SET AsActideUser=0;
		END IF;
	END IF;
	# return the list of projects that the user can work with #
	SELECT * FROM actide.projects AS p, actide.user_project AS up WHERE up.user_id=@user_id AND up.project_id=p.id;
END$$

DELIMITER ;

I think that I make some errors in php code, because if I execute this statements:

CALL actide.sp_logon('MyUser', md5('MyPass'), @Logged, @AsActideUser);
SELECT @Logged, @AsActideUser;

it's all ok: the CALL return the list of projects, and the second statement return the correct values of the variables.

---

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)

 

[jpadie]

great to hear it's working ok now.

 

[GuardianOfTheFlame]

it's ok the test using MySQL, but executing the statements in PHP code, this SQL query return no result:

SELECT @Logged, @AsActideUser;

I'm a newbie in this, is it possible that this code:

$sql = "call sp_logon('$User',md5('$Pwd'), @Logged, @AsActideUser)";
$mysqli = new mysqli($this->host, $this->user, $this->pwd, ACTIDE_DB);
if($result=$mysqli->query($sql)) {
    while ($row = $result->fetch_row()) {
        print_r($row);
        echo "<br><br>";
         }
}

and this code:

if($result=$mysqli->query("SELECT @Logged")) {
    while ($row = $result->fetch_row()) {
        echo "<br>Logged: $row[0] <br>";
        $Logged=$row[0];
        }
}
$mysq

use different MySQL sessions so they don't see the same session variables?
If so, how can I do?

Thanks again,
Salo


---

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)