CODEWITHSUNDEEP
mysqlperlvariables
BrianB
BrianB

Reputation: 143

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

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.

Upvotes: 3

Views: 1831

Answers (2)

harvey
harvey

Reputation: 2953

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

Upvotes: 2

Kim Ryan
Kim Ryan

Reputation: 515

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

Upvotes: 1

Related Questions