perl-mysql help - print table structure

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

Hi Monks,

I'm experimenting with a perl script to automate a couple of database creation processes. I'm aware these can be done at the mysql command line but I would nevertheless like to see how it can be done with a perl script.

Here is the code:

#!/usr/bin/perl

use DBI;

# Changed after advice from jeffa

my %conf = (
   db_type => 'mysql',
   db_name => 'new_db',
   db_user => 'root',
   db_pass => '****',
   table   => 'profile',
);


my $dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}", $conf{db_u
+ser}, $conf{db_pass}, {PrintError => 0, RaiseError => 0});

create_database();
create_table();
describe_table();

sub create_database {
      $dbh->do("DROP DATABASE $conf{db_name}") ||
           warn("Warning (expected): Can't drop db '$conf{db_name}': $
+DBI::errstr");
      $dbh->do("CREATE DATABASE $conf{db_name}") ||
        die("Can't create database '$conf{db_name}': $DBI::errstr");
}

sub create_table {
      $dbh->do("USE $conf{db_name}") ||
        die("Can't use database '$conf{db_name}': $DBI::errstr");
      $dbh->do("CREATE TABLE $conf{table} (" .
             'name varchar(255) not null, ' .
            'age int, ' .
            'birthday varchar(255) not null, ' .
                        'school varchar(255) not null)') ||
        die("Can't create table '$conf{table}': $DBI::errstr");
}

sub describe_table {
  # I'm trying to get it to print out the table's
  # structure (the sort you see when you type
  # 'DESCRIBE table_name' in mysql. 
}

$dbh->disconnect();

# Success.
print "Success\n";
exit(0);
[download]

The code creates a database and a table. I'm trying to get it to print out the table's structure (the sort you see when you type 'DESCRIBE table_name' in mysql). I've no idea how to do that.

Any help would be appreciated.

Thanks in anticipation :)

Comment on perl-mysql help - print table structure Download Code

Replies are listed 'Best First'.
Re: perl-mysql help - print table structure by jeffa (Bishop) on Oct 29, 2003 at 13:48 UTC
The replies at Perl and Mysql! should be of help to you. If not, feel free to ask specific questions about displaying database tables. (like what output format do you want, do you want to display columns or just the data, etc.) One more item ... i don't like the way you pass around global variables like that. If you really want to use a global because passing variables to subs is tedious, just slam your config vals into a hash: `my %conf = ( db_type => 'mysql', db_name => 'new_db', db_user => 'root', db_pass => '****', table => 'profile', );` [download] That is much easier to handle. jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l]
Re: perl-mysql help - print table structure by jdtoronto (Prior) on Oct 29, 2003 at 14:00 UTC
Their is a variety of ways to achieve what you want. Remember anything you can do in SQL in the MySQL monitor can be done through the DBI. Do you use SHOW COLUMNS in the monitor? Well, why not use it in your programme? `sub getTableStructure { # need: $dbh # tablename # returns: hash of field(column) records my ($dbh, $tablename) = @_; my %struct_hash; my $SQL = "SHOW COLUMNS FROM $tablename"; my $sth = $dbh->prepare( $SQL ); $sth->execute(); while ( my $inphash = $sth->fetchrow_hashref() ) { . .whatever you need to do .}` [download] Gives you the information about the table. You can take that info and then use it to build entire new tables! In my case I import a text file into a table where every field is of type VARCHAR and named 'fieldn'. Using this data form MySQL I map the input table fields to fields in the database, ALTER the table, add some fields and then append it to the destination table - all done using MySQL SQL statements via the DBI. Go and try this sort of stuff out, and come back with more specific questions wehn you get stumped. jdtoronto	[reply] [d/l]