How to stop Perl from removing table formatting from mysql command?

[lit8rtot]

I am executing the mysql command from within Perl scripts (long story why not using Perl DBI module). What I am finding is that Perl is stripping out the "table border" characters that are normally output by the mysql command when doing a select from a table.

For example, I get this:
ID DISPLAYNAME OPTLOCK IPADDRESS BASEASSET_ID
1 NULL 0 172.50.0.100 1

instead of:
+----+-------------+---------+--------------+--------------+
| ID | DISPLAYNAME | OPTLOCK | IPADDRESS | BASEASSET_ID |
+----+-------------+---------+--------------+--------------+
| 1 | NULL | 0 | 172.50.0.100 | 1 |
+----+-------------+---------+--------------+--------------+

which is fine when all fields do not have spaces, but that is rarely the case.

Why is this happening, and how can I get around this?

Thanks!

 

[Kirsle]

What command does Perl run, and what are your results if you run that exact same command?

There's no reason Perl would strip out the borders, my guess is that the command is slightly different to what you would run (do you run mysql, get the "mysql>" prompt, and then type your query? and I imagine Perl runs the query as part of the command line arguments? that might be why... a mysql> prompt would indicate a human is at the keyboard, so mysql would format it with borders, whereas a query on the command line would indicate a shell script or something running a query, where it only needs the data and not the pretty formatting)

Cuvou.com | My personal homepage

perl -e '$|=$i=1;print" oo\n<|>\n_|_";x:sleep$|;print"\b",$i++%2?"/":"_";goto x;'

 

[lit8rtot]

I think you are right - it isn't Perl that is doing the stripping:

From a shell prompt, I run the following:

$ mysql -u foo -D dbname -e 'Select * from ADDRESS'
+----+-------------+---------+--------------+--------------+
| ID | DISPLAYNAME | OPTLOCK | IPADDRESS | BASEASSET_ID |
+----+-------------+---------+--------------+--------------+
| 1 | NULL | 0 | 172.50.0.100 | 1 |
+----+-------------+---------+--------------+--------------+
$
If I run the same thing in a shell script:

$ cat foo.sh
#!/bin/sh
mysql -u foo -D dbname -e 'Select * from ADDRESS'
$
$ ./foo.sh
+----+-------------+---------+--------------+--------------+
| ID | DISPLAYNAME | OPTLOCK | IPADDRESS | BASEASSET_ID |
+----+-------------+---------+--------------+--------------+
| 1 | NULL | 0 | 172.50.0.100 | 1 |
+----+-------------+---------+--------------+--------------+
$
But if I modify the shell script ...

$ cat foo2.sh
#!/bin/sh
result=`mysql -u root -D dcos -e 'Select * from ADDRESS'`
echo $result
$
$ ./foo2.sh
ID DISPLAYNAME OPTLOCK IPADDRESS BASEASSET_ID 1 NULL 0 172.50.0.100 1
$

So it appears that the back-tick is stripping it out?
This is essentially the same thing I'm doing from a shell script:

$ cat foo.pl
#!/usr/bin/perl
use strict;

my $result = `mysql -u "foo" -D "dbname" -e 'SELECT * FROM ADDRESS'`;

print "$result\n";

$ ./foo.pl
ID DISPLAYNAME OPTLOCK IPADDRESS BASEASSET_ID
1 NULL 0 172.50.0.100 1

$

I assume that the backtick operator actually has the shell execute the command. I tried changing my $SHELL from /bin/bash to /bin/sh, but that had zero impact.

 

[TrojanWarBlade]

As Kirsle suggests, I suspect you will find that MySQL detects the controlling terminal (tty (or lack of)) and formats the output as it deems appropriate.

I have to ask though why you would try to use backticks to make calls to the database when you should probably be using something like DBI/DBD. This would allow you to return your data in a structured form (an array of columns or a hash) and can also allow you to minimise any SQL Injection attack risks with placeholders and other tools.


Trojan.

 

[mjoyner]

Hey Trojan!
What is more commonly used DBI or DBD? Are they about the same?


[root@netwatch ~]# yum remove windows
Loaded plugins: fastestmirror
Setting up Remove Process
No Match for argument: windows
No Packages marked for removal

OH YEAH!

 

[RBergin]

You need both.

DBI - Database independent interface for Perl

DBD::mysql - MySQL driver for the Perl5 Database Interface (DBI)

 

[mjoyner]

thanks

[root@netwatch ~]# yum remove windows
Loaded plugins: fastestmirror
Setting up Remove Process
No Match for argument: windows
No Packages marked for removal

OH YEAH!

 

[lit8rtot]

If the Perl DBI/DBD module came installed on all systems, I'd use it. However, my scripts have to run on a wide variety of systems, many of which I don't control what is installed. I've found that if the system has mysql installed, I have access to the mysql command line interface. Parsing the output of simple select statements into a structure is trivial - and fortunately, it appears that it is using tab as a field separator.