Re^2: Getting mysql data into gd

[shanta]

Thanks for the code and the link to the sample code

I get this error

Can't call method "add_point" on an undefined value at ./graph.cgi line 37.

guessing the query did not work.

[marinersk]

Hello, shanta.

An old-fashioned, brute-force way to troubleshoot a problem is to check what is happening at key points in the program. This helps ensure the data you think you should be getting and using is what you are actually getting and using.

For example, this code shows the data returns after every step. This is likely to be helpful in isolating the problem:

#!/usr/bin/perl
use strict;
use warnings;

use DBI;
use GD::Graph::Data;
use GD::Graph::bars;

# do DBI things, like connecting to the database, statement
# preparation and execution

# Don't forget to print the results of your various DBI commands.  
# Your open(), for example, might not be working correctly.

my $sql = "SELECT time, mastuntemp, LineTemp, spargtemp FROM brew_temp
+_tb WHERE sitename = 'Brew' AND batchnumber = '$batchnumber' ORDER BY
+ time";
print "DEBUG:  \$sql = \"$sql\"\n";

my $data = GD::Graph::Data->new();
print "DEBUG:  \$data = \"$data\"\n";

my $sth = $dbh->prepare($sql);
print "DEBUG:  \$sth = \"$sth\"\n";

if (!$sth->execute()) {
    die "Error: ". $sth->errstr ."\n";
}

my @row;
while (@row = $sth->fetchrow_array) {
    print "DEBUG:  \@row = \"@row\"\n";
    my $rowidx = 0;
    foreach my $rowdata (@row) {
        print "  [$rowidx] = \"$rowdata\"\n";
        $rowidx++;
    }
    $data->add_point(@row);
}

exit;

[shanta]

There are 4 or more rows of data

The query works

DEBUG:  $sth = "DBI::st=HASH(0x15e6e68)"
DEBUG:  @row = "07:40 62.50 64.00 69.40"
  [0] = "07:40"
  [1] = "62.50"
  [2] = "64.00"
  [3] = "69.40"

We are getting only one row and the fallowing error.

Can't call method "add_point" on an undefined value at ./graph.cgi line 54.

Time is the x value and the numbers are the temp. I removed time from the query and it still fails.

[poj]

Try

#!/usr/bin/perl
use strict;
use DBI;
use GD::Graph::Data;
use GD::Graph::bars;

my $batchnumber = '20170903nervana';

my $sql = '
  SELECT time, mastuntemp, LineTemp, spargtemp 
  FROM brew_temp_tb 
  WHERE sitename = ? 
    AND batchnumber = ?
  ORDER BY time';

my $dbh = dbh(); # connect
my $sth = $dbh->prepare($sql);
$sth->execute('Brew',$batchnumber);           
           
my $data = GD::Graph::Data->new();
while (my @row = $sth->fetchrow_array){
  $data->add_point(@row);
}

my $chart = GD::Graph::bars->new();
my $gd = $chart->plot($data);
open(IMG, '>','bar.png') or die $!;
binmode IMG;
print IMG $gd->png;

# connect
sub dbh{
  my $database = "test"; 
  my $user     = "user"; 
  my $pw       = "password";
  my $dsn = "dbi:mysql:$database:localhost:3306"; 
  my $dbh = DBI->connect($dsn, $user, $pw, 
     { RaiseError=>1, AutoCommit=>1 } );
  return $dbh;
}

poj

[thanos1983]

Hello shanta,

It looks like the data that you are trying to export from the DB they are not populated. At this point without any sample of data and sample of DB table we can only assume that the SELECT function is not populating the data.

If I was you I would follow the advice of fellow monk marinersk, you need to debug step by step your connection to the DB and that you are actually retrieving the expected data. As soon you have reached this point and you are sure that all the mandatory steps are met then make sure the data that you are providing to GD::Graph::Data are the ones that it expects.

Sample of expected data taken from the GD::Graph::Data/DESCRIPTION:

An object of this class contains a list of X values, and a number of l
+ists of corresponding Y values. This only really makes sense if the Y
+ values are numerical, but you can basically store anything.

Make sure that the data that you are trying to push into the Graph are as described by the DESCRIPTION.

Hope this helps, BR.

[shanta]

Got things down to the query

my $batchnumber = $CGI ->param('batchnumber')||"20170903nervana" ;

my $sql = "SELECT time, mastuntemp, LineTemp, spargtemp FROM brew_temp
+_tb WHERE sitename = 'Brew' AND batchnumber = '$batchnumber' ORDER BY
time";

There is data in the database. Which I display in ttml

[afoken]

guessing the query did not work

Don't guess, test for errors. Or, even better, make DBI do that for you:

my $dbh=DBI->connect($dsn,$user,$password,{ RaiseError => 1, ... });

Alexander

[shanta]

Tried that code but it failed had to remove ', ...' to make it work.

Dose this way of requesting and error work for all errors or just connect? As it is in connect.

[roboticus]

shanta:

The RaiseError=>1 part is telling DBI to raise an error whenever it sees one. The ", ..." part just meant that you would put in any other connect parameters you currently use.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

[shanta]

Thanks This failed with the ... had to remove. dose report all dbi errors or just connect?