display database records at the console

[Anonymous Monk]

Venerable monks

Is there a way to display database records returned by a query in a table at the console without having to define a format for the query results? I need to investigate the contents of a remote database by running lots of queries so writing a format for each query isn't feasible. I just want the results displayed in tabular form such as you would get with mysql console e.g. column headings at the top and the rows of data.

many thanks

[samarzone]

Why bother? Just use mysql client with -e option

If you are desperate to do it in Perl just for displaying record on console you can use system or backticks (``) with mysql client and -e option as a shell command

Even if you want to do it through DBI you need not write a separate format for each query. You can loop over column names for header row. Column names can be found through $sth->{NAME} . I hope you very well know how to get the field values from the record-set returned.

[Anonymous Monk]

thanks for both comments

[roboticus]

I'd suggest trying Text::Table. However, I've also tried things like printing to a file, something like this:

print $OF "<table><tr><td>", join("</td><td>", @{$ST->{NAME}}), "</td>
+</tr>\n";
while (my $ar=$ST->fetchrow_arrayref) {
   print $OF "<tr><td>", join("</td><td>", @$ar), "</td></tr>\n";
}
print $OF "</table>\n";

and then viewing the file in a browser. (Note: I wrote this off the top of my head, so it's untested, etc.)

...roboticus

When your only tool is a hammer, all problems look like your thumb.

[Your Mother]

Here is a sample using Text::SimpleTable with DBIx::Class. This isn't abstract enough. It should take the column names from the DB, the sizes from the max output record v max allowed, etc, etc. Still, it's fun and I might like to use it myself sometime so I post it here where I'll be able to find it again. It's not super short so <readmore/> it is-

Read more... (8 kB)