Trouble with Perl fetchrow_array and MySQL

software development

#1

I’m trying to use the ChartDirector perl library to plot some data from a MySQL database. I’m following along with some examples given by the ChartDirector demo. But they show a syntax I’m unfamiliar with and I’m not getting any data from the arrays onto the plot. How does this code work?:
my $dbh = DBI->connect(“dbi:mysql:host=xxx.org:xxx”, “xxx”, “xxx”) or die $DBI::errstr;
my $sth = $dbh->prepare($SQLstatement) or die $DBI::errstr;

my @software = (0) x 55;
my @hardware = (0) x 55;
my @services = (0) x 55;
$sth->execute() or die $DBI::errstr;
while (my @row = $sth->fetchrow_array) {
$software[$row[0]] = $row[1];
$hardware[$row[0]] = $row[2];
$services[$row[0]] = $row[3];
}
$dbh->disconnect;

What are these array declarations with (0) x 55 ? Do they take the place of $count = 0; and ++$count; ? How does that [$row[0]] = $row[1] syntax work? I’m more familiar with the way Paul Bubois’ books do fetchrow_array s :

$dbh = DBI->connect (“DBI:mysql:host=localhost;database=webdb”,^M
"webdev", “webdevpass”,^M
{PrintError => 0, RaiseError => 1});^M
$sth = $dbh->prepare (“SELECT name, wins, losses FROM teams”);^M
$sth->execute ();^M
print header(), start_html (“team data”);^M
$count = 0;^M
while (my @val = $sth->fetchrow_array ())^M
{^M
print p (sprintf (“name = %s, wins = %d, losses = %d\n”,^M
$val[0], $val[1], $val[2]));^M
++$count;^M
}^M


#2

[quote] my @software = (0) x 55;
my @hardware = (0) x 55;
my @services = (0) x 55;
[/quote]
The ‘x’ operator simply replicates the value.

‘*’ x 25 = string of 25 asterisks
(0) x 25 = list of 25 0’s

So basically he is initializing each array to have 55 elements whose value is the number 0.

[quote]while (my @row = $sth->fetchrow_array) {
$software[$row[0]] = $row[1];
$hardware[$row[0]] = $row[2];
$services[$row[0]] = $row[3];[/quote]
It is a bit confusing, but basically it read as:
for each row
the first column is an index
the second column is an value that goes into the software array at the index position
the third column is an value that goes into the hardware array at the index position
the fourth column is an value that goes into the services array at the index position

And what happens if there is no data in the database? The code doesn’t seem to catch that situation. If no rows are returned, the arrays are left full of zeros and thats it. You would need to put “my $count = 0” before the loop, and “$count += 1” inside the loop, and after the loop, if $count is greater than zero, there was data in the database.

Not to mention there is no range checking to make sure the index is valid (again should be 0 through 54, since thats how many elements are in the arrays) or that the values are not null (and null becomes undefined in perl)

:cool: Perl / MySQL / HTML CSS


#3

I think I’m getting this. DuBois’ examples haven’t used an index. But the test data for ChartDirector has an Index column as a primary index that contains values 0 to 54. The index values are used to get the table data into the right array rows. My data table doesn’t have a index of row numbers. I’ll have to add a column for an index or figure out a way to populate the array by referencing the rows differently, maybe by a loop counter. I’ll look for an example in DuBois.


#4

My loop counter seems to be working and My SQL connect seems to be working, but my arrays aren’t appearing on my chart. I inserted some lines for debugging that print out what’s supposed to be going into my arrays. But I’m not sure my debugging lines are working. Line 30 prints out all 43 lines of the data correctly right before they are supposed to go into the arrays. Line 37 correctly prints out the values for chinook[0] and chinook[1]. Line 36 prints the correct number of rows that are supposed to go into the arrays. But the while loop 42-46 just prints out a bunch of commas.

27 $sth->execute() or die $DBI::errstr;
28 $count = 0;
29 while (my @row = $sth->fetchrow_array ()) {
30 print (sprintf (“date = %s, chinook = %d\n”,$row[0],$row[1]));
31 $chinook[$count] = $row[1];
32 $steelhead[$count] = $row[2];
33 $wsteelhead[$count] = $row[3];
34 ++$count;
35 }
36 print ("$count rows total\n");
37 printf “chinook[0]=%d\nchinook[1]=%d\n”,$chinook[0],$chinook[1] ;
38 $dbh->disconnect;
39 return 1;
40 ';
41
42 $count = 0;
43 while ($count < 45 ) {
44 print “$date[$count],$chinook[$count]\n”;
45 ++$count;
46 }


#5

I repeated the diagnostic printf on line 37 down after line 46 and both the while loop 42-46 and the second printf command give blanks or zeros. Do you suppose that the arrays area being blanked out when the database is disconnected at line 38. $dbh->disconnect; ?


#6

You appear to be missing something.

Where is @date supposed to get its values? Did you leave

$date[$count] = $row[0]; out of the first loop?

Also, is the second loop in another subroutine? Perhaps @chinook is no longer in scope. Add something before the loop like:

:cool: Perl / MySQL / HTML CSS


#7

I found it. Way up on line 6 is an eval and a opening quote that doesn’t get closed until the return at line 40. Now it works. See results at http://ykfp.org/ChartDirector/perldemo_cgi/dbroza.png

1 #!/usr/bin/perl
2
3 use perlchartdir;
4
5 my $dead = 0;
6 #my $ret = eval ‘
7
8 local $SIG{DIE} = sub { print $_[0], “

”; $dead = 1; }; 9
10 use DBI;
11
12 #
13 #Create an samoke SQL statement to test the database
14 #
15 $SQLstatement = “Select Date, Chinook, Steelhead, WildSteelhead Fr om tblRoza2004 order by Date”;
16
17 #
18 #Read in the revenue data into arrays
19 #
20 my $dbh = DBI->connect(“dbi:mysql:host=mysql.ykfp.org:db28989a”, " us28989a", “simcoe”) or die $DBI::errstr;
21 my $sth = $dbh->prepare($SQLstatement) or die $DBI::errstr;
22
23 my @chinook = (0) x 44;
24 my @steelhead = (0) x 44;
25 my @wsteelhead = (0) x 44;
26
27 $sth->execute() or die $DBI::errstr;
28 $count = 0;
29 while (my @row = $sth->fetchrow_array ()) {
30 print (sprintf (“date = %s, chinook = %d\n”,$row[0],$row[1 ]));
31 $chinook[$count] = $row[1];
32 $steelhead[$count] = $row[2];
33 $wsteelhead[$count] = $row[3];
34 ++$count;
35 }
36 print ("$count rows total\n");
37 printf “chinook[42]=%d\nchinook[43]=%d\n”,$chinook[42],$chinook[43 ];
38 $dbh->disconnect;
39 # return 1
40 #’;
41
42 $count = 0;
43 while ($count < 45 ) {


#8

Not sure what that line 6 and line 39-40 were supposed to do or where I picked them up while cutting and pasting demo code.


#9

It helps to look things up using the Perl documentation. ActivateState includes an HTML version with their software, and its also available on-line. Here is a relevant link:

ActiveState.com - ActivePerl Help - Online Docs - eval

:cool: Perl / MySQL / HTML+CSS


#10

Thanks for the nice resource. I suppose Active 5.6 is the one most fimial to DH’s perl 5.6.


#11

Glad you caught that.

I am using ActivateState’s 5.8.0 locally myself. I have not encountered problems due to changes from the 5.6 branch, however I am did encounter a problem with CGI.pm back when DH was still on 5.5 or earlier. I think it was because I tried “upgrading” just the CGI.pm module by putting a newer version in my home directory and finding out the hard way the new version has code that doesn’t work with older versions of perl.

Anyways, for the most part unless you are trying to take advantage of various perl magic tricks (CGI.pm is full of them), you should be OK developing your own code. There’s always the perldata documentation that lets you know what changed between versions, too. As with CGI.pm, you have to watch out for libraries that are not your own.

:cool: Perl / MySQL / HTML+CSS