How to Set Variables and Process Variable for MySQL in a Perl Script

Question

HERE IS MY TABLE EXAMPLE:

Id  |   Time     |  Predicted |   Actual |   High
----+------------+------------+----------+---------
1   |   01:00:00 |  100       |    100   |    NULL
2   |   02:00:00 |  200       |    50    |    NULL
3   |   03:00:00 |  150       |    100   |    NULL
4   |   04:00:00 |  180       |    80    |    NULL

I want to find highest value in Predicted and place it in the 'High' column (IN A SPECIFIC ROW)

========= USING THE FOLLOWING SYNTAX I AM ABLE TO ACHIEVE THIS MANUALLY IN SQL WITH THE FOLLOWING:

SET @peak=(SELECT MAX(Predicted) FROM table);

UPDATE table SET Peak=@peak WHERE Id='1';

Id  |  Time      |  Predicted |    Actual |   High
----+------------+------------+-----------+---------
1   |   01:00:00 |  100       |    100    |   200
2   |   02:00:00 |  200       |    50     |   NULL
3   |   03:00:00 |  150       |    100    |   NULL
4   |   04:00:00 |  180       |    80     |   NULL

=======================================

However, when I attempt to use the above syntax in a Perl script it fails due to the '@" or any variable symbol. Here is the Perl syntax I attempted to overcome the variable issue with no real favourable results. This is true even when placing the @peak variable in the 'execute(@peak) with ? in the pre-leading syntax' parameter:

my $Id_var= '1';
my $sth = $dbh->prepare( 'set @peak  = (SELECT MAX(Predicted) FROM table)' );
my $sti = $dbh->prepare ( "UPDATE table SET Peak = @peak  WHERE Id = ? " );
$sth->execute;
$sth->finish();
$sti->execute('$Id_var');
$sti->finish();
$dbh->commit or die $DBI::errstr;

With the following error: Global symbol "@peak" requires explicit package name

I would appreciate any help to get this working within my Perl script.

You shouldn't be calling finish after your execute statements. Just about the only time you need to call finish is if you only want to fetch a subset of the results returned by a SELECT. See the documentation for details. — ThisSuitIsBlackNot
– ThisSuitIsBlackNot, Commented Oct 28, 2014 at 6:35
More importantly, the variable $Id_var is in single quotes, so its value won't be interpolated. That means your query will only update rows where Id is the literal string $Id_var, which I doubt is what you want. You can leave off the quotes (you've used a placeholder, so it will quote the value automatically if necessary): $sti->execute($Id_var); — ThisSuitIsBlackNot
– ThisSuitIsBlackNot, Commented Oct 28, 2014 at 6:38
Why did you switch from single quotes on the select statement to double quotes on the update statement? If you have stuck with single quotes then it would have just worked. — Dave Cross
– Dave Cross, Commented Oct 28, 2014 at 9:34

harvey · Accepted Answer · 2014-10-28 03:37:18Z

2

You need to escape the @ symbal (which denotes an array variable) or use single quotes, eg

my $sti = $dbh->prepare ( "UPDATE table SET Peak = \@peak WHE...

Or, use a single quote

my $sti = $dbh->prepare ( 'UPDATE table SET Peak = @peak WHE...

answered Oct 28, 2014 at 3:37

harvey

2,95312 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kim Ryan · Accepted Answer · 2014-10-28 03:35:06Z

1

Perl sees @peak as an array. Try referring to it as \@peak. The back slash means interpret next character literally.

answered Oct 28, 2014 at 3:35

Kim Ryan

5151 gold badge3 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to Set Variables and Process Variable for MySQL in a Perl Script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related