http://www.perlmonks.org?node_id=170903

Simple formatted output of sql describe(describe tables) for all tables in a db.

Question from this little exercise: Anyway to "pass" formats? Are they like filehandle refs?
#!/usr/local/bin/perl use Date::Manip; use DBI; main(); sub main { my ($table,$field,$type,$n,$key,$def,$ex) = @$row; my $dbase="dbName"; my $user="userName"; my $pass="passWord"; my $db = DBI->connect ("dbi:mysql:$dbase", "$user","$pass"); my $firstLine="header - 1st line"; my $secondLine="header - 2nd line"; my $date = UnixDate("today","%B %e, %Y"); #############<FORMAT DEFS> format STDOUT_TOP = @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< $firstLine @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< $secondLine Last Modified: @<<<<<<<<<<<<<<<<<<<<<<< $date PAGE: @<<<<<< $% . format TABLEHEADER = ---------------------------------------------------------------------- +--------- Field Type Null Key Default Ex +tra ---------------------------------------------------------------------- +--------- . format STDOUT = @<<<<<<<<<<< @<<<<<<<<<<<<< @<<<<<<< @<<<<<<<< @>>>>>> @|||||| +|||||||| $field, $type $n $key $def $ex ---------------------------------------------------------------------- +--------- . format TABLE = @||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| +|||||||||| $table . format NEWLINE = . format ENDTABLE = ---------------------------------------------------------------------- +--------- . #####################</FORMAT DEFS> my $std = select (STDOUT); my $tableRefs = getData ($db, "show tables"); foreach my $tab (@$tableRefs) { $~=TABLE; $table = "TABLE: $$tab[0]"; write TABLE; $~=NEWLINE; write NEWLINE; $~=TABLEHEADER; write TABLEHEADER; $~=STDOUT; my $rowRefs = getData ($db, "describe $$tab[0]"); foreach my $row (@$rowRefs) { ($field,$type,$n,$key,$def,$ex) = @$row; write STDOUT; } $~=ENDTABLE; write ENDTABLE; $~=NEWLINE; write NEWLINE; } $db->disconnect(); } sub getData { my ($db,$sql)=@_; my $query=$db->prepare("$sql"); $query->execute(); my $rows = $query->fetchall_arrayref(); $rows; }

Replies are listed 'Best First'.
Re: dbDescribe.pl
by rob_au (Abbot) on Jun 01, 2002 at 11:40 UTC
    Anyway to "pass" formats?

    Yes, there is a way to store formatting output, one which I only found out about recently while flicking through a copy of Perl in a Nutshell. The perlfunc:formline function, normally only used internally, allows for formats to be built and returned in the format output accumulator, $^A. Normally, the contents of this variable are outputted to the currently selected filehandle via the write command, but this variable, $^A, can be read and reset directly.

    For example:

    my @list = (1, 2); # the output format can be stored in a variable prior to # output my $format = '@<<<< @<<<<'; # format the contents of @list as per the format structure # defined in the variable $format, the output to be # stored in the format output accumulator, $^A formline $format, @list; print $^A, "\n"; $^A = "";

    Thus in this manner, through use of the formline function, both the output format and the subsequent formatted output can be retrieved and stored. For further information see perlfunc:formline and perlform.

    I had been intending to write an obfuscation based upon this trick, but ... oh well ... :-)

     

Re: dbDescribe.pl
by Beatnik (Parson) on Jun 01, 2002 at 11:14 UTC
    This is specific for certain RDBMSs, like mysql. Solid (the stuff I use at school) doesn't do show tables, neither does it do describe table_name.

    Greetz
    Beatnik
    ... Quidquid perl dictum sit, altum viditur.