I'm not sure I understand the problem, but I think(?) I see
one problem - see below.
Another thing I noticed was you are using the same "$sth"
statement handle for each prepare/execute/fetch you are
doing. As long as each fetch fetches *ALL* the rows, I
don't think that's a problem, but a safer approach is to
either "finish" the statement handle before you use it
again, or name your statement handles differently.
Also, not sure if you know about this and chose not to use
them, but "placeholders" will save you LOTS of time and
aggrevation with proper quoting, not to mention the performance
improvements they give in certain situations.
One more thing - it doesn't look to me like you are error
checking any of your DBI statements. Read the excellent
DBI perldocs by doing
perldoc DBI
at a command prompt and read about using RaiseError and
"eval" to error check DBI statements. Also read about
using "placeholders".
HTH.
--
Hardy Merrill
Senior Software Engineer
Red Hat, Inc.
chad kellerman [[EMAIL PROTECTED]] wrote:
> Ney guys,
>
> I am trying to fetch and array form a table and I am not getting the
> results I thought I would get:
>
> I thought it was quote simple but when I print them out....well you'll
> see?
>
>
> <code>
>
> #!/usr/bin/perl
> use strict;
> use warnings;
>
> use DBI;
>
> my( $dbname ) = "BACKUP";
> my( $mysqluser ) = "bob";
> my( $mysqlpasswd ) = "bobspasswd";
> my( $dbh, $sth, $rc, @failedCon, $var, $name, @names );
>
> #these variables will be passed to subroutine in main program.
> my( $hostId ) = "543";
> my( $biggyFlag ) = "0";
> my( $hostDirFlag ) = "0";
>
>
> $dbh = DBI->connect( "DBI:mysql:database=".$dbname, $mysqluser,
> $mysqlpasswd ) or die "Cannot connect to database: $!";
>
> #first check for failed attempts
>
> $sth = $dbh->prepare( qq{ SELECT failedConAttempt FROM tblControl WHERE
> hostId="$hostId" } );
> $sth->execute;
> $sth->bind_columns( \$var );
> while ( $sth->fetch ) {
>
> push @failedCon, $var;
>
> }
>
> print "backup failed to connect to $hostId: $failedCon[0] times.\n";
>
>
> #grab information
>
> if ($failedCon[0] != 0 ) {
> $sth = $dbh->prepare( q{
^^
This will prevent the "$" variables within this string from
being interpolated - I don't think that's what you want. You
should use "qq" here.
> SELECT name FROM tblProcess
> WHERE biggy_flag="$biggyFlag" AND
> hostId="$hostId" AND hostDirFlag="$hostDirFlag" AND status="In-Progress"
> OR status="Queued"
> } );
> $sth->execute;
> $sth->bind_columns( \$name );
> while ( $sth->fetch ) {
> push @names, $name;
> print "@names\n";
> }
> }else{
> $sth = $dbh->prepare( q{
> SELECT name FROM tblProcess
> WHERE biggy_flag="$biggyFlag" AND
> hostId="$hostId" AND hostDirFlag="$hostDirFlag" } );
> $sth->execute;
> $sth->bind_columns( \$name );
> while ( $sth->fetchrow_array ) {
> push @names, $name;
> print "@names\n";
^^^^^^^^^^^^^^^^^
For each fetch, you are 'push'ing the $name onto the @names
array, *AND* you are also printing the whole @names array.
You are doing this in this loop and the previous one. Don't
you want to do this instead:
while ( $sth->fetchrow_array ) {
push @names, $name;
}
print "@names\n";
???
> }
>
> }
>
> $rc = $dbh->disconnect;
>
>
> </code>
>
>
> when I run this I get:
>
>
> [root@widowmaker sbin]# perl mysql.pl
> backup failed to connect to 543: 5 times.
> alan
> alan alex
> alan alex bob
> alan alex bob cole
> alan alex bob cole brian
> alan alex bob cole brian coleman
> alan alex bob cole brian coleman david
> alan alex bob cole brian coleman david edward
> alan alex bob cole brian coleman david edward evilyn
>
> THe first print statemnet I get I expect, it came out right. But I
> just want to print the second array.. Why does it print
> alan
> alan alex
> alan alex bob
> alan alex bob cole
> alan alex bob cole brian
> alan alex bob cole brian coleman
> alan alex bob cole brian coleman david
> alan alex bob cole brian coleman david edward
> alan alex bob cole brian coleman david edward evilyn
>
> and not just:
> alan alex bob cole brian coleman david edward evilyn
>
> am I missing something???
>
> Thanks,
> Chad
>
>
>
>
>
>
>
>
> --
> Chad Kellerman
> Jr. Systems Administrator
> Alabanza Inc
> 410-234-3305
--
Hardy Merrill
Senior Software Engineer
Red Hat, Inc.
