All geek questions in one place

Perl DBI does not save MySQL stored procedure results

I had a problem executing a stored procedure with Perl (using the DBI module). If I do a simple SELECT * FROM table , no problem.

SQL code:

  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 ;

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') as id ; using the mysql CLI client, it correctly executes the stored function and returns an existing identifier or a new identifier.

However, Perl will only return a new identifier (an increment of 1 each time it starts). It also does not save the result in the database. It appears that it executes DELETE in the new identifier immediately after the update_current_stock_price function is update_current_stock_price .

Any help? Does Perl do anything interesting for procedures that I should know about?

Before you ask, I do not have access to the binary log, sorry.

+4
source share
3 answers

Perhaps you do this in a transaction and it returns? The row is inserted, but never becomes fixed and cannot be visible.

I would try it on my dev server and enable the general query log if in doubt.

You can also learn about the INSERT ... ON DUPLICATE KEY UPDATE syntax, which can probably do what you are trying to do anyway.

+2
source

try

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

before calling bind_columns to see if it really returns results, you can also try replacing SELECT storedprocedure with SELECT * FROM storedprocedure

+1
source

You should verify that you are using the latest version of DBD :: mysql (which is the MySQL driver used by DBI). Previously, there were several problems with stored procedures, at least some of them were fixed in recent versions. Perhaps these resources are also useful:

+1
source

Source: https://habr.com/ru/post/1305160/

More articles:


All Articles