RE: Problem with "while/fetchrow_array"

[Graeme St. Clair]

A second thought.  Are you sure this isn't a privileges issue?  That when
you're in cmd line mode, you have one userid & pswd that can 'see' both
d-b's, but when connecting via PERL & DBI, you have another that can only
'see' one?  Remember also, that MySQL distinguishes between user 'fred'
connecting locally and user 'fred' connecting from a remote location.

Rgds, GStC.

-----Original Message-----
From: Ron Wingfield [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, December 29, 2004 1:08 PM
To: dbi-users@perl.org
Subject: Problem with "while/fetchrow_array"

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=>[EMAIL PROTECTED]
  ),

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

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