×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

MySQL stored procedure output parameters

MySQL stored procedure output parameters

MySQL stored procedure output parameters

(OP)
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) winky smile

RE: MySQL stored procedure output parameters

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

For example

CODE

$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.


RE: MySQL stored procedure output parameters

(OP)
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) winky smile

RE: MySQL stored procedure output parameters

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

so you would do another select like

CODE

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.


RE: MySQL stored procedure output parameters

would you not do this the normal way?

CODE

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

RE: MySQL stored procedure output parameters

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.


RE: MySQL stored procedure output parameters

(OP)
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:

Quote:


$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) winky smile

RE: MySQL stored procedure output parameters

does your stored procedure return a result set?

RE: MySQL stored procedure output parameters

(OP)
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

Quote:

$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) winky smile

RE: MySQL stored procedure output parameters

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

RE: MySQL stored procedure output parameters

(OP)
ok this is my stored procedure:

CODE

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:

CODE

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) winky smile

RE: MySQL stored procedure output parameters

great to hear it's working ok now.

RE: MySQL stored procedure output parameters

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

Quote:

SELECT @Logged, @AsActideUser;

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

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:

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) winky smile

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close