Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

by abaxaba (Hermit)
on Jun 01, 2002 at 10:59 UTC ( #170903=snippet: print w/replies, xml ) Need Help??
Description: 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?

use Date::Manip;
use DBI;


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

    my $firstLine="header - 1st line";
    my $secondLine="header - 2nd line"; 
    my $date = UnixDate("today","%B %e, %Y");

#############<FORMAT DEFS>
format STDOUT_TOP =
Last Modified: @<<<<<<<<<<<<<<<<<<<<<<<
PAGE:  @<<<<<<

Field          Type               Null     Key        Default       Ex
format STDOUT =
@<<<<<<<<<<<   @<<<<<<<<<<<<<     @<<<<<<< @<<<<<<<<  @>>>>>>  @||||||
 $field,        $type              $n       $key       $def     $ex
format TABLE =
format NEWLINE =

format ENDTABLE =

#####################</FORMAT DEFS>

    my $std = select (STDOUT);
    my $tableRefs = getData ($db, "show tables");
    foreach my $tab (@$tableRefs)
        $table = "TABLE: $$tab[0]";
        write TABLE;
        write NEWLINE;
        write TABLEHEADER;
        my $rowRefs = getData ($db, "describe $$tab[0]");
        foreach my $row (@$rowRefs)
            ($field,$type,$n,$key,$def,$ex) = @$row;
            write STDOUT;

        write ENDTABLE;
        write NEWLINE;

sub getData
    my ($db,$sql)=@_;
    my $query=$db->prepare("$sql");
    my $rows = $query->fetchall_arrayref();
Replies are listed 'Best First'.
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 ... :-)


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.

    ... Quidquid perl dictum sit, altum viditur.
Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: snippet [id://170903]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (4)
As of 2022-12-04 22:26 GMT
Find Nodes?
    Voting Booth?

    No recent polls found