in reply to DBI fetchall_arrayref

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."

Replies are listed 'Best First'.
Re^2: DBI fetchall_arrayref
by kevind0718 (Scribe) on Dec 17, 2011 at 01:52 UTC
    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]
Re^2: DBI fetchall_arrayref
by kevind0718 (Scribe) on Dec 17, 2011 at 01:24 UTC

    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]

In Section Seekers of Perl Wisdom