How to query mysql database schema with DBI

punch_card_don has asked for the wisdom of the Perl Monks concerning the following question:

Monks,
Imagine I have just created a mysql table using the DBI in a cgi program:

$dbh = DBI->connect($db_datasource, $db_user_name, $db_password) or di
+e("Could not connect!");

$tbl_create = "
CREATE TABLE responses (
response smallint unsigned,
respondent smallint unsigned,
index response_index (response)
) ";

my $command = $dbh->prepare($tbl_create) or die("Could not prepare!");
$command->execute() or die("Could not create!");
[download]

Now I'd like to query the database using the DBI in a cgi program to ensure that my table was created correctly, but can't seem to find how. Would some be so kind?
Thanks,
Don.

Comment on How to query mysql database schema with DBI Download Code

Replies are listed 'Best First'.
Re: How to query mysql database schema with DBI by dragonchild (Archbishop) on Oct 04, 2004 at 20:04 UTC
`SHOW CREATE TABLE responses` There are other ways using DBI, but that's the MySQL way. I strongly suggest you read both the DBI documentation as well as the MySQL documentation. Furthermore, I wouldn't create my schema within a CGI script. Your schema should exist separate from your web application, for a whole host of reasons. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply] [d/l]
Re^2: How to query mysql database schema with DBI by punch_card_don (Curate) on Oct 04, 2004 at 20:50 UTC
Thanks. Uh, I hate to sound thick, but would that be used like this: `$dbh = DBI->connect($db_datasource, $db_user_name, $db_password) or di +e("Could not connect!"); $sql= "SHOW CREATE TABLE responses"; my $command = $dbh->prepare($sql) or die("Could not prepare!"); $command->execute() or die("Could not create!"); while ( my @row = $command->fetchrow_array( ) ) { print "@row\n"; }` [download]	[reply] [d/l]
Re^3: How to query mysql database schema with DBI by punch_card_don (Curate) on Oct 04, 2004 at 20:52 UTC
As for creating via a cgi program, I'm just playing right now. It would never be part of a running program. Just a script that's run once to create the schema, then removed. But I'm open to better suggestions if you have one!	[reply]
Re^4: How to query mysql database schema with DBI by shemp (Deacon) on Oct 04, 2004 at 22:50 UTC
Re^4: How to query mysql database schema with DBI by dragonchild (Archbishop) on Oct 04, 2004 at 21:08 UTC
Re^3: How to query mysql database schema with DBI by punch_card_don (Curate) on Oct 04, 2004 at 21:20 UTC
Nevermind - figfured it out. fetchrow_array works. Probably a more elegant way though...	[reply]


No such thing as a small change
	PerlMonks