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.

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]
      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]
        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]
        Nevermind - figfured it out. fetchrow_array works. Probably a more elegant way though...
[reply]

Back to Seekers of Perl Wisdom