Thanks Hardy, I suspect that you are correct, sir!
I'm going to go pickup a copy of "Programming the Perl DBI" book, today. I
believe that's an O'Reilly publication, isn't it? (I can "Google" it.)
By the way, I've just executed the following code. Notice that there is no
"while" loop, but rather three simple calls to fetchrow_array( ).
a.. The first returns the last database in the known world as a row.
b.. The second call as expected, returns nothing in $db_name; subsequently, a
NULL (or blank value) is pushed onto the array. It's interesting to note that
no DBI error is reported, and this is understandable because hitting the end of
a list is not considered a DBI error, . . .just that the list is exhausted.
c.. The third call pushes a hard-coded value, "foobar" onto the array.
The displayed popup menu will display the list as:
test
foobar
Here's the test code: (BTY Hardy, this is also an example of how I use the
DBI_error( ) as a test and debug tool.)
$db_name = $sth->fetchrow_array();
push(@db_name_values, $db_name); # Save each value in array.
DBI_error("GOT HERE! db_name=$db_name_values[0]<br>","1"); #### TEMP!
$db_name = $sth->fetchrow_array();
DBI_error("Any DBI Error will be printed above.<br>","7"); #### TEMP!
push(@db_name_values, $db_name); # Save each value in array.
DBI_error("GOT HERE! db_name=$db_name_values[1]<br>","1"); #### TEMP!
push(@db_name_values, "foobar"); # Save each value in array.
DBI_error("GOT HERE! db_name=$db_name_values[2]<br>","1"); #### TEMP!
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
--------------------------------------------------------------------------------
----- Original Message -----
From: Hardy Merrill
To: [EMAIL PROTECTED] ; dbi-users@perl.org
Sent: Wednesday, December 29, 2004 12:23 PM
Subject: Re: Problem with "while/fetchrow_array"
Ron, someone else will correct me if I'm wrong, but long ago this bit me
also. I believe I remember the answer being that "SHOW" is a mysql
client command - that is not a valid SQL command.
I'm not sure if mysql has a mysql-only sql command you can give to get
the names of the databases - or maybe they have some system table that
contains the names of the databases and you can SELECT from that. Check
the mysql docs to see if there's maybe a mysql specific SQL command you
can give - similar to SHOW but not specific to the mysql client - to
list databases.
If you have the "Programming the Perl DBI" book, I believe there's a
place in there that describes how to get the names of available drivers
and datasources (which may contain the database names). I'd also read
the perldocs for DBI and DBD::mysql to see what $dbh and $sth methods
are available - maybe one of them will give you a list of database
names.
Sorry I can't be more help - I'm not using DBI nor MySQL regularly :-(
Not that I don't want to...
Hardy Merrill
>>> "Ron Wingfield" <[EMAIL PROTECTED]> 12/29/04 01:07PM >>>
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
--------------------------------------------------------------------------------
