Executing procedural queries in perl
- by KalpaG
I have a query like this
set @valid_total:=0;
set @invalid_total:=0;
select week as weekno, measured_week,project_id as project,
role_category_id as role_category,
valid_count,valid_tickets,
(@valid_total := @valid_total + valid_count) as valid_total,
invalid_count,invalid_tickets,
(@invalid_total := @invalid_total + invalid_count) as invalid_total
from metric_fault_bug_project
where measured_week = yearweek(curdate())
and role_category_id = 1 and project_id = 11;
it executes fine in heidi (MySQL client) but when it comes to perl, it gives me this error
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the m
anual that corresponds to your MySQL server version for the right syntax to use
near ':=0;
set :=0;
select week as weekno, measured_week,project_id
as project' at line 1 at D:\Mx\scripts\test.pl line 35.
Can't execute SQL statement: You have an error in your SQL syntax; check the man
ual that corresponds to your MySQL server version for the right syntax to use ne
ar ':=0;
set :=0;
select week as weekno, measured_week,project_id
as project' at line 1
The problem seem to be in the set @valid_total := 0; line.
I am fairly new to Perl. Can anyone help?
this is the complete perl code
#!/usr/bin/perl
#use lib '/x01/home/kalpag/libs';
use DBI;
use strict;
use warnings;
my $sid = 'issues';
my $user = 'root';
my $passwd = 'kalpa';
my $connection = "DBI:mysql:database=$sid;host=localhost";
my $dbhh = DBI->connect( $connection, $user, $passwd) ||
die "Database connection not made: $DBI::errstr";
my $sql_query = 'set @valid_total:=0;
set @invalid_total:=0;
select week as weekno, measured_week,project_id,
role_category_id as role_category,
valid_count,valid_tickets,
(@valid_total := @valid_total + valid_count) as valid_total,
invalid_count,invalid_tickets,
(@invalid_total := @invalid_total + invalid_count) as invalid_total
from metric_fault_bug_project
where measured_week = yearweek(curdate())
and role_category_id = 1 and project_id = 11';
my $sth = $dbhh->prepare($sql_query) or die "Can't prepare SQL statement: $DBI::errstr\n";
$sth->execute() or die "Can't execute SQL statement: $DBI::errstr\n";
while ( my @memory = $sth->fetchrow() )
{
print "@memory \n";
}
