Perl DBI execute not maintaining MySQL stored procedure results

Question

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:

 $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.

Answer 1

Perhaps you're doing it in a transaction and it's getting rolled back? The row is inserted but never becomes committed and cannot be seen.

I'd try it on your dev server and enable general query log, if in doubt.

Also you may want to know about the INSERT ... ON DUPLICATE KEY UPDATE syntax, which can probably do what you're trying to do anyway.

Answer 2

try

$query_handle->dump_results(15, "\n", '|');

before the bind_columns call to see if it is actually getting the results back, you could also try replace SELECT storedprocedure with SELECT * FROM storedprocedure

Answer 3

You should check that you are running the latest version of DBD::mysql (which is the MySQL-driver used by DBI). There used to be several issues with stored procedures, at least some are fixed in recent versions. Maybe these ressources are also helpful:

• http://www.perlmonks.org/?node_id=609098
• http://www.perlmonks.org/?node_id=830585