I am struggling to call a stored procedure within another stored procedure. Like it is now the stored procedure never return the SELECT statement back as a result to the mysqli call in PHP (but it works fine in MySQL workbench).
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_bid`(IN bid_in decimal(6,2),
IN ticker_in varchar(5),
IN share_amount_in BIGINT)
BEGIN
DECLARE company_id_var INT;
DECLARE highest_bid BIT;
DECLARE generated_bid_id BIGINT;
SET company_id_var = (SELECT ID
FROM Companies
WHERE Ticker = ticker_in);
-- Put the bid into the Bids table.
INSERT INTO Bids(company_id,bid,share_amount)
VALUES (company_id_var,bid_in,share_amount_in);
SET generated_bid_id = LAST_INSERT_ID();
CALL check_available_shares(bid_in,share_amount_in,generated_bid_id,@shares_left);
-- Check if the bid is higher than the current highest bid.
-- If so update the CurrentState table to have the new value.
UPDATE CurrentState SET buyer = bid_in
WHERE ID = company_id_var
AND buyer < bid_in;
IF (ROW_COUNT() = 1)
THEN
SELECT 1 AS highest_bid, @shares_left AS shares_left, CS.buyer, CS.seller, CS.last_price, CO.name, CO.ticker
FROM CurrentState CS, Companies CO
WHERE CS.id = CO.id
AND CO.ticker = ticker_in;
ELSE
SELECT 0 AS highest_bid, @shares_left AS shares_left, CS.buyer, CS.seller, CS.last_price, CO.name, CO.ticker
FROM CurrentState CS, Companies CO
WHERE CS.id = CO.id
AND CO.ticker = ticker_in;
END IF;
END
If I remove the "CALL" function from this stored procedure it returns one of the SELECT statement at the bottom, but if I keep the "CALL" function it does not return anything.
I have tried with exec and execute but that only gives me syntax error when I try to save the procedure within MySQL workbench. This problem is related to https://stackoverflow.com/questions/23193085/mysqli-does-not-return-any-results-but-stored-procedure-does.
But it seems that that problem may be related to how mysqli handles the execution of the stored procedure since it works fine when I call the stored procedure manually in mySQL workbench(?)
After hours of banging my head against a wall I found a solution. Since this stored procedure calls another stored procedure which uses a cursor I had to use the $mysqli->multi_query() function. Here's how I got it working:
new_bid stored procedure:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_bid`(IN bid_in decimal(6,2),
IN ticker_in varchar(5),
IN share_amount_in BIGINT)
BEGIN
-- Store the company ID into a variable as this value will used more than once.
DECLARE company_id_var INT;
DECLARE ParamtoPass INT;
DECLARE highest_bid BIT;
DECLARE generated_bid_id BIGINT;
SET company_id_var = (SELECT ID
FROM Companies
WHERE Ticker = ticker_in);
-- Put the bid into the Bids table.
INSERT INTO Bids(company_id,bid,share_amount)
VALUES (company_id_var,bid_in,share_amount_in);
SET generated_bid_id = LAST_INSERT_ID();
-- EXECUTE check_available_shares bid_in, @share_amount_in,@generated_bid_id,@shares_left;
CALL check_available_shares (bid_in,@share_amount_in,generated_bid_id,@shares_left);
-- Check if the bid is higher than the current highest bid.
-- If so update the CurrentState table to have the new value.
UPDATE CurrentState SET buyer = bid_in
WHERE ID = company_id_var
AND buyer < bid_in;
IF (ROW_COUNT() = 1)
THEN
SELECT 1 AS highest_bid, @shares_left AS shares_left, CS.buyer, CS.seller, CS.last_price, CO.name, CO.ticker
FROM CurrentState CS, Companies CO
WHERE CS.id = CO.id
AND CO.ticker = ticker_in;
ELSE
SELECT 0 AS highest_bid, @shares_left AS shares_left, CS.buyer, CS.seller, CS.last_price, CO.name, CO.ticker
FROM CurrentState CS, Companies CO
WHERE CS.id = CO.id
AND CO.ticker = ticker_in;
END IF;
END
check_available_shares procedure:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `check_available_shares`(IN bid_in decimal(6,2),
IN share_amount_in int,
IN bid_id bigint,
OUT shares_left BIT)
BEGIN
DECLARE num_shares INT;
DECLARE selling_id INT;
DECLARE selling_price DECIMAL(6,2);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT share_amount,sell_price,ID
FROM ShareSell
WHERE sell_price <= bid_in
ORDER BY sell_price ASC, sell_timestamp ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur; /* open cursor */
read_loop: LOOP
FETCH NEXT FROM cur
INTO num_shares, selling_price, selling_id;
IF (done) THEN
LEAVE read_loop;
END IF;
IF ((share_amount_in - num_shares) > 0) THEN
SET share_amount_in = (share_amount_in - num_shares);
DELETE FROM ShareSell
WHERE ID = selling_id;
ELSEIF((share_amount_in - num_shares) < 0) THEN
UPDATE ShareSell
SET share_amount = (share_amount - share_amount_in)
WHERE ID = selling_id;
DELETE FROM Bids
WHERE ID = bid_id;
LEAVE read_loop;
ELSE
DELETE FROM ShareSell
WHERE ID = selling_id;
DELETE FROM Bids
WHERE ID = bid_id;
END IF;
SELECT num_shares,selling_price;
END LOOP;
CLOSE cur;
IF (share_amount_in > 0) THEN
UPDATE Bids
SET share_amount = share_amount_in
WHERE ID = bid_id;
SET shares_left = 1;
ELSE
SET shares_left = 0;
END IF;
END
And finally the PHP call:
$bid = 43.10;
$ticker = "GOS";
$share_amount = 1000;
if ($mysqli->multi_query("CALL new_bid($bid,'$ticker',$share_amount)"))
{
if ($result = $mysqli->store_result())
{
while ($row = $result->fetch_assoc())
{
print_r($row);
}
$result->close();
}
}
Thanks! This was exactly my problem too. I too broke my head on an exact similar problem (see PHP mysqli is NOT trapping some errors when calling stored procedure). Your post helped me solve my problem!
I too had a case of PHP calling SP1, which in turn called SP2, and everything working fine in a mysql client but breaking when called by PHP! And indeed, I too had had a SELECT of a result set in SP2. And my problem symptoms were exactly identical to yours, and it never struck me to use multi_query().
I notice that you also SELECT a "result set* in check_available_shares (see line SELECT num_shares,selling_price;). Not sure why you have this line. If you remove that line, I suspect you should be able to do a query() instead of multi_query().
