Getting mysql data into gd

[shanta]

I am trying to get data from MySQL into GD.

Never been good with strings arrays etc

I need to go from this.

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

to

                            
my @data = ( 
    ["08:00","08:10","08:20","08:30","08:50","09:00","09:10", "09:20",
+ "09:30"],# time
    [    160,    162,    165,    166,    163,  165,    165,     168,  
+   164],   #mastuntemp
     [    165,    162,    160,    166,    163,  165,    165,     168, 
+    164], #LineTemp
    [    160,    170,    165,    166,    163,  165,    165,     168,  
+   164],  #spargtemp
  
  )or die GD::Graph::Data->error;

Thanks for the help Shanta

Current state of code Gives the error code after code.

 
#!/usr/bin/perl 

use strict;
use GD::Graph::bars;
use GD::Graph::lines;
use GD::Graph::colour;
use GD::Graph::Data;

use CGI qw(:standard);
use DBI;
my $CGI = new CGI() or
    die("Unable to construct the CGI object" .
        ". Please contact the webmaster.");

foreach ($CGI->param()) {
    $CGI->param($1,$CGI->param($_)) if (/(.*)\.x$/);
}
 my $SiteName = $CGI->param('site') ;
 my $batchnumber = $CGI ->param('batchnumber')||"20170903nervana" ;
 

my $dsn = "ddbi stuff";
my $usr  = 'username';
my $password = 'pw'; 

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


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

print "DEBUG:  \$sql = \"$sql\"\n";

my $sth = $dbh->prepare($sql);


print "DEBUG:  \$sth = \"$sth\"\n";    


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

my @row;
my @row_array;

my $data = GD::Graph::Data->new(600,500);


 $data->set(
      markers           => [3, 5, 6] ,
      dclrs             => [ qw(green pink blue cyan) ], 
      x_label           => 'Time',
      y_label           => 'Tempurature',
      title             => "Batch $batchnumber Mashing Temperatures.",
      y_max_value       => 180,
      y_tick_number     => 8,
      y_label_skip      => 2 
  ) or die $data->error;
$data->set_legend('mashtontemp', 'LineTemp', 'spargtemp',
my $recordidx = 0                    );
while (@row = $sth->fetchrow_array) {
    print "DEBUG:  \@row = \"@row\"\n"; #debug
    my $rowidx = 0; #degug
    foreach my $rowdata (@row) { #debug
        print "  [$rowidx] = \"$rowdata\"\n";#debug
        $rowidx++;
    #debug
 print "DEBUG:  \$data = \"$data\"\n";#debug
   }#debug
    
   print "DEBUG:  \$data = \"$data\"\n";#debug
 print "record count $recordidx\n";#debug
 $data->add_point(@row);
 
 $recordidx++;#debug
 
}



print "DEBUG:  \$data = \"$data\"\n";#debug
 my $chart = GD::Graph::lines->new();
  my $gd = $chart->plot($data);

False [] range "\w-" in regex; marked by <-- HERE in m/Can.?t locate \
+./[\w- <-- HERE _]+\.cgi in \@INC/ at graph_debug.cgi line 78.
DEBUG:  $sql = "SELECT time, mastuntemp, LineTemp, spargtemp FROM brew
+_temp_tb WHERE sitename = 'Brew' AND batchnumber = ? ORDER BY
time"
DEBUG:  $sth = "DBI::st=HASH(0x1fe2d88)"
DEBUG:  @row = "07:40 62.50 64.00 69.40"
  [0] = "07:40"
DEBUG:  $data = "GD::Graph::lines=HASH(0x1ff1a78)"
  [1] = "62.50"
DEBUG:  $data = "GD::Graph::lines=HASH(0x1ff1a78)"
  [2] = "64.00"
DEBUG:  $data = "GD::Graph::lines=HASH(0x1ff1a78)"
  [3] = "69.40"
DEBUG:  $data = "GD::Graph::lines=HASH(0x1ff1a78)"
DEBUG:  $data = "GD::Graph::lines=HASH(0x1ff1a78)"
record count 0
Content-type: text/html

<H1>Software error:</H1>
<PRE>Can't call method &quot;add_point&quot; on an undefined value at 
+./graph.cgi line 64.

[thanos1983]

Hello shanta,

Welcome to the Monastery. Well I am not sure why you want to export your data perldsc/ARRAYS OF ARRAYS when the module GD::Graph::Data loads the data directly into the plot.

Sample code from the GD::Graph::Data/EXAMPLES:

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

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

my $data = GD::Graph::Data->new();

while (@row = $sth->fetchrow_array)
{
    $data->add_point(@row);
}

my $chart = GD::Graph::bars->new();
my $gd = $chart->plot($data);

So in your case if you are sure the data that you exporting is the necessary data from the plot something like that should work out of the box (code untested):

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

my $data = GD::Graph::Data->new();

my $sth = $dbh->prepare("SELECT time, mastuntemp, LineTemp, spargtemp 
+FROM brew_temp_tb WHERE sitename = 'Brew' AND batchnumber = '$batchnu
+mber' ORDER BY time";");

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

my @row;
while (@row = $sth->fetchrow_array) {
    $data->add_point(@row);
}

We can not test your code, be we need a minimum working sample of code that replicates the table and some demo data to insert for experimentation purposes. Alternatively you can test it and let us know if it worked.

Looking forward to your update, BR.

[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;

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

[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

[tybalt89]

Untested (and incomplete)

my $sth = $dbh->prepare($sql);

my @data;

while( @row_array = $sth->fetchrow_array )
  {
  for my $i ( 0..3 )
    {
    push @{ $data[$i] }, $row_array[$i];
    }
  }

[NetWallah]

In case you are still looking for a way to transpose data:
in mysql:
https://stackoverflow.com/questions/3392956/sql-how-to-transpose.

Of course, perl can do the transpose as well (but it is probably more memory-efficient to do it in SQL):
https://stackoverflow.com/questions/3249508/transpose-in-perl.

[chacham]

Side point: Using a variable inside a sql statement is known as dynamic sql, and is generally considered to be a bad idea. Instead, use a place holder, that is '?', and prepare the statement.

[shanta]

I am a hacker using Perl to get a job done. Entirely self taught. Perl Arrays and esoteric place holders have always mystified me!

Heck I can do a TTML quires and populate a table or whatever but Don't know how to access GD from TTML or I would rather than drop into Perl.

I can take code and make it do what I want. To a point at least.

Of course I am here to learn so can you demystify it for me?

[roboticus]

shanta:

Placeholders are actually very easy, and prevent a lot of problems. To use placeholders, you just replace variable references with question marks, then provide the values as arguments to execute.

So suppose you have something like this:

my $ST = $DB->prepare("select foo, bar from my_table "
                     ."where col1 < '$abc' or (col1 > '$def' and col3 
+< $fgh)");
$ST->execute();

As mentioned, you change the variables to question marks and provide the variables to the execute statement, like this:

my $ST = $DB->prepare("select foo, bar from table where col1 < ? or (c
+ol1 > ? and col3 < ?)");
$ST->execute($abc, $def, $fgh);

Note that we also removed the quotes on the first two variable references, because the database already knows that column1 is a string.

Using placeholders is nice, because you can use the same statement with different values for different executes which will let the database compile the statement once and re-use the same execution plan for successive runs. (Well, at least *some* databases will take advantage of that.)

Placeholders are even more important because they take advantage of any funky special cases for quoting the variable values, and thereby help you resist SQL injection attacks. For example, suppose $abc in the example above contains the value "'; drop table my_table; --". Then your original statement would be passed to the database as the following (with a few newlines thrown in for readability):

select foo, bar from my_table where col1 < '';
drop table my_table
; --' or (col1 > 'def_val' and col3 < fgh_val)

That's a recipe for disaster. So make the effort to learn placeholders a bit better (shouldn't take long), and you'll never go back to doing it the hard way.

...roboticus

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

[hippo]

Of course I am here to learn so can you demystify it for me?

In addition to roboticus's excellent reply, there is a succinct summary within the Q&A: What are placeholders in DBI, and why would I want to use them? which covers most of the salient points. If you are going to spend much time working with databases then Programming the Perl DBI would be an excellent investment.