[SOLVED] Using Mysql stored procedure from PHP

[Barand]

I've at last got around to upgrading to MySql 5, stored procedures at last!.

Having created and tested a simple SELECT procedure using MySql's Query Browser tool I'm now trying to call it from php.

I've tried
[code]$res = mysql_query ("call getBaaGridData('S')") or die (mysql_error());[/code]

which gives error "PROCEDURE test.getBaaGridData can't return a result set in the given context"

Anyone know how to get a result set back from a MySql stored procedure?

The procedure definition
[code]DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`getBaaGridData`$$
CREATE PROCEDURE `getBaaGridData`(IN parea varchar(5))
BEGIN
    SELECT * FROM baagriddata
    WHERE area = parea;
END$$

DELIMITER ;[/code]

[Barand]

nm. Looks like PHP4 users are out of luck, but MySqli users can do it

[code]<?php
$db = new mysqli("localhost","","","test");
$db->multi_query ("CALL getBaaGridData('S')");
$res = $db->store_result();
echo '<pre>';
while ($row = $res->fetch_row()) {
    vprintf ('%-12s%-20s%6d%12.2f%3s%5d<br>',$row);
}
echo '</pre>';
?>[/code]

However, v4 users are able to use functions and stored procedures with input and output parameters. Just stored procedures that grab a result set seem to be a no-no.

[jsimmons]

[quote author=Barand link=topic=105741.msg423465#msg423465 date=1156787782]
However, v4 users are able to use functions and stored procedures with input and output parameters. Just [b]stored procedures that grab a result set seem to be a no-no.[/b]
[/quote]

Well, that just plain sucks.  I'd love to move all the sql stuff out of my classes and into the db where it belongs.  :/

[Barand]

There may be a way - perhaps I'm just not casting the runes right or am uttering the wrong incantations. I've Googled and as is often the case, I can find lots pf people with the same problem but no actual solution yet.

[mwq27]

HOw could I call the procedure without PHP5?  here is the procedure:

DELIMITER //

CREATE PROCEDURE get_partner_link_id_old (IN shopper_link_code VARCHAR(255),OUT partner_code VARCHAR(255), OUT old_partner_id VARCHAR(255))

BEGIN
DECLARE OLD_ID, P_CODE VARCHAR(255);
DECLARE empty INT;
DECLARE pl_id_cur CURSOR FOR SELECT partner_link_id from shopper_link where code = 'shopper_link_code';

DECLARE p_code_cur CURSOR FOR SELECT partner_code,type from partner_link where partner_link_id = 'old_partner_id';

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET empty = 1;

SET empty = 0;
OPEN pl_id_cur;

FETCH pl_id_cur INTO OLD_ID;
IF empty = 0 THEN
CLOSE pl_id_cur;

SET old_partner_id = OLD_ID;

OPEN p_code_cur;
FETCH p_code_cur INTO P_CODE;
CLOSE p_code_cur;
SET partner_code = P_CODE;
END IF;


END;//

DELIMITER ;//

[Barand]

Found it. This was the code I was testing with

SP
[code]
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`getAreaSales`$$
CREATE PROCEDURE `getAreaSales`(
  OUT n float,
  OUT s float,
  OUT e float,
  OUT w float
  )
BEGIN
  SET n = (SELECT SUM(value) FROM baagriddata WHERE area = 'N');
  SET s = (SELECT SUM(value) FROM baagriddata WHERE area = 'S');
  SET e = (SELECT SUM(value) FROM baagriddata WHERE area = 'W');
  SET w = (SELECT SUM(value) FROM baagriddata WHERE area = 'E');

END$$

DELIMITER ;
[/code]

PHP
[code]<?php
$res = mysql_query ("CALL getAreaSales(@N,@S,@E,@W)") or die(mysql_error());
$res = mysql_query("SELECT @N as North, @S as South, @E as East, @W as West") or die(mysql_error());
$row = mysql_fetch_assoc($res);
echo '<pre>', print_r($row, true), '</pre>';
?>
[/code]