Perl DBI execute not maintaining MySQL stored procedure results
- by David Dolphin
I'm having a problem with executing a stored procedure from Perl (using the DBI Module). If I execute a simple SELECT * FROM table there are no problems.
The SQL code is:
DROP FUNCTION IF EXISTS update_current_stock_price;
DELIMITER |
CREATE FUNCTION update_current_stock_price (symbolIN VARCHAR(20), nameIN VARCHAR(150), currentPriceIN DECIMAL(10,2), currentPriceTimeIN DATETIME)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE outID INT;
SELECT id INTO outID FROM mydb449.app_stocks WHERE symbol = symbolIN;
IF outID 0 THEN
UPDATE mydb449.app_stocks
SET currentPrice = currentPriceIN, currentPriceTime = currentPriceTimeIN
WHERE id = outID;
ELSE
INSERT INTO mydb449.app_stocks
(symbol, name, currentPrice, currentPriceTime)
VALUES (symbolIN, nameIN, currentPriceIN, currentPriceTimeIN);
SELECT LAST_INSERT_ID() INTO outID;
END IF;
RETURN outID;
END|
DELIMITER ;
The Perl code snip is:
$sql = "select update_current_stock_price('$csv_result[0]', '$csv_result[1]', '$csv_result[2]', '$currentDateTime') as `id`;";
My::Extra::StandardLog("SQL being used: ".$sql);
my $query_handle = $dbh-prepare($sql);
$query_handle-execute();
$query_handle-bind_columns(\$returnID);
$query_handle-fetch();
If I execute select update_current_stock_price('aapl', 'Apple Corp', '264.4', '2010-03-17 00:00:00') asid; using the mysql CLI client it executes the stored function correctly and returns an existing ID, or the new ID.
However, the Perl will only return a new ID, (incrementing by 1 on each run). It also doesn't store the result in the database. It looks like it's executing a DELETE on the new id just after the update_current_stock_price function is run.
Any help? Does Perl do anything funky to procedures I should know about?
Before you ask, I don't have access to binary logging, sorry
