CODEWITHSUNDEEP
mysqlperl
KalpaG
KalpaG

Reputation: 27

Executing procedural queries in perl

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";
}

Upvotes: 2

Views: 380

Answers (2)

tinita
tinita

Reputation: 4336

Please always use strict and warnings. if you don't have a variable @valid_total, warnings would then warn you

Possible unintended interpolation of @valid_total in string

and strict would even die.

In double quoted strings you must escape @ signs:

"set \@valid_total:=0;"

and if you don't need interpolation at all, better use single quotes.

edit: after I can see the perl code:

rather then doing

my $sql_query = 'set @valid_total:=0;
         set @invalid_total:=0;
...';
 my $sth = $dbhh->prepare($sql_query)

you should do:

my $sql_query = 'set @valid_total:=0';
my $sth = $dbhh->prepare($sql_query);
$sth->execute;
$sth->finish;

$sql_query = 'set @invalid_total:=0'
$sth = $dbhh->prepare($sql_query);
$sth->execute;
$sth->finish;
...

you cannot execute more than one statement in one query.

Upvotes: 0

TLP
TLP

Reputation: 67900

You are probably using a double quoted string for your query string, in which case perl looks for the variables @valid_total and @invalid_total. This would imply that you are not using 

use strict;
use warnings;

Because otherwise you would already know the error. The result is that perl replaces the variables with nothing, which is reflected in your error.

What you need to do is single quote the string:

my $query = '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';

Upvotes: 1

Related Questions