eimaan has asked for the wisdom of the Perl Monks concerning the following question:

Hey Im coding a perl script which needs to get data from a database. But when I get a return value its comes back in an array so i dereference it. Yet it still prints out nothing. I ran the command in sqlplus and it worked there without issue. Im not sure how to solve this

my $sth = $dbh->prepare("select XMLRECORD from F_COMPANY") or
            die "Couldnt prepare statement: " . $dbh->errstr;

$sth->execute();

# loop through the returned data 
while( my ($row) = $sth->fetchrow_array()){
   print "@$row\n";
}

[download]
OUTPUT (literally nothing):

Replies are listed 'Best First'.
Re: fetchrow_array returns an empty array
by choroba (Cardinal) on Jun 28, 2022 at 22:44 UTC
    If you fetch an array , populate an array.

    while (my @row = $sth->fetchrow_array) {
    print "@row\n";
}

    [download]

    map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
[reply]
[d/l]
[select]

      Hey thank for the reply, when i do it like that it outputs as a bunch of addresses "ARRAY(0x...)". I tried to deref each entry and they all still come back as empty

[reply]
        Could you share the schema of the F_COMPANY table? Also, what database engine do you use?

        map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
[reply]
[d/l]
Re: fetchrow_array returns an empty array
by ait (Hermit) on Jun 29, 2022 at 18:07 UTC

    Use $sth->fetchrow_hashref instead. You can also use $sth->fetchrow_hashref($name) with $name being "NAME_lc" or "NAME_uc" so that column names in the hash can be predictably lower case or uppercase respectively.

    Using hash references for data structures is much easier than using array references, since you don't need to keep track of the order of your columns and your code will be much more maintainable.

    Also, don't slurp your query results into your program RAM. The server already manages the result set for you, so don't use up your program RAM to replicate the RDBMS result set.
    Generally you want to iterate the result set with something like: 

    while(my $row = $sth->fetchrow_hashref()){
  # evaluate $row->{column} here or 
  # push any interesting $row to a local @array 
  # (e.g. push @myarray, $row)
}

    [download]
[reply]
[d/l]
Re: fetchrow_array returns an empty array
by Marshall (Canon) on Jun 29, 2022 at 18:28 UTC
    I am not sure that your SQL works without knowing the details of what XMLRECORD and F_COMPANY mean.

    What does this code print?

    use strict;
use warnings;
use Data::Dumper;

#.....

my $sth = $dbh->prepare("select XMLRECORD from F_COMPANY") or
            die "Couldnt prepare statement: " . $dbh->errstr;

$sth->execute();

my $array_ref =  $sth->fetchall_arrayref();

print Dumper $array_ref;

    [download]
    It could be that @$array_ref contains no elements. That is completely legal. If you do have a non-zero array, then Dumper should be able to print it out. then we can work on your code to dereference the bits that you want. Update: and work on making this more memory efficient (if necessary).
[reply]
[d/l]

Back to Seekers of Perl Wisdom