How to get mysql stored procedure out parameter value

[eluser]Arun Joshi[/eluser]
Hi I have a stored procedure which accepts one input parameter and one output parameter. How can I get the out parameter value from the sp. I am trying

Code:

$query = $this->db->query('call getCategoryCount(2,@total)');
            foreach ($query->result() as $row)
            {
                //How to get value for @total?
            }

I want to display the value for @total.

[eluser]Dam1an[/eluser]
I've never used stored proceducres in MySQL, yet along with CI
Can you not just add an alias, something like

Code:

call getCategoryCount(2,@total) AS total

[eluser]Arun Joshi[/eluser]
sorry Dam

It is not working....

[eluser]Arun Joshi[/eluser]
Any help????

[eluser]obiron2[/eluser]
What do you get if you put

Code:

$query = $this->db->query('call getCategoryCount(2,@total)')
print_r($query)

I would expect the SP to return a value which gets stored in $query and $query will always be one row with one value. In which case you could treat it just like any other query result and either return the query object or return the query as an array, or return a specific row/value.

Why would you have to specify the output parameter in the SP call. The SP should determine what it returns. The SP may not return anything but will update all sorts of database values or apply business rules. I have debated using SPs rather than functions in models,
but it adds another layer of obscurity and I think you will lose some of the useful tools such as last record ID, number of rows affected etc as this work is no longer handled by PHP and CI.

Obiron