Problem with "while/fetchrow_array"

From: Ron Wingfield (rtwingfield_at_archaxis.net)
Date: 12/29/04 

To: <dbi-users@perl.org>
Date: Wed, 29 Dec 2004 12:07:38 -0600

Hello everyone,

I'm having difficulty solving this code problem. I can load a popup list of states from a "states" table, but I can't seem to get-it-right when I execute a SHOW, rather than a SELECT query. Actually, I want to create a popup list of all DATABASES through the CGI. From the command line, this works:

  mysql> SHOW DATABASES;
  +-----------+
  | Database |
  +-----------+
  | cookbook |
  | test |
  +-----------+
  2 rows in set (0.00 sec)

As you can see, there are two databases on my server, "cookbook" and "test". I prepare the query request, and execute it without any error complaints. The while/fetchrow_array returns only one iteration or row, . . .apparently the last row which contains the "test" database, and ultimately my popup list only contains the "test" database row. I'm not getting any error from the fetchrow_array function; however I only see one diagnostic display of "GOT HERE! db_name=test". Apparently, the while loop has only looped one time. Regarding the following code, why is "cookbook" missed?

  $sth=$dbh->prepare("SHOW DATABASES;");
  $sth->execute( );
  DBI_error("Query execution to SHOW DATABASES failed.","7");
  while($db_name = $sth->fetchrow_array( ))
  {
          DBI_error("Fetchrow_array failed.","7"); # Test/no exit-on-error.
          push(@db_name_values, $db_name); # Save each value in array.
          DBI_error("GOT HERE! db_name=$db_name<br>","1"); #### TEMP!
                                           # When used with option "1", ----^
                                           # this always prints a diagnostic "trace" message.
  }
  print # Start of print block
  $cgi-> popup_menu
  (
                  -name=>'db_name',
                  -values=>\@db_name_values
  ),

Thanks and OTTF,
Ron Wingfield

FreeBSD 4.8 -- Apache http 2.0.28 -- MySQL client/server 4.1.7
Perl 5.8.5 -- p5-DBD-mysql-2.9004 driver -- p5-DBI-1.46

--------------------------------------------------------------------------------

Relevant Pages

  • RE: Problem with "while/fetchrow_array"
    ... and execute it without any error complaints. ... the last row which contains the "test" database, ... Regarding the following code, why is "cookbook" missed? ... print # Start of print block ...
    (perl.dbi.users)
  • RE: Problem with "while/fetchrow_array"
    ... connecting locally and user 'fred' connecting from a remote location. ... and execute it without any error complaints. ... the last row which contains the "test" database, ... print # Start of print block ...
    (perl.dbi.users)
  • Re: Problem with "while/fetchrow_array"
    ... let alone connect to a database. ... I think I have only asked a group a question twice in many years (and ... even then I was told to bugger off). ... > print # Start of print block ...
    (perl.dbi.users)