Using DBI to get a SUM

[archulu]

hai this archulu, i have a doubt in Mod perl programing with pgsql; plz help to me
in my project i am canducting exam to students, in exam result , 1=right anwer, 0=wrong answer.

[CODE=perl]
$id=4;
$sth1=$dbh->prepare("selec t SUM(result) from result_master where exam_id = '$id' and result = '1' ") or warn "Erron in prepare statemt ".$dbh->errstr;
$res=$sth1->execute();
[/CODE]

in this program result $res prints the value is 1 insted of actuall sum value(suppose 80 etc). plz help to me. what fault in quarry.
iam also using count insted of SUM. this same qurry working in database but not in program printing
thanks again

[miller]

You need to do some reading up on DBI.

cpan DBI

I realize the documentation is quite long as the module is extensive. But whenever you are having trouble, go back and make sure that you are using the methods correctly. In this instance you would have discovered that execute does not return the results of a query, only the number of records that are returned or modified in a statement. To get the results you must use one of the many kinds of fetch routines.

This is one way that would work:

[CODE=perl]
$id=4;
$sth1=$dbh->prepare("selec t SUM(result) from result_master where exam_id = '$id' and result = '1' ") or warn "Erron in prepare statemt ".$dbh->errstr;
$sth1->execute();
my ($res) = $sth->fetchrow_array ;
[/CODE]

Also, to clean up your SQL code even more, I would make the following list of changes. Upper casing all SQL keywords. Using placeholders for variables instead of hardcoding them in the statement. Moving error checking to the execute instead of prepare.

[CODE=perl]
my $id = 4;
my $sth1 = $dbh->prepare(q{SELE CT SUM(result) FROM result_master WHERE exam_id=? AND result=?});
$sth1->execute($id, 1) or die $dbh->errstr;
my ($res) = $sth->fetchrow_array ;
[/CODE]

- Miller

[archulu]

thank you Mr.Millar, it's very helps to me. once again thanks