PERL: DBI - Is it possible to get a "nicer" formatted return?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting PERL: DBI - Is it possible to get a "nicer" formatted return?
# 1  
Old 11-10-2014
PERL: DBI - Is it possible to get a "nicer" formatted return?

Hi,

I am currently writing a perl module that will be passed queries from other scripts and use DBI to execute them on an Oracle Database. The problem I have is when it comes to the return.

I am currently getting this from my code:

Code:
FIELDA       FIELDB        FIELDC                 FIELDD               FIELDE
 
AAAAAAAA        MASTER  0       0       0       
AAAAAAAA        ABCD    0       0       0       
BBBBBBBA        DEFG    0       0       0       
BBBBBBBA        ABCDEF01        0       0       0       
CCCCCCCA        ABCDEF02        0       0       0       
CCCCCCC1        MASTER  0       0       0

Where as if I use an sql session and perform the query I get this format:

Code:
FIELDA       FIELDB   FIELDC             FIELDD             FIELDE
----------- -------- ---------------- --------------- -----------------
AAAAAAAA    MASTER                  0               0                 0
AAAAAAAA    ABCD                    0               0                 0
BBBBBBBA    DEFG                    0               0                 0
BBBBBBBA    ABCDEF01                0               0                 0
CCCCCCCA    ABCDEF02                0               0                 0
CCCCCCC1    MASTER                  0               0                 0
SBOSGBKD    AGRANT                  0               0                 0

This is my code:

Code:
#!/usr/local/bin/perl -w
use strict;
use Sys::Syslog; # for syslog
use DBI; # for databse connectivity
package UH_DBI;
sub run_query
{
##my $user = shift;
##my $password = shift;
##my $query = shift;
##my $host = shift;
##my $sid = shift;
##my $port = shift;
my @row;
my $user = "auser";
my $password = "apass";
my $query = qq (
SELECT FIELDA, FIELDB, FIELDC, FIELDD, FIELDE 
FROM ATABLE
);
my $host ="hostname";
my $sid ="TESTDB";
my $port ="9999";
 
my $data_source = "DBI:Oracle:host=$host;sid=$sid;port=$port";
my $dbh = DBI->connect($data_source, $user, $password) or die "Couldn't connect to database: " . DBI->errstr;
 
my $sth = $dbh->prepare($query)
or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute() or die "Couldn't execute statement: " . $sth->errstr;
 
open(FH, ">output_file") or 
die "\n cannot write to the file output_file. Check the file permissions";
my $fields = join("\t", @{ $sth->{NAME_uc} });
print FH "$fields\n\n";
while (my @next_row_fields = $sth->fetchrow_array) {
foreach my $next_field (@next_row_fields) {
if (defined $next_field) {
print FH $next_field;
}
else {
print FH "NULL";
}
print FH "\t";
}
print FH "\n";
}
close FH;
$sth->finish;
$dbh->disconnect;
}
run_query();

(Please excuse the lack of indentation, I copied into wordpad and then into the browser).

Can anyone help?

Thanks

Chris
# 2  
Old 11-10-2014
Take a look here:
perlform - perldoc.perl.org
This User Gave Thanks to bartus11 For This Post:
# 3  
Old 11-10-2014
Automatic alignment code: perl align

Hi.
Code:
$ align z3
FIELDA   FIELDB   FIELDC FIELDD FIELDE

AAAAAAAA MASTER   0      0      0
AAAAAAAA ABCD     0      0      0
BBBBBBBA DEFG     0      0      0
BBBBBBBA ABCDEF01 0      0      0
CCCCCCCA ABCDEF02 0      0      0
CCCCCCC1 MASTER   0      0      0

for more separation we increase the gutter:
Code:
$ align -g 4 z3
FIELDA      FIELDB      FIELDC    FIELDD    FIELDE

AAAAAAAA    MASTER      0         0         0
AAAAAAAA    ABCD        0         0         0
BBBBBBBA    DEFG        0         0         0
BBBBBBBA    ABCDEF01    0         0         0
CCCCCCCA    ABCDEF02    0         0         0
CCCCCCC1    MASTER      0         0         0

The perl code for align can be obtained at align

This was done on a system:
Code:
OS, ker|rel, machine: Linux, 2.6.26-2-amd64, x86_64
Distribution        : Debian 5.0.8 (lenny, workstation) 
align 1.7.0

Best wishes ... cheers, drl
This User Gave Thanks to drl For This Post:
# 4  
Old 11-11-2014
Oracle

Just reinvented the wheel Smilie Hope it helps!
Code:
#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $count = 0;
my $cfmt; #field format/alignment
my @harr; #array to store hypens
my %hash1; #hash to store fieldnumber <-> fieldtypename pairs
my %hash2; #hash to store fieldnumber <-> fieldlength pairs

my $dbh = DBI->connect("dbi:Oracle://orasrv1:1521/orcl",'scott','tiger');

    my $SQL = "SELECT * FROM EMP";
        my $sth = $dbh->prepare($SQL);

        $sth->execute;

    my $nof = $sth->{NUM_OF_FIELDS};

    for ( my $i = 0; $i < $nof; $i++ ) {
        my $name = $sth->{NAME}[$i];
        my $type = $sth->{TYPE}[$i];
        my $tn=$dbh->type_info($type)->{TYPE_NAME};
        my $prec = $sth->{PRECISION}[$i];

# special treatment for field type DATE, because it returns no field length
    if ($tn eq "DATE") {
        $prec = 10;
    } else {
        $prec = length($name) if (length($name) > $prec);
    }

    $hash1{"$i"} = "$tn";
    $hash2{"$i"} = "$prec";

    # print header, all column names left aligned
        $cfmt = "%-".$prec."s";
        printf("$cfmt ", $name);
        push @harr, '-'x$prec;
    }
    print "\n";

    # print line with hypens under the header
    print join(" ", @harr) . "\n";

 while ( my @row = $sth->fetchrow_array() ) {
    foreach (@row) {
        $_ = "NULL" if !defined($_);

# left align if field type is VARCHAR2, else right align
    my $ftyp = $hash1{"$count"};
    my $flen = $hash2{"$count"};

        if ($ftyp eq "VARCHAR2") {
            $cfmt = "%-".$flen."s";
        } else {
            $cfmt = "%".$flen."s";
        }

        printf("$cfmt ", $_);
    $count++;
    }
    print "\n";
    $count=0;
}

$dbh->disconnect if defined($dbh);

Demo:
Code:
$ ./testDBD.pl
EMPNO ENAME      JOB       MGR  HIREDATE   SAL     COMM    DEPTNO 
----- ---------- --------- ---- ---------- ------- ------- ------
 7369 SMITH      CLERK     7902  17-DEC-80     800    NULL     20 
 7499 ALLEN      SALESMAN  7698  20-FEB-81    1600     300     30 
 7521 WARD       SALESMAN  7698  22-FEB-81    1250     500     30 
 7566 JONES      MANAGER   7839  02-APR-81    2975    NULL     20 
 7654 MARTIN     SALESMAN  7698  28-SEP-81    1250    1400     30 
 7698 BLAKE      MANAGER   7839  01-MAY-81    2850    NULL     30 
 7782 CLARK      MANAGER   7839  09-JUN-81    2450    NULL     10 
 7788 SCOTT      ANALYST   7566  19-APR-87    3000    NULL     20 
 7839 KING       PRESIDENT NULL  17-NOV-81    5000    NULL     10 
 7844 TURNER     SALESMAN  7698  08-SEP-81    1500       0     30 
 7876 ADAMS      CLERK     7788  23-MAY-87    1100    NULL     20 
 7900 JAMES      CLERK     7698  03-DEC-81     950    NULL     30 
 7902 FORD       ANALYST   7566  03-DEC-81    3000    NULL     20 
 7934 MILLER     CLERK     7782  23-JAN-82    1300    NULL     10 
$

Original output (messed up during copy/paste process):
Code:
SQL> select * from emp;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
      7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
      7566 JONES      MANAGER          7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
      7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
      7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
      7900 JAMES      CLERK          7698 03-DEC-81        950            30
      7902 FORD       ANALYST          7566 03-DEC-81       3000            20
      7934 MILLER     CLERK          7782 23-JAN-82       1300            10

14 rows selected.

SQL>


Last edited by junior-helper; 11-11-2014 at 09:06 PM.. Reason: removed the word "condescend" from the code
# 5  
Old 11-11-2014
First of all, thank Bartus.

DRL - useful perl script, I'm going to see if I can turn it into a package.

junior helper - When I ran your code in a test script I got the following complilation errors:

Code:
syntax error at test_DBI.pl line 63, near "condescend
# left align if field type is VARCHAR2, else right align
    my "
Global symbol "$ftyp" requires explicit package name at test_DBI.pl line 63.
Global symbol "$ftyp" requires explicit package name at test_DBI.pl line 66.
syntax error at test_DBI.pl line 77, near "}"
test_DBI.pl had compilation errors.

# 6  
Old 11-11-2014
Doh! Smilie I had the word "condescend" in the clipboard and somehow it got pasted in the code Smilie Sorry.

Just remove the word condescend from the code and all should be fine Smilie
This User Gave Thanks to junior-helper For This Post:
# 7  
Old 11-12-2014
Brilliant now works!

Thank you all for your assistance, much appreciated.
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. AIX

Apache 2.4 directory cannot display "Last modified" "Size" "Description"

Hi 2 all, i have had AIX 7.2 :/# /usr/IBMAHS/bin/apachectl -v Server version: Apache/2.4.12 (Unix) Server built: May 25 2015 04:58:27 :/#:/# /usr/IBMAHS/bin/apachectl -M Loaded Modules: core_module (static) so_module (static) http_module (static) mpm_worker_module (static) ... (3 Replies)
Discussion started by: penchev
3 Replies

2. Shell Programming and Scripting

Bash script - Print an ascii file using specific font "Latin Modern Mono 12" "regular" "9"

Hello. System : opensuse leap 42.3 I have a bash script that build a text file. I would like the last command doing : print_cmd -o page-left=43 -o page-right=22 -o page-top=28 -o page-bottom=43 -o font=LatinModernMono12:regular:9 some_file.txt where : print_cmd ::= some printing... (1 Reply)
Discussion started by: jcdole
1 Replies

3. UNIX for Dummies Questions & Answers

Using "mailx" command to read "to" and "cc" email addreses from input file

How to use "mailx" command to do e-mail reading the input file containing email address, where column 1 has name and column 2 containing “To” e-mail address and column 3 contains “cc” e-mail address to include with same email. Sample input file, email.txt Below is an sample code where... (2 Replies)
Discussion started by: asjaiswal
2 Replies

4. Shell Programming and Scripting

Perl failure with "main::$fn" used only once:" in error logs

Hi all, Can anyone guess why this is happening? I am new to Perl, so please help me to fix this error: - I have a static html file which calls the cgi-perl script. HTML Code: <html> <head> <title> Hey Dude! </title> </head> <body> <form method="POST"... (3 Replies)
Discussion started by: bashily
3 Replies

5. Shell Programming and Scripting

awk command to replace ";" with "|" and ""|" at diferent places in line of file

Hi, I have line in input file as below: 3G_CENTRAL;INDONESIA_(M)_TELKOMSEL;SPECIAL_WORLD_GRP_7_FA_2_TELKOMSEL My expected output for line in the file must be : "1-Radon1-cMOC_deg"|"LDIndex"|"3G_CENTRAL|INDONESIA_(M)_TELKOMSEL"|LAST|"SPECIAL_WORLD_GRP_7_FA_2_TELKOMSEL" Can someone... (7 Replies)
Discussion started by: shis100
7 Replies

6. Shell Programming and Scripting

perl folder list with "..", without ".".

Hi Everyone, if my folder "foldera" inside has one file. so if i do if ($df =~ /^\./) { next; } then i will get ### filea ### if i want to have ### .. filea ### means also display the parent .., how should i modify the perl ~// in my code? Thanks ---------- Post updated... (6 Replies)
Discussion started by: jimmy_y
6 Replies

7. Shell Programming and Scripting

Perl - How to print a "carriage return" to an output file?

Let's say I want to write a program that run these 4 UNIX commands and redirect output to a file. #!/usr/local/bin/perl use strict; `cd \$HOME > output.txt`; `cut -f1 inputfile.txt >> output.txt`; `hostname >> output.txt`; `ifconfig >> output.txt`; I want to print a "carriage return"... (5 Replies)
Discussion started by: teiji
5 Replies

8. UNIX for Dummies Questions & Answers

Explain the line "mn_code=`env|grep "..mn"|awk -F"=" '{print $2}'`"

Hi Friends, Can any of you explain me about the below line of code? mn_code=`env|grep "..mn"|awk -F"=" '{print $2}'` Im not able to understand, what exactly it is doing :confused: Any help would be useful for me. Lokesha (4 Replies)
Discussion started by: Lokesha
4 Replies

9. Shell Programming and Scripting

perl "system" cmd return values..

perl 5.6.1: when i try a "system" command(with if loops for $?), i get this: child exited with value 1 what is meant by this $? values and what does it meant if it returns 1?.. (0 Replies)
Discussion started by: sekar sundaram
0 Replies
Login or Register to Ask a Question

Featured Tech Videos