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:

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:

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:

#!/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

Take a look here:
perlform - perldoc.perl.org

1 Like

Hi.

$ 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:

$ 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:

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

1 Like

Just reinvented the wheel :cool: Hope it helps!

#!/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:

$ ./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):

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> 

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:

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.

Doh! :eek: I had the word "condescend" in the clipboard and somehow it got pasted in the code :o Sorry.

Just remove the word condescend from the code and all should be fine :b:

1 Like

Brilliant now works!

Thank you all for your assistance, much appreciated.