Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Getting mysql data into gd

by shanta (Novice)
on Aug 27, 2017 at 20:22 UTC ( #1198110=perlquestion: print w/replies, xml ) Need Help??

shanta has asked for the wisdom of the Perl Monks concerning the following question:

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.

Replies are listed 'Best First'.
Re: Getting mysql data into gd
by thanos1983 (Parson) on Aug 27, 2017 at 21:46 UTC

    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.

    Seeking for Perl wisdom...on the process of learning...not there...yet!

      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.

        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;

        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.

        Seeking for Perl wisdom...on the process of learning...not there...yet!
        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

        --
        Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
Re: Getting mysql data into gd
by tybalt89 (Prior) on Aug 27, 2017 at 20:54 UTC

    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]; } }
Re: Getting mysql data into gd
by NetWallah (Canon) on Aug 28, 2017 at 00:29 UTC
Re: Getting mysql data into gd
by chacham (Prior) on Aug 28, 2017 at 20:17 UTC

    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.

      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?

        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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1198110]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (7)
As of 2021-01-28 00:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?