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
--------------------------------------------------------------------------------
