#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    2
    Rep Power
    0

    fetchrow_arrayref failed: fetch() without execute()


    Hello.

    Working on Linux+Perl, I must perform around 100 UPDATEs in a
    MySQL table. It works, but I get continuously this message:

    DBD::mysql::st fetchrow_arrayref failed: fetch() without execute() at
    /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/Mysql/Statement.pm
    line 14, <F> line 96.

    Does anybody have any experience? Thank you very much.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jul 2003
    Location
    Prague, Czech Republic
    Posts
    869
    Rep Power
    71
    I usualy use DBD::mysql as opposed to DBD::mysql::st.

    Then eg.:
    my $sth=$dbh->prepare(qq{update table set f_col=?,sec_col=? where id=?});
    $sth->execute or die $dbh->errstr;

    while(<STDIN>) {
    chomp;
    my($f_col,$s_col,$id=split(/,/);
    $sth->execute($f_col,$s_col,$id);
    }

    $sth->finish if $sth;
    $dbh->disconnect if $dbh;

    and this works fine to me.
    Helpful?

    It also occures to me that you might investigate your data file for it runs good all the way down to line 96 and then it gets stuck (fails to proceed). I suspect the data on this line to be somehow lame OR:
    Also check for the real existance of the newly updated data in the database. You say you update AROUND 100 rows (it may correspod to the number of lines in your data source, I suppose). What if the data source ends up by the line 96 and the script is just complaining about no escape char?
    Last edited by zby; September 12th, 2003 at 04:00 AM.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    2
    Rep Power
    0
    Thank you for answering.

    1) I use:
    use Mysql;
    (don't kow why DBD::mysql::st messages are shown)

    2) These three are executed about 100 times ('while'):
    -------//-------------
    $sql_query3 = "UPDATE my_table SET id='$id_field', name='$name_field', text='$text_field' WHERE id='$i'";
    $query3 = $DB->query($sql_query3);
    @array3 = $query3->fetchrow;
    #There are no more SQL commands here
    -------------//--------------

    3) The error message appears in each UPDATE I perform. I only posted the 96th.

    Regrds.
  6. #4
  7. 11
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Jul 2001
    Location
    Lynn, MA
    Posts
    4,635
    Rep Power
    83
    Without seeing your code, it's impossible to diagnose.

    zby- what's this?
    Code:
    my $sth=$dbh->prepare(qq{update table set f_col=?,sec_col=? where id=?});
    $sth->execute or die $dbh->errstr;
    Why no binding variables? Wouldn't this insert an empty record?

    Check out the RaiseError option to the DBI constructor- it saves you from doing all the "or die()" stuff because it automatically status checks all DBI methods for you. . .
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jul 2003
    Location
    Prague, Czech Republic
    Posts
    869
    Rep Power
    71

    Unhappy


    Guess I put the 'eg.' there, didn't I?

    I'm not trying to make somebody blindly use this code?
    This construction occures (a bit different) in perldoc as well... And that is ment as an example, too. Of course your objection is correct and raised the right protest.

    Feeling kinda guilty for posting such a kode...
  10. #6
  11. Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Moscow, Russia
    Posts
    22
    Rep Power
    0
    lanbide,

    First of all to avoid the confusion, DBD::mysql::st is an internal package that represents a statement in DBI. It is used by DBI itself and is loaded automatically by DBI itself.

    $sql_query3 = "UPDATE my_table SET id='$id_field', name='$name_field', text='$text_field' WHERE id='$i'";
    $query3 = $DB->query($sql_query3);
    @array3 = $query3->fetchrow;
    #There are no more SQL commands here
    I don't quite understand two things:

    1. What is "$DB->query($sql_query3);". There is no method called "query" in DBI. What is contained in variable $DB?

    2. Why you try to retreive a row after making an update? There is nothing to retrieve after you make an update. Fetching a row only makes sense after you will issue a select statement, not update.

    And anyway you should be able to do an update with this piece of code (as long as you have DBI's database handler object inside $DB):

    Code:
    $DB->do("UPDATE my_table SET id='$id_field', name='$name_field', text='$text_field' WHERE id='$i'")
  12. #7
  13. 11
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Jul 2001
    Location
    Lynn, MA
    Posts
    4,635
    Rep Power
    83
    Though you should be using placeholders if you want to be creating stable (and potentially more efficient) code.

    Search this forum for "DBI placeholders"

IMN logo majestic logo threadwatch logo seochat tools logo