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

I know somewhere in this code is a stupid mistake, but I just can't find it, so, I'm looking for another pair of eyes. Please help! The error message I'm getting on the following code is:
Transaction aborted because DBD::mysql::st fetchrow_arrayref failed: fetch() without execute() at info.pl line 239.
please look this over and tel me where I blew it. Thank you! 
foreach my $col(@Rcols){
push(@AllCols,$col);
}

my $table='foo';
my $fields=join(",",@Rcols);
my($sth);

my $sql = qq{SELECT $fields FROM $table Where workorder_id=?};

$sth = $dbhSt->prepare_cached($sql);
foreach my $rec (sort(keys(%workOrder))){
@workOrders='';
@workOrders=@{$workOrder{$rec}};
@AllKeys=sort(keys(%patient));
@theseIDs=sort grep(/^$rec/,@AllKeys);

foreach my $wo(sort @workOrders){
if($wo eq ''){next;}
else{

$sth->execute($wo);

my %row =();
$sth->bind_columns(map {\$row{$_}} @Rcols);
while (@values=$sth->fetchrow_arrayref){
@values='';

foreach my $col(@Rcols){

my $val= $row{$col};
if($val eq ''){
$val='NULL';
}
push(@values,$val);
}}
@AllValues='';

$k=shift(@theseIDs);
@AllValues=@{$patient{$k}};
push(@AllValues,@values);
$patient{"$k"}=[@AllValues];

}
}
}
$sth->finish;

[download]

Replies are listed 'Best First'.
Re: stupid mistake in DBI code
by Fletch (Bishop) on Apr 26, 2011 at 16:22 UTC

    If you actually indented sensibly you might be able to follow the flow of your code better. Get thee to a Perl::Tidy-ery.

    The cake is a lie.
    The cake is a lie.
    The cake is a lie.

[reply]
      I misdiagnosed his problem because of the lack of indentation. On a side note, I found vim does a great job of indenting code for you. 
      1) :set filetype=perl
2) :filetype indent on
3) :e
4) gg=G

      [download]
[reply]
[d/l]
        Thanks folks, I appreciate you taking the time. I found the error; I'm emptying @values right after i fill it. Blessings; Cliff
[reply]
        This is also easy to do in Vi or Vim:
        :%!perltidy

        [download]
        You could even create a shortcut to make it even easier :-)
[reply]
[d/l]
Re: stupid mistake in DBI code
by wind (Priest) on Apr 26, 2011 at 17:30 UTC

    You pull your values and immediately clear the array

    my %row =();
$sth->bind_columns(map {\$row{$_}} @Rcols);
while (@values=$sth->fetchrow_arrayref) {
    @values = '';

    [download]

    Also note, your bind_columns statement can be simplified a little by taking the reference of a hash slice

    $sth->bind_columns(\@row{@Rcols});

    [download]

    Update: Nevermind, appears you figured it out above

[reply]
[d/l]
[select]
Re: stupid mistake in DBI code
by fidesachates (Monk) on Apr 26, 2011 at 16:13 UTC
    If I had to guess, you're not executing the execute command in your else clause. Looking at it, you're going through an array and as long as the element in the array does not equal '', you're executing. However, what if the array never holds anything? The array is being based off of 
    foreach my $rec (sort(keys(%workOrder)) 
{
        @workOrders=@{$workOrder{$rec}};
}

    [download]
    Your code doesn't assign any value to the hash workOrder so I imagine the array workOrders is also empty. Do a print before your execute to see if the code ever enters the else block.

    Update:
    Standard things: use strictures, use warnings, and use more distinct variable names. I don't think I am correct about your problem.
    Check the DBI->connect() and $dbhST->prepare() succeeded
[reply]
[d/l]
Re: stupid mistake in DBI code
by runrig (Abbot) on Apr 26, 2011 at 19:09 UTC 
    while (@values=$sth->fetchrow_arrayref){
@values='';

    [download]
    You are fetching a arrayref into an array? (and then clearing the array, but I see you've figured that part out). And I agree with others that you should get some Perl-Tidy.
[reply]
[d/l]
      you folks are great! Thank you so much for your time and attention
[reply]

Back to Seekers of Perl Wisdom