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

hello kind monks:

I have a bit of code that needs retrieve an integer from the results a Postgresql function. I am having difficulty getting at the integer returned. How am I supposed to get at the value returned?

many thanks for you attention to this matter.

KD 
        
    $sqlStr =  "select createwbparserun( '$directory' , '$filename'  )
+ ";
    print $sqlStr.  "\n" ;
    my $sth = $db_in->prepare( $sqlStr );
    $sth->execute();
    
    my @aRow = $sth->fetchall_arrayref([0]);
    print Dumper @aRow;
    my @ta = $aRow[0];
    print Dumper @ta;
    print " the array: " . $ta[0][0]  . "\n" ;
    ## $wb_runkey = $aRow[0];
    
    print "WORKBOOK run key: " . $wb_runkey . "\n" ;

[download]
which returns the following: 
select createwbparserun( 'C:/dev/HN_optimum/' , 'FM Mk 14 - 60M200007_
+DB200609.xls'  )
$VAR1 = [
          [
            '45'
          ]
        ];
$VAR1 = [
          [
            '45'
          ]
        ];
 the array: ARRAY(0xcec7960)
WORKBOOK run key: 0
Died at parseExcelWB.pl line 197.

[download]

Replies are listed 'Best First'.
Re: DBI fetchall_arrayref
by NetWallah (Canon) on Dec 17, 2011 at 00:51 UTC
    You are doing a "fetchall_arrayref".

    What you get back is a SCALAR, not an array. (You happen to store this scalar in an array, complicating things)

    Try de-referencing and extracting the contents of that scalar.

                "XML is like violence: if it doesn't solve your problem, use more."

[reply]
      OK the code below works. the de-referencing is tricky

        $sqlStr =  "select createwbparserun( '$directory' , '$filename'  ) "
+;
    print $sqlStr.  "\n" ;
    my $sth = $db_in->prepare( $sqlStr );
    $sth->execute();
    
    my $aryRef = $sth->fetchall_arrayref([0]);
    my $a =  $ {$aryRef }[0];
    print "Reference: $aryRef\n";
    print "Dereferenced: $a\n";
    print "Deref again: ${$a}[0]  \n";   
    $wb_runkey = ${$a}[0];
    
    print  $aryRef->[0]->[0] . "\n";
    ##print Dumper @a;
    ##$wb_runkey = $a[0];
        
    print "WORKBOOK run key: " . $wb_runkey . "\n" ;

      [download]
      it produces the following:

      select createwbparserun( 'C:/dev/HN_optimum/' , 'FM Mk 14 - 60M200007_
+DB200609.xls'  )
Reference: ARRAY(0x34dfed8)
Dereferenced: ARRAY(0xd5079a8)
Deref again: 55
55
WORKBOOK run key: 55
Died at parseExcelWB.pl line 198.

      [download]
      Here is a link the explains the de-referencing: http://www.thegeekstuff.com/2010/06/perl-array-reference-examples/

      thanks for your kind assistance.

      KD
[reply]
[d/l]
[select]
        Your call to fetchall_arrayref returns ALL ROWS.

        So, your first dereference gets you an array of Rows (Your array has only one element).

        Now, you need to dereference again to get to the value stored within column(0) of that row.

        You can accomplish all this in a single step: 

         
 my $wb_runkey = $aryRef->[0]->[0];
 # Can also be written as :
 #     $aryRef->[0][0];  # Arrow in between is implied
 #     ${$aryRef}[0][0]; # Look ma - no arrows (But this is uglier, IM
+HO)

        [download]
        Hopefully, this exercise dipped your toe into dereferencing - an extremely powerful construct, for advancing with perl.

                    "XML is like violence: if it doesn't solve your problem, use more."

[reply]
[d/l]
        Don't really need to fetchall if you're only accessing the first record. And you're probably going to find a hash a lot simpler to work with in most circumstances. What do you get if you do $sth->fetchrow_hashref() ?
[reply]

      OK I am beyond confused.

      Changed my code as follows:

          $sqlStr =  "select createwbparserun( '$directory' , '$filename'  )
+ ";
    print $sqlStr.  "\n" ;
    my $sth = $db_in->prepare( $sqlStr );
    $sth->execute();
    
    my $aryRef = $sth->fetchall_arrayref([0]);
    my @a =  @$aryRef;
    print "Reference: $aryRef\n";
    print "Dereferenced: @a\n";
       
    
    ##print  @$ary;
    print Dumper @a;
    $wb_runkey = $a[0];
        
    print "WORKBOOK run key: " . $wb_runkey . "\n" ;
    
die;

      [download]
      and got the following: 
      select createwbparserun( 'C:/dev/HN_optimum/' , 'FM Mk 14 - 60M200007_
+DB200609.xls'  )
Reference: ARRAY(0x351fed8)
Dereferenced: ARRAY(0xcf786a0)
$VAR1 = [
          '48'
        ];
WORKBOOK run key: ARRAY(0xcf786a0)
Died at parseExcelWB.pl line 197.

      [download]

      the DBI documentation says, fetchall_arrayref returns a reference to an array that contains one reference per row. Does this mean I have to de-reference again?? Do not know how to do that.

      Best

      KD
[reply]
[d/l]
[select]
Re: DBI fetchall_arrayref
by tobyink (Canon) on Dec 17, 2011 at 13:18 UTC

    If you're fetching into an array, use fetchrow_array.

    $sqlStr = "select createwbparserun('$directory','$filename')";
print "$sqlStr\n";

my $sth = $db_in->prepare($sqlStr);
$sth->execute();

if (my @aRow = $sth->fetchrow_array)
{
  print Dumper \@aRow;
  $wb_runkey = $aRow[0];
  print "WORKBOOK run key: " . $wb_runkey . "\n";
}

    [download]

    A similar example using fetchrow_arrayref would be:

    #... same preamble as before ...

if (my $aRow = $sth->fetchrow_arrayref)
{
  print Dumper $aRow;
  $wb_runkey = $aRow->[0];
  print "WORKBOOK run key: " . $wb_runkey . "\n";
}

    [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom