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